Your query works. On your laptop, in CI, in staging with the fifty rows you seeded. Then it meets a real table and returns an error about bound parameters you have never seen before.
Every capability in the kit talks to D1 through Kysely — eighteen of twenty-one packages — which makes the query layer the most-shared code we have. Both are good. What sits between them is work every serious application on D1 eventually has to do.
Four problems come with the territory, and they share the property above: each passes on a laptop and only shows up with real data. None lives in a capability. They are all in core, so composing a capability means inheriting the answers.
Parameter budgets, not parameter counts
D1 rejects any statement carrying more than 100 bound parameters.
That number is easy to design around once you know it, and the obvious fix is to chunk long in (...) lists at 100. The obvious fix is also wrong, in a way that is worth spelling out:
where("indexName", "=", name).where("id", "in", <100 ids>)That binds 101. The name is a bound parameter too. An update … set(a, b) ahead of the list has already spent two before a single id is counted.
So the unit that matters is not the cap. It is the budget left after your statement’s own fixed parameters. A call site declares how many parameters it binds besides the list, and the chunk size is derived from that.
A list and an insert are different arithmetic
There are two chunkers, and the reason is worth internalizing.
An in (…) list binds one parameter per value. A multi-row insert binds one per column, per row. Use the list chunker for an insert and you silently allow columns times too many rows.
That is not hypothetical. One capability shipped exactly that bug — capping attachments at 10 rows of 11 columns, which is 110 parameters against a cap of 100, and losing every attachment to too many SQL variables. Ten rows sounds conservative. It was over by 10%.
So chunkRowsByBoundParameters takes a column count and divides the budget by it. Separate function, because the arithmetic is genuinely different and sharing one would reintroduce the bug.
And a backstop at the only place the number is real
Here is the part I like most, because it admits something.
The arithmetic above sat in the codebase, correct and available, while four capabilities bound past the cap anyway — importing nothing, doing it by hand, getting it wrong. The list of sites that got this wrong had been wrong five times running.
A rule that lives at every call site holds only where somebody remembered it. So the rule moved to the thing being called: every database is wrapped so that a statement over the cap fails as the rule, not as SQLite’s complaint.
The check happens at bind, and only there, because that is the only moment the number is real — after Kysely has compiled the statement and after any chunking the caller did. Anything measured earlier measures intent.
It does not chunk, and it cannot. Splitting an arbitrary statement is the caller’s arithmetic. What it buys is that you get told which rule you broke and which statement broke it, one step before the platform says too many SQL variables and leaves you to work out which list was too long.
A capability written next month that has never heard of the cap is covered by it. So is one written last year.
Retrying the failures worth retrying
D1 fails in two very different ways that look identical at the call site.
Some are transient: a timeout, a database briefly locked under contention, a dropped connection, a storage object reset mid-flight, an opaque internal error. All worth retrying with backoff.
Others are deterministic: a constraint violation, a SQL error. Retrying those is pointless at best, and at worst turns a clear failure into a slow one.
D1 distinguishes them only by message text, so that is what the matchers key on. Anything that does not match is re-thrown untouched — a passthrough, never an originator, so a foreign throw is not dressed up as one of ours.
The subtle part is the idempotency guard, which is always on:
You only get that right if you write it down once. Eighteen packages each reasoning about D1’s error vocabulary would produce eighteen slightly different answers, and the differences would only surface under load.
The types SQLite does not have
SQLite has no booleans, no real dates and no JSON. It has 0 | 1, millisecond-epoch numbers, and strings.
Rather than converting at every call site, conversion is the schema’s job. Codecs are bidirectional: .parse() decodes from the database, .encode() encodes back, and the decode side is a z.union rather than z.preprocess so the schema stays encode-compatible both ways.
There is a sharp edge here worth knowing if you write your own. The rule across the kit is that a boundary reader never throws — every one is safeParse(...) returning null on failure, and callers depend on that. But safeParse only catches ZodError. It does not catch an arbitrary exception raised inside a transform. So a transform that hits a value it cannot convert and simply throws would sail straight past safeParse and out of a function documented as never throwing.
So a codec that meets an unconvertible value pushes an issue onto the payload and returns z.NEVER. Zod aborts, the failure arrives as { success: false }, and the promise every reader was written against still holds.
Pagination that holds still while people write
Offset pagination assumes the rows underneath you hold still. None of these tables do — audit events, email jobs, users and ledger transactions are written constantly while somebody reads them.
With OFFSET 25, a row inserted at the head pushes one row from page one onto page two, so a client paging through sees it twice. A deletion drops a row entirely and the client never learns it existed. Nobody gets an error. The data is just quietly wrong.
Keyset pagination names the last row’s sort position instead, so the next page starts exactly where the previous one ended regardless of what happened in between.
The pattern underneath
Three of these four began inside one capability and moved into core the moment a second needed them. Cursor decoding is the clearest case: the difference between “malformed cursor” and “500” is a caller’s ability to probe your API, and four copies of a security-adjacent decode is four chances for one to be wrong.
The rule the kit follows is that a capability may depend on a core seam but never on a sibling. When the same problem shows up twice, it goes down into core rather than sideways between packages.
You get the same seam when you build your own capability, for the same reason.
None of this is exotic, and none of it is a complaint about D1. It is the ordinary distance between a database primitive and a query layer you would build a product on — parameter budgets, a retry policy that knows what not to retry, a type system over untyped storage, and pagination that survives concurrent writes.
The point of core is that the distance gets traveled once. Run pithy add for anything and the four answers arrive attached to it.