D1: bound params exclude multi-statement SQL

Cloudflare D1 answers "params with multiple statements is not supported". Bound parameters and multi-statement SQL are mutually exclusive, which means you cannot wrap several parameterised writes in one BEGIN and COMMIT. I checked this against the live database rather than inferring it, and it changes how every write path has to be ordered.

What the restriction actually is

You can send several statements in one string. You can send one statement with bound parameters. You cannot do both. The moment a payload contains both a semicolon separating statements and a parameter list, D1 rejects it with that exact message.

The obvious workaround is worse than the restriction. Interpolating values into the SQL to get multi-statement support trades a transaction for an injection surface, on a database reached over HTTP, and no transaction is worth that.

What that costs

It means a batch helper over the REST API is sequential and not atomic. Mine issues the statements one after another. If the connection drops halfway, half the writes landed and half did not, and nothing rolls back.

Any caller has to be ordered so a failure half way through leaves a recoverable state.

That sentence is the whole design rule. It is not a limitation to work around, it is a constraint to write code against, and the code is not harder. It is just ordered deliberately instead of accidentally.

Ordering a write so the failure is survivable

The availability calendar is the clearest case I have. Replacing a week of slots is a delete and an insert. Delete first and an interruption leaves an empty calendar, which silently stops every booking and looks like the feature is broken. Insert first and an interruption leaves duplicates, which the read path already collapses because it groups by day and time.

// Not atomic. So the order is the recovery strategy: insert the new rows
// first, delete the old ids afterwards. An interruption between the two
// leaves duplicates, which the read path collapses, rather than an empty
// calendar, which nobody can recover from.
await db.batch(rows.map((r) => ({
  sql: 'INSERT INTO availability (id, weekday, start_minute, end_minute) VALUES (?, ?, ?, ?)',
  params: [r.id, r.weekday, r.start, r.end],
})));
await db.batch(oldIds.map((id) => ({
  sql: 'DELETE FROM availability WHERE id = ?',
  params: [id],
})));

Same two operations, same absence of a transaction, and the difference between a bad afternoon and nobody noticing is which one runs first.

The timestamp trap in the same family

While you are writing SQL defaults, use the ISO form with the T separator: strftime('%Y-%m-%dT%H:%M:%fZ', 'now'). The space-separated variant looks equivalent and sorts differently, so string comparisons against a JavaScript toISOString stop matching, silently, on rows written by the database rather than by your code.

The rule I would give you

• Assume no transactions. Order every multi-write path so the intermediate state is one a read can survive.
• Prefer duplicates over absence. A read path that deduplicates is cheap; a user whose data vanished is not.
• Never interpolate values to buy multi-statement support.
• Write schema defaults in the T-separated ISO form and nothing else.