Why architecture matters here
The deepest design choice in doobie is that the transaction boundary is visible in the types. In most stacks, transactionality is ambient — an annotation, a thread-local, a framework interceptor — and the perennial bugs follow: a method that assumes it runs inside a transaction called from a context without one; two repository calls that silently run in separate transactions and half-commit on failure; a nested-transaction annotation whose semantics nobody can state. In doobie, ConnectionIO values compose into one program, and only .transact turns a program into an effect — acquiring a connection, beginning, running, and committing or rolling back atomically. 'Are these two writes atomic?' is answered by reading the code: were they composed before transact, or transacted separately? The type system won't let you accidentally interleave.
The second choice — SQL stays SQL — is a bet against ORMs that most high-performance teams eventually make anyway. doobie doesn't generate queries, doesn't lazy-load, doesn't cache entities: your query plan is the one you wrote, EXPLAIN works on the string in your source, and the database's full feature surface (CTEs, window functions, vendor extensions, hints) is available without escape hatches. What the library adds is exactly what raw SQL lacks: parameter safety, typed results, composability of fragments, and lifecycle correctness. The trade is explicit: you write more SQL and get precisely the SQL you wrote.
Third, doobie is honest about JDBC's nature: it blocks. Every JDBC call parks a thread, so doobie's interpreter runs operations on a blocking pool while your service's compute pool stays hot — and the arithmetic connecting HikariCP's pool size, the blocking pool, and downstream database capacity becomes an explicit design surface instead of an accident. Teams that internalize this (pool size ≈ what the DB can actually service concurrently; everything else queues in the pool, visibly measurable) get predictable latency; teams that don't discover that 200 threads waiting on 10 connections looks exactly like a slow database.
The architecture: every piece explained
Top row: from string to program. The sql interpolator — sql"select id, name from person where age > $minAge" — produces a Fragment: SQL text plus typed parameter encoders; interpolated values become JDBC parameters (never string concatenation), and fragments compose with ++, Fragments.whereAnd, in(...) — dynamic query construction with safety intact. .query[Person] turns a fragment into a Query0[Person] (or .update into Update0); running it yields ConnectionIO values via .to[List], .unique, .option, or .stream. ConnectionIO is a free monad over JDBC operations: pure data describing statements, parameter binding, and result-set walking — composable with map/flatMap, for-comprehensions, and traverse like any monad.
Middle row: interpretation and semantics. The Transactor holds the recipe: a connection source (usually HikariCP wrapped as a Resource), the blocking execution context, and the transaction strategy (before: setAutoCommit(false); after: commit; on error: rollback; always: close). program.transact(xa) is the only place database effects happen — one call, one connection, one transaction. Row mapping is positional and derived: Read[Person] assembles columns into case classes, Meta instances map individual column types (with newtype/enum mappings added by instance), and mismatches fail with precise column-level errors. Streaming: .stream yields Stream[ConnectionIO, Row] with configurable fetch size — the cursor advances as the stream is pulled, so a 50M-row export runs in constant memory; transacted, it becomes a Stream[F, Row] whose connection is held for the stream's lifetime — the detail that shapes how you architect long exports. The error channel carries SQLException with SQLSTATE-aware combinators (attemptSqlState, onUniqueViolation) so 'insert, or on duplicate do X' is a typed recovery, not string matching on vendor messages.
Bottom rows: the harness. Hikari integration builds the pool as a Resource — sized explicitly, metered (Hikari's metrics exposed), aligned with a fixed-size blocking EC (the canonical setup: EC threads = pool connections, so all queueing happens observably in Hikari, not invisibly on threads). Testing is doobie's signature: check(query) asks the database to prepare-and-describe the statement, then compares JDBC's reported parameter/column types and nullability against your Scala types — a CI suite that catches the renamed column, the nullable-but-mapped-as-String, and the int-vs-bigint drift before deploys do. The ops strip: pool arithmetic vs DB capacity, statement/transaction timeouts, and slow-query taps (log/metric instrumentation via Transactor hooks).
End-to-end flow
Build the persistence layer of an order service and run it through a bad day. The module defines fragments and programs: insertOrder: ConnectionIO[OrderId] (insert with generated-key retrieval), reserveStock(sku, qty): ConnectionIO[Int] (an UPDATE returning affected rows), and recordEvent: ConnectionIO[Unit]. The use case composes them: reserve stock; if zero rows affected, raise a domain error; insert the order; record the event — one for-comprehension, transacted once. The failure semantics are exactly what the composition says: when the event insert violates a constraint, the stock reservation and the order insert roll back with it; the service returns a clean error and the database holds no half-order. Nobody wrote rollback code.
The duplicate-order case shows typed recovery: insertOrder.onUniqueViolation(selectExistingId(clientRef)) — idempotent order creation expressed as a combinator on SQLSTATE 23505, not a try/catch on message text. The nightly export shows streaming: ordersSince(ts).stream.transact(xa) feeds an fs2 pipeline into S3 at a steady 40MB/s and flat heap; fetchSize=5000 keeps cursor round-trips efficient; and because the connection is pinned for the stream's duration, the export runs on a dedicated small pool so it can never starve the API's connections — an architecture decision made visible by the connection-lifetime semantics.
The bad day: a lock-contending migration makes UPDATEs slow. Requests pile up — but the pileup is in Hikari's wait queue, measured by its wait-time metric, which is exactly where the alert fires; the compute pool stays responsive (health checks green, other endpoints fast) because JDBC blocking lives on its own EC. Query timeouts (set via a Transactor hook applying setQueryTimeout) fail the stuck UPDATEs at 5s with a typed error mapped to 503s; the circuit stays bounded. Post-incident, the typecheck suite earns its keep one more time: the migration renamed a column, and before any human noticed, CI's check(selectOrderSummary) failed with 'column "total_amt" does not exist' — the drift caught at build time, the deploy that would have 500'd never shipped.