>_C2CZ
Serverless · Self-Hosting · Security · Independent Ops

D1_ERROR: too many SQL variables at offset 435

By Elif Demir · C2CZ · 27 September 2026 · Last verified: 2026-09-27

Elif Demir covers databases and container networking at C2CZ. Author page · All authors.

D1_ERROR: too many SQL variables at offset 435: SQLITE_ERROR is D1 refusing to prepare a statement that binds more than 100 parameters — and the number after offset is not the row number you think it is. Every D1 query is capped at 100 bound parameters, a limit Cloudflare publishes on its limits page and enforces before your SQL ever reaches the storage layer. The SQLite build you test against locally has no such ceiling: the copy on this host is compiled with MAX_VARIABLE_NUMBER=250000 and accepted a single statement with 163,835 placeholders a few minutes ago. So the bulk insert that is green in wrangler dev, green in CI and green in your integration test is the same statement that throws in production the moment a customer sends a batch one row larger than the one you tested.

This is not an exotic edge case. It is the default outcome of every INSERT … VALUES (?, ?), (?, ?), … loop written without a budget, of every WHERE id IN (?, ?, …) built from an array, and of every ORM insert().values(rows) call, because the ORMs sitting on D1 generally do not chunk for you. Below: what the error says, why local testing cannot catch it, and four fixes — a chunker with a parameter budget, batch() for the round trips, a one-parameter IN list with json_each, and a guard that turns the production error into a local test failure.

TL;DR

  • The cap is 100 bound parameters per query (D1 limits). Placeholders you write yourself, plus any your ORM generates, all count against the same 100.
  • Your local SQLite is not a test of this. Measured on this host: SQLite 3.53.4, MAX_VARIABLE_NUMBER=250000, 163,835 parameters in one statement — all accepted. The D1-specific limit exists only in D1.
  • Do not read the offset as arithmetic. A 50-row × 5-column insert — 250 of your own placeholders — has been reported with offset 435: count your own binds instead.
  • Chunk by the budget, not by a round number. With 4 columns per row, 25 rows fit in 100 parameters. With 5, only 20 do. Compute it: Math.floor((100 - fixed) / paramsPerRow).
  • Send the chunks with env.DB.batch() so the whole import is one round trip and one transaction. A failure anywhere in the batch aborts the sequence — that is the documented behaviour, and it decides how big your chunks should be.
  • Stop expanding IN lists. WHERE id IN (SELECT value FROM json_each(?)) takes a JSON array as a single bound parameter, so a 250-id lookup costs one parameter instead of 250.

What the error is really telling you

Read the three parts of the message separately.

D1_ERROR is the prefix D1 puts on every error it raises through the Workers binding. SQLITE_ERROR is the code the underlying engine returned — the same generic code behind a syntax error, an unknown table or, here, an arity rejection. Neither part tells you what to do. The actionable part is too many SQL variables, and it means exactly one thing: the statement carried more parameters than the platform will prepare. On the limits page it is row number eight of the table, Maximum bound parameters per query: 100, sitting between a 100 KB SQL statement length limit and a 32-argument limit per SQL function.

The offset is where readers lose the thread, because they try to divide. A 435 offset with 5 columns per row looks like row 87, which would be impossible under a 100-parameter cap — so the cap must mean something else. It does not. The offset is a position inside the statement D1 actually prepared, and with ORM-generated SQL that is not the statement you wrote, because drivers rewrite insert().values(rows) into their own parameter layout. The report that motivates this article is drizzle-orm issue #2659: 50 rows into a 5-column table — 250 placeholders in the author's mental model — failing with too many SQL variables at offset 435: SQLITE_ERROR. The offset described the prepared statement, not his array.

So use the offset as a fingerprint, not a calculator: it confirms the class of failure and says nothing about how many rows to send, because you cannot see the prepared statement from the outside unless you build the SQL yourself.

