D1: numbered migrations, prepare().bind(), batch() for atomicity

The answer. D1 is SQLite with a binding. Schema lives in numbered files under migrations_dir; wrangler d1 migrations apply <db> records which ones ran (in a d1_migrations table) and applies only the missing ones, separately for --local and --remote. Queries go through prepare(sql) with ? or ?1 placeholders, bind(...) for values, and one of three terminals: all() for rows plus meta, first() for one row (or first("col") for one value), run() for writes. There is no interactive transaction (no BEGIN across awaits); atomicity comes from batch([...]), which runs an array of prepared statements in a single implicit transaction and rolls all of them back on any failure. RETURNING saves the second query after an insert, and meta.rows_read and rows_written are what you are billed for.

The pattern.

npx wrangler d1 migrations create app-db add-jobs      # migrations/0003_add-jobs.sql
npx wrangler d1 migrations apply app-db --local        # dev database under .wrangler/state
npx wrangler d1 migrations apply app-db --remote       # production; run from CI, not a laptop

const job = await env.DB.prepare("insert into jobs (id, state) values (?1, 'pending') returning *").bind(id).first<Job>();
const [claim, event] = await env.DB.batch([
  env.DB.prepare("update jobs set state = 'claimed', worker = ?2 where id = ?1 and state = 'pending'").bind(id, me),
  env.DB.prepare("insert into events (job_id, kind) values (?1, 'claimed')").bind(id),
]);
if (claim.meta.changes === 0) return null;            // someone else claimed it; the event is rolled back with it

Watch out.

first() on a multi-row result silently returns one row. Use all() and assert the count when it matters.
Never interpolate values into the SQL string; placeholders are the only injection defense.
Time Travel can restore a database to any point in the last 30 days, which is the answer to the UPDATE without a WHERE.