Typesafe Activator

Slick Multi-DB Patterns

Slick Multi-DB Patterns

May 15, 2014
slick scala

This tutorial will show you how to write Slick applications that can use different database systems and how to use custom database functions in Slick queries.

How to get "Slick Multi-DB Patterns" on your computer

There are several ways to get this template.

Option 1: Choose slick-multidb in the Typesafe Activator UI.

Already have Typesafe Activator (get it here)? Launch the UI then search for slick-multidb in the list of templates.

Option 2: Download the slick-multidb project as a zip archive

If you haven't installed Activator, you can get the code by downloading the template bundle for slick-multidb.

  1. Download the Template Bundle for "Slick Multi-DB Patterns"
  2. Extract the downloaded zip file to your system
  3. The bundle includes a small bootstrap script that can start Activator. To start Typesafe Activator's UI:

    In your File Explorer, navigate into the directory that the template was extracted to, right-click on the file named "activator.bat", then select "Open", and if prompted with a warning, click to continue:

    Or from a command line:

     C:\Users\typesafe\slick-multidb> activator ui 
    This will start Typesafe Activator and open this template in your browser.

Option 3: Create a slick-multidb project from the command line

If you have Typesafe Activator, use its command line mode to create a new project from this template. Type activator new PROJECTNAME slick-multidb on the command line.

Option 4: View the template source

The creator of this template maintains it at https://github.com/typesafehub/activator-slick-multidb#master.

Option 5: Preview the tutorial below

We've included the text of this template's tutorial below, but it may work better if you view it inside Activator on your computer. Activator tutorials are often designed to be interactive.

Preview the tutorial


If you have gone through basic Slick tutorials like the Hello Slick template, you probably noticed that they import features directly from database drivers, as in:

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

This is the easiest way to use Slick but it makes it impossible to abstract over database drivers.

In many applications you want to be able to target different database systems, for example for running unit tests against an embedded database but using an enterprise-scale database server for integration tests and production. This tutorial will show you how to design your application to enable these use cases.

A DAO Class

We will start with a simple DAO (data access object) class, DAO.scala. It contains some database-related definitions (for a PROPS table that acts as a simple key/value store) and methods (for storing and reading entries).

The class is parameterized by a Slick driver of type JdbcProfile and imports all query language features from this driver's simple object:

class DAO(val driver: JdbcProfile) {
        import driver.simple._

Slick has multiple profiles but in most cases you will want to use JdbcProfile which contains all the features that you also get when importing directly from one of Slick's standard drivers.

Queries Outside of The DAO

Outside of the DAO class, you can still refer to its driver and the other features, but you have to get the imports from the driver in order to work with queries. This can be seen in DAOHelper.scala. It defines a new method restrictKey which we could have also put directly into the DAO class.

To gain access to all the driver's features, we parameterize the DAOHelper with the DAO and import from its driver:

class DAOHelper(val d: DAO) {
  import dao.driver.simple._

Note the use of the type projection DAO#Props in the definition of restrictKey:

def restrictKey(s: String,
      q: Query[DAO#Props, _]) = ...

This points to the Props type coming from any DAO instance. This is less type-safe then using a path-dependent type like dao.Props but generally easier to use. You still need to ensure not to mix drivers when you do this.

Running The App

We are using the DAO and DAOHelper in MultiDBExample.scala. You can see the output of this app in Run.

The run method is parameterized with both, a Slick driver and a matching JDBC Database:

def run(dao: DAO, db: Database)

Since we don't have the convenience of a single driver's simple._ import at this point, we need to import the Database and Session types directly from JdbcBackend:

import scala.slick.jdbc.JdbcBackend.{Database, Session}

In the body of MultiDBExample, we create two DAO instances with matching Database objects in order to run the same code on both, H2 and SQLite.

The Cake Pattern

In more complex designs where you want to split up the database code into separate modules that deal with different database entities, but still have dependencies between these modules, you can use the Cake Pattern to structure your code.

We are doing this here in a new app MultiDBCakeExample.scala. When you switch to the Run page, you can choose to start this instead of the default app.

From the point of view of this main app, the new approach looks exactly like the previous one: You create a DAL (data access layer) object with a Slick Driver, and use it together with a matching Database.


The most basic slice of the cake is the DriverComponent. It provides a JdbcProfile-based driver which is kept abstract at this point:

val driver: JdbcProfile


Through the use of a self-type, the PictureComponent requires a DriverComponent to me mixed in, so that it can import the query language features from the driver:

trait PictureComponent {
        this: DriverComponent =>
  import driver.simple._

Using the imported features, PictureComponent provides definitions and methods for working with Picture objects in the database.


UserComponent does the same for User entities. In addition to DriverComponent it also requires PictureComponent:

trait UserComponent {
        this: DriverComponent
        with PictureComponent =>
  import driver.simple._

The PictureComponent dependency allows UserComponent to insert a new Picture when needed:

val pic =
  if(user.picture.id.isEmpty) insert(user.picture)
  else user.picture

Baking The Cake

We put all slices of the cake together in DAL.scala. The DAL class inherits from all components and adds the missing driver through a constructor val:

class DAL(val driver: JdbcProfile)
      extends UserComponent
         with PictureComponent
         with DriverComponent {

This is also a good place to add functionality that affects all components, like the create method:

def create(implicit session: Session) =
  (users.ddl ++ pictures.ddl).create

Custom Database Functions

Sometimes you need to use a (possibly database-specific) function that is not provided out of the box by Slick. For scalar functions (i.e. functions which operate on single-column values, but not aggregation functions) this is very easy to do, as shown in CallNativeDBFunction.scala.

H2 has a day_of_week() function which extracts the day of week from a timestamp value. We lift it to a Slick SimpleFunction which returns a Column[Int]. For functions with zero to three arguments, you can use the provided factory methods, as shown in the definition of dayOfWeek:

val dayOfWeek =
  SimpleFunction.unary[Date, Int]("day_of_week")

For other arities you can get a generic untyped SimpleFunction and wrap that in a method which defines the correct parameter types, as shown in dayOfWeek2 (untyped, varargs-based parameters) and dayOfWeek3 (wrapper method):

val dayOfWeek2 =

def dayOfWeek3(c: Column[Date]) =

The app then goes on to use dayOfWeek in a query, just like a built-in function. You can see the result by switching to the Run page and selecting CallNativeDBFunction as the main class.

Next Steps

Check out the full Slick manual and API docs.

You can also find more Slick templates, contributed by both, the Slick team at Typesafe and the community, right here in Activator.

comments powered by Disqus