Cloudflare D1 skill for Codex

Done well means: schema changes go through numbered migration files applied to local, then staging, then production; every query uses bound parameters; multi-statement writes use batch(); hot queries are index-backed and checked with EXPLAIN QUERY PLAN; and a Time Travel bookmark is noted before any risky production change.

Is D1 the right store?

Need	Use
Relational data, SQL, joins, moderate write rate per database	D1
Global key-value reads, rare writes, eventual consistency OK	Workers KV
Strongly consistent state per user/room/tenant with high write rate	Durable Objects (SQLite storage)
Large blobs and files	R2, with keys stored in D1
Existing Postgres/MySQL, heavy analytics	Hyperdrive to that database

Each D1 database executes queries one at a time. Throughput is roughly 1 / average query duration, so a 1 ms query allows on the order of 1,000 queries per second and a 100 ms query only about 10. Databases are capped in size (10 GB on Paid at the time of writing), so large multi-tenant products often use one database per tenant.

Inspect first

1. Read AGENTS.md, the Wrangler config (d1_databases, migrations_dir) and the migrations/ folder. Never edit an already-applied migration file.
2. Check state (needs network and auth for --remote):

npx wrangler d1 list
npx wrangler d1 info shop
npx wrangler d1 migrations list shop --local
npx wrangler d1 migrations list shop --remote

Create and bind

npx wrangler d1 create shop            # prints the database_id

"d1_databases": [
  { "binding": "DB", "database_name": "shop", "database_id": "<uuid>", "migrations_dir": "migrations" }
]

Run npx wrangler types afterwards so env.DB is typed as D1Database. Repeat the binding inside each env.<name> block, pointing staging at its own database.

Migrations

npx wrangler d1 migrations create shop add_orders     # creates migrations/0002_add_orders.sql
npx wrangler d1 migrations apply shop --local
npx wrangler d1 migrations apply shop --remote --env staging
npx wrangler d1 migrations apply shop --remote        # production: confirm with the user

-- migrations/0002_add_orders.sql
CREATE TABLE orders (
  id          TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers(id),
  status      TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
  total_cents INTEGER NOT NULL,
  created_at  INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);

• Migrations are tracked in the d1_migrations table; applied files are skipped.
• SQLite ALTER TABLE is limited (no dropping constraints, limited column changes). The rebuild pattern is: create orders_new, copy, drop old, rename. D1 enforces foreign keys, so wrap such rebuilds with PRAGMA defer_foreign_keys = true; at the top of the migration.
• Store timestamps as INTEGER epoch seconds or ISO TEXT consistently; SQLite has no native timestamp type.
• Destructive migrations (DROP TABLE, column removal, data rewrites) need user confirmation and a Time Travel bookmark recorded first.

Querying

// One row
const order = await env.DB.prepare(
  "SELECT id, status, total_cents FROM orders WHERE id = ?1 AND customer_id = ?2",
).bind(orderId, customerId).first<{ id: string; status: string; total_cents: number }>();

// Many rows
const { results, meta } = await env.DB.prepare(
  "SELECT id, total_cents FROM orders WHERE customer_id = ?1 ORDER BY created_at DESC LIMIT ?2",
).bind(customerId, 20).all();
console.log("rows_read", meta.rows_read);

// Atomic multi-statement write: batch() runs as one transaction, all or nothing
await env.DB.batch([
  env.DB.prepare("INSERT INTO orders (id, customer_id, status, total_cents) VALUES (?1, ?2, 'pending', ?3)")
    .bind(id, customerId, total),
  env.DB.prepare("UPDATE inventory SET qty = qty - ?1 WHERE sku = ?2 AND qty >= ?1").bind(qty, sku),
]);

Method	Returns
.first() / .first("col")	First row (or one column) or null
.all()	{ results, meta }
.run()	{ meta } with changes, last_row_id, rows_read, rows_written
.raw()	Arrays instead of objects (smaller payloads)
DB.batch([...])	Array of results, executed in one transaction
DB.exec(sql)	Raw multi-statement SQL, no binding: maintenance only, never with user input

Rules:

• Never interpolate values into SQL strings. Use ?1-style placeholders and .bind().
• D1 does not support BEGIN/COMMIT from the Worker API. Use batch() for atomicity, or a single statement with conditions (UPDATE ... WHERE qty >= ?) and check meta.changes.
• IN (...) lists are bounded by the per-query parameter limit (100 bound parameters); chunk larger lists.
• Interactive read-modify-write across separate statements is not isolated; fold logic into one statement or move the entity to a Durable Object.

Cost and performance

Billing counts rows read and rows written, not queries. A full scan of 1M rows bills 1M reads every call.

npx wrangler d1 execute shop --remote --command \
  "EXPLAIN QUERY PLAN SELECT id FROM orders WHERE customer_id = 'c_1' ORDER BY created_at DESC LIMIT 20"

• SCAN orders on a hot query means a missing index; SEARCH orders USING INDEX is the goal.
• meta.rows_read far above rows returned is the cheapest signal of a bad plan; log it.
• Each index adds write cost; index columns used in WHERE, JOIN and ORDER BY of real queries.
• PRAGMA optimize after large data changes refreshes planner statistics.
• Paginate with keyset (WHERE created_at < ?1 ORDER BY created_at DESC LIMIT 20), not large OFFSET, which reads and discards rows.

Read replication

With read replication enabled, use the Sessions API so a user reads their own writes:

const session = env.DB.withSession(request.headers.get("x-d1-bookmark") ?? "first-unconstrained");
const rows = await session.prepare("SELECT ...").bind(id).all();
response.headers.set("x-d1-bookmark", session.getBookmark() ?? "");

Use "first-primary" when the first query must see the latest data. This feature is newer; confirm availability for the account before relying on it.

Backups and restore

npx wrangler d1 time-travel info shop                                  # current bookmark
npx wrangler d1 time-travel restore shop --timestamp=2026-11-04T09:30:00Z   # overwrites current data
npx wrangler d1 export shop --remote --output=shop-2026-11-04.sql
npx wrangler d1 execute shop-copy --remote --file=shop-2026-11-04.sql

Time Travel restores the whole database to a point in time (retention depends on plan, 30 days on Paid at the time of writing). Restore is destructive for anything written after that point: confirm with the user, and record the pre-restore bookmark printed by the command so the restore itself can be undone. To recover only a few rows, Time Travel cannot target another database: restore, export the needed rows, restore forward to the pre-restore bookmark, then re-insert them. Writes arriving during that window are lost, so pause writers or schedule it with the user.

ORMs

Drizzle supports D1 (drizzle-orm/d1): const db = drizzle(env.DB). Generate SQL with drizzle-kit into the migrations_dir and still apply with wrangler d1 migrations apply, so there is one migration history. Prisma also has a D1 adapter; check the project’s choice in AGENTS.md before adding either.

Gotchas

• --local and --remote are different databases. Forgetting --remote makes a “successful” production migration that touched only .wrangler/state.
• Large imports: split into files of a few thousand statements; a single huge statement hits the statement size limit.
• Booleans are 0/1 integers; JSON is TEXT (use json_extract and generated columns with indexes for queried JSON fields).
• last_row_id is meaningless for TEXT primary keys; generate IDs in the Worker.

Verification

• wrangler d1 migrations list shop --remote shows no pending migrations after apply.
• EXPLAIN QUERY PLAN for each hot query shows index use; meta.rows_read close to rows returned in logs.
• Tests run against local D1 (Vitest Workers pool or wrangler dev) with migrations applied.

Report template

**Database:** shop (D1, binding DB), staging shop-staging
**Migrations:** 0002_add_orders applied local -> staging -> production (user approved)
**Pre-change bookmark:** <bookmark> (Time Travel restore point)
**Queries:** 3 hot queries index-backed; rows_read per request 20-40 (was 48,000)
**Atomic writes:** order create + stock decrement in one batch()