Hacking with doobie and cats-effects

Lambda Ops

Sep 22, 2019 · 3 min read

About doobie

doobie is a pure functional JDBC layer for scala which built based on cats library. It is not an ORM, or a relational algebra. doobie simply provides a functional way to construct programs (and higher-level libraries) that use JDBC.

In this post I’m discussing about various types of query operations that can be done with doobie and cats-effects. All the source code which relates to this post available in gitlab. Please clone the repo and continue the post.

Configure database

I’m using mysql database to do the query operations with doobie. First I need to run mysql and create a database. Following is the way to do that. I’m running mysql with docker.

Sbt dependency

I’m using IntelliJ Idea as my IDE to work with Scala applications. I need to create sbt project and add the build.sbt dependency file with doobie and other dependencies. Following is the build.sbt dependency file.

DB connection

To connect to the database, I need to create an instance of a Transactor. A Transactor knows how to connect to the database, hand out connections and knows how to turn the query program into an asynchronous effect. doobie query does not contain any answers from the database, it's a description of a program that needs to be run to obtain these answers. Transactor give a way to run the queries and obtain the answers.

Transactor can be constructed by providing configuration such as database connection string, username, password. In here I’m using HikariTransactor which built based on HikariCP database connection pooling library. HikariTransactor wraps with cats-effect IO monad. It tells transactor to transform incoming doobie programs to a IO monad. In here IO monad is the asynchronous operation that executes the database query(side effect operation). Instead of IO monad we can use other implementation of cat-effects as well(ex Monix Task).


doobie queries are written directly in SQL, that’s exactly what’s sent to the database. Following are the queries I have used. As you can see queries are are created with the sql string interpolator. Complex queries can be made with doobie fragments.

Execute queries

I can run the queries using .transact(xa), which interprets the program in the IO monad. To unwrap and run the side-effecting code in the IO monad I need to invoke unsafeRunSync(). Following are the different queries that I have run and their outputs.

Query type checking

doobie doesn’t provide compile time type checking of the queries(they just map some fields from a SQL query to a case class). But doobie provides a way to type checking the queries during unit testing. When something is wrong with the query, doobie provides extensive feedback on what is going wrong. Following are the type checking tests I have done with the queries. On unit tests I have used h2 in memory database based transactor.


  1. https://blog.godatadriven.com/doobie-monix-jdbc-example
  2. https://softwaremill.com/comparing-scala-relational-database-access-libraries/
  3. https://medium.com/@wiemzin/zio-with-http4s-and-doobie-952fba51d089
  4. http://degoes.net/articles/zio-cats-effect
  5. https://tpolecat.github.io/doobie/docs/14-Managing-Connections.html
  6. https://www.innoq.com/en/blog/functional-service-in-scala/
  7. https://www.beyondthelines.net/programming/cats-effect-an-overview/


Have less, be more


Written by


Scala, Golang with Vim and Hockey: What else does a man need to be happy :)



Have less, be more

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade