Skip to main content

Row Codecs

Reading rows from JDBC means calling rs.getInt(1), rs.getString(2), rs.getTimestamp(3) — column by column, in the right order, with the right types. Get any of it wrong and you get a ClassCastException at runtime. Add a column to your query and you silently shift every index after it.

A RowCodec<T> replaces all of that with a single declaration: you list the database types and a constructor, and the codec does the rest. Once defined, the same codec drives everything the library does with your type:

  • Reading — decodes rows from a ResultSet (queries) or a CallableStatement (stored procedures)
  • Writing — encodes values into a PreparedStatement for inserts, updates, and batch operations
  • Streaming — feeds rows into the PostgreSQL COPY protocol for high-throughput inserts
  • JSON — round-trips your type to and from JSON objects using column names as keys
  • Analysis — Query Analysis inspects the codec's types to verify them against the database schema

You define the mapping once, and it propagates everywhere.

Named Row Codecs​

A named row codec tracks both types and column names. This is the recommended default — the small overhead of naming fields pays for itself quickly:

data class Product(val id: Int, val name: String, val price: BigDecimal, val createdAt: Instant)

val productCodec: RowCodecNamed<Product> =
    RowCodec.namedBuilder<Product>()
        .field("id", PgTypes.int4, Product::id)
        .field("name", PgTypes.text, Product::name)
        .field("price", PgTypes.numeric, Product::price)
        .field("created_at", PgTypes.timestamptz, Product::createdAt)
        .build(::Product)

// Column list for SQL — no hand-written strings to keep in sync
val allProducts =
    sql { "SELECT ${productCodec.columnList} FROM product" }

Having names lets you:

  • columnList() — emit column names as a Fragment for SELECT clauses, so queries stay in sync with the codec
  • columnNames() — get column names as a list
  • Fragment.insertInto(table, codec) — generate a complete INSERT template from the codec's column metadata
  • Fragment.insertIntoReturning(table, codec) — same, with a RETURNING clause that parses the inserted row back
  • fragment.row(codec, value) — emit an object's fields as comma-separated parameters for custom INSERT patterns
  • DbJsonRow.jsonObject(codec) — build a JSON object codec with column names as keys

Single-Column Codec​

For single-column queries, use the simpler of() factory:

val idCodec: RowCodec<Int> = RowCodec.of(PgTypes.int4)

Nullable Columns​

Use .opt() to wrap a type for nullable columns:

data class Person(val id: Int, val name: String, val createdAt: Instant?)

val personCodec: RowCodec<Person> =
    RowCodec.builder<Person>()
        .field(PgTypes.int4, Person::id)
        .field(PgTypes.text, Person::name)
        .field(PgTypes.timestamptz.opt(), Person::createdAt)
        .build(::Person)

Composing Codecs for Joins​

Row codecs compose for joins. Given a productCodec and a categoryCodec, combine them with .joined() or .leftJoined():

// Inner join — both sides always present
val innerJoined: RowCodec<Pair<ProductRow, CategoryRow>> =
    productCodec.joined(categoryCodec)

// Left join — right side is nullable
val leftJoined: RowCodec<Pair<ProductRow, CategoryRow?>> =
    productCodec.leftJoined(categoryCodec)

The result type is Tuple2<A, B> in Java (with ._1() and ._2() accessors), Pair<A, B> in Kotlin, and a tuple (A, B) in Scala. Left join wraps the right side in Optional (or nullable in Kotlin, Option in Scala).

This is why row codecs use index-based reading rather than column names. When you join two tables, both may have columns named id or name. Column-name-based reading would silently return the wrong value. Index-based reading makes composition safe — each codec reads its own slice of columns in sequence, and name clashes are irrelevant.

Data-Driven Inserts​

Fragment.insertIntoReturning() generates a complete INSERT statement from a named codec — column list, parameter placeholders, and RETURNING clause. Pass column names to except to skip columns with database defaults:

fun insert(product: Product): Product =
    Fragment.insertIntoReturning("product", productCodec)
        .on(product)
        .run(conn)

// Skip columns with database defaults — pass column names to except
fun insertWithDefault(product: Product): Product =
    Fragment.insertIntoReturning("product", productCodec, "id")
        .on(product)
        .run(conn)

Positional Codecs​

Use positional codecs when column names aren't needed — e.g., single-use queries or performance-sensitive paths. You build a positional codec by listing .field() calls — one per column, in SELECT order — and finishing with .build(constructor):

data class Person(val id: Int, val name: String, val createdAt: Instant)

val personCodec: RowCodec<Person> =
    RowCodec.builder<Person>()
        .field(PgTypes.int4, Person::id)
        .field(PgTypes.text, Person::name)
        .field(PgTypes.timestamptz, Person::createdAt)
        .build(::Person)

Each .field() takes a DbType that models the exact database column type. DbType<A> knows how to read a value of type A from a ResultSet and write it to a PreparedStatement — no JDBC integer codes, no manual rs.getX() calls. Each supported database has its own set (PgTypes, DuckDbTypes, MariaDbTypes, etc.) with full-precision mappings for every type. See Database Types for the complete catalog.

The builder is fully type-safe: the constructor receives exactly the types you declared, with no casts. Columns are read by index — the order of .field() calls must match the column order in your SELECT.