Database

Working with a database in Slick involves two steps:

1. Build a DatabaseConfig[P] — this describes which database profile to use and how to connect. Several construction methods are available depending on how your connection details are provided.
2. Open a Database value from that config using one of Slick’s three API facades, which adapt Slick’s execution model to your chosen effect system:

• slick.cats.Database (Cats Effect + FS2)
• slick.future.Database (Scala Future + Reactive Streams)
• slick.zio.Database (ZIO + ZStream)

Building a DatabaseConfig

Using Typesafe Config

The easiest 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 DatabaseConfig.forProfileConfig (see the API documentation of this method for details on the configuration parameters). It returns a DatabaseConfig value that you can open with any of the three facades.

copysourceval dc = DatabaseConfig.forProfileConfig(H2Profile, "mydb")
val db: Resource[IO, Database] = Database.resource(dc)

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"
  }
  maxConnections = 10
  maxInflightActions = 20
  queueSize = 1000
  # Optional timeout controls for waiting phases (not JDBC execution time):
  # inflightAdmissionTimeout = 2s
  # connectionAcquireTimeout = 5s
}

The dataSourceClass is a javax.sql.DataSource class provided by the JDBC driver; a Slick profile is not a DataSource. 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" % "4.0.0-pre.199.389bbbce",
  "org.slf4j" % "slf4j-nop" % "2.0.20",
  "com.typesafe.slick" %% "slick-hikaricp" % "4.0.0-pre.199.389bbbce",
  "org.postgresql" % "postgresql" % "42.7.14" //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. Old drivers fail with “The authentication type 10 is not supported” because they do not support the scram-sha-256 password authentication that PostgreSQL uses by default.

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"

SQLite

SQLite has no dedicated date and time storage classes. The sqlite-jdbc driver stores java.sql.Date, java.sql.Time and java.sql.Timestamp values as INTEGER numbers of milliseconds since the Unix epoch (1970-01-01 00:00:00 UTC). Slick uses the same representation for its SQL literals and maps LocalDate, LocalDateTime and Instant through these types, so they are stored in the same way. LocalDate and LocalDateTime values are converted using the JVM’s default time zone, so for example LocalDate.of(2020, 1, 2) is stored as midnight of that day in that zone. LocalTime, OffsetTime, OffsetDateTime and ZonedDateTime are stored as ISO-8601 TEXT instead (see Date and Time types).

SQLite’s own date and time functions (date, time, datetime, julianday, strftime, unixepoch) expect ISO-8601 text, Julian day numbers, or Unix time in seconds, so they cannot use the millisecond values directly. Divide by 1000 and add the 'unixepoch' modifier instead, for example datetime(created_at / 1000, 'unixepoch'), which yields the UTC date and time as text.

If you want date and time values to be stored as seconds, which SQLite’s functions understand directly (datetime(created_at, 'unixepoch')), set the sqlite-jdbc connection property date_precision to SECONDS in the properties block of the database configuration. This drops the sub-second part of the values. The driver also has a date_class property (INTEGER (the default), TEXT or REAL) to store the values as text (in the format given by date_string_format, by default yyyy-MM-dd HH:mm:ss.SSS) or as Julian day numbers.

