Connecting to a Database
In this chapter we start from the beginning. First we write a program that connects to a database and returns a value, and then we run that program in the REPL. We also touch on composing small programs to construct larger ones.
Our First Program
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(a = 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.
import doobie.util.ExecutionContexts
// This is just for testing. Consider using cats.effect.IOApp instead of calling
// unsafe methods directly.
import cats.effect.unsafe.implicits.global
// A transactor that gets connections from java.sql.DriverManager and executes blocking operations
// on an our synchronous EC. See the chapter on connection handling for more info.
val xa = Transactor.fromDriverManager[IO](
driver = "org.postgresql.Driver", // JDBC driver classname
url = "jdbc:postgresql:world", // Connect URL
user = "postgres", // Database user name
password = "password", // Database password
logHandler = None // Don't setup logging for now. See Logging page for how to log events in detail
)
Please consult your JDBC driver’s documentation for the driver class name and connection URL.
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.
The DriverManagerTransactor simply delegates to the java.sql.DriverManager to allocate connections, which is fine for development but inefficient for production use. In a later chapter we discuss other approaches for connection management.
And here we go.
val io = program1.transact(xa)
// io: IO[Int] = Uncancelable(
// body = cats.effect.IO$$$Lambda$8235/0x0000000842511040@16df4dce,
// event = cats.effect.tracing.TracingEvent$StackTrace
// )
io.unsafeRunSync()
// res0: Int = 42
Hooray! 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.
Keep in mind that all the code in this book is pure except the calls to
IO.unsafeRunSync, which is the “end of the world” operation that typically appears only at your application’s entry points. In the REPL we use it to force a computation to “happen”.
Right. Now let’s try something more interesting.
Our Second Program
Now let’s use the sql string interpolator to construct a query that asks the database to compute a constant. We will cover this construction in great detail later on, but the meaning of program2 is “run the query, interpret the resultset as a stream of Int values, and yield its one and only element.”
val program2 = sql"select 42".query[Int].unique
// program2: ConnectionIO[Int] = Suspend(
// a = Uncancelable(
// body = cats.effect.kernel.MonadCancel$$Lambda$8299/0x0000000842563840@67e7a37c
// )
// )
val io2 = program2.transact(xa)
// io2: IO[Int] = Uncancelable(
// body = cats.effect.IO$$$Lambda$8235/0x0000000842511040@37e1bf04,
// event = cats.effect.tracing.TracingEvent$StackTrace
// )
io2.unsafeRunSync()
// res1: Int = 42
Ok! We have now connected to a database to compute a constant. Considerably more impressive.
Our Third Program
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 program3: ConnectionIO[(Int, Double)] =
for {
a <- sql"select 42".query[Int].unique
b <- sql"select random()".query[Double].unique
} yield (a, b)
And behold!
program3.transact(xa).unsafeRunSync()
// res2: (Int, Double) = (42, 0.9348522741347551)
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 program3 as:
val program3a = {
val a: ConnectionIO[Int] = sql"select 42".query[Int].unique
val b: ConnectionIO[Double] = sql"select random()".query[Double].unique
(a, b).tupled
}
And lo, it was good:
program3a.transact(xa).unsafeRunSync()
// res3: (Int, Double) = (42, 0.9727930859662592)
And of course this composition can continue indefinitely.
val valuesList = program3a.replicateA(5)
// valuesList: ConnectionIO[List[(Int, Double)]] = FlatMapped(
// c = FlatMapped(
// c = FlatMapped(
// c = FlatMapped(
// c = FlatMapped(
// c = FlatMapped(
// c = FlatMapped(
// c = Suspend(
// a = Uncancelable(
// body = cats.effect.kernel.MonadCancel$$Lambda$8299/0x0000000842563840@62966ce1
// )
// ),
// f = cats.FlatMap$$Lambda$8366/0x00000008425c4840@40124ee1
// ),
// f = cats.Monad$$Lambda$8222/0x0000000842508840@d2214a5
// ),
// f = cats.Monad$$Lambda$8222/0x0000000842508840@66fd9b11
// ),
// f = cats.FlatMap$$Lambda$8408/0x00000008425f5840@37dd124b
// ),
// f = cats.FlatMap$$Lambda$8408/0x00000008425f5840@65ea3717
// ),
// f = cats.FlatMap$$Lambda$8408/0x00000008425f5840@78708033
// ),
// f = cats.Monad$$Lambda$8222/0x0000000842508840@725fb93f
// )
val result = valuesList.transact(xa)
// result: IO[List[(Int, Double)]] = Uncancelable(
// body = cats.effect.IO$$$Lambda$8235/0x0000000842511040@2dcfb275,
// event = cats.effect.tracing.TracingEvent$StackTrace
// )
result.unsafeRunSync().foreach(println)
// (42,0.8237372115254402)
// (42,0.4562834878452122)
// (42,0.3117581121623516)
// (42,0.6530480994842947)
// (42,0.962269420735538)
Diving Deeper
All of the doobie monads are implemented via Free and have no operational semantics; we can only “run” a doobie program by transforming FooIO (for some carrier type java.sql.Foo) to a monad that actually has some meaning.
Out of the box doobie provides an interpreter from its free monads to Kleisli[M, Foo, ?] given Async[M].
import cats.~>
import cats.data.Kleisli
import doobie.free.connection.ConnectionOp
import java.sql.Connection
val interpreter = KleisliInterpreter[IO](LogHandler.noop).ConnectionInterpreter
// interpreter: ConnectionOp ~> [γ$9$]Kleisli[IO, Connection, γ$9$] = doobie.free.KleisliInterpreter$$anon$10@19a97a54
val kleisli = program1.foldMap(interpreter)
// kleisli: Kleisli[IO, Connection, Int] = Kleisli(
// run = cats.data.KleisliFlatMap$$Lambda$8301/0x0000000842562040@6524004a
// )
val io3 = IO(null: java.sql.Connection) >>= kleisli.run
// io3: IO[Int] = FlatMap(
// ioe = Delay(
// thunk = <function0>,
// event = cats.effect.tracing.TracingEvent$StackTrace
// ),
// f = cats.data.KleisliFlatMap$$Lambda$8301/0x0000000842562040@6524004a,
// event = cats.effect.tracing.TracingEvent$StackTrace
// )
io3.unsafeRunSync() // sneaky; program1 never looks at the connection
// res5: Int = 42
So the interpreter above is used to transform a ConnectionIO[A] program into a Kleisli[IO, Connection, A]. Then we construct an IO[Connection] (returning null) and bind it through the Kleisli, yielding our IO[Int]. This of course only works because program1 is a pure value that does not look at the connection.
The Transactor that we defined at the beginning of this chapter is basically a utility that allows us to do the same as above using program1.transact(xa).
There is a bit more going on when calling transact (we add commit/rollback handling and ensure that the connection is closed in all cases) but fundamentally it’s just a natural transformation and a bind.
Using Your Own Target Monad
As mentioned earlier, you can use any monad M[_] given cats.effect.Async[M].