Group related writes and inspect plans

Some writes belong together. Creating a note and its audit record is one logical change. If the Worker crashes between two separate statements, you get a note that officially never happened.

D1’s tool for this is batch(). The statements run one after the other inside a single transaction. If any of them 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()),
])

Notice what batch() is not. It’s not an interactive transaction where you read, compute something in JavaScript, then write. The statements are fixed before anything runs. If a later statement needs a value the previous one produced, use SQL for that. last_insert_rowid() works inside the batch.

Make retries safe

Clients retry. A user double-clicks, a flaky network replays a request, and your “one logical operation” runs twice.

The fix is an idempotency key: a unique token the client generates per logical operation. Store it in a column with a UNIQUE constraint. The second attempt hits the constraint and fails cleanly, instead of creating a duplicate note.

Read the plan before adding indexes

When a query feels slow, don’t guess. Ask SQLite how it plans to run it:

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 both the filter and the ordering, then ask 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 two plans, before and after, are your evidence that the index earns its cost.

Because more indexes are not automatically faster. Every index slows down every write on that table. An index no query plan uses is pure overhead. My rule: add an index when the plan shows a SCAN on a query that matters, and check the plans again whenever the queries change.

Try this on the notes app: create a note and its audit record in one batch, then force the second statement to fail with a constraint violation. Check the tables afterwards. Neither row should be there.