Skip to main content

Composing Operations

Operations can be composed as values — combined, sequenced, and chained — so that multiple database actions run in a single transaction without manual connection handling.

Combining Independent Operations​

.combineWith() combines two operations that don't depend on each other. Both run in the same transaction, and a function combines their results:

data class User(val id: Int, val name: String)
data class Order(val id: Int, val userId: Int, val product: String)
data class Dashboard(val userCount: Long, val recentOrders: List<Order>)
data class Stats(val userCount: Long, val orderCount: Long, val revenue: Long)

val orderCodec: RowCodec<Order> =
    RowCodec.builder<Order>()
        .field(PgTypes.int4, Order::id)
        .field(PgTypes.int4, Order::userId)
        .field(PgTypes.text, Order::product)
        .build(::Order)

lateinit var tx: Transactor

// Combine two independent queries — both run in one transaction
val countUsers: Operation<Long> =
    sql { "SELECT count(*) FROM users" }
        .query(RowCodec.of(PgTypes.int8).exactlyOne())
val recentOrders: Operation<List<Order>> =
    sql { "SELECT * FROM orders ORDER BY id DESC LIMIT 10" }
        .query(orderCodec.all())

fun dashboard(): Dashboard =
    countUsers
        .combineWith(recentOrders, ::Dashboard)
        .transact(tx)

// Three-way: all run in one transaction, results combined
val countOrders: Operation<Long> =
    sql { "SELECT count(*) FROM orders" }
        .query(RowCodec.of(PgTypes.int8).exactlyOne())
val totalRevenue: Operation<Long> =
    sql { "SELECT coalesce(sum(amount), 0) FROM orders" }
        .query(RowCodec.of(PgTypes.int8).exactlyOne())

fun stats(): Stats =
    countUsers
        .combineWith(countOrders, totalRevenue, ::Stats)
        .transact(tx)

Running Multiple Writes​

When you have several write operations and only care about completion (not individual results), use Operation.allOf():

// Run multiple writes in one transaction, discard individual results
val insertUser: Operation<Int> =
    sql { "INSERT INTO users(name) VALUES(${PgTypes.text("Alice")})" }.update()
val insertAudit: Operation<Int> =
    sql { "INSERT INTO audit_log(action) VALUES(${PgTypes.text("user_created")})" }.update()
val updateStats: Operation<Int> =
    sql { "UPDATE stats SET user_count = user_count + 1" }.update()

fun createUserWithAudit() {
    Operation.allOf(insertUser, insertAudit, updateStats).transact(tx)
}

Sequencing a List​

When you have a dynamic list of operations, Operation.sequence() runs them all and collects the results:

// Execute a list of operations and collect all results
val names = listOf("Alice", "Bob", "Charlie")

fun insertAll(): List<Int> {
    val inserts: List<Operation<Int>> = names.map { name ->
        sql { "INSERT INTO users(name) VALUES(${PgTypes.text(name)}) RETURNING id" }
            .query(RowCodec.of(PgTypes.int4).exactlyOne())
    }

    return Operation.sequence(inserts).transact(tx)
}

Data Flow Between Operations​

Use .then() to feed one operation's result into the next operation's template. The first operation runs, and its result becomes the input to the template:

// Define templates
val insertUser: Template<String, Int> =
    sql { "INSERT INTO users(name) VALUES(" }
        .param(PgTypes.text)
        .append(") RETURNING id")
        .query(RowCodec.of(PgTypes.int4).exactlyOne())

val ordersByUser: Template<Int, List<Order>> =
    sql { "SELECT id, user_id, product FROM orders WHERE user_id = " }
        .param(PgTypes.int4)
        .query(orderCodec.all())

// Chain: insert user, then use returned id to fetch their orders
fun insertAndFetchOrders(): List<Order> =
    insertUser.on("Alice")
        .then(ordersByUser)
        .transact(tx)

When the first operation returns a record and the template uses .from(), use .then() with the Template.From directly:

// 1-param template: insert user, return id and name
val insertUser: Template<String, NewUser> =
    sql { "INSERT INTO users(name) VALUES(" }
        .param(PgTypes.text)
        .append(") RETURNING id, name")
        .query(newUserCodec.exactlyOne())

// 2-param template: log the creation with both fields
val logCreation: Template.Update2<Int, String> =
    sql { "INSERT INTO audit_log(user_id, username) VALUES(" }
        .param(PgTypes.int4)
        .append(", ")
        .param(PgTypes.text)
        .append(")")
        .update()

// Chain: .from() adapts the 2-param template to accept NewUser
fun insertAndLog(): Int =
    insertUser.on("Alice")
        .then(logCreation.from(NewUser::id, NewUser::name))
        .transact(tx)

Conditional Execution​

Operation.ifEmpty() implements the find-or-create pattern: run the first operation, and if it returns empty (empty Optional, null, or None), run the fallback instead:

// Find-or-create pattern
val findUser: Template<String, User?> =
    sql { "SELECT id, name, email FROM users WHERE email = " }
        .param(PgTypes.text)
        .query(userCodec.maxOne())

val createUser: Template.Query2<String, String, User> =
    sql { "INSERT INTO users(name, email) VALUES(" }
        .param(PgTypes.text)
        .append(", ")
        .param(PgTypes.text)
        .append(") RETURNING *")
        .query(userCodec.exactlyOne())

fun findOrCreate(): User =
    Operation.ifEmpty(
        findUser.on(email),
        createUser.on(name, email)
    ).transact(tx)

Analyzing Composed Operations​

QueryAnalyzer can walk the entire operation tree and analyze every SQL statement in one call. See Query Analysis for details.

fun analyzeComposedOperation() {
    // Build a composed operation
    val transaction: Operation<*> =
        insertUser.on("Alice")
            .thenIgnore(allUsers)

    // Analyze every SQL statement in the tree — one call
    val results: List<QueryAnalysis> =
        QueryAnalyzer.analyze(transaction, conn)

    for (analysis in results) {
        if (!analysis.succeeded()) {
            System.err.println(analysis.report())
        }
    }
}