// Count the parameters a statement will bind: skip literals and comments,
// then follow SQLite's numbering — highest index wins, bare ? = highest + 1.
export function countBindParams(sql: string): number {
  let maxIndex = 0;

  for (let i = 0; i < sql.length; i++) {
    const ch = sql[i];

    if (ch === "'" || ch === '"' || ch === "`" || ch === "[") {  // literal or quoted identifier
      const close = ch === "[" ? "]" : ch;
      for (i++; i < sql.length; i++) {
        if (sql[i] === close) {
          if (close !== "]" && sql[i + 1] === close) { i++; continue; }  // doubled = escaped
          break;
        }
      }
      continue;
    }
    if (ch === "-" && sql[i + 1] === "-") {            // line comment
      i = sql.indexOf("\n", i);
      if (i === -1) break;
      continue;
    }
    if (ch === "/" && sql[i + 1] === "*") {            // block comment
      i = sql.indexOf("*/", i + 2);
      if (i === -1) break;
      continue;
    }
    if (ch === "?") {
      const digits = /^\d+/.exec(sql.slice(i + 1))?.[0];
      if (digits) { maxIndex = Math.max(maxIndex, Number(digits)); i += digits.length; }
      else maxIndex++;                                  // bare ? = highest index so far + 1
    }
  }
  return maxIndex;
}

Two separate rules decide that number. The first is lexical: a ? inside a string literal, a quoted identifier or a comment is not a parameter — including the MySQL-style backtick and the square brackets SQLite also accepts (SELECT 1 AS `k?` and SELECT 1 AS [k?] both bind nothing), and counting the characters with a global regex counts it anyway — one phantom parameter for WHERE note = 'why?'. The second is arithmetic: SQLite numbers parameters and reports the highest index used, not the number of tokens. ?NNN sets the index to N, a bare ? takes the highest index so far plus one, and sqlite3_bind_parameter_count returns whatever the maximum ends up being. So SELECT ?, ?, ?1 wants 2 bindings, not 3, and SELECT ?10 wants 10, not 1. Counting distinct ?NNN tokens plus anonymous ones gets both of those wrong, and on the case that matters here it errs in the unsafe direction: VALUES (?200) looks like one parameter and walks straight through a 100-parameter guard. The counter below walks the string, skips literals and comments, and tracks the index:

ok   expected 0 got 0 :: SELECT * FROM t WHERE note = 'why?'
ok   expected 1 got 1 :: SELECT * FROM t WHERE a = ?1 OR b = ?1
ok   expected 1 got 1 :: /* ? */ SELECT ?
ok   expected 100 got 100 :: INSERT ... 25 x (?,?,?,?)
ok   expected 104 got 104 :: INSERT ... 26 x (?,?,?,?)
ok   expected 10 got 10 :: SELECT ?10
ok   expected 2 got 2 :: SELECT ?, ?, ?1
ok   expected 3 got 3 :: SELECT ?2, ?
ok   expected 5 got 5 :: SELECT ?5
ok   expected 3 got 3 :: SELECT * FROM t WHERE x = ?2 AND y = ?1 AND z = ?
ok   expected 0 got 0 :: SELECT 1 AS `k?`
ok   expected 0 got 0 :: SELECT 1 AS [k?]
12 cases, 0 failures

SQLite's own numbering agrees, measured on this host: binding SELECT ?10 is refused with the current statement uses 10, SELECT ?, ?, ?1 with uses 2, SELECT ?2, ? with uses 3 and SELECT ?5 with uses 5. Backtick- and bracket-quoted identifiers bind nothing either: SELECT 1 AS `k?` and SELECT 1 AS [k?] both run with zero parameters, a doubled backtick inside an identifier is an escape (`k``?` is the column k`?), and a doubled bracket is rejected with unrecognized token: "]" — so a bracket runs to its first ] and nothing escapes it. The naive ? sweep reports 1 parameter for every one of those forms, which is the direction that lets an over-budget statement through the guard.

Why your local run never catches it

The limit is a D1 property, not a SQLite property. SQLite's own ceiling is a compile-time constant, SQLITE_MAX_VARIABLE_NUMBER, and modern builds set it high enough that the practical limit is your memory, not the engine. I measured this host rather than quote a number from a forum:

$ python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
3.53.4
$ python3 -c "import sqlite3; print([r[0] for r in sqlite3.connect(':memory:').execute('pragma compile_options') if 'MAX_VARIABLE' in r[0]])"
['MAX_VARIABLE_NUMBER=250000']

Then I asked that engine for the statement D1 would have rejected. The table below is the raw output of a single 4-column insert sized at four different row counts, executed against the local database:

-- same SQL, local SQLite (no D1 cap)
   20 rows /    80 params: OK locally
   21 rows /    84 params: OK locally
   50 rows /   200 params: OK locally
 1000 rows /  4000 params: OK locally

