I have been using Slick on and off since 2011, when it was called ScalaQuery, and it remains my favourite database abstraction library to this day.

We at DigitalGenius are in the process of migrating a codebase that made heavy use of Doobie to Slick. With Doobie, you end up throwing out type safety and doing stringly typed programming. Since Doobie queries are essentially strings, your best shot at abstraction is concatenating string fragments. Our tables at DG follow many common patterns in how they store and retrieve data, but with mere strings at your disposal, it is terribly error-prone and often impossible to abstract over those patterns. I have expressed my displeasure with this sort of thing before.

Slick is different. It uses Scala’s abstraction mechanisms, both term and type level, to faithfully represent the SQL layer while avoiding the failings of ORMs. This allows you to compose, abstract, and model things in a typeful fashion.

We have been delighted with the migration overall, but we have stumbled into some problems along the way: bugs, unimplemented features, and jarring innards. This post covers one of the more interesting patterns that came out of that work: a way to make Slick table updates composable without giving up type safety.

Before we begin, let’s get the setup out of the way.

Setup

We will be using PostgreSQL version 9.6, Scala version 2.12.6, Slick version 3.2.2, and Slick-PG version 0.16.0.

Start a PostgreSQL instance, and create the following table in your database.

CREATE TABLE "employees" (
    "id"         TEXT     PRIMARY KEY,
    "name"       TEXT     NOT NULL,
    "department" TEXT     NOT NULL,
    "age"        INTEGER  NOT NULL,
    "salary"     INTEGER  NOT NULL
);

We will be using the following skeletons for our Scala code. In subsequent code snippets, we will only pull out the relevant sections from them.

package dbinfra


object MyPostgresProfile {
  // Copy this from Slick-PG README
}


object SlickExtensions {
  import MyPostgresProfile.api._


  // ...
}
package usage


import dbinfra._
import MyPostgresProfile.api._


final case class Employee(
  id: String,
  name: String,
  department: String,
  age: Int,
  salary: Int
)


final class EmployeesTable(tag: Tag) extends Table[Employee](tag, "employees") {
  def id = column[String]("id")
  def name = column[String]("name")
  def department = column[String]("department")
  def age = column[Int]("age")
  def salary = column[Int]("salary")


  def * = (id, name, department, age, salary) <> (Employee.tupled, Employee.unapply)
}


object EmployeesTable extends TableQuery(new EmployeesTable(_))


final class EmployeeRepository {
  // ...
}

In a real codebase, you will probably use UUID for IDs; newtypes, not naked strings, to represent textual fields; and enums to represent departments, and so on. Since none of that is relevant to this post, we leave them as strings here. We will be taking more such shortcuts in the code snippets that follow to avoid losing focus.

We will be testing this code out at the REPL. The following prelude will be needed.

// Prelude for a REPL session
import MyPostgresProfile.api._
import SlickExtensions._
import dbinfra._
import usage._
import scala.concurrent.duration._
import scala.concurrent.Await
import scala.concurrent.ExecutionContext.Implicits.global


val db = Database.forDriver(new org.postgresql.Driver, "jdbc:postgresql://localhost:54321/db-name", "user-name", "password")


def runDB[A](action: DBIO[A]): A = Await.result(db.run(action), 1.minute)

Problem

Imagine you are implementing a PATCH request for a simple domain entity, something that maps to both a REST resource and an SQL representation almost as-is. In such a case, your repository layer might have an .update method that looks something like this:

final class EmployeeRepository {
  def update(
    id: String,
    name: Option[String],
    department: Option[String],
    age: Option[Int],
    salary: Option[Int],
  ): DBIO[Int] = {
    ???
  }
}

The id will be used for a lookup, and then the other fields will be set to the provided values, if Some, or left unaltered otherwise.

Non-solution

This is how you normally update a single field with Slick:

scala> runDB { EmployeesTable += Employee("x6", "Rahul", "engg", 28, 1000) }
res19: Int = 1


scala> runDB { EmployeesTable.filter(_.id === "x6").map(_.name).update("Luffy") }
res20: Int = 1


