Thoughts on programming, homebrewing, and life, by Patrick Kaeding

Often when designing a database-driven application, you will want flexibility as to which database you want to connect to. You will certainly want different databases for production, test, and development environments. You might even want different database engines for different environments. For example, you might use an H2 database in your local development environment, and Postgres in test and production. Or perhaps you are writing an application that will be used in many environments, and you need to support Postgres, MySQL, DB2, Oracle, etc. The usual answer to this problem is dependency injection, and while there are frameworks for this, in many cases, you can accomplish these goals using just the language features of Scala.

Most tutorials (that I have found, anyway) for using Typesafe Slick assume that you are picking one database engine and sticking with it. You need to import implicits specific to the driver you are using. You can easily specify different connection URIs at runtime, but you need to commit to a driver throughout your application.

Here, I will present an alternative that allows you to specify your driver in one place, and have various classes of your database interface code all agnostic to the particular driver you are using. There is no runtime switching of the driver, and (almost) everything is type checked at compile time.

Schema Definition

The Slick docs start with selecting a database driver before doing anything else:

// Use H2Driver to connect to an H2 database
import scala.slick.driver.H2Driver.simple._

But of course, we don’t want to do that. So, here is a schema definition class that only assumes that we will be using some kind of scala.slick.driver.JdbcDriver:

import slick.driver._
class Tables(val profile: JdbcProfile) {
  import profile.simple._
  
  def ddl: profile.SchemaDescription = names.ddl

  class PersonTable(tag: Tag) extends Table[(Long, String)](tag, "person") {
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    
    def * = (id, name)
  }
  val names = TableQuery[PersonTable]
}

Note that this gets the implicits from the JdbcProfile that is passed in. This allows us to pass in the profile when we know what it is, but we don’t have to worry about it here.

DAO layer

Next, lets add some DAO code that uses this new table:

class PersonDao(t: Tables) {
  import t.profile.simple._
  def makePerson(name: String)(implicit sess: Session): Long =
    (t.names returning t.names.map(_.id)) += (-1, name)
}

Again, we just import the implicits needed for the Slick DSL from the profile that was given to the schema definition.

Finally, we bind the database driver

Up until now, we have been agnostic as to which database driver we use. At some point, though, you need to choose one. Here is a simple Main class that does that with a hardcoded value. You can imagine some logic that reads a config file, or some other mechanism to do this, though.

import slick.driver._

object Main extends App {
  import scala.slick.driver.H2Driver.simple._
  import slick.driver.H2Driver.profile
  val db = Database.forURL("jdbc:h2:db", driver="org.h2.Driver")
  db.withSession { implicit s => 
    val foo = new PersonDao(new Tables(profile))
    println(foo.makePerson("george"))
    println(foo.makePerson("sally"))
    println(foo.makePerson("becky"))
    println(foo.makePerson("barry"))
  }
}

Only lines 4-6 in the above snippet commit us to a specific database. They could be swapped out for a different driver, with a different profile, and a different connection string, and our schema definition and DAO code could remain unchanged.

While coming up with this, I did find one case where the compiler got confused, and I needed to cast something, though. This was while adding a function to recreate the DDL:

  db.withSession { implicit s => 
    val ddl: profile.SchemaDescription = new Tables(profile).ddl.asInstanceOf[profile.SchemaDescription]
    import profile.Implicit._
    ddl.drop
    ddl.create 
  }

From looking at the code, you can tell that the Tables.ddl object should be of the type profile.SchemaDescription (for whatever type profile you are passing in), but the cast was necessary to make the compiler happy.

Caveats to this approach

• You are stuck with the lowest common denominator. Some features may compile, but then fail at runtime if you use a feature that one driver doesn’t support. Likewise, if one driver presents a non-standard feature, you may not be able to use it.
• Be sure to run your test suite (at least the part that exercises your db interface) against all drivers you intend to support. If your database functions prove to behave identically whether run on an H2 in-memory database or a real Postgres DB, you can then test the rest of your code (that just calls your db interface layer) against the H2 in-memory db, and possibly achieve faster test runs.