Value Members

1.  object ConnectingToDatabaseSection extends AnyFlatSpec with Matchers with Section

   doobie is a monadic API that provides a number of data types that all work the same way but describe computations in different contexts.

   Introduction

   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.

   First programs

   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.

2.  object DoobieLibrary extends Library

   doobie is a pure functional JDBC layer for Scala.

3.  object DoobieUtils
4.  object ErrorHandlingSection extends AnyFlatSpec with Matchers with Section

   Exceptions are a fact of life when interacting with databases, and they are largely nondeterministic; whether an operation will succeed or not depends on unpredictable factors like network health, the current contents of tables, locking state, and so on.

   About Exceptions

   Exceptions are a fact of life when interacting with databases, and they are largely nondeterministic; whether an operation will succeed or not depends on unpredictable factors like network health, the current contents of tables, locking state, and so on. So we must decide whether to compute everything in a disjunction like EitherT[ConnectionIO, Throwable, A] or allow exceptions to propagate until they are caught explicitly. doobie adopts the second strategy: exceptions are allowed to propagate and escape unless handled explicitly (exactly as IO works). This means when a doobie action (transformed to some target monad) is executed, exceptions can escape.

   There are three main types of exceptions that are likely to arise:

   1. Various types of IOException can happen with any kind of I/O, and these exceptions tend to be unrecoverable.

   2. Database exceptions, typically as a generic SQLException with a vendor-specific SQLState identifying the specific error, are raised for common situations such as key violations. Some vendors (PostgreSQL for instance) publish a table of error codes, and in these cases doobie can provide a matching set of exception-handling combinators. However in most cases the error codes must be passed down as folklore or discovered by experimentation. There exist the XOPEN and SQL:2003 standards, but it seems that no vendor adheres closely to these specifications. Some of these errors are recoverable and others aren’t.

   3. doobie will raise an InvariantViolation in response to invalid type mappings, unknown JDBC constants returned by drivers, observed NULL values, and other violations of invariants that doobie assumes. These exceptions indicate programmer error or driver non-compliance and are generally unrecoverable.

   MonadError and Derived Combinators

   All doobie monads have associated instances of the Async instance, which extends MonadError[?[_], Throwable]. This means ConnectionIO, etc., have the following primitive operations:

   • attempt converts M[A] into M[Either[Throwable, A]]
   • fail constructs an M[A] that fails with a provided Throwable

   So any doobie program can be lifted into a disjunction simply by adding .attempt.

     val p = 42.pure[ConnectionIO]
   // p: ConnectionIO[Int] = Pure(42)
   
   p.attempt
   // res0: ConnectionIO[Either[Throwable, Int]] = Suspend(
   //   HandleErrorWith(
   //     FlatMapped(Pure(42), cats.Monad$$Lambda$8968/1197202183@4abff98c),
   //     cats.ApplicativeError$$Lambda$9059/715221672@694b43d5
   //   )
   // )

   From the .attempt and fail combinators we can derive many other operations, as described in the Cats documentation. In addition doobie provides the following specialized combinators that only pay attention to SQLException:

   • attemptSql is like attempt but only traps SQLException.
   • attemptSomeSql traps only specified SQLExceptions.
   • exceptSql recovers from a SQLException with a new action.
   • onSqlException executes an action on SQLException and discards its result.

   And finally we have a set of combinators that focus on SQLStates.

   • attemptSqlState is like attemptSql but yields M[Either[SQLState, A]].
   • attemptSomeSqlState traps only specified SQLStates.
   • exceptSqlState recovers from a SQLState with a new action.
   • exceptSomeSqlState recovers from specified SQLStates with a new action.
5.  object ErrorHandlingSectionHelpers
6.  object Model
7.  object MultiColumnQueriesSection extends AnyFlatSpec with Matchers with Section

   So far, we have constructed queries that return single-column results.

   So far, we have constructed queries that return single-column results. These results were mapped to Scala types. But how can we deal with multi-column queries?

   In this section, we'll see what alternatives doobie offers us to work with multi-column queries.

   As in previous sections, we'll keep working with the 'country' table:

   code    name                      population    gnp
   "DEU"  "Germany"                    82164700    2133367.00
   "ESP"  "Spain"                      39441700          null
   "FRA"  "France",                    59225700    1424285.00
   "GBR"  "United Kingdom"             59623400    1378330.00
   "USA"  "United States of America"  278357000    8510700.00

   To make simpler the code we built a method which prepares the database, makes the query and transacts it all:

   def transactorBlock[A](f: => ConnectionIO[A]): IO[A] =
      transactor.use((createCountryTable *> insertCountries(countries) *> f).transact[IO])

