Database I/O Actions

This chapter explains how to execute, compose, and control DBIOAction values.

In this chapter:

• Execute actions with run (materialized) or stream (streaming).
• Compose actions with sequencing and error-handling combinators.
• Control transaction/session behavior with transactionally and withPinnedSession.
• Drop down to JDBC when needed with SimpleDBIO.

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 DBIOAction, parameterized by the result type it will produce when you execute it.

Database I/O Actions can be combined with several different combinators (see the DBIOAction class and DBIOAction object, which is also available under the alias DBIO, for details), but they will always be executed strictly sequentially and (at least conceptually) in a single database session.

In most cases you will want to use the type aliases DBIO and StreamingDBIO for non-streaming and streaming Database I/O Actions. They omit the optional effect types supported by DBIOAction. The alias DBIOEffect keeps the effect type and omits only the streaming type; see Cats Type Classes.

copysourceimport java.sql.Blob
import javax.sql.rowset.serial.SerialBlob

import scala.collection.mutable.ArrayBuffer
import scala.util.{Failure, Success}

import cats.effect.IO
import cats.effect.Resource
import cats.effect.unsafe.implicits.global

import slick.cats.Database
import slick.jdbc.DatabaseConfig
import slick.jdbc.H2Profile
import slick.jdbc.H2Profile.api._
import slick.ControlsConfig

Executing Database Actions

Start here if you want to run a query or action and get results back.

DBIOActions 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 a DBIOAction 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 DBIOAction supports this mode of execution.

Execution of the action starts in the background when run is called. The calling thread is not blocked. The materialized result is returned as an F[R] value (for example IO[Seq[User]]) that completes asynchronously and can be composed with the rest of your effect-based program:

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")
}

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 the streaming type of the selected Slick facade.

Execution of the DBIOAction does not start until the stream is consumed.

Repeated-consumption semantics depend on the selected facade stream type. See the facade-specific Scaladoc for slick.cats.Database and slick.zio.Database.

Stream elements are signaled as soon as they become available in the streaming part of the DBIOAction. 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.

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

When streaming a JDBC result set, each iterator.next() call is wrapped in F.blocking, so the OS thread is only occupied during the actual row fetch. LOB values (such as Blob) are safe to access synchronously on the element because the result set pointer is not advanced until the consumer requests the next element:

copysourceval q = for (c <- coffees) yield c.image
val a = q.result
val s: fs2.Stream[IO, Blob] = db.stream(a)
val bytesStream: fs2.Stream[IO, Array[Byte]] =
  s.map(b => b.getBytes(0, b.length().toInt))

Composing Database I/O Actions

This section covers how to build larger workflows from smaller actions.

DBIOActions describe sequences of individual actions to execute in strictly sequential order on one database session (at least conceptually), therefore the most commonly used combinators deal with sequencing. Since a DBIOAction eventually results in a Success or Failure, its combinators have to distinguish between successful and failed executions. Unless specifically noted, all combinators only apply to successful actions. Any failure aborts the sequence of execution.

Sequential Execution

The simplest combinator is DBIO.seq which takes a varargs list of actions to run in sequence, discarding their return value. If you need the return value, you can use andThen to combine two actions and keep the result of the second one. If you need both return values of two actions, there is the zip combinator. For getting all result values from a sequence of actions (of compatible types), use DBIO.sequence. All these combinators work with pre-existing DBIOActions which are composed eagerly:

copysourceval ins1: DBIO[Int] = coffees += ("Colombian", 7.99)
val ins2: DBIO[Int] = coffees += ("French_Roast", 8.99)

val a1: DBIO[Unit] = DBIO.seq(ins1, ins2)

val a2: DBIO[Int] = ins1.andThen(ins2)

val a3: DBIO[(Int, Int)] = ins1.zip(ins2)

val a4: DBIO[Vector[Int]] = DBIO.sequence(Vector(ins1, ins2))

