Introduction to doobie – a JDBC Layer for Scala

1. Introduction

Database access is an essential part of many software systems. The JVM provides us with JDBC, which we can use to make that interaction easier. In this article, we’ll be taking a look at doobie – a purely functional JDBC layer for Scala.

It is important to note that doobie makes use of the Cats and Cats Effect libraries, so it’s important to be somewhat familiar with them before using doobie.

2. Setup

To add doobie to our project, we’ll use sbt:

libraryDependencies += "org.tpolecat" %% "doobie-core" % "1.0.0-RC1"

This is the only dependency we need to use doobie, but since we’re going to be using PostgreSQL as a database, let’s add PostgreSQL extensions as well:

libraryDependencies += "org.tpolecat" %% "doobie-postgres" % "1.0.0-RC1"

For this introduction, we’ll be using PostgreSQL with the world database loaded – specifically, its city table:

create table city (
    id           integer not null,
    name         text    not null,
    country_code char(3) not null,
    district     text    not null,
    population   integer not null
);

3. Using doobie

Now that we have doobie added to our project, let’s start by looking at how we can create a database connection using Transactor.

3.1. Connecting to the Database

I’ve mentioned that we’re going to connect to the database using Transactor, but that’s not entirely accurate. While it’s true that the resulting type, Transactor, knows how to connect us to the database, it also knows how to clean up this connection afterward and, most importantly, how to take IOConnection and transform its result into our effect of choice – in this case, IO.

That allows us to cleanly separate our definitions from the execution:

val transactor: Transactor[IO] = Transactor.fromDriverManager[IO](
  "org.postgresql.Driver",
  "jdbc:postgresql://localhost:5432/world-db",
  "world",
  "world123"
)

val operations: ConnectionIO[Unit] = ???

operations.transact(transactor) // IO[Unit]

All of the actions inside operations will run in a single transaction.

Performing database operations directly via Transactor will certainly work, but it’s not efficient since we’ll be creating a new connection each time. Therefore, we can, and should, use connection pools.

3.2. Querying

Let’s say we want to select the names of the cities from our database. We could write the query as:

sql"select name from city limit 5"
  .query[String]
  .to[List]

Let’s take a look at this code step by step. First, we define our SQL statement using the sql interpolator:

sql"select name from city" // Fragment

That results in a Fragment type, which we will explore in a later section. Next, we can tell doobie that we want to run this SQL as a query with a specific type:

.query[String] // Query0[String]

We’re using String because the name is a string. We could also provide a tuple or a matching case class as a type – what’s important is that the number of parameters must match what we’ve specified in SQL itself.

Finally, we can complete the query definition by specifying the collection type:

.to[List] // ConnectionIO[List[String]]

This results in the ConnectionIO monad, which we can use to chain multiple operations. If we’re sure that our query will return a single row, we can also use the .unique method, which results in the ConnectionIO[String] and will also throw an exception if more than one row is returned.

Now that we know how to get data from the database, we can think about inserting and updating some data.

3.3. Inserting, Updating, and Deleting Data

Let’s insert a new city into the database:

sql"insert into city (id, name, country_code, district, population) values (5000, 'Baeldung', 'NLD', 'Baeldungland', 1337)"
  .update
  .run

Now, let’s dissect this code as we’ve done for a select operation. First, we have a sql interpolator with our SQL statement:

sql"insert into city (id, name, country_code, district, population) values (5000, 'Baeldung', 'NLD', 'Baeldungland', 1337)"

We’ve inserted the data directly to the String, but it also could be interpolated:

val id = 5000
sql"insert into city (id, name, country_code, district, population) values ($id, 'Baeldung', 'NLD', 'Baeldungland', 1337)"

Next, we tell doobie that this SQL will modify by using the .update method:

.update // Update0

And then, all that’s left is to finalize this operation by using the .run method:

.run // ConnectionIO[Int]

Note the Int inside our monad. In this case, it signifies the number of affected rows, but we can also ask for specific data in return:

insertedId <- sql"insert into city (id, name, country_code, district, population) values (${baeldungCity.id}, ${baeldungCity.name}, ${baeldungCity.countryCode}, ${baeldungCity.district}, ${baeldungCity.population})"
  .update
  .withUniqueGeneratedKeys[Int]("id")

We use withUniqueGeneratedKeys to grab the inserted id, but we could use it to grab other columns as well. It’s important to note, though, that this function has to be supported by the database in order to work.

Updating and deleting is very similar to inserting, in the sense that it’s modifying the database state. Because of that, we can use the same methods:

sql"update city set name = 'DungBael' where id = 5000".update.run

And for delete:

sql"delete from city where id = 5000".update.run

3.4. Fragments

Fragments are a very important concept in doobie. Their usefulness is best shown in an example. Let’s say we have some optional parameter, optionalCityNameParam:

val optionalCityNameParam: Option[String] = Some("%Pol%")

We can map it to create an optional Fragment using the fragment interpolator fr:

val optionalCityNameFragment: Option[Fragment] = optionalCityNameParam.map(name => fr"name like $name")

Finally, we can place it inside our query:

val operation = (fr"select name from city" ++ whereAndOpt(optionalCityNameFragment)).query[String].to[List]

Note the usage of the whereAndOpt method. It’s one of many helper methods that can be found in doobie.Fragments, but this particular one accepts optional fragments and applies them only if they are Some. We can create many fragments and connect them in different ways. For example, a piece of SQL that limits the results to five rows can be a fragment:

val limitFragment = fr"limit 5"

We could append it to any other SQL as well:

fr"select id, name, country_code, district, population from city" ++ limitFragment

It’s an immensely useful feature that allows for easy reuse of SQL snippets and ease of usage with optionals. As a side note, the only difference between fr and sql interpolators is that fr adds a space after a fragment to help with concatenation, and sql does not. Therefore, whenever we want to join two SQL snippets, we should be using the fr interpolator.

3.5. Error Handling

In general, doobie, similarly to IO, allows errors to propagate and escape unless handled explicitly. To do that, we can use Cat’s .attempt or .raiseError, but doobie provides us with some shorthands of its own. They focus mainly on handling SQLException and are situational in use, so describing them is outside of the scope of this introduction. The full list is available over in doobie’s scaladoc.

4. Conclusion

In this article, we’ve seen how to perform basic operations in doobie. We’ve learned how to form a connection and how to select, insert, delete, and update, and we’ve learned about doobie fragments. While there are still some unexplored topics like logging, data streaming, and how to use effects other than IO, this should be enough for the introduction.

As always, code from this article is available over on GitHub.