Group related writes and inspect plans

Related writes must succeed or fail together when partial state would be wrong. Creating a note and its audit record is one logical change; a crash between two separate statements leaves a note that officially never happened.

D1’s tool for this is batch(). The statements run sequentially inside one transaction — if any statement fails, the earlier ones roll back:

await env.DB.batch([
  env.DB.prepare(
    'insert into notes (user_id, title, created_at) values (?, ?, ?)'
  ).bind(userId, title, Date.now()),
  env.DB.prepare(
    'insert into audit_log (user_id, action, created_at) values (?, ?, ?)'
  ).bind(userId, 'note.created', Date.now()),
])

Note what batch() is not: an interactive transaction where you read, compute in JavaScript, then write. The statements are fixed up front. If a later statement needs a value a previous one produced, restructure — SQLite gives you tools like last_insert_rowid() within the batch.

Clients retry. A user double-clicks, a network hiccup replays a request, and your “one logical operation” runs twice. Make retries safe with an idempotency key: a unique token per logical operation, stored with a UNIQUE constraint, so the second attempt fails cleanly instead of duplicating data.

Read the plan before adding indexes

When a query feels slow, do not guess. Use EXPLAIN QUERY PLAN on important reads:

explain query plan
select * from notes where user_id = 42 order by created_at desc limit 10;
-- SCAN notes

SCAN means SQLite reads the whole table. Add the index that matches the filter and the ordering, then measure again:

create index idx_notes_user_created on notes (user_id, created_at desc);
-- after:
-- SEARCH notes USING INDEX idx_notes_user_created (user_id=?)

SEARCH ... USING INDEX is the confirmation. The pair of plans, before and after, is the evidence that the index earns its cost — because more indexes are not automatically faster. Each one slows every write, so an index that no query plan uses is pure overhead. Add an index when the plan and the workload justify it, and re-check plans when queries change.

Now create a note and its audit record as one batch, force the second statement to fail with a constraint violation, and verify neither row remains.