Slick and Play

In building a few applications with Slick and Play, I was very pleased by how easily Slick integrates with Play: the framework makes no assumptions at all about what kind of database interaction you’re going to use, so there’s nothing to bypass, you just get slick on the classpath and use Slick however you’d like to.

Here’s an example from one of my applications that’s using Slick:

import sbt._
import sbt._
import play.Project._

object ApplicationBuild extends Build {

  val appName         = "example"
  val appVersion      = "1.0"

  val appDependencies = Seq("com.typesafe.slick" %% "slick" % "1.0.0",
    "org.scalatest" %% "scalatest" % "2.0.M5b" % "test",
    "com.typesafe" %% "scalalogging-slf4j" % "1.0.1",
    "com.jolbox" % "bonecp" % "0.7.1.RELEASE",
    "com.softwaremill.macwire" %% "core" % "0.1"
  )
  
  val main = play.Project(appName, appVersion, appDependencies).settings(
    sbt.Keys.fork in Test := false
  )
}

Two small areas were not immediately obvious, though, and took a bit of tinkering. Slick does not prescribe any particular form of connection pooling, so you must add your own. This is a must for any kind of serious production application, IMO, but it’s quite easy to do.

The second issue was database-independance: Although Slick is not database-specific, the drivers you use to do lifted mapping are, unless you take a few simple steps (one of which has a small Gotcha) to ensure you’re not tied to a specific database.

As I like to use Hypersonic SQL for my testing database, but often select Postgres for my deploy database, I switch databases a lot.

Below I’ll describe how I handled both these issues.

Connection Pooling

In one of our apps that did a lot of small database operations we found a six-times performance boost just by adding connection pooling, so it’s a must for production deploys, I think.

The preferred choice for connection pooling in Play appears to be BoneCP, although I’ve successfully used C3PO as well.

For BoneCP, the setup of a data source is pretty straightforward:

lazy val datasource = {
    val dbSuffix = System.getProperty("db", "mem:testdb")
    val dbUrl = s"jdbc:hsqldb:$dbSuffix"

    import com.jolbox.bonecp.BoneCPDataSource

    val ds = new BoneCPDataSource
    ds.setDriverClass("org.hsqldb.jdbc.JDBCDriver")
    ds.setJdbcUrl(dbUrl)
    ds.setAcquireIncrement(5)
    ds
  }

There are many other options you can configure, and the BoneCP documentation enumerates them.

Multiple Database Types

Allowing Slick to easy switch between databases is slightly trickier (at the moment in any case), but not that hard.

Here’s a simple class that accesses Customer records, defined in a Schema class, with DTO object called CustomerDTO (the case class corresponding to a row in the database table).

package infrastructure.datastore
import scala.slick.jdbc.{GetResult, StaticQuery}
import scala.slick.jdbc.StaticQuery.interpolation
import scala.slick.driver.ExtendedDriver
import slick.session.{Session, Database}
import Schema._
import java.util.UUID
import javax.sql.DataSource

class CustomerDataStore(driver: ExtendedDriver, dataSource: DataSource) {
  import driver.simple._
  import driver.DeleteInvoker
  implicit val session = database.createSession
  
 implicit def session(dataSource: DataSource): Session = Database.forDataSource(dataSource).createSession

  def insert(dto: CustomerDTO) = Customers.insert(dto)

  def findByCustomerCode(code: String): List[CustomerDTO] = Query(Customers).filter(_.customerCode === code.bind).list

  def clear(implicit session: Session) { new DeleteInvoker(Query(Customers)).delete }
}

In the above code the only tricky bit is the definition of “clear” – it must use the DeleteInvoker due to an oddity in the way Slick’s implicits work for the database-independent drivers – if you were using only a single driver, the clear method is slightly simpler.

Now at startup, we simply pass the appropriate driver to the CustomerDataStore class when it’s instantiated. We can specify the type of database using a system property or some other config value (a string “dbType” in the following example), like so:

private def driver: ExtendedDriver =
  dbType match {
    case "hsql" => HsqldbDriver
    case "mssql" => SQLServerDriver
    case _ => throw new IllegalArgumentException("dbType property must be either hsql or mssql")
  }

Then we hand the “driver” and our previously-defined “datasource” as the constructor parameters to our CustomerDataStore class:

lazy val customerDataStore = new CustomerDataStore(driver, dataSource)

Of course, if we’re using something like MacWire to do our dependency injection, this can be simplified to

lazy val customerDataStore = wire[CustomerDataStore]

With these couple of tricks, you’ve got Slick wired up to use any database type you need (that it supports, which is quite a few), and you get a substantial boost in overall performance with the use of connection pooling!

Principles and Practices

Tired of the Software Development Grind? Know it can be done better? Check out my book (almost finished!): Principles and Practices of Software Craftsmanship or sign up for my Craftsmanship Dispatches newsletter.

Published: July 11 2013