# 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](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/#optimistic-locking-with-version>)). An empty collection succeeds with `0` without touching the database.

## Batch insert

```kotlin
// 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

```kotlin
// 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](<https://smyrgeorge.github.io/sqlx4k/codegen/hooks/index.md>).