Earlier still, a 32,767-row statement with 163,835 placeholders executed without complaint. Three of those four rows are over the D1 cap and one is 1,600 times over it. If your test harness runs against a local D1 emulation backed by a normal SQLite build, your suite will keep reporting green while production rejects the same SQL — miniflare's local database and your CI container are not the platform that enforces the 100.

The failure is size-dependent, so it appears only for the customers with large imports, and it surfaces as an unhandled 500 rather than a validation error. “Too many variables” reads like a payload problem, which sends you looking at row contents instead of row counts.

Fix 1: chunk against a parameter budget

A chunk size is a division, not a taste. For 4 columns per row the honest answer is 25 rows per statement, which consumes the budget exactly; for 5 columns it is 20; for 12 columns, 8 rows with 4 parameters left unused. Here is the table, computed on this host for the column counts that show up in practice:

cols  rows  params  headroom
   1   100     100         0
   2    50     100         0
   3    33      99         1
   5    20     100         0
   8    12      96         4
  12     8      96         4
  20     5     100         0

Round-number chunk sizes are the common mistake. A BATCH_SIZE = 50 copied from a Postgres habit binds 250 parameters for 5 columns and 600 for 12 columns — both over the cap, both invisible until the data grows past whatever you tested with. Derive the size from the schema instead:

export const D1_MAX_BOUND_PARAMS = 100;

/**
 * Split rows into groups that fit D1's per-query parameter budget.
 * @param rows          the rows to insert
 * @param paramsPerRow  placeholders each row contributes
 * @param fixedParams   placeholders used by the statement outside the row list
 */
export function chunkRows<T>(rows: T[], paramsPerRow: number, fixedParams = 0): T[][] {
  const budget = D1_MAX_BOUND_PARAMS - fixedParams;
  const capacity = Math.floor(budget / paramsPerRow);

  if (capacity < 1) {
    throw new Error(
      `one row needs ${paramsPerRow} parameters, only ${budget} remain after ${fixedParams} fixed bindings`,
    );
  }

  const chunks: T[][] = [];
  for (let i = 0; i < rows.length; i += capacity) {
    chunks.push(rows.slice(i, i + capacity));
  }
  return chunks;
}

The fixedParams argument is the part people miss. Widen the same table with two scoping columns and the arithmetic changes with it:

-- 6 placeholders per row, 2 fixed bindings: floor((100 - 2) / 6) = 16 rows, 98 parameters
INSERT INTO events (tenant_id, batch_id, at, id, kind, payload)
VALUES (?, ?, ?, ?, ?, ?) /* ... repeat to 16 rows ... */;

Six columns per row and two fixed bindings gives Math.floor((100 - 2) / 6) = 16 rows and 98 parameters; 17 rows would be 104 and rejected. Getting that wrong is a one-row-over failure that shows up only on full chunks, i.e. on the largest import you will ever receive.

Fix 2: one round trip per import with batch()

Chunking solves the cap and creates a new cost: 200 chunks of 25 rows are 200 round trips if you await them one at a time, and 200 separate transactions if any of them fails halfway. D1Database.batch() is the documented answer to both. It sends an array of prepared statements in a single call, executes them sequentially and non-concurrently, and treats the whole set as a SQL transaction:

D1 operates in auto-commit. Our implementation guarantees that each statement in the list will execute and commit, sequentially, non-concurrently. Batched statements are SQL transactions. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

That last sentence is a design constraint, not a footnote. A batch is atomic, so a 25-chunk batch of 625 rows either lands whole or disappears whole. For an import endpoint that is usually what you want: the client retries and nothing is half-written. For a long backfill it is the opposite of what you want, because row 600 failing discards rows 1–599 of that batch — and it also means the per-statement results you get back are worth inspecting rather than counting:

type Event = { id: number; kind: string; payload: string; at: string };

const COLS_PER_ROW = 4;

export async function insertEvents(db: D1Database, events: Event[]): Promise<number> {
  const chunks = chunkRows(events, COLS_PER_ROW);

  const statements = chunks.map((chunk) =>
    db
      .prepare(
        `INSERT INTO events (id, kind, payload, at) VALUES ${chunk
          .map(() => "(?,?,?,?)")
          .join(",")}`,
      )
      .bind(...chunk.flatMap((e) => [e.id, e.kind, e.payload, e.at])),
  );

  const results = await db.batch(statements);

  let written = 0;
  for (const [i, result] of results.entries()) {
    if (!result.success) {
      throw new Error(`chunk ${i} failed: ${JSON.stringify(result.error ?? result)}`);
    }
    written += result.meta.changes ?? 0;
  }

  if (written !== events.length) {
    throw new Error(`expected ${events.length} rows written, D1 reported ${written}`);
  }
  return written;
}

