CRUD trait for Slick models in the Play! framework

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:

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:

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.

Comments 7

  1. Great work. I started learning slick, scala and play all at once and I had the desire to do something like what you have. Unfortunately, I don’t have the scala skills to master the abstractions. Hey can you give us newbies some clue on how to use the crud.

    I’m struggling with the trait definition CrudModel[T

    How would I use it in the table below:
    object Users extends Table[User](“USERS”) with CrudModel[User]{ //with Model{
    private val debug = true
    def userId = column[Int](“USER_ID”, O.PrimaryKey, O.AutoInc)
    def firstName = column[String](“FIRST_NAME”, O.NotNull)
    def lastName = column[String](“LAST_NAME”, O.NotNull)
    def email = column[String](“EMAIL”, O.NotNull)
    def password = column[String](“PASSWORD”, O.Nullable)
    def about = column[String](“ABOUT”, O.Nullable)

    def * = userId.? ~ firstName ~ lastName ~ email ~ password.? ~ about.? (User.apply _, User.unapply _)

    def autInc = * returning userId

    1. Post

      Hi Ricardo,

      the CrudModel trait expects the Table it is used with to have an “id” column (to be an optional Long), so instead of the userId column you’ve defined, just rename that to “id” and change its type to “Long” (or change that in the CrudModel). You also don’t need to define the autoInc insertion handler here because it is already provided by the trait.

  2. Hey Manu,

    Great job. Although that mightn’t be a bit compliment, since I’m a beginner with play+scala 🙂
    I have a question though.

    I tried to use this trait but I get an Exception when executing an update.

    object Products extends Table[Product](“PRODUCTS”) with CRUDModel[Product{

    def id = column[Long](“ID”, O.PrimaryKey, O.AutoInc) // This is the primary key column
    def name = column[String](“NAME”)

    def * = id.? ~ name (Product.apply _, Product.unapply _)

    JdbcSQLException: Referential integrity constraint violation: “PRODUCT_FK: PUBLIC.LINE_ITEM FOREIGN KEY(PRODUCT_ID) REFERENCES PUBLIC.PRODUCTS(ID) (1)”; SQL statement:
    [error] update “PRODUCTS” set “ID” = ?, “NAME” = ?, “DESC” = ?, “PRICE” = ?, “AVAILABILITY” = ? where “PRODUCTS”.”ID” = 1

    So basically the ID is included in the update but I don’t really like that.

    If I implement update in the Products, it’s all okay:

    def update(id: Long, product: Product) {
    database.withSession {
    val productToUpdate: Product = product.copy(Some(id))
    Products.where( === id).update(productToUpdate)

    Do you have any idea why it won’t work with your trait? (other methods are working flawlessly).


  3. Post

    Hi Balázs,

    sorry for the late reply. I think that what’s happening here is that the trait above works well for in-memory DBs such as H2 but needs a little tweaking for real-world databases. In particular you may need to write another projection leaving out the ID for updates, such as e.g.

    def updateProjection = name <> (Product.apply _, Product.unapply _)

    Hope that helps!

  4. Some more methods you can add

    def select(id:Long) = DB.withSession { implicit session =>
    Query(this).filter( === id).first() // firstOption() seems to crash the compiler

    // could even add paging here
    def selectAll() = DB.withSession { implicit session =>

    def insertAndReturn(entity: T) = DB.withSession { implicit session =>
    val id = insert(entity)

    Strange that I have the compiler blowing up on the firstOption and cleaning doesn’t help

  5. On the autoinc not working for certain databases try this

    // only works for databases that ignore the id being inserted like H2
    // should be solved/autogenerated in slick 2.0
    // def autoInc = * returning id
    def forInsert: scala.slick.lifted.ColumnBase[T]
    def autoInc = forInsert returning id

    and implement the forInsert like this

    def forInsert = firstname ~ lastname (
    { t => User(None, t._1, t._2)},
    { (u: User) => Some((u.firstname, u.lastname))}

  6. Pingback: CRUD trait for Slick 2.0 | Coding tips and notes

Leave a Reply

Your email address will not be published.