Database Configuration

You can tell Slick how to connect to the JDBC database of your choice by creating a Database object, which encapsulates the information. There are several factory methods on slick.jdbc.JdbcBackend.Database that you can use depending on what connection data you have available.

Using Typesafe Config

The preferred way to configure database connections is through Typesafe Config in your application.conf, which is also used by Play and Akka for their configuration.

Such a configuration can be loaded with Database.forConfig (see the API documentation of this method for details on the configuration parameters).

copysourceval db = Database.forConfig("mydb")

Examples

PostgreSQL

copysourcemydb = {
  connectionPool = "HikariCP" //use HikariCP for our connection pool
  dataSourceClass = "org.postgresql.ds.PGSimpleDataSource" //Simple datasource with no connection pooling. The connection pool has already been specified with HikariCP.
  properties = {
    serverName = "localhost"
    portNumber = "5432"
    databaseName = "mydb"
    user = "postgres"
    password = "postgres"
  }
  numThreads = 10
}

When specifying a dataSourceClass you will need to bring in the sbt dependency for that class. The following is an example for the org.postgresql.ds.PGSimpleDataSource data source class:

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.5.1",
  "org.slf4j" % "slf4j-nop" % "1.7.26",
  "com.typesafe.slick" %% "slick-hikaricp" % "3.5.1",
  "org.postgresql" % "postgresql" % "42.2.5" //org.postgresql.ds.PGSimpleDataSource dependency
)

Note, some examples on the internet point to the 9.4.X versions of the PostgreSQL JDBC drivers. Those examples are incorrect and many years out of date. The 42.X development line has been the main line of development since February 2017. As noted on the Postgres site, “Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using.” See https://jdbc.postgresql.org/download.html for more information about when you may want to use the 9.4 line.

MySQL

Very simple example without a connection pool and using the driver directly:

copysourcemydb = {
  driver = "com.mysql.cj.jdbc.Driver",
  url = "jdbc:mysql://127.0.0.1:3306/mydb?serverTimezone=UTC",
  user = "user",
  password = "pass",
  connectionPool = disabled
}

To use the MySQL driver, the following library dependency needs to be configured via SBT:

libraryDependencies += "com.mysql" % "mysql-connector-j" % "8.0.33"

Generic JDBC

Sometimes a database system provides a JDBC driver, but no specific profile for its dialect has been implemented for Slick, yet. It is still possible to connect to such a database with not much effort and use the generic JDBC feature set.

Here is an example for setting up a connection to a Databricks DB using the Databricks JDBC driver, depended on via SBT:

libraryDependencies += "com.databricks" % "databricks-jdbc" % "2.6.29"

To configure the connection a profile implementation is required. It is sufficient to extend the JdbcProfile trait with an empty object.

copysourcepackage com.typesafe.slick.docs

import slick.jdbc.{JdbcActionComponent, JdbcProfile}

object GenericJdbcProfile extends JdbcProfile with JdbcActionComponent.MultipleRowsPerStatementSupport

Now this profile can be used in the configuration.

copysourcedatabricks_db {
  profile = "com.typesafe.slick.docs.GenericJdbcProfile$"
  db {
    driver = "com.databricks.client.jdbc.Driver"
    url = "jdbc:databricks://DATABRICKS_HOST:443;HttpPath=/sql/1.0/endpoints/XXXXXXXXX;TransportMode=http;SSL=1"
    PWD = "????????????"
    UID = "token"
    AuthMech = "3"
  }
}

Connection parameters for database systems can differ significantly. Please refer to the respective documentation.

Using a JDBC URL

You can pass a JDBC URL to forURL. (see your database’s JDBC driver’s documentation for the correct URL syntax).

copysourceval db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1",
  driver="org.h2.Driver")

Here we are connecting to a new, empty, in-memory H2 database called test1 and keep it resident until the JVM ends (DB_CLOSE_DELAY=-1, which is H2 specific).

Using a Database URL

A Database URL, a platform independent URL in the form vendor://user:password@host:port/db, is often provided by platforms such as Heroku. You can use a Database URL in Typesafe Config as shown here:

copysourcedatabaseUrl {
  dataSourceClass = "slick.jdbc.DatabaseUrlDataSource"
  properties = {
    driver = "org.postgresql.Driver"
    url = "postgres://user:pass@host/dbname"
  }
}

By default, the data source will use the value of the DATABASE_URL environment variable. Thus you may omit the url property if the DATABASE_URL environment variable is set. You may also define a custom environment variable with standard Typesafe Config syntax, such as ${?MYSQL_DATABASE_URL}.

Or you may pass a DatabaseUrlDataSource object to forDataSource .

copysourceval db = Database.forDataSource(dataSource: slick.jdbc.DatabaseUrlDataSource,
  None)

Using a DataSource

You can pass a DataSource object to forDataSource. If you got it from the connection pool of your application framework, this plugs the pool into Slick. If the pool has a size limit, the correct size should always be specified.

copysourceval db = Database.forDataSource(dataSource: javax.sql.DataSource,
  Some(size: Int))

Using a JNDI Name

If you are using JNDI you can pass a JNDI name to forName under which a DataSource object can be looked up. If the data source has a limit in the number of connections it can provide, the correct size should always be specified.

copysourceval db = Database.forName(jndiName: String, Some(size: Int))

