Best Practices for Using Slick on Production


Slick is most popular library for relational database access in Scala ecosystem. When we are going to use Slick for production , then some questions arise  like where should the mapping tables be defined and how to join with other tables, how to write unit tests. Apart from this there is lack of clarity on the design guidelines.

In this blog post , I am sharing my experience how we are using slick on production.

Let us start the example with a simple scenario. Consider there are three tables Bank , BankInfo and BankProduct. Bank and BankInfo have one to one relation (You could possibly argue that we do not need this distinction but for now let us assume that it is meant to be that way for the example) , Bank and BankProduct have one to many relationship.

One of the design goals or best practice that we want to follow is that we would like to keep our database access code database agnostic. What does that mean? It means that our Slick code should not care about the ultimate database that we are using. It could be PostgreSQL, MySQL , SQlServer or H2 for unit testing.

 

For the database access Slick need two things:
1) Database drive
2) Slick driver

Lets keep abstract from database access methods :

package com.knol.db.connection
import scala.slick.driver.JdbcProfile

trait DBComponent {

val driver: JdbcProfile

import driver.api._

val db: Database

}

JdbcProfile is trait. All drivers for JDBC-based databases implement this profile.

Lets define database access methods Bank repo:

package com.knol.db.repo

import com.knol.db.connection.DBComponent
import scala.concurrent.Future

trait BankRepository extends BankTable { this: DBComponent =>

  import driver.api._

  def create(bank: Bank): Future[Int] = db.run { bankTableAutoInc += bank }

  def update(bank: Bank): Future[Int] = db.run { bankTableQuery.filter(_.id === bank.id.get).update(bank) }

  def getById(id: Int): Future[Option[Bank]] = db.run { bankTableQuery.filter(_.id === id).result.headOption }

  def getAll(): Future[List[Bank]] = db.run { bankTableQuery.to[List].result }

  def delete(id: Int): Future[Int] = db.run { bankTableQuery.filter(_.id === id).delete }

}

private[repo] trait BankTable { this: DBComponent =>

  import driver.api._

  private[BankTable] class BankTable(tag: Tag) extends Table[Bank](tag,"bank") {
    val id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    val name = column[String]("name")
    def * = (name, id.?) <> (Bank.tupled, Bank.unapply)
  }

  protected val bankTableQuery = TableQuery[BankTable]

  protected def bankTableAutoInc = bankTableQuery returning bankTableQuery.map(_.id)

}

case class Bank(name: String, id: Option[Int] = None)


Bank and BankInfo have one to one relation. Lets define BankInfo repo and write joins with bank repo:

package com.knol.db.repo

import com.knol.db.connection.DBComponent
import scala.concurrent.Future

trait BankInfoRepository extends BankInfoTable { this: DBComponent =>

  import driver.api._

  def create(bankInfo: BankInfo): Future[Int] = db.run { bankTableInfoAutoInc += bankInfo }

  def update(bankInfo: BankInfo): Future[Int] = db.run { bankInfoTableQuery.filter(_.id === bankInfo.id.get).update(bankInfo) }

  def getById(id: Int): Future[Option[BankInfo]] = db.run { bankInfoTableQuery.filter(_.id === id).result.headOption }

  def getAll(): Future[List[BankInfo]] = db.run { bankInfoTableQuery.to[List].result }

  def delete(id: Int): Future[Int] = db.run { bankInfoTableQuery.filter(_.id === id).delete }

  def getBankWithInfo(): Future[List[(Bank, BankInfo)]] =
    db.run {
      (for {
        info <- bankInfoTableQuery
        bank <- info.bank
      } yield (bank, info)).to[List].result
    }


  def getAllBankWithInfo(): Future[List[(Bank, Option[BankInfo])]] =
    db.run {
      bankTableQuery.joinLeft(bankInfoTableQuery).on(_.id === _.bankId).to[List].result
    }
}