If an action depends on a previous action in the sequence, you have to compute it on the fly with flatMap or map. These two methods plus filter enable the use of for comprehensions for action sequencing.

For example, to run an action only if a previous one returned a certain result, use flatMap and return DBIO.successful (an action that does nothing but return the given value) in the other case:

copysourceval insertIfMissing: DBIO[Boolean] =
  coffees.filter(_.name === "Colombian").exists.result.flatMap {
    case true  => DBIO.successful(false) // nothing to do
    case false => (coffees += ("Colombian", 7.99)).map(_ => true)
  }

Similar to DBIO.sequence for upfront composition, there is DBIO.fold for working with sequences of actions and composing them based on the previous result.

Error Handling

You can use andFinally to perform a cleanup action, no matter whether the previous action succeeded or failed. This is similar to using try ... finally ... in imperative Scala code. A more flexible version of andFinally is cleanUp. It lets you transform the failure and decide how to fail the resulting action if both the original one and the cleanup failed.

Primitives

You can lift any CE3 effect F[R] into an action with DBIO.from. DBIO.liftF is an alias for DBIO.from. This allows an IO (or any other F[_]: Async value) to be used in an action sequence:

val action: DBIO[String] = for {
  id <- DBIO.from(IO(java.util.UUID.randomUUID().toString))
  _  <- users += User(id, "Alice")
} yield id

A pre-existing value or failure can be converted with DBIO.successful and DBIO.failed, respectively.

To suspend the construction of an action until it is run, use DBIO.defer. Its by-name argument is evaluated each time the resulting action is run rather than once when it is built, and an exception thrown while evaluating it fails the action instead of the caller:

copysourcevar attempts = 0
// The block is evaluated each time the action is run, not when it is built
val countedInsert: DBIO[Int] = DBIO.defer {
  attempts += 1
  coffees += (s"Attempt $attempts", 1.0)
}

