Fragments

A Fragment is a composable SQL building block — it holds a SQL string together with its bound parameters. There are two ways to build fragments: string interpolation (Kotlin and Scala) and the builder pattern (all languages).

String Interpolation

Kotlin uses sql { } and Scala uses sql"" to build fragments from string templates. Database values are embedded as typed, bound parameters — 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
val users: List[User] =
  query.query(userCodec.all()).run(connection)

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

.value() binds, .param() creates a hole

The vocabulary matters because both occupy the same SQL ? position:

• .value(type, x) — bound value, immediately captured into the Fragment. Produces a ready-to-execute Operation.
• .param(type) — parameter hole, filled later. Produces a Template — see Templates.

.param(type, value) does not exist — if you know the value, use .value(type, value).

Which style should I use?

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

Composing Fragments

Build small reusable fragments, then combine them into full queries. Static factories like Fragment.whereAnd(), Fragment.set(), and Fragment.comma() handle SQL syntax — commas, AND/OR separators, SET clauses — so you don't have to:

Kotlin

// Build small reusable filters
fun byName(name: String): Fragment =
    sql { "name ILIKE ${PgTypes.text(name)}" }

fun cheaperThan(max: BigDecimal): Fragment =
    sql { "price < ${PgTypes.numeric(max)}" }

// Compose dynamically — only include the filters that are present
fun query(): List<ProductRow> {
    val filters = listOfNotNull(
        byName("%widget%"),
        maxPrice?.let(::cheaperThan)
    )

    return tx.transact { conn ->
        sql { "SELECT * FROM product ${Fragment.whereAnd(filters)}" }
            .query(rowCodec.all())
            .run(conn)
    }
}

Java

// Build small reusable filters
Fragment byName(String name) {
  return Fragment.of("name ILIKE ").value(PgTypes.text, name);
}

Fragment cheaperThan(BigDecimal max) {
  return Fragment.of("price < ").value(PgTypes.numeric, max);
}

// Compose dynamically — only include the filters that are present
List<ProductRow> query() {
  List<Fragment> filters =
      Stream.of(Optional.of(byName("%widget%")), maxPrice.map(this::cheaperThan))
          .flatMap(Optional::stream)
          .toList();

  return tx.execute(
      conn ->
          Fragment.of("SELECT * FROM product ")
              .append(Fragment.whereAnd(filters))
              .query(rowCodec.all())
              .run(conn));
}

Scala

// Build small reusable filters
def byName(name: String): Fragment =
  sql"name ILIKE ${PgTypes.text(name)}"

def cheaperThan(max: BigDecimal): Fragment =
  sql"price < ${PgTypes.numeric(max)}"

// Compose dynamically
def query(): List[ProductRow] =
  val filters: List[Fragment] =
    List(
      Some(byName("%widget%")),
      maxPrice.map(cheaperThan)
    ).flatten

  tx.transact { conn =>
    sql"SELECT * FROM product ${Fragment.whereAnd(filters)}"
      .query(rowCodec.all())
      .run(conn)
  }

Show entire file

The same approach works for UPDATE statements — build a list of assignments and let Fragment.set() join them:

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]): 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(conn)

Show entire file

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

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).