Skip to main content

Templates

Most parameterized queries work fine as plain functions that build a Fragment with bound values. Upgrade to Templates when you need: batch execution with .onMany(), dynamic query variants with .optionally(), or reusable query values for Query Analysis.

Templates let you define the SQL structure once and supply values later. Use .param(type) (without a value) to create a typed parameter hole. This produces a Template — a reusable operation factory that can be analyzed by Query Analysis without providing concrete values:

// Define a reusable template — SQL structure is fixed, values come later
val findByEmail: Template<String, User?> =
    Fragment.of("SELECT id, name, email FROM users WHERE email = ")
        .param(PgTypes.text)
        .query(userCodec.maxOne())

// Fill the template with a value to get a concrete operation
fun findAlice(): User? =
    findByEmail.on("alice@example.com").transact(tx)

// Reuse the same template with different values
fun findBob(): User? =
    findByEmail.on("bob@example.com").transact(tx)

Fill the template with .on(value) to get a concrete operation.

When to use Templates vs bound values

Use bound values (.value(type, value), sql { }, sql"") when all values are known at definition time — they produce a Fragment directly.

Use Templates (.param(type)) when values come later — the SQL structure is fixed but values are supplied per-call. Templates also enable batch operations and dynamic queries with compile-time analysis of all variants.

Multiple Parameters​

You can mix .value(type, value) (bound immediately) with .param(type) (filled later) in the same fragment. Only the unbound parameters become template parameters:

// Mix bound and unbound parameters in the same template.
// Status is fixed at "active"; name filter and limit vary per call.
val activeUsersByName: Template.Query2<String, Int, List<User>> =
    sql { "SELECT id, name, status FROM users WHERE status = " }
        .value(PgTypes.text, "active")
        .append(" AND name ILIKE ")
        .param(PgTypes.text)
        .append(" ORDER BY name LIMIT ")
        .param(PgTypes.int4)
        .query(userCodec.all())

fun example(): List<User> =
    activeUsersByName.on("%alice%", 10).transact(tx)

Packaging Parameters in a Record​

When a template has multiple parameters, use .from() to map a record's fields to the template parameters. This gives each parameter a name and lets callers pass a single object:

// A data class gives names to each template parameter
data class InsertUser(val name: String, val email: String)

// .from() maps data class fields to template params
val insertUser: Template.From<InsertUser, Int> =
    sql { "INSERT INTO users(name, email) VALUES(" }
        .param(PgTypes.text)
        .append(", ")
        .param(PgTypes.text)
        .append(")")
        .update()
        .from(InsertUser::name, InsertUser::email)

// Callers pass the data class
fun createUser(): Int =
    insertUser.on(InsertUser("Alice", "alice@example.com"))
        .transact(tx)

Template.From implements Template, so it works with all combinators including .then() for chaining operations and .optionally() for dynamic queries.

Batch Operations​

Use a template with .onMany() to batch-insert or batch-update rows. The template defines the SQL once, and .onMany() executes it for each row using JDBC batch mode (addBatch() / executeBatch()):

// Batch insert — all columns as parameters
val insertAll: RowTemplate.Update<Product> =
    Fragment.insertInto("product", productCodec)

fun insertProducts(products: List<Product>): IntArray =
    insertAll.onMany(products.iterator()).run(conn)

// Batch insert — skip auto-generated ID column
val insertAutoId: RowTemplate.Update<Product> =
    Fragment.insertInto("product", productCodec, "id")

fun insertProductsAutoId(products: List<Product>): IntArray =
    insertAutoId.onMany(products.iterator()).run(conn)

Driver-level optimizations like .reWriteBatchedInserts() (PostgreSQL), .useBulkStmts() (MariaDB), and .useBulkCopyForBatchInsert() (SQL Server) must be enabled on the connection config for best performance.

For PostgreSQL high-throughput inserts, use streaming inserts with the COPY protocol instead.

Dynamic Templates​

.optionally() wraps a fragment so it is included in the SQL when a value is present and omitted entirely when absent. Each combination of present/absent filters produces a structurally different query, and Query Analysis verifies all of them automatically.

Optional Filters​

Use .optionally() with a parameterized fragment to create a filter that is included when a value is provided and skipped when absent:

// A search with an optional name filter.
// When present, the filter is applied; when absent, it's skipped.
val searchUsers: Template<String?, List<User>> =
    sql { "SELECT id, name, email FROM users WHERE 1=1" }
        .optionally(
            sql { " AND name ILIKE " }.param(PgTypes.text))
        .query(userCodec.all())

