doobie Documentation

doobie provides extended support for many PostgreSQL types not natively supported by JDBC. Most mappings are provided in the pgtypes module. To enable them, use:

import org.typelevel.doobie.postgres._
import org.typelevel.doobie.postgres.implicits._

Java 8 Time Types (JSR310)

Use the following mappings for correct timezone handling:

• TIMESTAMP $\rightarrow$ java.time.LocalDateTime
• TIMESTAMPTZ $\rightarrow$ java.time.Instant or java.time.OffsetDateTime
• DATE $\rightarrow$ java.time.LocalDate
• TIME $\rightarrow$ java.time.LocalTime

Array Types

doobie supports single-dimensional arrays of these types:

• bit[] $\rightarrow$ Array[Boolean]
• int4[] $\rightarrow$ Array[Int]
• int8[] $\rightarrow$ Array[Long]
• float4[] $\rightarrow$ Array[Float]
• float8[] $\rightarrow$ Array[Double]
• varchar[], char[], text[], bpchar[] $\rightarrow$ Array[String]
• uuid[] $\rightarrow$ Array[UUID]

Note: You can also map to List and Vector. Arrays of int2 are not supported and are incorrectly mapped as Array[Int] by the driver.

Other Nonstandard Types

• uuid $\rightarrow$ java.util.UUID
• inet $\rightarrow$ java.net.InetAddress
• hstore $\rightarrow$ java.util.Map[String, String] or Map[String, String]

When working with arrays, you must distinguish between the column itself being NULL and the individual cells within the array being NULL. There are four primary mapping strategies. Note that if you attempt to read a NULL cell into a non-optional type, a NullableCellRead exception will be thrown.

Example of the four mapping variations:

sql"select array['foo','bar','baz']".query[List[String]].quick.unsafeRunSync()
sql"select array['foo','bar','baz']".query[Option[List[String]]].quick.unsafeRunSync()
sql"select array['foo',NULL,'baz']".query[List[Option[String]]].quick.unsafeRunSync()
sql"select array['foo',NULL,'baz']".query[Option[List[Option[String]]]].quick.unsafeRunSync()

By default, fragments created with fr or Fragment.const have a single trailing space appended. To control whitespace:

1. Remove trailing space: Use the fr0 interpolator or the Fragment.const0 constructor. These yield fragments without a trailing space.
2. Ensure spacing: Use the +~+ operator to concatenate two fragments. This operator ensures that at least one space exists between them, which is useful when combining fragments that might not have trailing/leading whitespace.

Note: sql is an alias for fr0.

// Using fr0 to avoid trailing spaces
fr"IN (" ++ List(1, 2, 3).map(n => fr0"$n").intercalate(fr",") ++ fr")"

// Using +~+ to ensure spacing between fragments
import Fragment.const0
def codeCondFrag: Fragment = fr0"code = 'USA'"
def populationCondFrag: Fragment = fr0"population > 1000000"

const0("SELECT code, name, population FROM country\n") +~+
  fr0"WHERE" +~+ codeCondFrag +~+ fr0"AND" +~+ populationCondFrag +~+
  fr0"ORDER BY population DESC"

The doobie.hi (High-level Interface) modules build upon the low-level doobie.free API but automatically handle resource management (closing Connections, PreparedStatements, and ResultSets) and logging. This is the recommended way to perform custom JDBC operations without the risk of resource leaks.

import cats.effect.IO
import cats.effect.unsafe.implicits.global // To allow .unsafeRunSync
import org.typelevel.doobie.Transactor

// Create the transactor
val xa: Transactor[IO] = Transactor.fromDriverManager[IO](
  driver = "org.postgresql.Driver", 
  url = "jdbc:postgresql:world",   
  user = "postgres",
  password = "password",
  logHandler = None                  
)

import org.typelevel.doobie.HC  // High-level API over java.sql.Connection
import org.typelevel.doobie.HRS  // High-level API over java.sql.ResultSet
import org.typelevel.doobie.ConnectionIO
import org.typelevel.doobie.util.log.{LoggingInfo, Parameters}
import cats.effect.unsafe.implicits.global
import org.typelevel.doobie.util.unlabeled

val sql = "SELECT * FROM (VALUES (1, '1'), (2, '2'))"
val program: ConnectionIO[List[(Int, String)]] = HC.executeWithResultSet(
  create = FC.prepareStatement(sql),
  prep = FPS.unit,
  exec = FPS.executeQuery,
  process = HRS.list[(Int, String)],
  loggingInfo = LoggingInfo(sql, Parameters.NonBatch(List.empty), label = unlabeled)
)

program.transact(xa).unsafeRunSync()

While Get and Put handle mappings between Scala types and single columns, Read and Write handle mappings between Scala types and heterogeneous vectors (multiple columns).

• Read[A]: Maps a vector of schema types to a Scala type A. This is used to read rows as composite values.
• Write[A]: Maps a Scala type A to a vector of schema types. This is used to set multiple statement parameters (e.g., in a VALUES clause).

Automatic Derivation Rules

Doobie can automatically derive Read and Write instances for most composite types based on these rules:

1. Base Cases:
   • Zero-width types: Unit and HNil.
   • Single-column types: Any type with a Get or Put instance, including Options of those types.
2. Inductive Cases:
   • Shapeless HLists (if elements are readable/writable).
   • Shapeless records (if values are readable/writable).
   • Product types (case classes or tuples) via their Generic representation.

Handling Nullability

Doobie provides Read[Option[A]] and Write[Option[A]] for the cases above, mapping all columns to nullable schema types. This is useful for mapping results from an OUTER JOIN to optional data types, such as (Parent, Option[Child]).