1,400 rows broke my D1 batch — here's why 7 statements beat 1,400

A burst payload of 1,400 event rows turned a working D1 worker into a 3am Slack alert. The error wasn't even what I expected.

The message from wrangler tail said D1_ERROR: too many SQL variables — not "too many statements." That distinction matters. When you use individual prepared statements with bound parameters, you're burning two counts simultaneously: the 1,000-statement batch ceiling and SQLite's 32,766-variable-per-statement cap. Hit either one and the whole batch dies. Most debugging threads focus on the statement count; the variable count is what bit me.

The real fix isn't just chunking into smaller db.batch() calls — that's the obvious move, and it works, but it trades one problem for another. Each db.batch() call is a separate HTTP round-trip to D1's SQLite layer. Split 1,400 individual-row statements into two chunks of 900 and you've gone from one failed request to two successful ones — but you're still writing 1,400 rows against the free-tier daily quota of 100,000 row writes. More importantly, you're making two network calls where one would do.

The better approach: multi-row VALUES inserts, chunked by variable count instead of statement count.

const ROWS_PER_STMT = 200; // 5 columns × 200 = 1000 variables, well under 32766
const chunks = chunkArray(rows, ROWS_PER_STMT);
const stmts = chunks.map((chunk) => {
  const placeholders = chunk.map(() => "(?, ?, ?, ?, ?)").join(", ");
  const values = chunk.flatMap((r) => [r.id, r.session_id, r.event_type, r.payload, r.created_at]);
  return db.prepare(`INSERT INTO events (id, session_id, event_type, payload, created_at) VALUES ${placeholders}`).bind(...values);
});
await db.batch(stmts);

1,400 rows collapses to 7 statements in a single db.batch() call. One round-trip. The statement count stays comfortably under 1,000 and the variable count per statement tops out at 1,000 — leaving a safe margin under the 32,766 ceiling.

I wrote up the full breakdown — including the side-by-side latency numbers between both approaches, the 2MB per-request body cap that creates a third failure mode, and the five wrangler commands I run in sequence when a batch worker goes down — over on dailymanuallab.com.

Full post →