Databases & Actions

Anything that you can execute on a database, whether it is a getting the result of a query (“myQuery.result”), creating a table (“myTable.schema.create”), inserting data (“myTable += item”) or something else, is an instance of Action, parameterized by the result type it will produce when you execute it.

Actions can be combined with several different combinators (see the Action class and object for details), but they will always be executed strictly sequentially and (at least conceptually) in a single database session.

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 scala.slick.jdbc.JdbcBackend.Database that you can use depending on what connection data you have available.

Using Typesafe Config

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

mydb = {
  dataSourceClass = org.postgresql.ds.PGSimpleDataSource
  properties = {
    databaseName = "mydb"
    user = "myuser"
    password = "secret"
  }
  numThreads = 10
}

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

val db = Database.forConfig("mydb")

Using a JDBC URL

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

val 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 DataSource

You can provide a DataSource object to forDataSource. If you got it from the connection pool of your application framework, this plugs the pool into Slick.

val db = Database.forDataSource(dataSource: javax.sql.DataSource)

Using a JNDI Name

If you are using JNDI you can provide a JNDI name to forName under which a DataSource object can be looked up.

val db = Database.forName(jndiName: String)

Database thread pool

Every Database contains an AsyncExecutor that manages the thread pool for asynchronous execution of database 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:

val 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.

Executing Actions

Actions can be executed either with the goal of producing a fully materialized result or streaming data back from the database.

Materialized

You can use run to execute an Action on a Database and produce a materialized result. This can be, for example, a scalar query result (“myTable.length.result”), a collection-valued query result (“myTable.to[Set].result”), or any other Action. Every Action supports this mode of execution.

Execution of the Action starts when run is called, and the materialized result is returned as a Future which is completed asynchronously as soon as the result is available:

val q = for (c <- coffees) yield c.name
val a = q.result
val f: Future[Seq[String]] = db.run(a)

f.onSuccess { case s => println(s"Result: $s") }

Streaming

Collection-valued queries also support streaming results. In this case, the actual collection type is ignored and elements are streamed directly from the result set through a Reactive Streams Publisher, which can be processed and consumed by Akka Streams.

Execution of the Action does not start until a Subscriber is attached to the stream. Only a single Subscriber is supported, and any further attempts to subscribe again will fail. Stream elements are signaled as soon as they become available in the streaming part of the Action. The end of the stream is signaled only after the entire Action has completed. For example, when streaming inside a transaction and all elements have been delivered successfully, the stream can still fail afterwards if the transaction cannot be committed.

val q = for (c <- coffees) yield c.name
val a = q.result
val p: DatabasePublisher[String] = db.stream(a)

// .foreach is a convenience method on DatabasePublisher.
// Use Akka Streams for more elaborate stream processing.
p.foreach { s => println(s"Element: $s") }

When streaming a JDBC result set, the next result page will be buffered in the background if the Subscriber is not ready to receive more data, but all elements are signaled synchronously and the result set is not advanced before synchronous processing is finished. This allows synchronous callbacks to low-level JDBC values like Blob which depend on the state of the result set. The convenience method mapResult is provided for this purpose:

val q = for (c <- coffees) yield c.image
val a = q.result
val p1: DatabasePublisher[Blob] = db.stream(a)
val p2: DatabasePublisher[Array[Byte]] = p1.mapResult { b =>
  b.getBytes(0, b.length().toInt)
}

Transactions and Pinned Sessions

When executing an Action that is composed of several smaller Actions, Slick acquires sessions from the connection pool and releases them again as needed so that a session is not kept in use unnecessarily while waiting for the result from a non-database computation (e.g. the function passed to flatMap that determines the next Action to run). All Action combinators which combine two database Actions without any non-database computations in between (e.g. andThen or zip) can fuse these Actions for more efficient execution, with the side-effect that the fused Action runs inside a single session. You can use withPinnedSession to force the use of a single session, keeping the existing session open even when waiting for non-database computations.

There is a similar combinator transactionally to force the use of a transaction. This guarantees that the entire Action that is executed will either succeed or fail atomically. Note that failure is not guaranteed to be atomic at the level of an individual Action that is wrapped with .transactionally, so you should not apply error recovery combinators at that point.

val a = (for {
  ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
  _ <- Action.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally

val f: Future[Unit] = db.run(a)

JDBC Interoperability

In order to drop down to the JDBC level for functionality that is not available in Slick, you can use a SimpleAction which is run on a database thread and gets access to the JDBC Connection:

val getAutoCommit = SimpleAction[Boolean](_.session.conn.getAutoCommit)