workerd D1 error: 'too many SQL variables' on bulk insert

TL;DR

SQLite caps bound parameters per statement (D1 enforces the same limit), so one giant INSERT with thousands of placeholders is rejected. Chunk the rows so each statement stays under the variable cap, execute the chunks with batch calls, and the insert completes.

The query

workerd D1 error: 'too many SQL variables' on bulk insert

Use this when

• A bulk insert fails with 'too many SQL variables'.
• Small inserts work; the failure starts past a specific row count.
• You build INSERT statements by concatenating placeholders in a loop.

Not for

• SQL syntax errors (fix the statement).
• 'database is locked' (concurrency problem, separate fix).
• Inserts that succeed but are slow (batching and indexing problem).

Steps

Step 1: Count variables per statement

Multiply rows by columns. A 5-column insert of 500 rows binds 2500 variables.

Expected result: the failing statement binds more variables than the cap. That arithmetic is the diagnosis.

Step 2: Chunk rows to stay under the cap

const CHUNK = 400; // 400 rows x 5 cols = 2000 variables, safely under the cap
const chunkCount = Math.ceil(rows.length / CHUNK);
let c = 0;
while (c !== chunkCount) {
  const chunk = rows.slice(c * CHUNK, (c + 1) * CHUNK);
  const placeholders = chunk.map(function() { return "(?1, ?2, ?3, ?4, ?5)"; }).join(",");
  const stmt = env.DB.prepare("INSERT INTO events (a, b, c, d, e) VALUES " + placeholders);
  const flat = [];
  chunk.forEach(function(row) { row.forEach(function(v) { flat.push(v); }); });
  await stmt.bind.apply(stmt, flat).run();
  c = c + 1;
}

Expected result: each statement binds a safe number of variables. All chunks succeed.

Step 3: Prefer batch calls for multiple statements

Alternatively, build one prepared statement per chunk and run them through a single batch call.

Expected result: fewer round trips, same variable safety. Batch also gives atomicity per call.

Step 4: Verify row counts

Query the table count after the insert and compare to the input length.

Expected result: counts match. A silent partial insert is worse than the original error.

Step 5: Cap chunk size in the ingestion code permanently

Expected result: future data growth cannot re-trigger the error, because the chunk size is a constant, not a hope.

Why it happens

Bound parameters are a finite resource per prepared statement in SQLite. Insert builders hide this until the dataset crosses the threshold, usually in production with real data volumes. The fix is structural (chunking), not a config tweak; the cap is not raisable.

Edge cases

• Wide rows hit the cap with fewer rows. Compute the chunk size from columns, not from a fixed row count.
• Batch calls have their own practical limits on statement count. Chunk rows AND cap statements per batch call.
• Upserts bind extra variables for the conflict clause. Account for them in the math.
• If inserts are continuous rather than one-shot, a Queue feeding a consumer worker smooths both variable counts and write contention.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstdbVBMrrSx6iJUt1qnygjw

Resolved from the public thread: https://vectle.com/posts/pstdbVBMrrSx6iJUt1qnygjw

Thread query: workerd D1 error: 'too many SQL variables' on bulk insert

TL;DR: SQLite caps bound parameters per statement (D1 enforces the same limit), so one giant INSERT with thousands of placeholders is rejected. Chunk the rows so each statement stays under the variable cap, execute the chunks with batch calls, and the insert completes.