scala> runDB { EmployeesTable.filter(_.id === "x6").result }
res21: Seq[usage.EmployeesTable#TableElementType] = Vector(Employee(x6,Luffy,engg,28,1000))

And this is how you normally update multiple fields, all at once:

scala> runDB { EmployeesTable.filter(_.id === "x6").map(r => (r.name, r.department)).update(("Ruhi", "product")) }
res23: Int = 1


scala> runDB { EmployeesTable.filter(_.id === "x6").result }
res24: Seq[usage.EmployeesTable#TableElementType] = Vector(Employee(x6,Ruhi,product,28,1000))

If you want to update more than two fields, you can do it in one of the following two ways, and more still by defining custom Shapes.

scala> runDB { EmployeesTable.filter(_.id === "x6").map(r => (r.name, r.department, r.age)).update(("Ruhi", "product", 29)) }
res0: Int = 1


scala> runDB { EmployeesTable.filter(_.id === "x6").result }
res1: Seq[usage.EmployeesTable#TableElementType] = Vector(Employee(x6,Ruhi,product,29,2000))


scala> runDB { EmployeesTable.filter(_.id === "x6").map(r => (r.name, (r.department, r.age))).update(("Ruhi", ("product", 29))) }
res2: Int = 1


scala> runDB { EmployeesTable.filter(_.id === "x6").result }
res3: Seq[usage.EmployeesTable#TableElementType] = Vector(Employee(x6,Ruhi,product,29,2000))

The first approach is syntactically nicer, while the second one is more compositional and therefore easier to abstract over. Scala 3 might combine the two: the former syntax with the latter representation.

In our case, the updates are conditional on whether or not a new value is supplied. So we cannot pass them all at once as shown in the examples above, but must be able to stack them on one after another. In this respect, Slick update queries do not compose.

scala> runDB { EmployeesTable.filter(_.id === "x6").map(_.name).update("Luffy").map(_.department).update("engg") }
<console>:33: error: value department is not a member of Int
       runDB { EmployeesTable.filter(_.id === "x6").map(_.name).update("Luffy").map(_.department).update("engg") }
                                                                                      ^

This happens because when you invoke .update, Slick already turns your SQL Query into a DBIO, making further query modifications impossible.

One way to do what we want with the available machinery is to pattern-match over all possible combinations of given update values, as shown below.

scala> val id = "x6"
id: String = x6


scala> val name: Option[String] = Some("Ruhi")
name: Option[String] = Some(Ruhi)


scala> val department: Option[String] = None
department: Option[String] = None


scala> val age: Option[Int] = Some(27)
age: Option[Int] = Some(27)


scala> val salary: Option[Int] = Some(1500)
salary: Option[Int] = Some(1500)


scala> val employeeWithGivenId = EmployeesTable.filter(_.id === id)


scala> (name, department, age, salary) match {
     |   case (None, None, None, None)                   => employeeWithGivenId.result.map(_ => 0)
     |   case (Some(name), None, None, None)             => employeeWithGivenId.map(_.name).update(name)
     |   case (Some(name), Some(department), None, None) => employeeWithGivenId.map(e => (e.name, e.department)).update((name, department))
     |   // ...
     | }

The above “works”, but now we have 2number of fields = 24 = 16 branches. When we add an extra field, we will have to add 16 more branches.

Clearly this does not scale, and we need something better.

First stab at a solution

If updates in Slick were first-class values, we could produce them independently of their application. We could put them in a list, compose them with each other, and so on.

The easiest way to model this would be a data type that represents a delayed .map(field).update(newValue) application. We can navigate to Slick’s codebase and copy over the arguments of .map and .update. Let’s build this data type, and also add an extension method to Query to apply this update.

object SlickExtensions {
  import slick.dbio.{DBIOAction, Effect}
  import slick.jdbc.{PositionedParameters, PositionedResult}
  import slick.lifted.{BaseColumnExtensionMethods, CanBeQueryCondition, FlatShapeLevel, OptionMapper2, Shape}
  import slick.sql.{FixedSqlAction, SqlAction}


  final case class Update[Record, Field, Value](field: Record => Field, value: Value)


  implicit class RichQuery[Record, U, C[_]](val underlying: Query[Record, U, C]) {
    def applyUpdate[Field, Value](
      update: Update[Record, Field, Value]
    )(
      implicit
      shape: Shape[_ <: FlatShapeLevel, Field, Value, Field]
    ): FixedSqlAction[Int, NoStream, Effect.Write] = {
      underlying.map(update.field).update(update.value)
    }
  }
}

Let’s test this out.

scala> runDB { EmployeesTable.filter(_.id === "x6").applyUpdate(Update(_.name, "Monkey")) }
res5: Int = 1


scala> runDB { EmployeesTable.filter(_.id === "x6").result }
res6: Seq[usage.EmployeesTable#TableElementType] = Vector(Employee(x6,Monkey,product,28,1000))

It works!

Now we need a way to compose these updates. Let’s add a combinator, .and, to Update to enable that.

final case class Update[Record, Field, Value](field: Record => Field, value: Value) {
  def and[Field2, Value2](that: Update[Record, Field2, Value2]): Update[Record, (Field, Field2), (Value, Value2)] = {
    Update(record => (this.field(record), that.field(record)), (this.value, that.value))
  }
}

If, say, we had updates u1, u2, and u3, then by executing u1 and u2 and u3, we will get a composed update that represents updates on three different fields. The application of this update should work as long as Slick can figure out the needed Shape.

scala> val update = Update[EmployeesTable, Rep[String], String](_.name, "Pintya").
     |   and(Update[EmployeesTable, Rep[String], String](_.department, "management")).
     |   and(Update[EmployeesTable, Rep[Int], Int](_.salary, 2000))
update: utils.slick.SlickExtensions.Update[usage.EmployeesTable,((utils.slick.DgPostgreSqlProfile.api.Rep[String], utils.slick.DgPostgreSqlProfile.api.Rep[String]), utils.slick.DgPostgreSqlProfile.api.Rep[Int]),((String, String), Int)] = Update(utils.slick.SlickExtensions$Update$$Lambda$7649/987431776@3f774e9b,((Pintya,management),2000))


scala> runDB { EmployeesTable.filter(_.id === "x6").applyUpdate(update) }
res1: Int = 1


scala> runDB { EmployeesTable.filter(_.id === "x6").result }
res2: Seq[usage.EmployeesTable#TableElementType] = Vector(Employee(x6,Pintya,management,28,2000))

Woot! This works too.

Let’s now move on to optional updates. To make this work, we will need a way of representing “no update”, a null update object, if you will. We could revise our Update into a sum type like this:

sealed trait Update[Record, Field, Value] extends Product with Serializable


object Update {
  final case class Perform[Record, Field, Value](field: Record => Field, value: Value) extends Update[Record, Field, Value]
  final case class Pass[Record, Field, Value]() extends Update[Record, Field, Value]
}

But you will notice that defining .and on this revised Update type proves impossible. What do you do when you have a Perform[R, F1, V1] on one side, and Pass[R, F2, V2] on the other? You cannot possibly produce Update[R, (F1, F2), (V1, V2)] that performs the update on field F1.

We got very far with this approach, but we cannot seem to get any further. How could we make this work?

Existential types to the rescue

If we go back to our original Update formulation, we can make one important observation: the Update type need not wear the Field and Value type parameters on its sleeve. All we care about in our Update values is that 1) they work against EmployeesTable, and 2) the types Field and Value are internally consistent, that is, .map and .update work together. As long as that happens, we do not care what those types specifically are.

Existential types are a mechanism to ensure such internal consistency.

Here is what a reformulation along these lines looks like.

sealed trait Update[Record] { self =>
  type Field
  type Value


  def field: Record => Field
  def newValue: Value
  def shape: Shape[_ <: FlatShapeLevel, Field, Value, Field]


  final def apply[U, C[_]](query: Query[Record, U, C]): FixedSqlAction[Int, NoStream, Effect.Write] = {
    query.map(field)(shape).update(newValue)
  }
}


object Update {
  def apply[Record, _Field, _Value](
    _field: Record => _Field,
    _newValue: _Value
  )(
    implicit
    _shape: Shape[_ <: FlatShapeLevel, _Field, _Value, _Field]
  ): Update[Record] = {
    new Update[Record] {
      type Field = _Field
      type Value = _Value


      def field: Record => Field = _field
      def newValue: Value = _newValue
      def shape: Shape[_ <: FlatShapeLevel, Field, Value, Field] = _shape
    }
  }
}

There is a lot happening here, so let’s unpack it slowly.

Just like before, this Update type is nothing but a delayed application of .map and .update. Only the record type Record shows up on the outside; every other type parameter has been made internal. Along with all the types, any values that might use them have moved inside too, including the implicit needed by .map. Since all of these things are now internal, we also have to move the application of the update inside. Hence Update#apply.

Update.apply, the smart constructor in the companion object, acts as a seam from the point where all the types are statically known to where some of them become existential.

Now this is how you can define .and on this type.

sealed trait Update[Record] { self =>
  type Field
  type Value


  def field: Record => Field
  def newValue: Value
  def shape: Shape[_ <: FlatShapeLevel, Field, Value, Field]


  final def apply[U, C[_]](query: Query[Record, U, C]): FixedSqlAction[Int, NoStream, Effect.Write] = {
    query.map(field)(shape).update(newValue)
  }


  final def and(another: Update[Record]): Update[Record] = {
    new Update[Record] {
      type Field = (self.Field, another.Field)
      type Value = (self.Value, another.Value)


      def field: Record => Field = record => (self.field(record), another.field(record))


      def newValue: Value = (self.newValue, another.newValue)


      def shape: Shape[_ <: FlatShapeLevel, Field, Value, Field] = {
        Shape.tuple2Shape(self.shape, another.shape)
      }
    }
  }
}

Since Update[Record] does not track the fields it updates in its type, we can even create a dynamic list of updates, that is, a List[Update[Record]]. This removes the need for a no-update case, as that is already captured by an empty list. Here is how we can rewrite our Query extensions.

object SlickExtensions {
  implicit class RichQuery[Record, U, C[_]](val underlying: Query[Record, U, C]) {
    def applyUpdate(update: Update[Record]): FixedSqlAction[Int, NoStream, Effect.Write] = {
      update.apply(underlying)
    }


    def applyUpdates(updates: List[Update[Record]])(implicit ec: ExecutionContext): DBIOAction[Int, NoStream, Effect.Write with Effect.Read] = {
      updates.reduceLeftOption(_ and _) match {
        case Some(composedUpdate) => underlying.applyUpdate(composedUpdate)
        case None                 => underlying.result.map(_ => 0)
      }
    }
  }
}

With this new formulation, we can finally write EmployeeRepository#update as follows.

final class EmployeeRepository {
  def update(
    id: String,
    name: Option[String],
    department: Option[String],
    age: Option[Int],
    salary: Option[Int],
  ): DBIO[Int] = {
    val updates: List[Update[EmployeesTable]] = List(
      name.map(value => Update((_: EmployeesTable).name, value)),
      department.map(value => Update((_: EmployeesTable).department, value)),
      age.map(value => Update((_: EmployeesTable).age, value)),
      salary.map(value => Update((_: EmployeesTable).salary, value))
    ).flatten


    EmployeesTable.filter(_.id === id).applyUpdates(updates)
  }
}

And there you have it: composable updates with Slick.

A note on using Option to model update inputs

In our Employee model, none of the fields were optional. So the meaning of Option[A] for updates was clear: set the value if given, Some[A], else leave it unaltered, None.

Imagine we had an optional field, say, pensionPlan: Option[PensionPlan]. In this case, the meaning of None becomes ambiguous. Does the caller want to set pensionPlan to None, or leave it unaltered? There is no way to know. Option is a bad fit for representing patches in general. Consider defining a custom sum type like this instead:

sealed trait Patch[+A] extends Product with Serializable


object Patch {
  final case class Set[+A](value: A) extends Patch[A]
  case object Keep extends Patch[Nothing]
  case object Delete extends Patch[Nothing]
}

Do not shy away from defining small sum types like these. Ambiguities can cost you big.

Exercise for the reader: Redefine Update so that value has type Patch, or similar, instead.

Book recommendation

It had been a while since I last used Slick. Dave Gurnell’s book Essential Slick 3 was a great help in getting me back up to speed. If you want to get the most out of Slick, I highly recommend reading it.

Thanks to Tom Wadeson and Amar Potghan for reviewing the blog post and for valuable feedback.