Skip to main content

Operations

Call .query() or .update() on a Fragment to get an Operation<T> — a database action that produces a value of type T. It doesn't run until you call .transact(tx) or .run(conn).

Queries​

Read rows from the database. Pass a RowCodec with a result mode to control how the ResultSet is consumed. For single-column results, shorthand methods skip the codec:

// Multi-column: pass a RowCodec with a result mode
val allUsers: Operation<List<User>> = fragment.query(userCodec.all())
val maybeUser: Operation<User?> = fragment.query(userCodec.maxOne())
val oneUser: Operation<User> = fragment.query(userCodec.exactlyOne())

// Single-column: shorthand methods skip the codec
val allIds: Operation<List<Int>> =
    sql { "SELECT id FROM users" }.queryAll(PgTypes.int4)
val maybeName: Operation<String?> =
    sql { "SELECT name FROM users LIMIT 1" }.queryMaxOne(PgTypes.text)
val count: Operation<Int> =
    sql { "SELECT count(*) FROM users" }.queryExactlyOne(PgTypes.int4)

Updates​

Write to the database — INSERT, UPDATE, DELETE, or DDL:

// Get the affected row count
val rowCount: Operation<Int> = fragment.update()

// Ignore the row count (DDL, fire-and-forget DML)
val ignored: Operation<Unit> = fragment.execute()

Returning Rows from Updates​

INSERT ... RETURNING or UPDATE ... RETURNING — run a write and read back the affected rows:

// INSERT ... RETURNING id, name
val insertedUsers: Operation<List<User>> =
    sql { "INSERT INTO users (name) VALUES ('alice') RETURNING id, name" }
        .updateReturning(userCodec.all())

val insertedUser: Operation<User> =
    sql { "INSERT INTO users (name) VALUES ('alice') RETURNING id, name" }
        .updateReturning(userCodec.exactlyOne())

// For databases that use generated keys instead of RETURNING (SQL Server, MariaDB)
val generatedId: Operation<Int> =
    sql { "INSERT INTO users (name) VALUES ('alice')" }
        .updateReturningGeneratedKeys(arrayOf("id"), RowCodec.of(PgTypes.int4).exactlyOne())

Execute (No Result)​

When you don't need the row count — DDL statements, fire-and-forget DML — use .execute() instead of .update(). It returns Operation<Void> (Java) / Operation<Unit> (Kotlin/Scala):

Fragment.of("CREATE TABLE users (id INT, name VARCHAR)").execute()

This is equivalent to .update().voided().

Running Operations​

Use a Transactor to obtain a connection, run the operation, and handle commit/rollback automatically:

fun cities(): List<City> = tx.transact { conn ->
    findCities.run(conn)
}

For multiple operations in a single transaction, call .run(conn) on each one inside the same block:

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

// Run both in one transaction using the connection directly
fun dashboard(): Dashboard =
    tx.transact { conn ->
        val count = countUsers.run(conn)
        val orders = recentOrders.run(conn)
        Dashboard(count, orders)
    }

Operation Modifiers​

Every operation supports these modifiers before execution:

ModifierEffect
.named("findUser")Prepends /* findUser */ to the SQL — visible in pg_stat_activity, slow query logs, and listener callbacks
.timeout(Duration.ofSeconds(5))Sets a query timeout via Statement.setQueryTimeout()
.withListener(listener)Attaches a QueryListener to this specific operation
.map(row -> transform(row))Transforms the result after execution
.voided()Discards the result, returning Operation<Void>

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. For the full set of combinators (.combineWith(), .then(), Operation.sequence(), Operation.ifEmpty(), and more), see Composing Operations.

Here's a quick taste — .combineWith() combines two independent operations:

fun dashboard(): Dashboard = tx.transact { conn ->
    countUsers.combineWith(recentOrders, ::Dashboard).run(conn)
}

Query Analysis can walk an entire composed operation tree and verify every SQL statement in one call.