Best Practices for Using Slick on Production

January 1, 2016January 7, 2016 Database, scalatest, Slick, SQL, Studio-Scala13 Comments

Table of contents

Reading Time: 5 minutes

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!!! 🙂

13 thoughts on “Best Practices for Using Slick on Production7 min read”

Anand Kumar Singh says:

January 2, 2016 at 3:27 PM

Reblogged this on Play!ng with Scala.
ksilin says:

February 1, 2016 at 10:58 PM

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 { …}
}
Satendra Kumar says:

February 2, 2016 at 9:16 PM

@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)

}
Mike Slinn says:

May 22, 2016 at 1:44 AM

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.
Satendra Kumar says:

May 22, 2016 at 4:00 PM

@Mike Slinn

Full Working example:

[code language=”scala”]
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

[/code]
Mike Slinn says:

May 24, 2016 at 11:36 PM

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.
Satendra Kumar says:

May 25, 2016 at 11:53 AM

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

[code language=”scala”]
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)
}

[/code]

It should be:

[code language=”scala”]

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)
}

[/code]
Mike Slinn says:

July 28, 2016 at 1:19 AM

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
Satendra Kumar says:

July 28, 2016 at 2:14 PM

@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.
1. Mike Slinn says:
  
  July 29, 2016 at 1:56 AM
  
  Satendra, I have updated my fork to incorporate similar code to what I described. It mostly works, except for one of the upsert tests.
  https://github.com/mslinn/slick-starting-on-the-right-foot/blob/action/src/test/scala/com/knol/db/repo/BankRepositoryTest.scala#L29-L39
Satendra Kumar says:

July 30, 2016 at 2:41 PM

[code language=”scala”]
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))
}
}
[/code]
@Mike: In this case method insertOrUpdate returns None if updated, otherwise return inserted row Some(Bank).
1. Mike Slinn says:
  
  July 30, 2016 at 10:25 PM
  
  I did not know that. Thank you!
Sunand says:

February 3, 2017 at 2:47 PM

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] = …