8.  object ParameterizedQueriesSection extends AnyFlatSpec with Matchers with Section

   Previously we have worked with static SQL queries where the values used to filter data were hard-coded and didn't change.

   Previously we have worked with static SQL queries where the values used to filter data were hard-coded and didn't change.

   select code, name, population, gnp from country where code = "GBR"

   In this section, we'll learn how to construct parameterized queries.

   We’re still playing with the country table, shown here for reference.

   code    name                      population    gnp
   "DEU"  "Germany"                    82164700    2133367.00
   "ESP"  "Spain"                      39441700          null
   "FRA"  "France",                    59225700    1424285.00
   "GBR"  "United Kingdom"             59623400    1378330.00
   "USA"  "United States of America"  278357000    8510700.00

   To make simpler the code we built a method which prepares the database, makes the query and transacts it all:

   def transactorBlock[A](f: => ConnectionIO[A]): IO[A] =
      transactor.use((createCountryTable *> insertCountries(countries) *> f).transact[IO])

9.  object ParameterizedQueryHelpers
10.  object SelectingDataSection extends AnyFlatSpec with Matchers with Section

    We are going to construct some programs that retrieve data from the database and stream it back, mapping to Scala types on the way.

    We are going to construct some programs that retrieve data from the database and stream it back, mapping to Scala types on the way.

    We will be playing with the country table that has the following structure:

    CREATE TABLE country (
    code       character(3)  NOT NULL,
    name       text          NOT NULL,
    population integer       NOT NULL,
    gnp        numeric(10,2)
    )

    For the exercises, the country table will contain:

    code    name                      population    gnp
    "DEU"  "Germany"                    82164700    2133367.00
    "ESP"  "Spain"                      39441700          null
    "FRA"  "France"                     59225700    1424285.00
    "GBR"  "United Kingdom"             59623400    1378330.00
    "USA"  "United States of America"  278357000    8510700.00

    How to select data

    As we commented in the previous section, the sql string interpolator allows us to create a query to select data from the database.

    For instance, sql"select name from country".query[String] defines a Query0[String], which is a one-column query that maps each returned row to a String.

    .to[List] is a convenience method that accumulates rows into a List, in this case yielding a ConnectionIO[List[String]]. It works with any collection type that has a CanBuildFrom. Similar methods are: - .unique which returns a single value, raising an exception if there is not exactly one row returned. - .option which returns an Option, raising an exception if there is more than one row returned. - .nel which returns a NonEmptyList, raising an exception if there are no rows returned. See the Scaladoc for Query0 for more information on these and other methods.

11.  object UpdatesSection extends AnyFlatSpec with Matchers with Section

    In this section we examine operations that modify data in the database, and ways to retrieve the results of these updates.

    In this section we examine operations that modify data in the database, and ways to retrieve the results of these updates.

    Data Definition

    It is uncommon to define database structures at runtime, but doobie handles it just fine and treats such operations like any other kind of update. And it happens to be useful here!

    Let’s create a new table, which we will use for the exercises to follow. This looks a lot like our prior usage of the sql interpolator, but this time we’re using update rather than query. The .run method gives a ConnectionIO[Int] that yields the total number of rows modified.

    val drop: Update0 =
    sql"""
      DROP TABLE IF EXISTS person
    """.update
    
    val create: Update0 =
    sql"""
      CREATE TABLE person (
        id   SERIAL,
        name VARCHAR NOT NULL UNIQUE,
        age  SMALLINT
      )
    """.update

    We can compose these and run them together.

    (drop, create).mapN(_ + _).transact(xa).unsafeRunSync
     // res0: Int = 0

    Inserting

    Inserting is straightforward and works just as with selects. Here we define a method that constructs an Update0 that inserts a row into the person table.

    def insert1(name: String, age: Option[Short]): Update0 =
      sql"insert into person (name, age) values ($name, $age)".update

12.  object UpdatesSectionHelpers