Batch operations¶

The code generator creates batch INSERT and UPDATE operations for writing multiple entities in a single statement (one database round-trip). They return the number of affected rows, not the entities: the rows are not read back, so values generated by the database (auto-incremented ids, column defaults, the incremented @Version) are not returned. Read the rows again if you need them.

The number of affected rows is checked against the number of entities: when they differ, the call fails with an SQLError whose code is RowMismatch (OptimisticLockFailed for a stale versioned batchUpdate, see Optimistic locking with @Version). An empty collection succeeds with 0 without touching the database.

Batch insert¶

// Insert multiple entities at once
val users = listOf(
    User(name = "Alice", email = "alice@example.com"),
    User(name = "Bob", email = "bob@example.com"),
    User(name = "Charlie", email = "charlie@example.com")
)

// Returns the number of inserted rows (3)
val inserted: Result<Long> = userRepository.batchInsert(db, users)

Batch update¶

// Update multiple entities at once (each row is matched by the entity's @Id)
val updatedUsers = users.map { it.copy(status = "active") }

// Returns the number of updated rows; fails with RowMismatch if an entity's row does not exist
val updated: Result<Long> = userRepository.batchUpdate(db, updatedUsers)

Warning

A batchUpdate that fails the row count check has already updated the rows that matched. Run it inside a transaction and roll back on failure if you need all-or-nothing semantics.

Database support¶

Both operations are supported by every dialect:

Dialect	`batchInsert`	`batchUpdate`
PostgreSQL	`INSERT ... VALUES (...), (...)`	`UPDATE ... AS t SET ... FROM (VALUES (...), (...)) AS v(...) WHERE t.id = v.id`
SQLite	`INSERT ... VALUES (...), (...)`	`WITH v(...) AS (VALUES (...), (...)) UPDATE ... SET ... FROM v WHERE ...`
MySQL/MariaDB	`INSERT ... VALUES (...), (...)`	`UPDATE ... AS t JOIN (SELECT ... FROM ... WHERE false UNION ALL SELECT ...) AS v ON t.id = v.id SET ...`
Generic	`INSERT ... VALUES (...), (...)`	Like PostgreSQL

On PostgreSQL a null property value is bound with its type (bigint, timestamptz, ...), as the VALUES list takes its column types from its rows.

Note

On MySQL and MariaDB the drivers report the matched rows as affected, so an entity whose values did not change still counts as updated.

Note

The pre-hooks (preInsertHook/preUpdateHook) run for each entity before the statement; the after-hooks (afterInsertHook/afterUpdateHook) run for each entity, as written, once the statement succeeded, and their results are not used. See Repository hooks.