Environment-dependent database drivers with Slick

When we started to you use Slick for the first time, we had no clue on how to set up our code for multiple database types in our Play! application. In the development phase, our main goal was to run the application quickly with no interference with other developers’ code and development velocity, so we decided to use the in-memory database. We are dedicated towards Continuous Integration, so before every merge we run all the tests to check if the new code breaks the app. The QA environment should have the same properties as the production and we were aiming for the Azure SQL database. These two database types (in-memory and Azure SQL) use different drivers to operate. To keep our code consistent we only change the application.conf via CI Tools while we switch between stages of development.

We use Play’s default DB for establishing the connection to the database. In the DAO – data access object – classes, you have to import a specific driver to operate well. When you import multiple drivers, like

import com.typesafe.slick.driver.ms.SQLServerDriver.api._ and import slick.driver.H2Driver.api._

the DAO will only use one of those. This is really problematic, because those two objects contain the exact same methods with different implementation.

We needed to map the stages of development to the type of databases we were using.

text

Solution A – Mapping with application.conf on ‘default’ database

slick.dbs.default = {
  driver = "slick.driver.H2Driver$"
  db {
    url = "jdbc:h2:mem:play;MODE=MSSQLServer;DB_CLOSE_DELAY=-1"
    driver = org.h2.Driver
    connectionTimeout = 30 second
    connectionTestQuery = "SELECT 1"
    connectionPool = "disabled"
    keepAliveConnection = true
  }
}

Have the development stage in a configuration file so you can easily change it, but the development stage value should always be synchronized with your database config.

# stages can be: ["dev", "test", "qa", "prod"]
development.stage = "dev"

Now that we have the stages and the databases, the mapping is easy:

import com.typesafe.slick.driver.ms.SQLServerDriver
import slick.driver.H2Driver
  
trait MultiDatabase {
    lazy val profile = {
        Play.current.configuration.getString("development.stage") match {
            case Some("dev") | Some("test") => H2Driver
            case Some("qa") | Some("prod") =>  SQLServerDriver
            case _ => throw FatalException("message")
        }
    }
}

Using it is quite easy:

class ExampleDAO with MultiDatabase {
  
    import profile.api._
    //provide your implementation here
}

Solution B – Using Database config

Instead of using the default value for database config, you can easily create configs for each environment (dev, test, qa, prod) that also represent the required database driver. In this case, you don’t need separate configuration files, you can put all in one. So the previous example would look more like:

dev {
  driver = "slick.driver.H2Driver$"
  db {
    url = "jdbc:h2:mem:play;MODE=MSSQLServer;DB_CLOSE_DELAY=-1"
    driver = org.h2.Driver
    connectionTimeout = 30 second
    connectionTestQuery = "SELECT 1"
    connectionPool = "disabled"
    keepAliveConnection = true
  }
}
qa {
  driver = "com.typesafe.slick.driver.ms.SQLServerDriver$"
  db {
    url = "jdbc:sqlserver://host:port"
    driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
    connectionTimeout = 30 second
    connectionTestQuery = "SELECT 1"
    connectionPool = "disabled"
    keepAliveConnection = true
  }
}
test {
  driver = "slick.driver.H2Driver$"
  db {
    url = "jdbc:h2:mem:play;MODE=MSSQLServer;DB_CLOSE_DELAY=-1"
    driver = org.h2.Driver
    connectionTimeout = 30 second
    connectionTestQuery = "SELECT 1"
    connectionPool = "disabled"
    keepAliveConnection = true
  }
}
test {
  driver = "slick.driver.H2Driver$"
  db {
    url = "jdbc:h2:mem:play;MODE=MSSQLServer;DB_CLOSE_DELAY=-1"
    driver = org.h2.Driver
    connectionTimeout = 30 second
    connectionTestQuery = "SELECT 1"
    connectionPool = "disabled"
    keepAliveConnection = true
  }
}
prod {
  driver = "com.typesafe.slick.driver.ms.SQLServerDriver$"
  db {
    url = "jdbc:sqlserver://host:port"
    driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
    connectionTimeout = 30 second
    connectionTestQuery = "SELECT 1"
    connectionPool = "disabled"
    keepAliveConnection = true
  }
}

The mapping is still necessary. So our previous MultiDatabase trait would look like the following code, but you can use it the same way:

trait MultiDatabase {
    lazy val profile = {
        Play.current.configuration.getString("development.stage") match {
            case Some(devStage: String) if List("dev", "test", "qa", "prod") contains devStage => DatabaseConfig.forConfig[JdbcProfile](devStage).diver
            case _ => throw FatalException("message")
        }
    }
}

Putting all the database configs in one file may be cause for concern. You can accidentally run your app in prod mode while your development stage should be ‘in development’. Deploying app on-premise to your customer can cause security issues as well, for example by providing information about your infrastructure, but deploying the app to Azure can solve this problem.

Further Opportunities

While you are using the mapping, you can add extra features to your ‘profile’ like using the specific JodaTime for your database.

import com.github.tototoshi.slick.{GenericJodaSupport, H2JodaSupport, SQLServerJodaSupport}
import com.typesafe.slick.driver.ms.SQLServerDriver
import slick.driver.H2Driver
import slick.driver.JdbcProfile
  
case class JdbcSupport(profile: JdbcProfile, jodaSupport: GenericJodaSupport)
 
trait MultiDatabase {
    lazy val jdbcSupport = {
        Play.current.configuration.getString("development.stage") match {
            case Some("dev") | Some("test") => JdbcSupport(H2Driver,H2JodaSupport)
            case Some("qa") | Some("prod") =>  JdbcSupport(SQLServerDriver, SQLServerJodaSupport)
            case _ => throw FatalException("message")
        }
    }
}

Summary

You need to map your Driver to your development stage no matter what. You can mix the solutions and change them according to your architecture. You can use them with the cake pattern, dependency injection and passing your JdbcProfile as a class member.

I hope you’ve found this article useful and feel free to contact me if you have any questions.

P.S.: Scala is awesome. But you should know that already. ;)

member photo

Never underestimate the capabilities of a lazy person.

Latest post by Barnabás Oláh

Deploying Your Play! Framework App to Azure as a PaaS