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:

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

For parameterized templates with unfilled parameter holes, see Templates (Advanced).

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