Skip to main content

Operations

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

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: OperationRead<List<User>> = fragment.query(userCodec.all())
val maybeUser: OperationRead<User?> = fragment.query(userCodec.maxOne())
val oneUser: OperationRead<User> = fragment.query(userCodec.exactlyOne())

// Single-column: shorthand methods skip the codec
val allIds: OperationRead<List<Int>> =
    sql { "SELECT id FROM users" }.queryAll(PgTypes.int4)
val maybeName: OperationRead<String?> =
    sql { "SELECT name FROM users LIMIT 1" }.queryMaxOne(PgTypes.text)
val count: OperationRead<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) / Operation[Unit] (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:

// Single-operation form: .transact(tx) handles commit/rollback/close.
fun cities(): List<City> = findCities.transact(tx)

// Multiple operations in one transaction: pass a block to tx.transact { … }.
// Each .run(mc) inside the block shares the same transaction.
fun citiesWithCount(): List<City> = tx.transact { mc ->
    val list = findCities.run(mc)
    val count = countCities.run(mc)
    println("rows: $count")
    list
}

For multiple operations in a single transaction, execute each one inside a transact block:

val countUsers: OperationRead<Long> =
    sql { "SELECT count(*) FROM users" }
        .query(RowCodec.of(PgTypes.int8).exactlyOne())
val recentOrders: OperationRead<List<Order>> =
    sql { "SELECT * FROM orders ORDER BY id DESC LIMIT 10" }
        .query(orderCodec.all())

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

For void operations — DDL, schema setup — use mc.update() inside a transact block:

fun applySchema() {
    val createTable = Fragment.of("CREATE TABLE users (id INT, name VARCHAR(100))").execute()
    val createIndex = Fragment.of("CREATE INDEX idx_users_name ON users (name)").execute()
    tx.execute(Operation.allOf(createTable, createIndex))
}

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

Composing operations​

Operations compose as values: combine, sequence, and chain them so that multiple database actions run in a single transaction without manual connection handling. For the full set of combinators (.combineWith(), .then(), OperationRead.sequence(), OperationRead.ifEmpty(), and more), see Composing Operations.

.combineWith() combines two independent operations:

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

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