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.
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
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.
To connect to the database, I need to create an instance of 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
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
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.