Library

Doobie

Overview

Doobie is a functional database access library for Scala that achieves the "perfect fusion of type safety and composability." It features a type-safe API for manual query writing, enabling large-scale query construction through combination and manipulation of query fragments. As a high-freedom library that allows optimized specialized query writing without hiding, it has become the standard choice for functional programming enthusiasts.

Details

Doobie 2025 version provides pure functional database access faithful to functional programming principles through deep integration with Cats Effect and the Cats ecosystem. Built on top of the JDBC layer, it enables composition of complex database operations through the type-safe ConnectionIO monad. The design philosophy gives developers complete control over SQL writing, allowing flexible construction of optimized queries.

Key Features

• Type-safe SQL Construction: Compile-time SQL type checking and safe query assembly
• ConnectionIO Monad: Safe resource management through functional effect system
• Fragment-oriented Design: Combination of reusable query parts
• Cats Effect Integration: Complete support for asynchronous processing and error handling
• Custom Mapping: Flexible type conversion and schema mapping capabilities
• Testing Support: Rich tools for query validation and debugging

Pros and Cons

Pros

• Perfect integration and consistency with functional programming paradigm
• Achieving optimized queries through complete control of SQL writing
• Significant reduction of runtime errors through type safety
• Safe and structured database operations via ConnectionIO monad
• Rich functional primitives through Cats Effect ecosystem
• Improved reusability and maintainability of query fragments

Cons

• High learning cost requiring functional programming knowledge
• No traditional ORM features (automatic table generation, etc.)
• Increased complexity due to Cats/Cats Effect ecosystem dependencies
• Potential development speed reduction due to manual SQL writing
• Design philosophy differences from object-oriented approaches
• Tendency towards over-engineering in small projects

Reference Pages

• Doobie Official Documentation
• Doobie GitHub Repository
• Typelevel Project

Code Examples

Project Setup and Dependencies

// build.sbt
libraryDependencies ++= Seq(
  // Core doobie dependencies
  "org.tpolecat" %% "doobie-core"      % "1.0.0-RC4",
  
  // Database drivers (as needed)
  "org.tpolecat" %% "doobie-h2"        % "1.0.0-RC4",          // H2
  "org.tpolecat" %% "doobie-postgres"  % "1.0.0-RC4",          // PostgreSQL
  "org.tpolecat" %% "doobie-hikari"    % "1.0.0-RC4",          // HikariCP
  
  // Testing support (optional)
  "org.tpolecat" %% "doobie-scalatest" % "1.0.0-RC4" % "test"
)

// Basic imports
import cats._, cats.data._, cats.implicits._
import cats.effect._
import doobie._
import doobie.implicits._

Basic Connection Setup and Transactor

import cats.effect.IO
import doobie.util.ExecutionContexts
import cats.effect.unsafe.implicits.global

// PostgreSQL connection example
val xa = Transactor.fromDriverManager[IO](
  driver = "org.postgresql.Driver",
  url = "jdbc:postgresql://localhost:5432/mydb",
  user = "username",
  password = "password",
  logHandler = None // Set appropriate handler to enable logging
)

// H2 in-memory database connection example
val h2xa = Transactor.fromDriverManager[IO](
  driver = "org.h2.Driver",
  url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
  user = "sa",
  password = "",
  logHandler = None
)

// HikariCP connection pool usage example
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

val config = new HikariConfig()
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb")
config.setUsername("username")
config.setPassword("password")
config.setMaximumPoolSize(10)

val dataSource = new HikariDataSource(config)
val pooledXa = Transactor.fromDataSource[IO](dataSource, ExecutionContexts.synchronous)

Case Classes and Query Definition

// Data model definition
case class User(id: Int, name: String, email: String, age: Option[Int])
case class Post(id: Int, userId: Int, title: String, content: String)

// Basic query functions
def findUserById(id: Int): ConnectionIO[Option[User]] =
  sql"SELECT id, name, email, age FROM users WHERE id = $id"
    .query[User]
    .option

def findUsersByName(name: String): ConnectionIO[List[User]] =
  sql"SELECT id, name, email, age FROM users WHERE name LIKE ${s"%$name%"}"
    .query[User]
    .to(List)

def getAllUsers: ConnectionIO[List[User]] =
  sql"SELECT id, name, email, age FROM users"
    .query[User]
    .to(List)

