Fragments

A Fragment is a SQL building block that holds a SQL string together with its bound parameters. Build fragments with string interpolation (Kotlin and Scala) or the builder pattern (all languages).

String interpolation

Kotlin uses sql { } and Scala uses sql"" to build fragments from string templates. Database values become typed, bound parameters. They are never concatenated into the SQL string.

For a thorough explanation of how sql { } works internally and its thread safety guarantees, see Kotlin String Interpolation.

Kotlin

val query: Fragment =
    sql { """
        SELECT * FROM users
        WHERE id = ${PgTypes.int4(userId)}
            AND status = ${PgTypes.text("active")}
            AND created_at > ${PgTypes.timestamptz(cutoffDate)}
    """ }

// Execute safely — parameters are bound, not interpolated
val users: List<User> =
    query.query(userCodec.all()).run(connection)

Java

Fragment query =
    Fragment.of(
            """
            SELECT * FROM users
            WHERE id =
            """)
        .value(PgTypes.int4, userId)
        .append(" AND status = ")
        .value(PgTypes.text, "active")
        .append(" AND created_at > ")
        .value(PgTypes.timestamptz, cutoffDate);

// Execute safely — parameters are bound, not interpolated
List<User> users = query.query(userCodec.all()).run(connection);

Scala

val query: Fragment =
  sql"""SELECT * FROM users
        WHERE id = ${PgTypes.int4(userId)}
        AND status = ${PgTypes.text("active")}
        AND created_at > ${PgTypes.timestamptz(cutoffDate)}"""

// Parameters are bound, not interpolated
def users(using Connection): List[User] =
  query.query(userCodec.all()).run

Show entire file

Inside the interpolation block, you can embed:

• Bound values — ${PgTypes.int4(userId)} becomes a ? parameter
• Other fragments — ${codec.columnList}, ${Fragment.whereAnd(filters)}, or another sql { } / sql"" block are spliced into the SQL

Builder pattern

The builder pattern works in all languages and is useful for constructing fragments programmatically. Start from Fragment.of("SELECT …") (or Fragment.builder() for an empty start) and chain .value(type, x) / .append(fragment):

Kotlin

// Build fragments programmatically with the builder pattern
val frag: Fragment =
    Fragment.of("SELECT * FROM users WHERE id = ")
        .value(PgTypes.int4, 42)
        .append(" AND active = ")
        .value(PgTypes.bool, true)

Java

// Build fragments programmatically with the builder pattern
Fragment frag =
    Fragment.of(
            """
            SELECT * FROM users
            WHERE id =
            """)
        .value(PgTypes.int4, 42)
        .append(" AND active = ")
        .value(PgTypes.bool, true);

Scala

// Build fragments with the builder pattern
val frag: Fragment =
  Fragment
    .of("SELECT * FROM users WHERE id = ")
    .value(PgTypes.int4, 42)
    .append(" AND active = ")
    .value(PgTypes.bool, true)

Show entire file

Which style should I use?

• Kotlin — Use sql { } for queries where all values are known. Use the builder pattern when you need to compose fragments programmatically.
• Scala — Same guidance, using sql"" instead of sql { }.
• Java — Use the builder pattern for everything (no string interpolation available).

Composing fragments

For dynamic queries — searches with optional filters, conditional clauses — chain .optionally(value).append(sql, type) onto a base fragment. Each optionally is a branch point Query Analysis expands into all 2^N possible SQL shapes, so every code path is verified against the schema:

Kotlin

// Compose dynamic filters with the `optionally` DSL. Each `.optionally().append(...)`
// is a branch point Query Analysis verifies against the schema, even when
// the runtime never takes that branch at this call site.
fun query(): List<ProductRow> = tx.execute(
    sql { "SELECT id, name, price FROM product WHERE 1 = 1" }
        .optionally(namePattern).append(" AND name ILIKE ", PgTypes.text)
        .optionally(maxPrice)  .append(" AND price < ",    PgTypes.numeric)
        .query(rowCodec.all())
)

Java

