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 :
JdbcProfile is trait. All drivers for JDBC-based databases implement this profile.
Lets define database access methods Bank repo:
Bank and BankInfo have one to one relation. Lets define BankInfo repo and write joins with bank repo:
Bank and BankProduct have one to many relationship. Lets define BankProduct repo and write joins with bank repo:
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:
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:
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:
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:
See full code on Github
Happy Hacking!!! 🙂
Reblogged this on Play!ng with Scala.
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 { …}
}
@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)
}
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.
@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]
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.
@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]
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
@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.
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
[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).
I did not know that. Thank you!
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] = …