copysourcesqlitedb = {
  driver = "org.sqlite.JDBC"
  url = "jdbc:sqlite:mydb.db"
  connectionPool = disabled
  properties = {
    # Store java.sql.Date/Time/Timestamp (and the java.time types mapped to them)
    # as seconds instead of milliseconds since the epoch
    date_precision = "SECONDS"
  }
}

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 dc = DatabaseConfig.forURL(H2Profile, "jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver="org.h2.Driver")
val db = Database.resource(dc)

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

DatabaseConfig.forURL(H2Profile, url, driver = "org.h2.Driver")

val p: profile.type = profile
val dc: DatabaseConfig[p.type] = DatabaseConfig.forURL(p, url, null, null, null, driver, false, ClassLoaderUtil.defaultClassLoader)
dc.profile.backend.makeDatabase[IO](dc).unsafeRunSync()

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 dc = DatabaseConfig.forDataSource(H2Profile, dataSource: slick.jdbc.DatabaseUrlDataSource)
val db = Database.resource(dc)

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, pass the size via .withControls:

copysourceval dc = DatabaseConfig.forDataSource(H2Profile, dataSource: javax.sql.DataSource)
  .withControls(ControlsConfig(maxConnections = size: Int))
val db: Resource[IO, Database] = Database.resource(dc)

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, pass the size via .withControls:

copysourceval dc = DatabaseConfig.forName(H2Profile, jndiName: String)
  .withControls(ControlsConfig(maxConnections = size: Int))
val db = Database.resource(dc)

Opening a Database

Once you have a DatabaseConfig, open a Database using the facade for your effect system. All three facades expose the same operations: run for materialized query execution, stream for streaming, and close for unmanaged lifecycles.

Cats Effect / FS2 (slick.cats.Database)

The Cats facade is part of the core slick module.

Use Database.resource for managed lifecycle (recommended):

copysourceval managedDb: Resource[IO, Database] = Database.resource(dc)

If you need manual lifecycle control, use Database.make and call close() yourself:

copysourceval openedDb: IO[Database] = Database.make(dc)

Run and stream queries:

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

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

copysourceval q = for (c <- coffees) yield c.name
val a: StreamingDBIO[Seq[String], String] = q.result
val s: fs2.Stream[IO, String] = db.stream(a)

// Use FS2 combinators to process the stream:
s.evalMap(name => IO(println(s"Element: $name")))
  .compile
  .drain

Future / Reactive Streams (slick.future.Database)

Add the Future facade module:

libraryDependencies += "com.typesafe.slick" %% "slick-future" % "4.0.0-pre.199.389bbbce"

Use Database.use for managed lifecycle (recommended):

copysourceval managedResult: Future[Seq[String]] =
  Database.use(dc) { db =>
    db.run(coffees.map(_.name).result)
  }

If you need manual lifecycle control, use Database.open and call close() yourself:

copysourceval opened: Future[Database] = Database.open(dc)

Run and stream queries:

copysourceval runResult: Future[Seq[String]] =
  Database.use(dc) { db =>
    db.run(coffees.map(_.name).result)
  }

copysourceval streamResult: Future[slick.future.DatabasePublisher[String]] =
  Database.use(dc) { db =>
    Future.successful(db.stream(coffees.map(_.name).result))
  }

ZIO (slick.zio.Database)

Add the ZIO facade module:

libraryDependencies += "com.typesafe.slick" %% "slick-zio" % "4.0.0-pre.199.389bbbce"

Use Database.scoped for managed lifecycle (recommended):

copysourceval managedResult: ZIO[Scope, Throwable, Seq[String]] =
  Database.scoped(dc).flatMap { db =>
    db.run(coffees.map(_.name).result)
  }

If you need manual lifecycle control, use Database.make and call close() yourself:

copysourceval opened: Task[Database] = Database.make(dc)

Run and stream queries:

copysourceval runResult: ZIO[Scope, Throwable, Seq[String]] =
  Database.scoped(dc).flatMap { db =>
    db.run(coffees.map(_.name).result)
  }

copysourceval streamResult: ZIO[Scope, Throwable, zio.Chunk[String]] =
  Database.scoped(dc).flatMap { db =>
    db.stream(coffees.map(_.name).result).runCollect
  }

Action listeners

An ActionListener wraps the execution of every action, for example for tracing or timing. Synchronous actions combined with >>, DBIO.seq, zip, asTry and similar combinators are fused into one action, which the listener sees as one node. For db.stream, the listener wraps opening the stream, not reading the rows. By default, Slick uses a listener that writes to the slick.basic.BasicBackend.action and slick.basic.BasicBackend.timing loggers when they are enabled at debug level.

To use your own listener instead, create the database with makeDatabase on the profile’s backend and wrap it with fromCore of your facade (slick.cats.Database, slick.future.Database or slick.zio.Database). You are then responsible for closing it. ActionListener.combine(first, second) installs several listeners, the first one being the outermost; combine your listener with ActionListener.timing to keep the slick.basic.BasicBackend.timing log:

import cats.effect.{IO, Resource}
import slick.basic.ActionListener

val listener = ActionListener.combine(myListener, ActionListener.timing[IO])
val database: Resource[IO, slick.cats.Database] =
  Resource.make(dc.profile.backend.makeDatabase[IO](dc, listener).map(slick.cats.Database.fromCore))(db =>
    IO.blocking(db.close()))

Connection pools

Using a connection pool is always recommended in production environments. Slick integrates with HikariCP via the slick-hikaricp module and will use it automatically when it is on the classpath (see Getting Started for the dependency).

Set the maximum pool size to the number of concurrent database connections your database server can handle efficiently. Because Slick’s execution model is non-blocking, you do not need to add extra connections to “absorb” thread blocking — size the pool purely based on the database server’s capacity. See About Pool Sizing in the HikariCP documentation for background.

Note that reasonable defaults for the connection pool sizes are calculated automatically when using DatabaseConfig.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.

HikariCP settings

With connectionPool = "HikariCP" (the default), the pool is configured from the same config section as the database. Most keys have the same names as the HikariCP configuration properties and are passed on to HikariCP. These keys need some explanation:

• dataSourceClass or dataSourceClassName: the javax.sql.DataSource class provided by the JDBC driver, configured with properties. Without it, the pool uses driver (or driverClassName) and url (or jdbcUrl). Slick fails with a SlickException when the class is not a DataSource.
• maximumPoolSize or maxConnections (default: numThreads, or 20): the maximum pool size. Slick also limits concurrent JDBC work to the pool size. If both keys are set, maximumPoolSize is used, and if their values differ, Slick logs a warning, because a maximumPoolSize in a reference.conf file would otherwise silently override your maxConnections.
• minimumIdle or minConnections (default: maxConnections, or numThreads, or 20): the minimum number of idle connections.
• initializationFailTimeout (default: -1): what happens when the pool cannot get a first connection while starting. A positive value is the time to try (in milliseconds, or a duration such as 5s) before the pool fails to start; 1 makes the pool fail fast. With 0 the pool starts even if it cannot get a connection, and with -1 it does not try to get one. Note that Slick’s default (-1) differs from HikariCP’s (1).

Pool metrics

To register metrics or health checks on the HikariCP pool, write a JdbcDataSourceFactory object that gets the pool configuration from HikariCPJdbcDataSource.hikariConfig, sets them on it and then creates the pool:

object InstrumentedHikariCP extends JdbcDataSourceFactory {
  def forConfig(c: Config, driver: Driver, name: String, classLoader: ClassLoader): HikariCPJdbcDataSource = {
    val hconf = HikariCPJdbcDataSource.hikariConfig(c, name, classLoader)
    hconf.setMetricRegistry(myMetricRegistry) // or setMetricsTrackerFactory
    hconf.setHealthCheckRegistry(myHealthCheckRegistry)
    new HikariCPJdbcDataSource(new HikariDataSource(hconf), hconf)
  }
}

Name that object in the configuration with connectionPool = "com.example.InstrumentedHikariCP$".

Execution Concurrency Model

Slick 4 uses a two-stage admission model for db.run(...) and db.stream(...) calls:

1. Queue (queueSize): bounds how many callers are allowed to wait for admission.
2. In-flight (maxInflightActions): bounds how many DBIO chains may run concurrently.

After a call is admitted, actual JDBC work is still bounded by the connection pool size (maxConnections).

Why these two limits exist

• Queue protects the application from unbounded memory growth under overload.
• In-flight limits total concurrent action chains (including chains that are between DB steps).
• Connections limit actual simultaneous JDBC I/O.

This separation avoids a common overload pattern where too many callers pile up waiting forever, while still allowing some headroom when actions do non-DB work between JDBC steps.

Configuration keys

These keys are read from the same database config section used by DatabaseConfig.forConfig / forProfileConfig. For programmatic configs (forDataSource, forSource, forName, forURL) use ControlsConfig and .withControls instead:

DatabaseConfig.forDataSource(MyProfile, ds)
  .withControls(ControlsConfig(maxConnections = 10, queueSize = 500))

Config-file keys (for forConfig / forProfileConfig):

• maxConnections: maximum concurrent JDBC connections (existing key).
• queueSize: maximum number of callers waiting for in-flight admission (default: 1000).
• maxInflightActions: maximum concurrently running DBIO chains (default: 2 * maxConnections).
• inflightAdmissionTimeout: optional timeout while waiting for in-flight admission.
• connectionAcquireTimeout: optional timeout while waiting for a connection slot.

If maxInflightActions is configured lower than maxConnections, Slick uses maxConnections as the effective value.

Example:

mydb {
  connectionPool = "HikariCP"

  # JDBC concurrency
  maxConnections = 10

  # DBIO admission control
  maxInflightActions = 20
  queueSize = 1000

  # Optional timeout controls
  inflightAdmissionTimeout = 2s
  connectionAcquireTimeout = 5s
}

Timeout failures are returned as SlickException:

• SlickException("Timed out waiting for inflight admission after ...")
• SlickException("Timed out waiting for connection slot after ...")

Overload behavior

When the queue is full, new calls fail fast with:

• SlickException("DBIOAction queue full")

This is intentional back-pressure. Typical responses are to retry with jittered backoff, shed load, or increase capacity (queueSize, maxInflightActions, and/or database connection pool size) based on measured behavior.