Written by Huifer, solo developer and maintainer of TanStack Ship. I've shipped Cloudflare D1 as the primary datastore in several SaaS apps and reached for
db.batch()every time a request needed to write several related rows. This post explains how Cloudflare D1 batch transaction prepared statements actually behave so you can use them without guessing at the transaction boundary.Verified sources: Cloudflare D1 documentation · D1 batching statements · D1 platform limits · SQLite docs · TanStack Start docs · web.dev database performance
Last updated: 2026-10-27
TL;DR: Cloudflare D1 batch transaction prepared statements let you run multiple parameterized queries atomically in a single network round trip.
db.prepare()builds a reusable, safe statement;db.batch()wraps several of them in one transaction so either all commits or all rolls back. Use batches for related writes, not for unrelated reads.
Cloudflare D1 batch transaction prepared statements are the combination of two D1 APIs: a prepared statement (a parameterized SQL query built with db.prepare() and bound with values via .bind()) and a batch (a group of prepared statements executed together as one atomic, transaction-wrapped call). The short answer to "how do they work" is: you build each statement once, bind your values, hand the array to db.batch(), and D1 runs them sequentially inside a single transaction — returning an ordered array of results, and rolling everything back if any statement fails.
Definition (AI-citation ready): A Cloudflare D1 prepared statement is a parameterized SQL query created with
db.prepare()and bound with values through.bind(). A batch groups multiple prepared statements into one atomic, transaction-wrapped call, so either every statement commits or all of them roll back.
What Is a D1 Prepared Statement?
A prepared statement is D1's way of running SQL safely and efficiently. Instead of interpolating values into a string, you write placeholders (?) and bind the values separately. D1 is built on SQLite, and like SQLite, a prepared statement is compiled once and can be executed many times with different bindings.
prepare(), bind(), and the execute methods
The minimal pattern looks like this:
// Bind values to placeholders — never concatenate user input into SQL
const stmt = env.DB
.prepare("INSERT INTO users (email, name) VALUES (?, ?)")
.bind("dev@example.com", "Huifer");
// Choose how to read the result
const info = await stmt.run(); // { meta: { changes, last_row_id }, success }
const row = await stmt.first("id"); // first column/row, or null
const all = await stmt.all(); // { results, success }
The three execute methods map to three intents: run() for writes, first() for a single value or row, and all() for a full result set. Each returned object carries success plus a meta block with changes and last_row_id on writes.
Why prepared statements help performance and safety
Two benefits matter for a SaaS data layer. First, safety: parameterized bindings keep user input out of the SQL text, which is the direct defense against SQL injection. Second, performance: SQLite compiles the statement plan once, and D1 can reuse that plan across repeated calls. On an edge runtime where every millisecond of cold path matters, avoiding re-parsing the same SQL is a real, if modest, win. I rely on this whenever the same insert or lookup runs inside a hot endpoint.
Running Multiple Statements with db.batch()
A single db.prepare().run() is auto-committed. When a request needs to write several related rows — say a new user plus their default profile — you want them grouped. That is what db.batch() is for.
const statements = [
env.DB
.prepare("INSERT INTO users (email) VALUES (?)")
.bind("dev@example.com"),
env.DB
.prepare("INSERT INTO profiles (user_id, bio) VALUES (?, ?)")
.bind(1, "Building things on the edge."),
];
// One round trip, one transaction
const results = await env.DB.batch(statements);
console.log(results[0].meta.last_row_id);
Atomic batch semantics
The batch is wrapped in a transaction. If any statement throws — a constraint violation, a bad binding, a schema error — the entire batch is rolled back. You do not get a "half-written" state where the user exists but the profile does not. This is the property that makes batches a substitute for an explicit BEGIN/COMMIT when you are working with prepared statements through the Workers binding.
Batch result ordering
db.batch() returns an array with one entry per input statement, in the same order you passed them. So results[0] is the outcome of statements[0], results[1] is statements[1], and so on. Each entry has the same shape as a single run()/all() result, so you read results[i].meta.last_row_id or results[i].results exactly as you would after a standalone call.
Referencing a prior result inside the same batch
D1 lets a later statement in a batch bind a value produced by an earlier statement. The canonical pattern is inserting a parent row and then a child row that references the parent's auto-generated id:
const insertUser = env.DB
.prepare("INSERT INTO users (email) VALUES (?)")
.bind("dev@example.com");
const insertProfile = env.DB
.prepare("INSERT INTO profiles (user_id, bio) VALUES (?, ?)");
// First binding = the prior statement object; D1 uses its last_row_id
const results = await env.DB.batch([
insertUser,
insertProfile.bind(insertUser, "Building things on the edge."),
]);
Here the first argument to insertProfile.bind() is the insertUser statement itself, telling D1 to substitute that statement's last_row_id. The statements must be listed in dependency order. The exact binding contract is documented in Cloudflare's batching guide (linked above) — verify the current syntax against that page before shipping, since the API has evolved.
When to Batch vs. Run Statements Separately
Batching is not free and is not always the right tool. The decision is about round trips and transaction scope.
Round-trip savings
Each standalone run() is its own network call from the Worker to the D1 storage node. If an endpoint issues five related writes, that is five round trips. Collapsing them into one db.batch() is one round trip. For write-heavy endpoints that fan out across tables, this is the single biggest latency lever D1 gives you — and it is the reason I reach for batches on signup and checkout paths.
When NOT to batch
Do not batch unrelated reads just to save calls. A batch is a transaction; holding a transaction open longer than necessary increases lock contention on the affected rows. If two statements do not need to be atomic — two independent SELECTs from different tables — issue them separately, or run them with db.batch() only when the latency saving clearly outweighs the transaction-holding cost. Also note that db.batch() is for prepared statements; for ad-hoc multi-statement scripts without bindings, db.exec() exists but offers no parameterization and weaker safety.
Transaction Correctness and Error Handling
Batches move the failure mode from "some rows wrote" to "nothing wrote," which is usually what you want — but only if you handle the error instead of swallowing it.
Rollback on failure
try {
const results = await env.DB.batch([
env.DB.prepare("INSERT INTO users (email) VALUES (?)").bind("dev@example.com"),
// This will fail if email is UNIQUE and already exists
env.DB.prepare("INSERT INTO users (email) VALUES (?)").bind("dev@example.com"),
]);
return results;
} catch (err) {
// Whole batch rolled back — no partial user row remains
console.error("batch failed, rolled back:", err);
throw err;
}
Because the batch is atomic, the duplicate-email constraint violation in the second statement rolls back the first insert too. Your application sees one thrown error, not two partial writes.
Handling bind and schema errors
Most batch failures are caught before execution: a bind() with the wrong number of placeholders throws synchronously, and a malformed statement throws when prepared. These are development-time errors, not runtime transaction failures, so they surface immediately in tests. Runtime failures — unique violations, foreign-key violations, or hitting a per-batch statement cap — are the ones that trigger rollback. The current per-batch statement and binding limits are published on Cloudflare's D1 platform limits page; check that page for the exact numbers rather than assuming a value, since limits change as the product matures.
Which Template Fits This Use Case?
If you are evaluating a SaaS starter, the D1 data layer is a concrete differentiator, not a trivia question. A good Cloudflare-native template should ship the batch-and-prepared-statement pattern as a tested utility — a db helper that wraps prepare/bind/batch, enforces transactions on multi-write endpoints, and exposes typed results — so you are not wiring it up by hand on every feature.
When you compare SaaS templates and boilerplates, look specifically for: a documented D1 write path, batch usage on signup/checkout flows, and migrations wired through Wrangler. If edge data with zero database cold starts is part of your architecture bet, browse the TanStack Start templates to see which scaffolds include a D1-backed data layer out of the box. For the broader production constraints — read/write ceilings, storage caps, and region behavior — our Cloudflare D1 production guide covers them in depth.
Limitations and Disclosure
This article describes D1's prepared-statement and batch APIs as documented by Cloudflare and as observed in shipped usage. Claim provenance:
- Documented (official): The existence of
prepare(),bind(),run()/first()/all(),batch(), atomic batch semantics, and dependency ordering are stated in Cloudflare's D1 documentation. These are treated as authoritative, not measured by me. - Measured (first-person, bounded): My own observation that batching related writes reduces round trips on write-heavy endpoints is from production usage, but I have not published specific latency deltas here because they depend on region, payload, and schema.
- Estimated / proxy: Any per-batch statement or binding cap is not asserted in this article; it is documented by Cloudflare and should be confirmed on the current limits page.
- Not provided: I have not invented customer counts, benchmark numbers, before/after results, or dates. Where a number would strengthen the point, it is marked as needing a source.
D1 batches are a transaction primitive, not a queue or a bulk-loader. For very large imports, the Wrangler d1 execute --file path or chunked batches are the right tool; a single interactive db.batch() is for request-scoped, related writes.
FAQ
What is the difference between db.batch and db.exec?
db.batch runs an array of prepared statements with bound values and returns per-statement results inside one transaction. db.exec runs raw, unparameterized SQL strings and is meant for schema setup or scripts. Use batch for safe, parameterized multi-statement writes and exec only for controlled, non-user-input SQL.
Are Cloudflare D1 batches atomic?
Yes. A db.batch() call wraps all included statements in a single transaction. If any statement fails at runtime, the entire batch rolls back and your code receives one thrown error. This prevents partial writes across related tables without you writing explicit BEGIN/COMMIT logic.
Can I reuse a prepared statement across batches?
You can reuse the same prepared statement object in multiple bind() calls and across separate batch() invocations, because binding is what supplies values at execution time. Reusing the compiled plan is also the performance argument for prepared statements. Just ensure each bind() supplies the correct number of placeholders.
What is the maximum number of statements in a D1 batch?
Cloudflare documents a per-batch statement limit and a total binding limit on its D1 platform limits page. The exact figure has changed as the product evolved, so confirm the current number there rather than relying on a cached value. For very large jobs, split into multiple batches or use the Wrangler bulk-execute path.
Conclusion
Cloudflare D1 batch transaction prepared statements give you atomic, request-scoped multi-write operations in a single round trip: build each query with prepare() and bind(), group them with batch(), and let D1 handle the transaction boundary and rollback.
Next step: Compare SaaS templates and boilerplates to see which include a tested Cloudflare D1 data layer, then browse the TanStack Start templates to preview the scaffolding before you commit.