Two properties of that function are worth keeping when you adapt it. First, the chunk size is derived from COLS_PER_ROW in one place, so adding a column to the INSERT cannot silently break the arithmetic. Second, the final written !== events.length check catches the case where D1 accepts every statement and quietly writes fewer rows than you sent, which is the normal behaviour for INSERT OR IGNORE and for inserts that hit a unique index. Without it, the endpoint returns 200 and your reconciliation job finds the gap a week later — the same class of silent-partial-write that makes the several faces of “database is locked” so expensive to debug.

One accounting detail deserves a measurement. The limits page lists Queries per Worker invocation: 1,000 (Workers Paid) / 50 (Free), and a batch of 40 statements is 40 statements by any reading. I have not seen Cloudflare publish whether a batch counts as one query against that budget, so measure it on your own plan before designing an import around batches of five hundred. What is documented and safe to rely on is the parameter cap, the transactional behaviour, and the latency win over 200 sequential round trips.

Latency is also where chunk size stops being free: fewer, larger statements mean fewer round trips but a longer single statement, and preparing it runs inside the Workers CPU budget — which is how a well-meaning chunk size of 100 reaches for Error 1102 after a data-layer change. Benchmark two chunk sizes and keep the smaller one.

Fix 3: an IN list is one parameter, not a thousand

The other place the 100-parameter cap bites is the read path. Building IN lists from arrays is the default habit — SELECT … WHERE id IN (${ids.map(() => "?").join(",")}) — and it produces exactly one placeholder per id, so a 250-id lookup is 250 parameters and a hard failure. D1 supports a documented alternative: pass the array as a single bound parameter and let SQLite's JSON1 extension turn it into rows.

SELECT id, kind, payload, at
FROM events
WHERE id IN (SELECT value FROM json_each(?1))

The json_each table-valued function expands a JSON array into one row per element, and because the array arrives as one text parameter the statement costs one of your hundred. I ran it against a real SQLite engine with 250 ids to confirm the shape and the result, and compared it to the naive form:

sql: SELECT count(*) FROM events WHERE id IN (SELECT value FROM json_each(?))
bound params: 1  (payload: JSON array of 250 ids)
result: 1 row (n = 250)   -- count(*) collapses the 250 matches
naive IN-list placeholders: 250 -> over the D1 cap

From a Worker, the binding is a string, so stringify at the call site and keep the array an array in TypeScript:

export async function loadEvents(db: D1Database, ids: number[]): Promise<EventRow[]> {
  if (ids.length === 0) return [];

  const { results } = await db
    .prepare(`SELECT id, kind, payload, at FROM events WHERE id IN (SELECT value FROM json_each(?1))`)
    .bind(JSON.stringify(ids))
    .all<EventRow>();

  return results ?? [];
}

Three caveats. The JSON array grows with the id list and the statement is capped at 100 KB, so a hundred thousand ids is still not a query you can send. The expanded elements are rows to the planner, so the lookup wants an index on events.id like any other IN — this fix rescues your parameter count, not your query plan. And json_each is top-level only: nested ids need a path argument, as covered on the Query JSON page.

Fix 4: make the failure happen in your test suite

All three fixes share an obvious weakness: they are correct until someone edits the SQL. The cheapest insurance is a guard at the boundary between your code and the binding, which is also the only place where you can see both the placeholders and the bindings:

export function assertBindCapacity(sql: string, bindings: unknown[], limit = D1_MAX_BOUND_PARAMS): void {
  const placeholders = countBindParams(sql);

  if (placeholders !== bindings.length) {
    throw new Error(`statement declares ${placeholders} placeholders but received ${bindings.length} bindings`);
  }
  if (placeholders > limit) {
    throw new Error(`statement binds ${placeholders} parameters, D1 allows ${limit}: chunk before calling`);
  }
}

export async function run(db: D1Database, sql: string, bindings: unknown[]) {
  assertBindCapacity(sql, bindings);
  return db.prepare(sql).bind(...bindings).run();
}