// Program execution
val program: IO[Option[User]] = findUserById(1).transact(xa)
val result = program.unsafeRunSync()
println(result)

// Getting multiple users
val users: IO[List[User]] = getAllUsers.transact(xa)
users.unsafeRunSync().foreach(println)

Data Insert, Update, and Delete Operations

// Data insertion
def insertUser(name: String, email: String, age: Option[Int]): ConnectionIO[Int] =
  sql"INSERT INTO users (name, email, age) VALUES ($name, $email, $age)"
    .update
    .run

// Insertion with generated key retrieval
def insertUserWithGeneratedId(name: String, email: String, age: Option[Int]): ConnectionIO[User] =
  sql"INSERT INTO users (name, email, age) VALUES ($name, $email, $age)"
    .update
    .withUniqueGeneratedKeys("id", "name", "email", "age")

// Data update
def updateUserEmail(id: Int, newEmail: String): ConnectionIO[Int] =
  sql"UPDATE users SET email = $newEmail WHERE id = $id"
    .update
    .run

// Data deletion
def deleteUser(id: Int): ConnectionIO[Int] =
  sql"DELETE FROM users WHERE id = $id"
    .update
    .run

// Execution example
val insertProgram = for {
  userId <- insertUserWithGeneratedId("John Doe", "[email protected]", Some(30))
  _      <- updateUserEmail(userId.id, "[email protected]")
  user   <- findUserById(userId.id)
} yield user

val result = insertProgram.transact(xa).unsafeRunSync()
println(s"Updated user: $result")

Fragment-oriented Programming and Query Assembly

import doobie.implicits._

// Reusable query fragments
def baseUserQuery: Fragment = fr"SELECT id, name, email, age FROM users"

def whereIdEquals(id: Int): Fragment = fr"WHERE id = $id"

def whereNameLike(name: String): Fragment = fr"WHERE name LIKE ${s"%$name%"}"

def whereAgeGreaterThan(age: Int): Fragment = fr"WHERE age > $age"

def orderByName: Fragment = fr"ORDER BY name ASC"

def limitResults(limit: Int): Fragment = fr"LIMIT $limit"

// Dynamic query combining fragments
def findUsersWithFilters(
    nameFilter: Option[String] = None,
    minAge: Option[Int] = None,
    limit: Option[Int] = None
): ConnectionIO[List[User]] = {
  
  val whereClause = List(
    nameFilter.map(name => fr"name LIKE ${s"%$name%"}"),
    minAge.map(age => fr"age > $age")
  ).flatten
  
  val query = baseUserQuery ++ 
    (if (whereClause.nonEmpty) fr"WHERE" ++ whereClause.reduce(_ ++ fr"AND" ++ _) else Fragment.empty) ++
    orderByName ++
    (limit.map(l => fr"LIMIT $l").getOrElse(Fragment.empty))
  
  query.query[User].to(List)
}

// Usage example
val dynamicQuery = findUsersWithFilters(
  nameFilter = Some("John"),
  minAge = Some(25),
  limit = Some(10)
)

dynamicQuery.transact(xa).unsafeRunSync().foreach(println)

Transaction Processing and Error Handling

import cats.implicits._

// Complex transaction example
def transferUserPosts(fromUserId: Int, toUserId: Int): ConnectionIO[Int] = for {
  // User existence verification
  fromUser <- findUserById(fromUserId).flatMap {
    case Some(user) => user.pure[ConnectionIO]
    case None => 
      val error: Throwable = new RuntimeException(s"User $fromUserId not found")
      error.raiseError[ConnectionIO, User]
  }
  
  toUser <- findUserById(toUserId).flatMap {
    case Some(user) => user.pure[ConnectionIO]
    case None =>
      val error: Throwable = new RuntimeException(s"User $toUserId not found")
      error.raiseError[ConnectionIO, User]
  }
  
  // Post transfer
  postsUpdated <- sql"UPDATE posts SET user_id = $toUserId WHERE user_id = $fromUserId"
    .update
    .run
    
} yield postsUpdated

// Program with error handling
def safeTransferPosts(fromUserId: Int, toUserId: Int): IO[Either[String, Int]] = {
  transferUserPosts(fromUserId, toUserId)
    .transact(xa)
    .attempt
    .map {
      case Right(count) => Right(count)
      case Left(error) => Left(s"Transfer failed: ${error.getMessage}")
    }
}

