Batch

POST /v1/databases/{id}/batch

A batch is one SQLite transaction and one durable segment. Either every statement committed and the whole thing is in object storage, or none of it happened. There is no partial batch.

It is also the only way to get atomicity here — BEGIN and COMMIT are not available, because a transaction held open across HTTP requests would mean one client’s open transaction blocking the single writer for every other client. See Explicit transactions.

Request

{
  "statements": [
    { "sql": "UPDATE accounts SET balance = balance - ? WHERE id = ?", "params": [100, 1] },
    { "sql": "UPDATE accounts SET balance = balance + ? WHERE id = ?", "params": [100, 2] },
    {
      "sql": "INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)",
      "params": [1, 2, 100]
    }
  ]
}

statements is a non-empty array of at most 100 entries, each with the same sql and params fields as /query. Reads and writes can be mixed freely.

Response

{
  "results": [
    { "columns": [], "rows": [], "rowsAffected": 1, "lastInsertRowid": 1 },
    { "columns": [], "rows": [], "rowsAffected": 1, "lastInsertRowid": 1 },
    { "columns": [], "rows": [], "rowsAffected": 1, "lastInsertRowid": 7 }
  ],
  "epoch": 3,
  "txid": 414,
  "durability": "durable",
  "waitedMs": 119
}

results has one entry per statement, in order, each shaped like a /query response minus the durability fields — those describe the batch as a whole, because the batch committed as a whole.

Why you should use this more than you think

The latency is per batch, not per statement. A durable commit costs two sequential round trips to object storage, and a batch pays for them once:

Approach	Round trips to S3	Wall clock
10 inserts, 10 /query calls	20	~1200 ms
10 inserts, 1 /batch call	2	~120 ms
100 inserts, 1 /batch call	2	~130 ms

A tenfold speedup for restructuring a loop, and it also costs a tenth as much, since the bill is largely PUT requests. If you are writing rows in a loop, you are leaving both on the table.

Failure is all-or-nothing

If any statement fails, the transaction rolls back and nothing in the batch took effect:

{
  "statements": [
    { "sql": "CREATE TABLE audit (a INTEGER)" },
    { "sql": "INSERT INTO does_not_exist VALUES (1)" }
  ]
}

{
  "type": "https://errors.sqlited.dev/sql-error",
  "status": 400,
  "detail": "no such table: does_not_exist"
}

The audit table does not exist afterwards. The error identifies what went wrong but not which index in the batch it came from — if you need that, the statement order and the message are usually enough, and a batch small enough to reason about is a batch worth keeping small.

Caution

A 503 durability-uncertain on a batch is uncertain about the whole batch, atomically. Either all of it is durable or none of it is; you simply do not know which. Read back one row from it to find out.

What a batch is not

Not a way to run more than 100 statements. For a bulk load, send batches of 100 in sequence and accept that each is its own atomic unit. If the load must be atomic overall, build it with a staging table and one final INSERT … SELECT.

Not a transaction you control. You cannot read a result, decide, and then write in the same transaction — the statements are all sent before any of them runs. Where you would reach for that, use a conditional write and check changes():

{
  "statements": [
    { "sql": "UPDATE seats SET taken = 1 WHERE id = ? AND taken = 0", "params": [12] },
    { "sql": "SELECT changes() AS claimed" }
  ]
}

claimed is 1 if you got the seat and 0 if someone else already had it — decided atomically, and durable by the time you read the answer. This pattern replaces most uses of an interactive transaction, and it is better than one anyway: it holds no lock while your application thinks.

Not a savepoint. There is no partial rollback inside a batch, and SAVEPOINT is refused along with the other transaction-control statements.