SqlErrorHandling.scala

import java.sql.SQLException

import cats.MonadError
import cats.implicits._

trait SqlStateMapping {

  val constraintViolation: String
  val foreignKeyViolation: String
  val uniqueViolation: String
  val failedTransaction: String

}

/**
 * See https://www.postgresql.org/docs/12/errcodes-appendix.html for full list.
 */
object PostgresSqlStateMapping extends SqlStateMapping {

  override val constraintViolation: String = "23000"
  override val foreignKeyViolation: String = "23503"
  override val uniqueViolation: String = "23505"
  override val failedTransaction: String = "25P02"

}

class SqlErrorHandling(sqlStateMapping: SqlStateMapping) {

  def handleViolation[F[_], A](fa: F[A], sqlState: String)
                              (onViolation: => A, errorFragment: Option[String])
                              (implicit F: MonadError[F, Throwable]): F[A] =
    fa.recover {
      case ex: SQLException if ex.getSQLState === sqlState && errorFragment.forall(ex.getMessage contains _) =>
        onViolation
    }

  def handleConstraintViolation[F[_], A](fa: F[A])
                                        (onViolation: => A, constraintName: Option[String])
                                        (implicit F: MonadError[F, Throwable]): F[A] =
    handleViolation(fa, sqlStateMapping.constraintViolation)(onViolation, constraintName)

  def handleForeignKeyViolation[F[_], A](fa: F[A])
                                        (onViolation: => A, foreignKeyName: Option[String])
                                        (implicit F: MonadError[F, Throwable]): F[A] =
    handleViolation(fa, sqlStateMapping.foreignKeyViolation)(onViolation, foreignKeyName)

  def handleUniqueViolation[F[_], A](fa: F[A])
                                    (onViolation: => A, indexName: Option[String])
                                    (implicit F: MonadError[F, Throwable]): F[A] =
    handleViolation(fa, sqlStateMapping.uniqueViolation)(onViolation, indexName)

  object implicits {

    implicit class SqlErrorHandleSyntax[F[_], A](fa: F[A])
                                                (implicit F: MonadError[F, Throwable]) {

      def handleViolation(sqlState: String)
                         (onViolation: => A): F[A] =
        SqlErrorHandling.this.handleViolation(fa, sqlState)(onViolation, None)

      def handleConstraintViolation(onViolation: => A, constraintName: Option[String] = None): F[A] =
        SqlErrorHandling.this.handleConstraintViolation(fa)(onViolation, constraintName)

      def handleForeignKeyViolation(onViolation: => A, foreignKeyName: Option[String] = None): F[A] =
        SqlErrorHandling.this.handleForeignKeyViolation(fa)(onViolation, foreignKeyName)

      def handleUniqueViolation(onViolation: => A, indexName: Option[String] = None): F[A] =
        SqlErrorHandling.this.handleUniqueViolation(fa)(onViolation, indexName)

    }

  }

}