// Compose dynamic filters with the `optionally` DSL — each `.optionally().append(...)`
// is a branch point Query Analysis verifies against the schema, even when
// the runtime never takes that branch at this call site.
List<ProductRow> query() {
  return tx.execute(
      Fragment.of("SELECT id, name, price FROM product WHERE 1 = 1")
          .optionally(namePattern).append(" AND name ILIKE ", PgTypes.text)
          .optionally(maxPrice)  .append(" AND price < ",   PgTypes.numeric)
          .query(rowCodec.all()));
}

Scala

// Compose dynamic filters with the `optionally` DSL. Each `.optionally().append(...)`
// is a branch point Query Analysis verifies against the schema, even when
// the runtime never takes that branch at this call site.
def query(): List[ProductRow] =
  sql"SELECT id, name, price FROM product WHERE 1 = 1"
    .optionally(namePattern).append(" AND name ILIKE ", PgTypes.text)
    .optionally(maxPrice).append(" AND price < ", PgTypes.numeric)
    .query(rowCodec.all())
    .transact(tx)

Show entire file

For list-shaped composition (joining a List<Fragment> from arbitrary sources), static factories like Fragment.whereAnd(), Fragment.set(), and Fragment.comma() handle SQL syntax (commas, AND/OR separators, SET clauses):

Kotlin

// Dynamic UPDATE — only set the fields that changed
fun updateUser(userId: Int, newName: String?, newEmail: String?) {
    val sets = listOfNotNull(
        newName?.let { sql { "name = ${PgTypes.text(it)}" } },
        newEmail?.let { sql { "email = ${PgTypes.text(it)}" } }
    )

    if (sets.isNotEmpty()) {
        sql { "UPDATE users ${Fragment.set(sets)} WHERE id = ${PgTypes.int4(userId)}" }
            .update()
            .run(conn)
    }
}

Java

// Dynamic UPDATE — only set the fields that changed
void updateUser(int userId, String newName, String newEmail) {
  List<Fragment> sets = new ArrayList<>();
  if (newName != null) sets.add(Fragment.of("name = ").value(PgTypes.text, newName));
  if (newEmail != null) sets.add(Fragment.of("email = ").value(PgTypes.text, newEmail));

  if (!sets.isEmpty()) {
    Fragment.of("UPDATE users ")
        .append(Fragment.set(sets)) // SET name = ?, email = ?
        .append(" WHERE id = ")
        .value(PgTypes.int4, userId)
        .update()
        .run(conn);
  }
}

Scala

// Dynamic UPDATE — only set the fields that changed
def updateUser(userId: Int, newName: Option[String], newEmail: Option[String])(using Connection): Unit =
  val sets = List(
    newName.map(n => sql"name = ${PgTypes.text(n)}"),
    newEmail.map(e => sql"email = ${PgTypes.text(e)}")
  ).flatten

  if sets.nonEmpty then
    sql"UPDATE users ${Fragment.set(sets)} WHERE id = ${PgTypes.int4(userId)}"
      .update()
      .run

Show entire file

Other useful combinators: Fragment.and(), Fragment.or(), Fragment.whereOr(), Fragment.orderBy(), Fragment.comma(), Fragment.parentheses().

See Dynamic Queries for a comparison of the two styles, including trade-offs around Query Analysis coverage.

IN-clause helper

For IN clauses against dialects without native array types (MariaDB, SQL Server, Oracle, DB2), Fragment.valuesList(type, values) emits (?, ?, …) with each value bound as a typed parameter:

Fragment.of("SELECT * FROM emp WHERE id IN ")
    .append(Fragment.valuesList(MariaTypes.int_, List.of(1, 2, 3)))
// SELECT * FROM emp WHERE id IN (?, ?, ?)

On PostgreSQL or DuckDB, prefer the native array idiom: .value(int4.array(), ids) with WHERE id = ANY(?).

valuesList throws IllegalArgumentException on an empty list — an empty IN() is SQL-invalid, so the caller has to branch (typically: return an empty result without issuing the query).