private[repo] trait BankInfoTable extends BankTable { this: DBComponent =>

  import driver.api._

  private[BankInfoTable] class BankInfoTable(tag: Tag) extends Table[BankInfo](tag,"bankinfo") {
    val id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    val owner = column[String]("owner")
    val bankId = column[Int]("bank_id")
    val branches = column[Int]("branches")
    def bank = foreignKey("bank_product_fk", bankId, bankTableQuery)(_.id)
    def * = (owner, branches, bankId, id.?) <> (BankInfo.tupled, BankInfo.unapply)
  }

  protected val bankInfoTableQuery = TableQuery[BankInfoTable]

  protected def bankTableInfoAutoInc = bankInfoTableQuery returning bankInfoTableQuery.map(_.id)

}

case class BankInfo(owner: String, branches: Int, bankId: Int, id: Option[Int] = None)


Bank and BankProduct have one to many relationship. Lets define BankProduct repo and write joins with bank repo:


package com.knol.db.repo

import com.knol.db.connection.DBComponent
import scala.concurrent.Future

trait BankProductRepository extends BankProductTable { this: DBComponent =>

  import driver.api._

  def create(bankProduct: BankProduct): Future[Int] = db.run { bankProductTableAutoInc += bankProduct }

  def update(bankProduct: BankProduct): Future[Int] = db.run { bankProductTableQuery.filter(_.id === bankProduct.id.get).update(bankProduct) }

  def getById(id: Int): Future[Option[BankProduct]] = db.run { bankProductTableQuery.filter(_.id === id).result.headOption }

  def getAll(): Future[List[BankProduct]] = db.run { bankProductTableQuery.to[List].result }

  def delete(id: Int): Future[Int] = db.run { bankProductTableQuery.filter(_.id === id).delete }

  /**
   * Get bank and product using foreign key relationship
   */
  def getBankWithProduct(): Future[List[(Bank, BankProduct)]] =
    db.run {
      (for {
        product <- bankProductTableQuery
        bank <- product.bank
      } yield (bank, product)).to[List].result
    }

  /**
   * Get all bank and their product.It is possible some bank do not have their product
   */
  def getAllBankWithProduct(): Future[List[(Bank, Option[BankProduct])]] =
    db.run {
      bankTableQuery.joinLeft(bankProductTableQuery).on(_.id === _.bankId).to[List].result
    }

}

private[repo] trait BankProductTable extends BankTable { this: DBComponent =>

  import driver.api._

  private[BankProductTable] class BankProductTable(tag: Tag) extends Table[BankProduct](tag, "bankproduct") {
    val id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    val name = column[String]("name")
    val bankId = column[Int]("bank_id")
    def bank = foreignKey("bank_product_fk", bankId, bankTableQuery)(_.id)
    def * = (name, bankId, id.?) <> (BankProduct.tupled, BankProduct.unapply)

  }

  protected val bankProductTableQuery = TableQuery[BankProductTable]

  protected def bankProductTableAutoInc = bankProductTableQuery returning bankProductTableQuery.map(_.id)

}

case class BankProduct(name: String, bankId: Int, id: Option[Int] = None)

As we have see all database access code are database independent we don’t mention database name any where in code.

It is time to write unit test.For unit testing, It is good if we use in memory database like H2. There are lot framework for writing unit test, I am using ScalaTest(It is my preference).

To avoid  boilerplating  in unit test, define  concrete H2 database driver implementation at one place for all repositories  like:


trait H2DBComponent extends DBComponent {

  val driver = slick.driver.H2Driver

  import driver.api._

  val h2Url = "jdbc:h2:mem:test;MODE=MySql;DATABASE_TO_UPPER=false;INIT=runscript from 'src/test/resources/schema.sql'\\;runscript from 'src/test/resources/schemadata.sql'"

  val db: Database = Database.forURL(url = h2Url, driver = "org.h2.Driver")

}

H2 is in memory database so we need to create database schema each time when you want to run unit test. The good way is provide a script to H2 database so when H2 is going to start it will automatically run this script.Same as schema script we can  provide  a script  for data set which is required  for testing  purpose.
Lets write unit tests of bank repo:

package com.knol.db.repo

import org.scalatest.FunSuite
import com.knol.db.connection.H2DBComponent
import org.scalatest.concurrent.ScalaFutures
import org.scalatest.time.{ Millis, Seconds, Span }


class BankRepositoryTest extends FunSuite with BankRepository with H2DBComponent with ScalaFutures {

  implicit val defaultPatience = PatienceConfig(timeout = Span(5, Seconds), interval = Span(500, Millis))

