I have been using Slick on and off since 2011 (when it was called ScalaQuery), and it remains my most 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. Doobie queries essentially being 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’s terribly error-prone and just impossible in general 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 stumbled into some problems along the way: bugs, unimplemented features, and jarring innards. I am going to do a short blog series on how we use Slick: some interesting ways we abstract over patterns I alluded to before, how we work around the bugs we ran into, and some other tips and tricks. This is the first post in that series.

Before we begin though, let’s get some 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 "employee" (
    "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 out of it.

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, "employee") {
  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; enum to represent department etc, but since none of that is of relevance to this post, we leave them as strings here. We will be taking more such shortcuts in 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 a method .update that looks something like shown below.

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 look up, and then the other fields will be set to the provided values (i.e. 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. (More even, 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 above examples, 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 2^{number of fields} = 2⁴ = 16 branches. When we add an extra field, we will have to add 16 more branches.

Clearly this doesn’t 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 type 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, 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 afford this, 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 to be impossible. What do you do when you have a Perform[R, F1, V1] on one side, and Pass[R, F2, V2] on another? 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 type Update need not wear the Field and Value type parameters on its sleeve. All we care about our Update values is that 1) they work against EmployeesTable, 2) the types Field and Value are internally consistent, i.e. .map and .update work together. As long as that happens, we do not care what those types specifically are.

Existential types is a mechanism to ensure such internal consistency.

Here is what a re-formulation 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 this 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. With all the types that went in, so did any values that might use them. (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 fields it updates in its type, we can even create a dynamic list of updates i.e. an 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 the 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

    EmployeeTable.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 below 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 value has a type Patch (or similar) instead.

Book recommendation

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

(Thanks to Tom Wadeson and Amar Potghan for reviewing the blog post and for valuable feedback. Special thanks to Ishant Solanki for the beautiful re-theming of the website!)

Composable table updates in Slick

Setup

Problem

Non-solution

First stab at a solution

Existential types to the rescue

A note on using `Option` to model update inputs

Book recommendation

TDD is Dead, Long Live TyDD!

Rahul Goma Phulore

Share this post

Comments

Setup

Problem

Non-solution

First stab at a solution

Existential types to the rescue

A note on using Option to model update inputs

Book recommendation

TDD is Dead, Long Live TyDD!

Rahul Goma Phulore

Share this post

Comments

A note on using `Option` to model update inputs