Database thread pool

Every Database contains an AsyncExecutor that manages the thread pool for asynchronous execution of Database I/O Actions. Its size is the main parameter to tune for the best performance of the Database object. It should be set to the value that you would use for the size of the connection pool in a traditional, blocking application (see About Pool Sizing in the HikariCP documentation for further information). When using Database.forConfig, the thread pool is configured directly in the external configuration file together with the connection parameters. If you use any other factory method to get a Database, you can either use a default configuration or specify a custom AsyncExecutor:

copysourceval db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1",
  driver="org.h2.Driver",
  executor = AsyncExecutor("test1", numThreads=10, queueSize=1000))

Connection pools

When using a connection pool (which is always recommended in production environments) the minimum size of the connection pool should also be set to at least the same size. The maximum size of the connection pool can be set much higher than in a blocking application. Any connections beyond the size of the thread pool will only be used when other connections are required to keep a database session open (e.g. while waiting for the result from an asynchronous computation in the middle of a transaction) but are not actively doing any work on the database.

Note that reasonable defaults for the connection pool sizes are calculated from the thread pool size when using Database.forConfig.

Slick uses prepared statements wherever possible but it does not cache them on its own. You should therefore enable prepared statement caching in the connection pool’s configuration.

DatabaseConfig

On top of the configuration syntax for Database, there is another layer in the form of DatabaseConfig which allows you to configure a Slick profile plus a matching Database together. This makes it easy to abstract over different kinds of database systems by simply changing a configuration file.

You can see it in action in SimpleExample.scala. First we load the DatabaseConfig and then import the Slick API from its profile:

copysourceval dc = DatabaseConfig.forConfig[JdbcProfile]("h2_dc")

// Import the JdbcProfile API from the configured profile
import dc.profile.api._

The JdbcProfile type annotation specifies the profile level whose API you get. You have to configure a profile of a matching type in the external configuration file. Since we’re using the basic forConfig method with only a path (“h2_dc”), the configuration must be located in the global application config, usually found in application.conf:

copysourceh2_dc {
  profile = "slick.jdbc.H2Profile$"
  db {
    url = "jdbc:h2:mem:test1"
    driver = org.h2.Driver
    connectionPool = disabled
    keepAliveConnection = true
  }
}

You can use different database systems in your application by either switching out or overriding the application config (e.g. different application.conf files for development and production) or by passing a config path into the application. This way of implementing multi-database support is also used when building a Play app with Slick.

Other Multi-DB Patterns

Since its addition in Slick 3.0 DatabaseConfig (see above) is the recommended solution. More complex scenarios (for example, where you need to map custom functions differently for different database systems, or where you cannot use the simple application.conf syntax) may require abstracting over databases in Scala code. The following sections explain two different ways of accomplishing this.

A DAO Class

We 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 concrete JdbcProfile and it imports all API features from this profile’s api object:

copysourceclass DAO(val profile: JdbcProfile) {
  // Import the Scala API from the profile
  import profile.api._

Slick has multiple abstract 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 concrete profiles for JDBC databases.

Outside of the DAO class, you can still refer to its profile and the other features, but you have to get the imports from the profile 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 of the profile’s features, we parameterize the DAOHelper with the DAO and import from its profile:

copysourceclass DAOHelper(val dao: DAO) {
  import dao.profile.api._

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

Note the use of the type projection DAO#Props in the definition of restrictKey. This points to the Props type coming from any DAO instance. This is less type-safe than 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.

We are using the DAO and DAOHelper in MultiDBExample.scala. The run method is parameterized with both, a Slick profile and a matching JDBC Database:

copysourcedef run(dao: DAO, db: Database): Future[Unit] = {
  val h = new DAOHelper(dao)
  println("Using profile " + dao.profile)

Since we don’t have the convenience of a single profile’s api._ import at this point, we need to import the Database and DBIO types directly:

copysourceimport slick.dbio.DBIO
import slick.jdbc.JdbcBackend.Database

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:

copysourceval f = {
  val h2db = Database.forConfig("h2")
  run(new DAO(H2Profile), h2db).andThen { case _ => h2db.close }
}.flatMap { _ =>
  val sqlitedb = Database.forConfig("sqlite")
  run(new DAO(SQLiteProfile), sqlitedb).andThen { case _ => sqlitedb.close }
}

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 the app MultiDBCakeExample.scala. 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 profile, and use it together with a matching Database.

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

copysourceimport slick.jdbc.JdbcProfile

/** The slice of the cake which provides the Slick profile */
trait ProfileComponent {
  val profile: JdbcProfile
}

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

copysourcetrait PictureComponent { this: ProfileComponent =>
  import profile.api._

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 ProfileComponent it also requires PictureComponent:

copysourcetrait UserComponent { this: ProfileComponent with PictureComponent =>
  import profile.api._

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

copysourcepic <-
  if(user.picture.id.isEmpty) insert(user.picture)
  else DBIO.successful(user.picture)

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

copysourceimport slick.jdbc.JdbcProfile

/** The Data Access Layer contains all components and a profile */
class DAL(val profile: JdbcProfile)
      extends UserComponent with PictureComponent with ProfileComponent {
  import profile.api._

  def create =
    (users.schema ++ pictures.schema).create
}

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