How to catch slick postgres exceptions for duplicate key value violations

12
votes

My table has a unique index on a pair of columns in my postgresql database.

I want to know how I can catch a duplicate key exception when I am inserting:

def save(user: User)(implicit session: Session): User = {
  val newId = (users returning users.map(_id) += user
  user.copy(id = newId)
}

My logs show this exception:

Execution exception[[PSQLException: ERROR: duplicate key value violates unique constraint "...."

I haven't really used exceptions much in scala either yet also.

2
scalaplayframeworkslick

2 Answers

27
votes

Your save method should probably return something different than just User, to indicate the possibility of failure. If the only exception that will be thrown is by unique key, and you really only care about success or failure (and not the type of failure), one way to go would be to return Option[User].

You could use a simple try/catch block, mapping successful saves to Some[User] and PSQLException to None:

def save(user: User)(implicit session: Session): Option[User] = {
  try {
    val newId = (users returning users.map(_id) += user
    Some(user.copy(id = newId))
  } catch {
      case PSQLException => None
  }
}

Personally not the way I'd go, as try/catch isn't really idiomatic Scala, and your error type is discarded. The next option is to use scala.util.Try.

def save(user: User)(implicit session: Session): Try[User] = Try {
  val newId = (users returning users.map(_id) += user
  user.copy(id = newId)
}

The code here is simpler. If the body of Try is successful, then save will return Success[User], and if not it will return the exception wrapped in Failure. This will allow you to do many things with Try.

You could pattern match:

save(user) match {
   case Success(user) => Ok(user)
   case Failure(t: PSQLException) if(e.getSQLState == "23505") => InternalServerError("Some sort of unique key violation..")
   case Failure(t: PSQLException) => InternalServerError("Some sort of psql error..")
   case Failure(_) => InternalServerError("Something else happened.. it was bad..")
}

You could use it like Option:

save(user) map { user =>
   Ok(user)
} getOrElse {
   InternalServerError("Something terrible happened..")
}

You can compose many together at once, and stop on the first failure:

(for {
   u1 <- save(user1)
   u2 <- save(user2)
   u3 <- save(user3)
} yield {
  (u1, u2, u3)
}) match {
   case Success((u1, u2, u3)) => Ok(...)
   case Failure(...) => ...
}
16
votes

In Slick 3.x you can use asTry.

I'm using MySQL, but the same code can be used for PostgreSQL, only the exceptions are different.

import scala.util.Try
import scala.util.Success
import scala.util.Failure

db.run(myAction.asTry).map {result =>  

  result match {

    case Success(res) => 
      println("success")
      // ...

    case Failure(e: MySQLIntegrityConstraintViolationException) => {
      //code: 1062, status: 23000, e: Duplicate entry 'foo' for key 'name'
      println(s"MySQLIntegrityConstraintViolationException, code: ${e.getErrorCode}, sql status: ${e.getSQLState}, message: ${e.getMessage}")
      // ...
    }

    case Failure(e) => {
      println(s"Exception in insertOrUpdateListItem, ${e.getMessage}")
      // ...
    }
  }
}

Note: It's also possible to map the action (myAction.asTry.map ...) instead of the future returned by db.run.