Scala/Slick: using inheritance and mixins to reduce boilerplate

Question

I'm scala/play/slick newbie so please don't be too mad if I ask dumb question.

Here goes the question.
I have several slick table definitions, here is one of them:

import javax.inject.Inject

import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.api.libs.concurrent.Execution.Implicits.defaultContext
import play.db.NamedDatabase
import slick.driver.JdbcProfile

import scala.concurrent.Future

case class User(id: Int, login: String, password: String) extends Identifiable

class UserDAO @Inject()(@NamedDatabase protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {
  import driver.api._

  private val users = TableQuery[UsersTable]

  def all(): Future[Seq[User]] = db.run(users.result)
  def insert(dog: User): Future[Unit] = db.run(users += dog).map { _ => () }
  def delete(id: Int): Future[Int] = db.run(users.filter(_.id === id).delete)


  private class UsersTable(tag: Tag) extends Table[User](tag, "USER") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def email = column[String]("email")
    def password = column[String]("password")
    def * = (id, email, password) <> (User.tupled, User.unapply)
  }
}  

Imagine I have much more tables which have def id = column[Int]("id", O.PrimaryKey, O.AutoInc) to eliminate this I need to write something like:

trait Identifiable {
  this: Table[_] =>
  def id = column[String]("id", O.PrimaryKey)
}

But how do I import Table here in a database agnostic manner? Moreover there is more room for enhancements: all DAO objects providing access to Identifiable Tables can be inherited from a common abstract class containing all, insert, find and delete methods. Something like (was unable to compile it):

abstract class BaseDAO[E <: Identifiable] extends DAO[E] with HasDatabaseConfigProvider[JdbcProfile] {
  import driver.api._
  private val entities = TableQuery[BaseTable]

  def all(): Future[Seq[E]] = db.run(entities.result)
  def insert(entity: E): Future[Unit] = db.run(entities += entity).map { _ => () }
  def delete(entity: E): Future[Int] = db.run(entities.filter(_.id === entity.id).delete)
  def find(id: Int): Future[E] = db.run(entities.filter(_.id === entities.id))

  trait BaseTable { this: Table[_] =>
    def id = column[String]("id", O.PrimaryKey, O.AutoInc)
  }
}

Could somebody please point me to my mistakes? Thanks.

Answer 1

Database agnostic and Code is highly reusable

I am using Slick with Playframework and this is how I achieved database agnostic and generic repository.

Note that this work is inspired from Active Slick

I want to have basic crud operations like this to be defined on my case class. I should be able to do count, update, delete and create. I want to write the curd code just once and reuse it for ever.

Here is the snippet which demonstrates this.

case class Dog(name: String, id: Option[Long] = None)
Dog("some_dog").save()
Dog("some_dog").insert()
Dog("some_dog", Some(1)).delete()

CrudActions.scala

import slick.backend.DatabaseConfig
import slick.driver.JdbcProfile

import scala.concurrent.ExecutionContext


trait CrudActions {
  val dbConfig: DatabaseConfig[JdbcProfile]
  import dbConfig.driver.api._

  type Model

  def count: DBIO[Int]

  def save(model: Model)(implicit ec: ExecutionContext): DBIO[Model]

  def update(model: Model)(implicit ec: ExecutionContext): DBIO[Model]

  def delete(model: Model)(implicit ec: ExecutionContext): DBIO[Int]

  def fetchAll(fetchSize: Int = 100)(implicit ec: ExecutionContext): StreamingDBIO[Seq[Model], Model]
}

Now lets get our Entity into picture. Note that Entity is nothing but our case class

Entity is case class on which we do crud operations. For locating our entity lets also have Id in place. Id is important for locating and operating an entity or record in the database. Also Id uniquely identities for entity

EntityActionsLike.scala

import slick.backend.DatabaseConfig
import slick.driver.JdbcProfile

import scala.concurrent.ExecutionContext

trait EntityActionsLike extends CrudActions {
  val dbConfig: DatabaseConfig[JdbcProfile]
  import dbConfig.driver.api._

  type Entity

  type Id

  type Model = Entity

  def insert(entity: Entity)(implicit ec: ExecutionContext): DBIO[Id]

  def deleteById(id: Id)(implicit ec: ExecutionContext): DBIO[Int]

  def findById(id: Id)(implicit ec: ExecutionContext): DBIO[Entity]

  def findOptionById(id: Id)(implicit ec: ExecutionContext): DBIO[Option[Entity]]
}

import slick.ast.BaseTypedType import slick.backend.DatabaseConfig import slick.driver.JdbcProfile

import scala.concurrent.ExecutionContext

Now lets implement these methods. For doing operations we need Table and TableQuery. Lets say we have table and tableQuery. The good about traits is we can declare a contract and leave the implementation details to subclasses or subtypes

