Summary

Add a new zio-blocks-sql module providing schema-driven, type-safe SQL database access that integrates naturally with zio-blocks' existing Schema, Scope, and optics infrastructure.

Three modules:

Motivation

zio-blocks has a mature Schema/Reflect/Deriver pipeline, Scope for resource lifecycle, and SchemaExpr for query ASTs — but no database module to tie them together. This module fills that gap with a direct-style, zero-dependency core inspired by Magnum, leveraging Schema for automatic codec derivation instead of separate compile-time macros.

Design Principles

• Schema-driven: DbCodec[A] derived automatically from any Schema[A] via Deriver[DbCodec] — no separate derivation mechanism
• Direct-style core: No effect types in the core module. DbCon ?=> T / DbTx ?=> T context functions for type-level transaction boundaries
• Zero-dependency: Core depends only on zio-blocks-schema and zio-blocks-scope. JDBC is in jvm/ only
• Effect-agnostic: sql-zio wraps in ZIO.blocking; future sql-cats possible
• Scope integration: Connection lifecycle managed by Scope's Resource.fromAutoCloseable

Core Types

DbCodec[A]

Derived via Schema's Deriver mechanism. Maps case class fields to DB columns automatically:

trait DbCodec[A]:
  def columns: Vector[String]
  def read(rs: DbResultSet, offset: Int): A
  def write(ps: DbPreparedStatement, offset: Int, value: A): Unit

Flattening: Nested records flatten to prefixed columns by default (address.street → address_street). Opt-out via Modifier.config("sql.column_type", "JSONB").

Column naming: NameMapper trait (snakeCase default). Per-field override via Modifier.rename("custom_name").

DbValue

Sealed trait representing SQL values: DbInt, DbLong, DbDouble, DbString, DbBoolean, DbBigDecimal, DbBytes, DbLocalDate, DbLocalDateTime, DbInstant, DbUUID, DbNull, etc.

SqlDialect

Sealed trait with PostgreSQL and SQLite cases. Handles type name mapping (DbBoolean → BOOLEAN for PostgreSQL, INTEGER for SQLite) and parameter placeholder style ($N vs ?).

sql"" Interpolator

val name = "Alice"
val age = 30
val frag = sql"SELECT * FROM users WHERE name = $name AND age > $age"
// Produces parameterized query — no SQL injection
frag.query[User](using DbCon)  // returns Vector[User]
frag.update(using DbCon)       // returns Int (rows affected)

Frag holds SQL text + typed parameters. Composable via ++.

DDL Generation

val ddl = DDL.createTable[User](using SqlDialect.PostgreSQL)
// CREATE TABLE users (
//   id BIGINT NOT NULL,
//   name TEXT NOT NULL,
//   email TEXT NOT NULL,
//   created_at TIMESTAMPTZ NOT NULL
// )

Table name from Modifier.config("sql.table_name", "users") or auto-derived (snake_case of type name).

Table[A]

Derivation from Schema metadata. Captures table name, column list, primary key.

Transactor + Context Functions

// Direct-style, Scope-managed
Scope.global: scope ?=>
  val transactor = JdbcTransactor(dataSource)(using scope)
  
  // Read-only
  transactor.connect: (DbCon) ?=>
    sql"SELECT * FROM users".query[User]
  
  // Read-write with commit/rollback
  transactor.transact: (DbTx) ?=>
    sql"INSERT INTO users (name) VALUES ($name)".update

DbTx <: DbCon — transaction context is a subtype of connection context. Scope guarantees cleanup.

Fluent Query Builder (sql-query module)

// Separate module: sql-query
Select.from[User]
  .where(_.age > 21)
  .orderBy(_.name.asc)
  .toFrag  // produces Frag for execution via sql module

Bridges SchemaExpr (already exists in zio-blocks-schema) to SQL generation. Supports SELECT, INSERT, UPDATE, DELETE with WHERE clauses.

Dependencies

zio-blocks-schema    (Schema, Reflect, Deriver, SchemaExpr, Modifier)
zio-blocks-scope     (Scope, Resource)

zio-blocks-sql       (depends on schema + scope)
zio-blocks-sql-query (depends on sql)
zio-blocks-sql-zio   (depends on sql + zio)

Blocking prerequisite: PR #1025 (Pool[A]) must land before transaction/connection pooling integration.

Transport Abstraction

Abstract DbConnection / DbPreparedStatement / DbResultSet traits in shared/ for future non-JDBC backends (Scala.js). Concrete JDBC implementation in jvm/. No Scala.js backend implementation for v1 — abstract types only.

Dialect Support (v1)

Type mapping only — no query syntax normalization across dialects.

NULL Handling

• Option[A] → nullable column, None → SQL NULL
• Required field with SQL NULL → throws SchemaError (direct-style), converted to typed error in effect wrappers

Non-Goals

• JOINs, subqueries, aggregations, window functions, CTEs
• ORM features (lazy loading, change tracking, identity maps, repositories)
• Migration system (separate: Schema Migration System for ZIO Schema 2 #519)
• Batch operations (insertMany, updateMany)
• Streaming result sets
• Scala.js backend implementation
• More than 2 SQL dialects
• Connection pooling beyond Pool[A] integration
• Caching layer or query optimizer

Related

• Schema Migration System for ZIO Schema 2 #519 — Schema Migration System (complementary, separate concern)
• feat(scope): Resource pooling feature proposal — Pool[A] and ResourcePool[A] #1025 — Resource Pool (blocking prerequisite for transaction layer)
• zio-blocks-rpc: RPC type class for service trait introspection (derives RPC) #1143 — OpsMirror (future: could enable derives Repository)
• zio-blocks-endpoint: Pure endpoint descriptor module #1119 — zio-blocks-endpoint (similar module pattern)
• feat(schema-csv): Add CSV codec module with schema-driven derivation #1147 — CSV codec (complementary schema-driven codec)

Prior Art

• Magnum — Primary inspiration. Direct-style, Scala 3, JDBC, DbCodec + context functions + sql""
• zio-blocks schema-bson — Existing Deriver-based codec module (pattern reference for DbCodec derivation)