// Apply filter
fun filtered(): List<User> =
    searchUsers.on("%alice%").transact(tx)

// Skip filter — returns all users
fun all(): List<User> =
    searchUsers.on(null).transact(tx)

The template parameter type reflects the optionality — Optional<String> in Java, String? in Kotlin, Option[String] in Scala.

About WHERE 1=1

The WHERE 1=1 pattern lets you unconditionally prefix every filter with AND — since 1=1 is always true, the query works correctly whether zero, one, or all filters are present. This avoids conditional logic to decide whether to emit WHERE or AND.

For non-template scenarios, Fragment.whereAnd(filters) handles this automatically.

Boolean Flags​

For SQL chunks without parameters (e.g., AND active = TRUE), pass a plain Fragment to .optionally(). The template parameter becomes a Boolean — true includes the chunk, false skips it:

val activeUsers: Template<Boolean, List<User>> =
    sql { "SELECT id, name, email FROM users WHERE 1=1" }
        .optionally(sql { " AND active = TRUE" })
        .query(userCodec.all())

// Include the filter
fun onlyActive(): List<User> =
    activeUsers.on(true).transact(tx)

// Skip the filter — returns all users
fun all(): List<User> =
    activeUsers.on(false).transact(tx)

Multiple Optional Filters​

Chain multiple .optionally() calls to build queries with many independent filters. Each adds a template parameter:

// Multiple optional filters — each independently present or absent
val search: Template.Query3<String?, String?, Boolean, List<User>> =
    sql { "SELECT id, name, email FROM users WHERE 1=1" }
        .optionally(
            sql { " AND name ILIKE " }.param(PgTypes.text))
        .optionally(
            sql { " AND email ILIKE " }.param(PgTypes.text))
        .optionally(
            sql { " AND active = TRUE" })
        .append(" ORDER BY name")
        .query(userCodec.all())

// Each combination is type-safe
fun example(): List<User> =
    search.on("%alice%", null, true).transact(tx)

// Query analysis expands all 2³ = 8 combinations automatically
fun verifyAllVariants() {
    checker.check(search)
}

With 3 optional filters, there are 2³ = 8 possible query structures. For example, calling the template above with two different inputs:

search.on("alice", null, true)
→ SELECT id, name, email FROM users WHERE 1=1 AND name ILIKE ? AND active = TRUE ORDER BY name

search.on(null, null, false)
→ SELECT id, name, email FROM users WHERE 1=1 ORDER BY name

checker.check(search) expands and verifies all 8 combinations against the database — you don't test them individually.

Grouped Parameters​

When an optional clause needs multiple parameters (e.g., a BETWEEN range), pass a multi-parameter builder. The grouped parameters are provided or omitted together as a single unit:

// When an optional clause needs multiple parameters,
// pass a multi-parameter builder.
// The grouped parameters are provided or omitted together.
val byPriceRange: Template<Pair<BigDecimal, BigDecimal>?, List<Product>> =
    sql { "SELECT id, name, price FROM products WHERE 1=1" }
        .optionally(
            sql { " AND price BETWEEN " }
                .param(PgTypes.numeric)
                .append(" AND ")
                .param(PgTypes.numeric))
        .query(productCodec.all())

// With range
fun inRange(): List<Product> =
    byPriceRange
        .on(BigDecimal("10") to BigDecimal("50"))
        .transact(tx)

// Without range — returns all products
fun all(): List<Product> =
    byPriceRange.on(null).transact(tx)

Packaging Filters in a Record​

As the number of optional predicates grows, the raw template signature becomes unwieldy. Use .from() with getter references to map a record (or data class / case class) directly to template parameters — callers just pass the record:

// Package filters into a data class so callers see a clean API
data class UserSearch(
    val name: String?,
    val email: String?,
    val activeOnly: Boolean
)

// .from() maps getters to template params
private val searchTemplate: Template.From<UserSearch, List<User>> =
    sql { "SELECT id, name, email FROM users WHERE 1=1" }
        .optionally(
            sql { " AND name ILIKE " }.param(PgTypes.text))
        .optionally(
            sql { " AND email ILIKE " }.param(PgTypes.text))
        .optionally(
            sql { " AND active = TRUE" })
        .append(" ORDER BY name")
        .query(userCodec.all())
        .from(UserSearch::name, UserSearch::email,
            UserSearch::activeOnly)

// Callers just pass the data class
fun searchUsers(search: UserSearch): List<User> =
    searchTemplate.on(search).transact(tx)

fun example(): List<User> {
    val search = UserSearch(
        name = "%alice%", email = null, activeOnly = true)
    return searchUsers(search)
}