  test("Add new bank") {
    val response = create(Bank("ICICI bank"))
    whenReady(response) { bankId =>
      assert(bankId === 3)
    }
  }

  test("Update  SBI bank") {
    val response = update(Bank("SBI Bank", Some(1)))
    whenReady(response) { res =>
      assert(res === 1)
    }
  }

  test("Delete SBI bank") {
    val response = delete(2)
    whenReady(response) { res =>
      assert(res === 1)
    }
  }

  test("Get bank list") {
    val bankList = getAll()
    whenReady(bankList) { result =>
      assert(result === List(Bank("SBI bank", Some(1)), Bank("PNB bank", Some(2))))
    }
  }

}

Similarity we can write unit test for all repositories.

For production use,We need to define concrete database driver implementation. Here, I am taking MySQL as production database. Database Configuration setting look like:


mysql = {
 dataSourceClass="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
 properties {
 user="root"
 password="root"
 databaseName="bank_db"
 serverName="localhost"
 }
 numThreads=10
 maxConnections = 12
 minConnections = 4
}

Slick recommend, Connection pool size should be larger than the thread pool size.Equal is also ok. A very good answer by Stefan Zeiger on Google group. Database implementation would be like:


trait MySqlDBComponent extends DBComponent {

 val driver = slick.driver.MySQLDriver

 import driver.api._

 val db: Database = MySqlDB.connectionPool

}

private[connection] object MySqlDB {

 import slick.driver.MySQLDriver.api._

val connectionPool = Database.forConfig("mysql")

}
// Singleton instance of bank repository for  Production 
object BankInfoRepository extends BankInfoRepository with MySqlDBComponent

// or we can define concrete class for production usage 
class BankInfoRepositoryImpl extends BankInfoRepository with MySqlDBComponent

See full code on Github

Happy Hacking!!! 🙂

About Satendra Kumar

Satendra kumar is the Senior Associate @ Sapient Global Market. In his 5 years of experience he has become developer with proven experience in architecting and developing the Reactive Systems and Big Data Platform with Scala, Akka, Spark, Kafka & Hadoop. He is the big fan of Scala. Satendra has a B.Tech in Computer Engineering from Uttar Pradesh Technical University.
This entry was posted in Best Practices, database, rdbms, Scala, scalatest, Slick, sql, tests. Bookmark the permalink.

