Slick is a powerful library for functional database access, leveraging Scala language features such as implicit conversions and macro’s to allow type-safe interaction with databases.

The play-slick module allows easy integration with the Play! framework by providing e.g. support for database evolutions and other convenience methods.

As I got to work with Slick more extensively, one thing that started to annoy me pretty soon is that I had to repeat boilerplate code for generic CRUD methods such as insert, update, delete, etc. At the same time, it didn’t look really easy to provide a CRUD trait for models, given that Slick’s design is heavily centered around the idea of drivers encapsulating the implementation details for all generic concerns (querying, insertion, etc.) and is therefore already making use of large portions of abstractions.

Lifted embedding (currently the only method that allows insert, update and delete operations) requires to have a table definition declaring all the columns and associated methods, like so:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

package models

import play.api.db.slick.Config.driver.simple._

case class Cat(name: String, gender: String, color: String)

object Cats extends Table[Cat]("CAT") {

 def name = column[String]("name", O.PrimaryKey)

 def gender = column[String]("gender", O.NotNull)

 def color = column[String]("color", O.NotNull)

 def * = name ~ gender ~ color <> (Cat, Cat.unapply _)

 // auto increment handler
 def autoInc = * returning name

 def insert(cat: Cat) = DB.withSession { implicit session =>
  autoInc.insert(cat)
 }

 def update(name: String, cat: Cat) {
  DB.withSession { implicit session =>
   Cats.where(_.name === name).update(cat)
  }
 }

 def delete(name: String) {
  DB.withSession { implicit session =>
   Cats.where(_.name === name).delete
  }
 }

}

The autoInc, insert, update and delete operations are arguably always the same for any kind of entity and don’t add much value when repeated over time.

After a bit of tinkering, I ended up with the following trait which does a quite good job add keeping Slick models dry:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
    package models

import play.api.Play.current
import play.api.db.slick.Config.driver.simple._
import play.api.db.slick.DB

/**
 * Helper for otherwise verbose Slick model definitions
 */
trait CRUDModel[T <: AnyRef {val id: Option[Long]}] {
 self: Table[T] =>

 def id: Column[Long]

 def * : scala.slick.lifted.ColumnBase[T]

 def autoInc = * returning id

 def insert(entity: T) = {
  DB.withSession { implicit session =>
   autoInc.insert(entity)
  }
 }

 def insertAll(entities: Seq[T]) {
  DB.withSession { implicit session =>
   autoInc.insertAll(entities: _*)
  }
 }

 def update(id: Long, entity: T) {
  DB.withSession { implicit session =>
   tableQueryToUpdateInvoker(
    tableToQuery(this).where(_.id === id)
   ).update(entity)
  }
 }

 def delete(id: Long) {
  DB.withSession { implicit session =>
   queryToDeleteInvoker(
    tableToQuery(this).where(_.id === id)
   ).delete
  }
 }

 def count = DB.withSession { implicit session =>
  Query(tableToQuery(this).length).first
 }

}

This trait works in the following way:

  • since we don’t want to have to impose anything too special on the case classes, a structural type definition is used to require model definitions (the case classes) to have an identifier being an Option[Long]. The field is optional given that the ID should be provided by the database at insertion time and not the other way around
  • there doesn’t seem to be a way to get the implicit conversion scope provided by the Slick drivers to be applied in this case, hence the implicit conversions (tableToQuery and friends) need to be called explicitly
  • the example above only works with the driver provided by the play.api.db.slick.Config.driver.simple._ import, but it is easy to adapt it to other drivers

There seems to be an interesting side-effect to using this trait, which is that SBT’s incremental compiler seems to get confused with the type of the table definition and fails to compile the table definition object after a change is made right away. Triggering a change in another class, or cleaning the project gets things to work again.