
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 Shape
s.)
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 2number of fields = 24 = 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!)