Then test the failure path, not just the happy one. A test that only inserts four rows proves nothing about the cap; a test that builds the largest statement your code can produce proves it stays under 100 with room to spare:

import { describe, expect, it } from "vitest";
import { assertBindCapacity, chunkRows, D1_MAX_BOUND_PARAMS } from "../src/d1";

describe("d1 bind budget", () => {
  it("keeps the biggest INSERT under the cap", () => {
    const rows = Array.from({ length: 10_000 }, (_, i) => ({ id: i }));
    const chunks = chunkRows(rows, 4);

    for (const chunk of chunks) {
      const sql = `INSERT INTO events (id, kind, payload, at) VALUES ${chunk
        .map(() => "(?,?,?,?)")
        .join(",")}`;
      const bindings = chunk.flatMap((r) => [r.id, "", "", ""]);
      expect(bindings.length).toBeLessThanOrEqual(D1_MAX_BOUND_PARAMS);
      expect(() => assertBindCapacity(sql, bindings)).not.toThrow();
    }
    expect(chunks.flat()).toHaveLength(rows.length);
  });

  it("rejects an over-budget statement before the request leaves the Worker", () => {
    const sql = `SELECT * FROM events WHERE id IN (${Array(101).fill("?").join(",")})`;
    expect(() => assertBindCapacity(sql, Array(101).fill(1))).toThrow(/D1 allows 100/);
  });
});

The second test is the one that pays for itself: it asserts the guard fires before a request is made, which is the difference between a caught mistake in CI and a 500 in production. If you would rather not maintain a guard, the next best thing is a query-level assertion in a staging script, run against the database that enforces the limit:

# run a probe statement against the real D1 instance, not the local emulation
npx wrangler d1 execute app-db --remote --json \
  --command "SELECT count(*) AS n FROM events WHERE id IN (SELECT value FROM json_each('[1,2,3]'))"

The --remote flag matters. Without it the command runs against the local database and, as the measurements above show, a local database is not the thing that was rejecting you.

If an ORM builds the statement

When Drizzle, Kysely or Prisma writes the SQL, you do not control the placeholder layout and the offset you get back will not match your arithmetic — the drizzle #2659 report is the canonical example, and the related #2479 thread is still open. The reliable pattern is to keep the ORM for queries and chunk before you hand it rows:

// drizzle does not chunk multi-row inserts for D1: do it at the call site
const CHUNK = 25; // 4 columns per row, 100-parameter budget

for (let i = 0; i < rows.length; i += CHUNK) {
  await db.insert(events).values(rows.slice(i, i + CHUNK));
}

That is slower than one statement and correct on the day a customer sends 4,000 rows. If throughput matters, wrap the same slices in db.batch() where the ORM exposes it, or drop to env.DB.prepare() for the write path — the statement in Fix 1 is twenty lines with no ORM-level parameters to get wrong.

A short checklist before you deploy the insert

  • Compute Math.floor((100 - fixedParams) / paramsPerRow) and pin it in one constant next to the schema.
  • Send chunked statements through batch(); decide deliberately whether a failed batch should roll back 25 rows or 2,500.
  • Replace generated IN lists with json_each(?) and one stringified parameter.
  • Check meta.changes against the row count you sent, and throw when they disagree.
  • Test the largest statement your code can emit, against the 100-parameter budget, in CI.
  • Re-read the limits table whenever you add a column: the 100 KB statement cap and the 2 MB row size are the next two walls.

Last verified: 2026-09-27 — the 100-parameter cap, the 100 KB statement limit, the 2 MB row limit and the 1,000/50 queries-per-invocation figures come from Cloudflare's D1 limits page, and the batch() transactional wording is quoted from the D1 Database API reference. Every number in the measurement blocks was produced by scripts executed on this host immediately before publishing (SQLite 3.53.4 via Python 3.13.5, MAX_VARIABLE_NUMBER=250000; Node v26.5.1 for the parameter counter), including the 163,835-parameter statement, the json_each run and the twelve counter cases. The offset 435 example is a verbatim error string from drizzle-orm issue #2659, labelled as a third-party report and not a reproduction; no D1 request was made from this host.

C2CZ

Hyper-specific engineering guides: Cloudflare Workers, Turso, self-hosting, network security, offensive security, independent operations.