13 Responses to Best Practices for Using Slick on Production

  1. ksilin says:

    Very elaborate. Many thanks for the ideas. However, as a slick newb, it did put me under some heavy cognitive load. cogniviWhat do yout thing about a simplified version of a Repository, modeled after the ‘simple’ example from https://github.com/typesafehub/activator-slick-multidb?

    class UserRepository(val dc: DatabaseConfig[JdbcProfile]) {

    import dc.driver.api._
    val db: Database = dc.db

    def create(user: User): Future[Int] = db.run {userTableAutoInc += user}

    class UserTable(tag: Tag) extends Table[User](tag, “user”) {
    val id = column[Int](“id”, O.PrimaryKey, O.AutoInc)
    val name = column[String](“name”)
    def * = (name, id.?) (User.tupled, User.unapply)
    }
    val userTableQuery = TableQuery[UserTable]

    }
    case class User(name: String, id: Option[Int] = None)

    creating a DatabaseConfig: DatabaseConfig.forConfig[JdbcProfile](“h2_dc”)

    config:
    h2_dc {
    driver = “slick.driver.H2Driver$”
    db { …}
    }

  2. @ksilin This is very good example for starting with Slick. Whenever you want perform joins on multiple tables then it will become a bit complex. In my example, BankProductTable and BankTable are Slick mapping tables. I have put these classes in separate trait because If slick mapping table needs another slick mapping table for defining a foreignKey relationship, you can Mixin it very easily. Simply, I don’t want to Mixin methods of one Repository to another Repository. May be two repository have methods which have same name.

    private[repo] trait BankProductTable extends BankTable { this: DBComponent =>

    import driver.api._

    private[BankProductTable] class BankProductTable(tag: Tag) extends Table[BankProduct](tag, “bankproduct”) {
    val id = column[Int](“id”, O.PrimaryKey, O.AutoInc)
    val name = column[String](“name”)
    val bankId = column[Int](“bank_id”)
    def bank = foreignKey(“bank_product_fk”, bankId, bankTableQuery)(_.id)
    def * = (name, bankId, id.?) (BankProduct.tupled, BankProduct.unapply)

    }

    protected val bankProductTableQuery = TableQuery[BankProductTable]

    protected def bankProductTableAutoInc = bankProductTableQuery returning bankProductTableQuery.map(_.id)

    }

  3. Mike Slinn says:

    I tried to extend the pattern you show. I would like to be able to specify an upper bound for a Slick 3.1.1 query type so the id-related actions can be factored out and usable by other persisted classes. I wrote up a SO question (http://stackoverflow.com/questions/37349378/upper-bound-for-slick-3-1-1-query-type) which points to my fork of your GitHub project (https://github.com/mslinn/slick-starting-on-the-right-foot/blob/action/src/main/scala/com/knol/db/repo/BankInfoRepository.scala). I would be grateful if you would offer a suggestion.

  4. @Mike Slinn

    Full Working example:

    package com.knol.db.repo
    
    import com.knol.db.connection.DBComponent
    import com.knol.db.connection.MySqlDBComponent
    import scala.concurrent.{Await, Future}
    import concurrent.duration.Duration
    
    trait LiftedHasId {
      def id: slick.lifted.Rep[Int]
    }
    
    trait HasId {
      def id: Option[Int]
    }
    
    trait GenericAction[T <: HasId]{this: DBComponent =>
      import driver.api._
    
      type QueryType <: slick.lifted.TableQuery[_ <: Table[T] with LiftedHasId]
    
      val tableQuery: QueryType
    
      @inline def deleteAsync(id: Int): Future[Int] = db.run { tableQuery.filter(_.id === id).delete }
      @inline def delete(id: Int): Int = Await.result(deleteAsync(id), Duration.Inf)
    
      @inline def deleteAllAsync(): Future[Int] = db.run { tableQuery.delete }
      @inline def deleteAll(): Int = Await.result(deleteAllAsync(), Duration.Inf)
    
      @inline def getAllAsync: Future[List[T]] = db.run { tableQuery.to[List].result }
      @inline def getAll: List[T] = Await.result(getAllAsync, Duration.Inf)
    
      @inline def getByIdAsync(id: Int): Future[Option[T]] =
        db.run { tableQuery.filter(_.id === id).result.headOption }
    
      @inline def getById(id: Int): Option[T] = Await.result(getByIdAsync(id), Duration.Inf)
    
      @inline def deleteById(id: Option[Int]): Unit =
        db.run { tableQuery.filter(_.id === id).delete }
    
      @inline def findAll: Future[List[T]] = db.run { tableQuery.to[List].result }
    
    
    
    
    }
    trait BankInfoRepository extends BankInfoTable  with GenericAction[BankInfo] { this: DBComponent =>
    
      import driver.api._
    
      type QueryType  = TableQuery[BankInfoTable]
    
      val tableQuery=bankInfoTableQuery
    
      def create(bankInfo: BankInfo): Future[Int] = db.run { bankTableInfoAutoInc += bankInfo }
    
      def update(bankInfo: BankInfo): Future[Int] = db.run { bankInfoTableQuery.filter(_.id === bankInfo.id.get).update(bankInfo) }
    
      /**
       * Get bank and info using foreign key relationship
       */
      def getBankWithInfo(): Future[List[(Bank, BankInfo)]] =
        db.run {
          (for {
            info <- bankInfoTableQuery
            bank <- info.bank
          } yield (bank, info)).to[List].result
        }
    
      /**
       * Get all bank and their info.It is possible some bank do not have their product
       */
      def getAllBankWithInfo(): Future[List[(Bank, Option[BankInfo])]] =
        db.run {
          bankTableQuery.joinLeft(bankInfoTableQuery).on(_.id === _.bankId).to[List].result
        }
    }
    
    private[repo] trait BankInfoTable extends BankTable{ this: DBComponent =>
    
      import driver.api._
    
      class BankInfoTable(tag: Tag) extends Table[BankInfo](tag, "bankinfo") with LiftedHasId {
        val id = column[Int]("id", O.PrimaryKey, O.AutoInc)
        val owner = column[String]("owner")
        val bankId = column[Int]("bank_id")
        val branches = column[Int]("branches")
        def bank = foreignKey("bank_product_fk", bankId, bankTableQuery)(_.id)
        def * = (owner, branches, bankId, id.?) <> (BankInfo.tupled, BankInfo.unapply)
    
      }
    
      protected val bankInfoTableQuery = TableQuery[BankInfoTable]
    
      protected def bankTableInfoAutoInc = bankInfoTableQuery returning bankInfoTableQuery.map(_.id)
    
    }
    
    object BankInfoRepository extends BankInfoRepository with MySqlDBComponent
    
    case class BankInfo(owner: String, branches: Int, bankId: Int, id: Option[Int] = None) extends HasId
    
    
  5. Mike Slinn says:

    Would you please fork my fork of your GitHub repo, and add your code there? I cannot get your code to work. “type QueryType = TableQuery[BankInfoTable]” in BankInfoRepository results in an incompatible override error.

  6. @Mike Actually It was very minor issue. I have fixed and created pull request on your repo.
    Changes:

      class BankInfoTable(tag: Tag) extends Table[BankInfo](tag, "bankinfo") {
        val id       = column[Int]("id", O.PrimaryKey, O.AutoInc)
        val owner    = column[String]("owner")
        val bankId   = column[Int]("bank_id")
        val branches = column[Int]("branches")
    
        def bankFK = foreignKey("bank_product_fk", bankId, bankTableQuery)(_.id)
    
        def * = (owner, branches, bankId, id.?) <> (BankInfo.tupled, BankInfo.unapply)
      }
    
    

    It should be:

    
      class BankInfoTable(tag: Tag) extends Table[BankInfo](tag, "bankinfo") with LiftedHasId {
        val id       = column[Int]("id", O.PrimaryKey, O.AutoInc)
        val owner    = column[String]("owner")
        val bankId   = column[Int]("bank_id")
        val branches = column[Int]("branches")
    
        def bankFK = foreignKey("bank_product_fk", bankId, bankTableQuery)(_.id)
    
        def * = (owner, branches, bankId, id.?) <> (BankInfo.tupled, BankInfo.unapply)
      }
    
    
  7. Mike Slinn says:

    I have used the approach shown above successfully on several table. However, I have a Postgres table that has a key defined like this:

    id BIGSERIAL PRIMARY KEY

    The other tables have keys defined exactly the same. This table, throws “java.lang.IllegalArgumentException: Unknown field: id” when autoInc is invoked. Not sure what makes this table different. The database record does get written, even though an exception is thrown.

    Here is a simplified version of the code:

    class CourseTable(tag: Tag) extends Table[Course](tag, “courses”) {
    // …
    def id = column[Option[Id]](“id”, O.PrimaryKey, O.AutoInc)
    // …
    }

    val sanitize = (course: Course) => {
    course.copy( /* blah blah */ )
    }

    // Type Course has a public property id of type Id
    val course: Course = ??? // constructor details are irrelevant

    val copyUpdate = (course: Course, id: Option[Id]) => sanitize(course)
    lazy val queryAll = TableQuery[CourseTable]
    def autoInc = queryAll returning queryAll.map(_.id) into copyUpdate
    database.run(autoInc += course) // Boom!

    Suggestions?

    Mike

  8. @Mike: It seems problem with “into” method where you are passing copyUpdate. Have you try without calling “into” method ?
    Could please share branch name where this code reside then i will take a look in real code and full stack of exception.

  9.   
    test("Upsert existing bank") {
        val bankOne: Bank = getAll.head
        whenReady(upsertAsync(bankOne)) { bank => // why is bank==None?
          assert(bank.exists(_.idAsInt >= 0))
        }
    
        val modifiedBank = bankOne.copy(name="Beyondanana")
        whenReady(upsertAsync(modifiedBank)) { bank => // why is bank==None?
          assert(bank.exists(_.idAsInt >= 0))
        }
      }
    

    @Mike: In this case method insertOrUpdate returns None if updated, otherwise return inserted row Some(Bank).

  10. Sunand says:

    Could you please suggest how to write the query to handle one to many relation. the below scenario:

    case class BankWithProducts(bank: Bank, products: Seq[BankProduct])

    def getBankWithProducts(): Future[BankWithProducts] = …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s