doobie is a monadic API that provides a number of data types that all work the same way but describe computations in different contexts.
In the doobie high level API the most common types we will deal with have the form ConnectionIO[A], specifying computations that take place in a context where a java.sql.Connection is available, ultimately producing a value of type A.
doobie programs are values. You can compose small programs to build larger programs. Once you have constructed a program you wish to run, you interpret it into an effectful target monad of your choice (Task or IO for example) and drop it into your main application wherever you like.
Before we can use doobie we need to import some symbols. We will use package imports here as a convenience; this will give us the most commonly-used symbols when working with the high-level API.
import doobie._
import doobie.implicits._
Let’s also bring in Cats.
import cats._
import cats.effect._
import cats.implicits._
In the doobie high level API the most common types we will deal with have the form ConnectionIO[A]
,
specifying computations that take place in a context where a java.sql.Connection
is available,
ultimately producing a value of type A
.
So let’s start with a ConnectionIO
program that simply returns a constant.
val program1 = 42.pure[ConnectionIO]
// program1: ConnectionIO[Int] = Pure(42)
This is a perfectly respectable doobie program, but we can’t run it as-is; we need a Connection
first. There are several ways to do this, but here let’s use a Transactor
.
Note: DriverManagerTransactors have the advantage of no connection pooling and configuration, so
are perfect for testing. The main disadvantage is that it is slower than pooling connection managers,
no provides upper bound for concurrent connections and executes blocking operations in an unbounded
pool of threads. The doobie-hikari
add-on provides a Transactor
implementation backed by a HikariCP
connection pool. The connection pool is a lifetime-managed object that must be shut down cleanly, so
it is managed as a Resource.
import doobie.hikari._
// Resource yielding a transactor configured with a bounded connect EC and an unbounded
// transaction EC. Everything will be closed and shut down cleanly after use.
val transactor: Resource[IO, HikariTransactor[IO]] =
for {
ce <- ExecutionContexts.fixedThreadPool[IO](32) // our connect EC
be <- Blocker[IO] // our blocking EC
xa <- HikariTransactor.newHikariTransactor[IO](
"org.h2.Driver", // driver classname
"jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", // connect URL
"sa", // username
"", // password
ce, // await connection here
be // execute JDBC operations here
)
} yield xa
A Transactor
is a data type that knows how to connect to a database, hand out connections, and
clean them up; and with this knowledge it can transform ConnectionIO ~> IO
, which gives us a
program we can run. Specifically it gives us an IO
that, when run, will connect to the database
and execute single transaction.
We are using cats.effect.IO
as our final effect type, but you can use any monad M[_]
given
cats.effect.Async[M]
. See Using Your Own Target Monad at the end of this chapter for more details.
And here we go.
transactor.use(42.pure[ConnectionIO].transact[IO]).unsafeRunSync() should be(res0)
We have computed a constant. It’s not very interesting because we never ask the database to perform any work, but it’s a first step
We are gonna connect to a database to compute a constant. Let’s use the sql string interpolator to construct a query that asks the database to compute a constant. The meaning of this program is “run the query, interpret the resultset as a stream of Int values, and yield its one and only element.”
transactor.use(sql"select 42".query[Int].unique.transact[IO]).unsafeRunSync() should be(res0)
What if we want to do more than one thing in a transaction? Easy! ConnectionIO
is a monad,
so we can use a for comprehension to compose two smaller programs into one larger program.
val largerProgram = for {
a <- sql"select 42".query[Int].unique
b <- sql"select power(5, 2)".query[Int].unique
} yield (a, b)
transactor.use(largerProgram.transact[IO]).unsafeRunSync() should be(res0)
The astute among you will note that we don’t actually need a monad to do this; an applicative functor is all we need here. So we could also write the above program as:
val oneProgram = sql"select 42".query[Int].unique
val anotherProgram = sql"select power(5, 2)".query[Int].unique
transactor.use((oneProgram, anotherProgram).mapN(_ + _).transact[IO]).unsafeRunSync() should be(
res0)