Useful Slick Implicits

I have used slick successfully in many projects. It offers some type safety while still allowing a great deal of control over the executed SQL code.

This control comes at a price, a big portion of slick code is repetitive and it often leads to APIs difficult to use and maintain.

While working on many different projects, we noticed a few common patterns that can be abstracted and reused across projects using slick.

The purpose of this post is not to say slick is better than any other orm-like framework, if you are interested in that subject read this Sofware Mill blob post.

During this blog post I assume you are fairly familiar with slick and it's APIs, so you might want to read slick documentation.

All examples here are open sourced and developed at Advanced Telematic Systems.

Example Schema

For the following examples I'll be using the following schema:

object Schema {
  case class Book(id: Int, title: String)

  class BooksTable(tag: Tag) extends Table[Book](tag, "books") {
    def id = column[Int]("id", O.PrimaryKey)
    def repoId = column[String]("title")

    override def * = (id, title) <> ((Book.apply _).tupled, Bok.unapply)
  }

  val books = TableQuery[BooksTable]
}

Handling Errors

Integrity Constraint Violations

Slick often returns JDBC exceptions when a query causes integrity constraint violations. These exceptions are cumbersome to handle and usually we would like to convert them to specific app level exceptions that can be handled by our business logic. This would mean writing code like this:

(books += book).asTry.flatMap {
  case Success(v) => DBIO.successful(v)
  case Failure(e: SQLIntegrityConstraintViolationException) =>
      DBIO.failed(BookAlreadyExistsError)
  case Failure(e) =>
      DBIO.failed(e)
}

With the help of DbioActionExtensions we can rewrite this as:

(books += book).handleIntegrityErrors(BookAlreadyExistsError)

implicit class DbioActionExtensions[T](action: DBIO[T]) {
  def handleIntegrityErrors(error: Throwable)(implicit ec: ExecutionContext): DBIO[T] = {
    action.asTry.flatMap {
      case Success(i) =>
        DBIO.successful(i)
      case Failure(e: SQLIntegrityConstraintViolationException) =>
        DBIO.failed(error)
      case Failure(e: BatchUpdateException) if e.getCause.isInstanceOf[SQLIntegrityConstraintViolationException] =>
        DBIO.failed(error)
      case Failure(e) =>
        DBIO.failed(e)
    }
  }
}

Handling unexpected query results

Handling Missing Rows

When building slick DBIO instances you often end up with DBIO[Option[T]], since the query can return an empty result set. But this is awkward to handle as you then need to pattern match on the DBIO result just to return an application level error:

val q = books.filter(_.title === "1984").result.headOption.flatMap {
  case Some(book) => DBIO.successful(book)
  case None => DBIO.failure(BookNotFound)
}

This can be re written if we have DBIOOptionOps in scope:

books.filter(_.title === "1984").result.failIfNone(BookNotFound)

implicit class DBIOOptionOps[T](io: DBIO[Option[T]]) {
  def failIfNone(t: Throwable)
                (implicit ec: ExecutionContext): DBIO[T] =
    io.flatMap(_.fold[DBIO[T]](DBIO.failed(t))(DBIO.successful))
}

Fetching Rows by Id

We often need to get a specific row of a table. You might have a primary key on the table, so you know there can only be one row for that key, but with slick there is no short way to find a row by some key. This means we need to write something like this:

books.filter(_id === id).result.headOption.flatMap {
    case Some(e) => DBIO.successful(e)
    case None => DBIO.failure(BookNotFound)
}

It would be good generalize this and be able to write:

val q = someTable.filter(_id === id).result.failIfNotSingle(BookNotFound)

This can be done with the following implicit class in scope.

implicit class DBIOSeqOps[+T](io: DBIO[Seq[T]]) {
  def failIfNotSingle(t: Throwable)(implicit ec: ExecutionContext): DBIO[T] =
    DBIOOptionOps {
      io.flatMap { result =>
        if (result.size > 1)
          DBIO.failed(Errors.TooManyElements)
        else
          DBIO.successful(result.headOption)
      }
    }.failIfNone(t)
}

This class also adds failIfEmpty which can be used to transform DBIO[Seq[T]] into a failure if the resulting Seq[T] is empty.

Updating Rows

It's common to run update statements on one column, but if the query is not written properly you might end up update more rows than you want to update, or not updating rows at all, and the query will still be successful.

books.filter(_.title === "1984").map(_.name).update("2017")

With DbioUpdateActionExtensions in scope you could write:

books.filter(_.title === "1984")
     .map(_.name)
     .update("2017")
     .handleSingleUpdateError(BookUpdateError)

    def handleSingleUpdateError(result: Throwable)(implicit ec: ExecutionContext): DBIO[Unit] = {
      action.flatMap {
        case c if c == 1 =>
          DBIO.successful(())
        case c if c == 0 =>
          DBIO.failed(result)
        case _ =>
          DBIO.failed(Errors.TooManyElements)
      }
    }

This does not prevent the app from updating the rows unless this is executed in a transaction, but it does return an error that you can investigate. It will also return an error if the row to be updated does not exist and no rows were updated.

Pagination

Pagination can be abstracted in slick using the implicits in libats:

books.paginate(offset = 10, limit = 100)

books.paginateAndSort(_.id, offset = 10, limit = 100)

Conclusion

Slick is very close to SQL, which can be very powerful and very repetitive at the same time. But as you can see slick can simply be extended using implicits.

In a future blog post I will write about abstracting Slick Mappings that can be used to store custom types like io.circe.Json, eu.timepit.Refined or your own BookId.

All examples in this blog post are available at libats.