Cats Type Classes {#cats-type-classes}

Database I/O Actions have cats instances: MonadError[F, Throwable], and a Semigroup or Monoid for actions whose result type has one. The instances live in the companion objects of DBIOBase and DBIOAction, so they are in implicit scope and need no import besides the cats syntax itself (import cats.syntax.all._).

A cats type class takes a unary type constructor F[_], and there are two ways to view an action as one:

• DBIOEffect[E, R] is DBIOAction[R, NoStream, E] with the result type last, so that DBIOEffect[E, *] is an F[_] for every effect E. DBIO[R] is DBIOEffect[Effect.All, R]. The non-streaming combinators of DBIOAction and its companion (map, zip, asTry, DBIO.sequence, DBIO.successful, …) declare their results as DBIOEffect, and toAction gives any action, including the profile-specific ones returned by the lifted API, the DBIOEffect type with its effect.
• DBIOBase[E, R] is the supertype of every DBIOAction[R, S, E] with the effect and result type in that order and no streaming type. On Scala 3 a DBIOAction unifies with an F[_] as DBIOBase[E, *], so any action, including streaming and profile-specific ones, works with cats syntax as it is and keeps its effect: List(1, 2).traverse(i => coffees += (s"Coffee $i", 1.0)) is a DBIOBase[Effect.Write, List[Int]]. Scala 2 does not unify a DBIOAction type with DBIOBase on its own; there DBIOBase is used when spelled out, and any value whose static type is a full DBIOAction[R, S, E] goes through toAction: the profile-specific actions of the lifted API, and the results of the combinators that keep the streaming type (flatMap, andThen, >>, for-comprehensions, transactionally, withPinnedSession, named, andFinally, cleanUp).

The following works on every Scala version:

copysource// Values typed with DBIO or DBIOEffect, and the results of Slick's own combinators, have
// cats instances. toAction gives a profile action the DBIOEffect type with its effect.
val inserts: DBIOEffect[Effect.Write, List[Int]] =
  List(("Colombian", 7.99), ("French_Roast", 8.99)).traverse(c => (coffees += c).toAction)

val counts: DBIOEffect[Effect.Read, (Int, Int)] =
  (coffees.length.result.toAction, coffees.filter(_.price > 8.0).length.result.toAction).tupled

// |+| runs both actions in order and combines the results with the Semigroup of Int
val rows: DBIOEffect[Effect.Write, Int] =
  (coffees += ("Java", 6.99)).toAction |+| (coffees += ("Espresso", 9.99)).toAction

// The results compose with Slick's combinators like any other action; DBIO accepts every effect
val program: DBIO[(Int, (Int, Int))] = for {
  n <- inserts
  _ <- rows
  c <- counts
} yield (n.sum, c)

A DBIOBase produced by a cats combinator is accepted wherever a DBIOAction is expected, including db.run, the arguments of Slick’s combinators and the right-hand side of a for comprehension generator; map, flatMap, andThen and >> are members of DBIOBase and intersect the effects as usual. The same view gives a DBIOBase the other members of DBIOAction such as transactionally, withPinnedSession and named, and the JDBC profile’s extension methods (transactionally(isolation), withStatementParameters) accept a DBIOBase directly. The explicit toAction remains for the cases no view reaches, e.g. a List of DBIOBase values passed to DBIO.sequence.

Debugging

The named combinator names an action. This name can be seen in debug logs if you enable the slick.basic.BasicBackend.action logger.

Transactions and Pinned Sessions {#transactions}

When executing a DBIOAction which 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 of a non-database computation (e.g. the function passed to flatMap that determines the next action to run). You can use withPinnedSession to force the use of a single session, keeping the existing session open even when waiting for non-database computations.

All DBIOAction combinators which combine database actions without any non-database computations in between (e.g. andThen or zip applied to two database computations) can fuse these actions for more efficient execution, with the side-effect that the fused action runs inside a single session, even without withPinnedSession.

There is a related combinator called transactionally to force the use of a transaction. This guarantees that the entire DBIOAction that is executed will either succeed or fail atomically. Without it, all database actions run in auto-commit mode. The use of a transaction always implies a pinned session.

To run several actions in one transaction, compose them first (e.g. in a for comprehension) and call transactionally on the result; every action it contains runs in that transaction. A transaction makes the action atomic, but it does not keep concurrent transactions apart: two of them that read and then write the same rows can still deadlock, or fail with a serialization error at Serializable isolation, and then have to be retried.

An overload transactionally(ti) accepts a TransactionIsolation level:

action.transactionally(TransactionIsolation.Serializable)

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

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

Rollbacks

In case you want to force a rollback, you can return DBIO.failed within a DBIOAction.

copysourceval countAction = coffees.length.result

val rollbackAction = (coffees ++= Seq(
  ("Cold_Drip", new SerialBlob(Array[Byte](101))),
  ("Dutch_Coffee", new SerialBlob(Array[Byte](49)))
)).flatMap { _ =>
  DBIO.failed(new Exception("Roll it back"))
}.transactionally

val errorHandleAction = rollbackAction.asTry.flatMap {
  case Failure(e: Throwable) => DBIO.successful(e.getMessage)
  case Success(_) => DBIO.successful("never reached")
}

// Here we show that that coffee count is the same before and after the attempted insert.
// We also show that the result of the action is filled in with the exception's message.
val f: IO[String] = db.run(countAction.zip(errorHandleAction).zip(countAction)).map {
  case ((initialCount, result), finalCount) =>
    // init: 5, final: 5, result: Roll it back
    println(s"init: ${initialCount}, final: ${finalCount}, result: ${result}")
    result
}

JDBC Interoperability

Use this when you need JDBC functionality that is not directly modeled in Slick.

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

copysourceval getAutoCommit = SimpleDBIO[Boolean](_.connection.getAutoCommit)

If you need to access state of the database session across multiple SimpleDBIO actions, make sure to use withPinnedSession or transactionally accordingly (see above).