// Execution example
val transferResult = safeTransferPosts(1, 2).unsafeRunSync()
transferResult match {
  case Right(count) => println(s"Successfully transferred $count posts")
  case Left(error) => println(s"Error: $error")
}

Batch Processing and Streaming

import fs2._

// Batch insertion
def insertUsers(users: List[User]): ConnectionIO[Int] = {
  val sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
  Update[(String, String, Option[Int])](sql)
    .updateMany(users.map(u => (u.name, u.email, u.age)))
}

// Streaming processing
def streamAllUsers: Stream[ConnectionIO, User] =
  sql"SELECT id, name, email, age FROM users"
    .query[User]
    .stream

def processUsersInBatches: IO[Unit] = {
  streamAllUsers
    .chunkN(100) // Process 100 at a time
    .evalMap { chunk =>
      val users = chunk.toList
      // Processing logic for each batch
      IO.println(s"Processing batch of ${users.size} users")
    }
    .compile
    .drain
    .transact(xa)
}

// Large data processing example
def exportUsersToCSV: IO[Unit] = {
  streamAllUsers
    .map { user =>
      s"${user.id},${user.name},${user.email},${user.age.getOrElse("")}"
    }
    .intersperse("\n")
    .through(fs2.text.utf8.encode)
    .through(fs2.io.file.Files[IO].writeAll(fs2.io.file.Path("users.csv")))
    .compile
    .drain
    .transact(xa)
}

processUsersInBatches.unsafeRunSync()

Advanced Type Mapping and Custom Conversions

import java.time.LocalDateTime
import doobie.Meta

// Custom type mapping
case class UserId(value: Int) extends AnyVal
case class Email(value: String) extends AnyVal

// Implicit Meta instance definition
implicit val userIdMeta: Meta[UserId] = Meta[Int].imap(UserId.apply)(_.value)
implicit val emailMeta: Meta[Email] = Meta[String].imap(Email.apply)(_.value)

// Strongly typed user model
case class TypedUser(
  id: UserId,
  name: String,
  email: Email,
  createdAt: LocalDateTime
)

def findTypedUserById(id: UserId): ConnectionIO[Option[TypedUser]] =
  sql"SELECT id, name, email, created_at FROM users WHERE id = ${id.value}"
    .query[TypedUser]
    .option

// Enum type mapping
sealed trait UserStatus
case object Active extends UserStatus
case object Inactive extends UserStatus
case object Suspended extends UserStatus

implicit val userStatusMeta: Meta[UserStatus] = 
  Meta[String].imap {
    case "active" => Active
    case "inactive" => Inactive
    case "suspended" => Suspended
    case other => throw new IllegalArgumentException(s"Unknown status: $other")
  } {
    case Active => "active"
    case Inactive => "inactive"
    case Suspended => "suspended"
  }

case class UserWithStatus(
  id: UserId,
  name: String,
  email: Email,
  status: UserStatus
)

def findActiveUsers: ConnectionIO[List[UserWithStatus]] =
  sql"SELECT id, name, email, status FROM users WHERE status = 'active'"
    .query[UserWithStatus]
    .to(List)

Debugging and Testing Support

import doobie.util.log.LogHandler

// SQL logging configuration
val logHandler = LogHandler.jdkLogHandler

val xaWithLogging = Transactor.fromDriverManager[IO](
  driver = "org.h2.Driver",
  url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
  user = "sa",
  password = "",
  logHandler = Some(logHandler)
)

// YOLO mode (for development/debugging)
val y = xa.yolo
import y._

// Immediate query execution and testing
findUserById(1).quick.unsafeRunSync()
getAllUsers.check.unsafeRunSync() // Query type checking

// Test helper functions
def setupTestData: ConnectionIO[Unit] = for {
  _ <- sql"CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(255), email VARCHAR(255), age INTEGER)".update.run
  _ <- sql"DELETE FROM users".update.run
  _ <- insertUser("Test User 1", "[email protected]", Some(25))
  _ <- insertUser("Test User 2", "[email protected]", Some(30))
  _ <- insertUser("Test User 3", "[email protected]", None)
} yield ()

// Test execution
val testProgram = for {
  _ <- setupTestData
  users <- getAllUsers
  _ <- IO.println(s"Test users: ${users.length}")
} yield users

testProgram.transact(xa).unsafeRunSync()