EntityActions.scala

trait EntityActions extends EntityActionsLike {
  val dbConfig: DatabaseConfig[JdbcProfile]
  import dbConfig.driver.api._

  type EntityTable <: Table[Entity]

  def tableQuery: TableQuery[EntityTable]

  def $id(table: EntityTable): Rep[Id]

  def modelIdContract: ModelIdContract[Entity,Id]

  override def count: DBIO[Int] = tableQuery.size.result

  override def insert(entity: Entity)(implicit ec: ExecutionContext): DBIO[Id] = {
    tableQuery.returning(tableQuery.map($id(_))) += entity
  }

  override def deleteById(id: Id)(implicit ec: ExecutionContext): DBIO[Int] = {
    filterById(id).delete
  }

  override def findById(id: Id)(implicit ec: ExecutionContext): DBIO[Entity] = {
    filterById(id).result.head
  }

  override def findOptionById(id: Id)(implicit ec: ExecutionContext): DBIO[Option[Entity]] = {
    filterById(id).result.headOption
  }

  override def save(model: Entity)(implicit ec: ExecutionContext): DBIO[Entity] = {
    insert(model).flatMap { id =>
      filterById(id).result.head
    }.transactionally
  }

  override def update(model: Entity)(implicit ec: ExecutionContext): DBIO[Entity] = {
    filterById(modelIdContract.get(model)).update(model).map { _ => model }.transactionally
  }

  override def delete(model: Entity)(implicit ec: ExecutionContext): DBIO[Int] = {
    filterById(modelIdContract.get(model)).delete
  }

  override def fetchAll(fetchSize: Int)(implicit ec: ExecutionContext): StreamingDBIO[Seq[Entity], Entity] = {
    tableQuery.result.transactionally.withStatementParameters(fetchSize = fetchSize)
  }

  def filterById(id: Id) = tableQuery.filter($id(_) === id)

  def baseTypedType: BaseTypedType[Id]

  protected implicit lazy val btt: BaseTypedType[Id] = baseTypedType

}

ActiveRecord.scala

import slick.dbio.DBIO

import scala.concurrent.ExecutionContext


abstract class ActiveRecord[R <: CrudActions](val repo: R) {
  def model: repo.Model
  def save()(implicit ec: ExecutionContext): DBIO[repo.Model] = repo.save(model)
  def update()(implicit ec: ExecutionContext): DBIO[repo.Model] = repo.update(model)
  def delete()(implicit ec: ExecutionContext): DBIO[Int] = repo.delete(model)
}

ModelContract.scala

case class ModelIdContract[A, B](get: A => B, set: (A, B) => A)

How to Use

Sample.scala

import com.google.inject.{Inject, Singleton}
import play.api.db.slick.DatabaseConfigProvider
import slick.ast.BaseTypedType
import slick.backend.DatabaseConfig
import slick.driver.JdbcProfile
import slick.{ActiveRecord, EntityActions, ModelIdContract}

case class Dog(name: String, id: Option[Long] = None)

@Singleton
class DogActiveRecord @Inject() (databaseConfigProvider: DatabaseConfigProvider) extends EntityActions {

  override val dbConfig: DatabaseConfig[JdbcProfile] = databaseConfigProvider.get[JdbcProfile]

  import dbConfig.driver.api._

  override def tableQuery = TableQuery(new Dogs(_))

  override def $id(table: Dogs): Rep[Id] = table.id

  override def modelIdContract: ModelIdContract[Dog, Id] = ModelIdContract(dog => dog.id.get, (dog, id) => dog.copy(id = Some(id)))

  override def baseTypedType: BaseTypedType[Id] = implicitly[BaseTypedType[Id]]

  override type Entity = Dog
  override type Id = Long
  override type EntityTable = Dogs

  class Dogs(tag: Tag) extends Table[Dog](tag, "DogsTable") {
    def name = column[String]("name")
    def id = column[Long]("id", O.PrimaryKey)
    def * = (name, id.?) <> (Dog.tupled, Dog.unapply)
  }

  implicit class ActiveRecordImplicit(val model: Entity) extends ActiveRecord(this)

  import scala.concurrent.ExecutionContext.Implicits.global

  val result = Dog("some_dog").save()

  val res2 = Dog("some_other_dog", Some(1)).delete()

  val res3 = Dog("some_crazy_dog", Some(1)).update()
}

Now we can do operations on Dog directly like this

Dog("some_dog").save()

This implicit does the magic for us

implicit class ActiveRecordImplicit(val model: Entity) extends ActiveRecord(this)

You can also add scheme creation and dropping logic in EntityActions

tableQuery.schema.create
table.schema.drop