How to catch slick postgres exceptions for duplicate key value violations

Blankman picture Blankman · Nov 22, 2014 · Viewed 10.2k times · Source

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.

Answer

Michael Zajac picture Michael Zajac · Nov 22, 2014

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(...) => ...
}