In Cloudflare D1, how does env.DB.batch() make several statements atomic, and why does a Worker not send BEGIN TRANSACTION instead?
answer
- auto-commit per awaited call
- one call, statements in order
- all or nothing, promise rejects
- no code between statements
basics
~20 senv.DB.batch() runs an array of prepared statements in order as one SQL transaction in a single call; if one fails, all roll back and the call rejects. D1 already wraps each query in a transaction, so BEGIN is rejected.
solid answer
~40 sD1 gives a Worker no transaction it can hold open across `await`s: each `prepare().run()` is its own auto-committed statement, so two awaited inserts can leave the first committed when the second fails. `env.DB.batch([stmtA, stmtB])` sends the statements in one call; D1 executes them sequentially, non-concurrently, as one SQL transaction, resolves to an array of `D1Result`s in the same order, and on any failure rolls the whole sequence back and rejects with that statement's error. Because D1 already runs each query in an implicit transaction, a `BEGIN TRANSACTION` or `SAVEPOINT` sent from a Worker fails. The catch is that no JavaScript runs between batch statements, so a statement that needs an earlier one's result gets it in SQL: `last_insert_rowid()`, a subquery or `RETURNING`.
code
javascript · 17 linesexport default {
async fetch(request, env) {
const { email } = await request.json();
try {
const [user, note] = await env.DB.batch([
env.DB.prepare("INSERT INTO users (email) VALUES (?) RETURNING id").bind(email),
env.DB
.prepare("INSERT INTO notes (user_id, body) VALUES (last_insert_rowid(), ?)")
.bind("Welcome to your notes"),
]);
return Response.json({ userId: user.results[0].id, noteId: note.meta.last_row_id }, { status: 201 });
} catch (err) {
// Neither row was written: the batch rolled back as one transaction.
return Response.json({ error: err.message }, { status: 409 });
}
},
};go deeper
Know that batch() takes an array of prepared statements and that it writes all of them or none of them.
Explain auto-commit per call, sequential execution, the ordered array of D1Result, the rejected promise on failure and why BEGIN TRANSACTION fails from a Worker.
Show how ids pass between statements with last_insert_rowid(), subqueries or RETURNING, and when a check must move into SQL or a constraint because no code runs mid-batch.
Decide which multi-write flows the team must batch, how a rolled-back batch surfaces to clients, and when a flow has outgrown a single database transaction.
## Each awaited query commits on its own D1 runs in **auto-commit** mode. When a Worker awaits `env.DB.prepare(...).run()`, that statement is executed and committed before the promise resolves, so two awaited writes are two independent commits. In a notes API, creating an account might insert a row into `users` and then a welcome row into `notes`. If the second insert fails (a constraint, an exception in the Worker between the two calls, a lost connection), the first is already permanent and the account exists without its note. There is no way to hold a transaction open across `await`s either. D1 already runs each query inside an implicit transaction, and a `BEGIN TRANSACTION` or `SAVEPOINT` statement sent from a Worker fails with a `D1_ERROR`. In local development the error message even points to the transaction methods of Durable Object storage, which belong to Durable Objects and are not available on a D1 binding. For D1 the tool is `batch()`. ## What batch() guarantees `env.DB.batch(statements)` takes an array of prepared, bound statements and sends them to the database in one call. The documented guarantees are: 1. The statements run **sequentially and non-concurrently**, in array order. 2. Together they form one **SQL transaction**: if any statement fails, the error is reported for that statement and the entire sequence is rolled back. 3. On failure the promise rejects, so `await` throws, and no partial result array is returned. 4. On success it resolves to an **array of `D1Result` objects**, one per statement and in the same order, each with its own `results` and `meta`. Because the statements execute in order inside one transaction, a later statement sees what an earlier one wrote: an `INSERT` followed by `SELECT COUNT(*)` in the same batch counts the new row. One batch instead of several awaited calls also saves network round trips between the Worker and the database, which matters when the Worker runs far from where the database lives. ## Passing values between statements The price of atomicity is that **no JavaScript runs between the statements of a batch**. Every statement is prepared and bound before the call, and results come back only after the whole transaction has committed. A statement that needs a value produced by an earlier one has to get it in SQL: - `last_insert_rowid()` returns the rowid of the most recent insert, so a welcome note can reference the user inserted just before it. - A subquery such as `(SELECT id FROM users WHERE email = ?)` looks the value up by a natural key. - `RETURNING id` on an insert hands the new id back in that statement's `results`, for the Worker to use after the batch. What a batch cannot do is a decision in code, such as "refuse if the user already has 100 notes". That rule has to move into the SQL (a conditional `INSERT ... SELECT ... WHERE`) or into a constraint that makes a statement fail and so rolls the whole batch back. ## batch() compared with exec() | | `batch()` | `exec()` | |---|---|---| | Input | An array of prepared statements | One string of SQL, statements separated by newlines | | Parameters | Bound with `bind()` | None; values must be written into the SQL | | Result | An array of `D1Result`, with rows and `meta` | A `D1ExecResult` with `count` and `duration` | | On error | Whole batch rolled back, promise rejects | Throws a `D1_EXEC_ERROR`; execution stops at the failing statement | | Intended for | Application writes that belong together | Maintenance and one-shot jobs | `exec()` takes no bound parameters, so it is unsafe for anything built from request data, and its documentation promises only that execution stops at the first error. It is not a substitute for a transaction. ## Limits that still apply - Each statement in a batch is held to the per-query limits, such as the 100 KB maximum statement length and 100 bound parameters. - The 30-second maximum query duration applies to each query and also to the entire batch call. - A batch is the unit of rollback, not of retry. D1's automatic retries cover read-only queries, so retrying a failed write batch is the Worker's decision, and it is safe only when the writes are idempotent.
- If batch() is atomic, why not put every write in one large batch?The 30-second duration limit applies to the whole call, each statement still faces its own size and parameter limits, and one failure discards everything in it. Bulk loads belong in `wrangler d1 execute --file` or in several batches. Keep a batch to the statements that must succeed or fail together.
- How do you tell which statement made a batch roll back?The rejected error carries the failing statement's message, for example a `D1_ERROR` naming the violated constraint and column, such as `UNIQUE constraint failed: users.email`. No partial result array comes back, so log that message with the request context; the database is unchanged, so retrying with corrected input is safe.
- Can one D1 batch mix reads and writes?Yes. Statements of any kind can be batched, and each sees the writes of the statements before it, because they run in order inside one transaction. A trailing `SELECT` is a common way to return the final state in the same call; its rows are in that statement's `D1Result`.
- Two awaited run() calls in one handler form a single transaction.
- Send BEGIN TRANSACTION first and COMMIT last, as with any SQL database.
- batch() runs its statements in parallel to finish faster.
- A failed batch keeps the statements that ran before the error.
- exec() with several statements is a safe atomic alternative to batch().