Database

Every app is created with its own SQL database — Cloudflare D1, which is SQLite — bound as c.env.DB. There's no connection string, no pool, no setup. Each environment gets its own database, so staging queries never touch production rows.

Querying from Worker code

Prepared statements, always bound:

// Many rows
const { results } = await c.env.DB
  .prepare('SELECT id, name, email FROM leads WHERE status = ? ORDER BY created_at DESC LIMIT 20')
  .bind('new')
  .all<{ id: number; name: string; email: string }>();

// One row, or null
const lead = await c.env.DB
  .prepare('SELECT * FROM leads WHERE id = ?')
  .bind(id)
  .first<Lead>();

// Write
const res = await c.env.DB
  .prepare('INSERT INTO leads (name, email) VALUES (?, ?)')
  .bind(name, email)
  .run();

Non-negotiables:

• Always .bind(). String-concatenated SQL is an injection hole, full stop.
• Always pass the type parameter — .all<T>(), .first<T>(). Otherwise you're handling unknown and TypeScript can't help.
• .all() returns { results }, .first() returns the row or null, .run() returns result metadata.

Migrations

Schema changes are files in app/migrations/, numbered and named:

app/migrations/
├── 0001_i18n.sql
├── 0002_demo_requests.sql
├── 0003_leads.sql
└── 0004_add_lead_status.sql   ← your new one

sf deploy applies any file whose name isn't yet recorded in the app's _migrations table, in order, per environment. Because tracking is by filename, a migration never runs twice — and it does run on a brand-new environment, which is what makes staging reproducible.

-- app/migrations/0004_add_lead_status.sql
ALTER TABLE leads ADD COLUMN status TEXT NOT NULL DEFAULT 'new';
CREATE INDEX idx_leads_status ON leads(status);

Never edit a migration that has already been applied. Your edit won't run here (the filename is recorded) but will run on a fresh environment — so the two databases silently diverge. Always add a new file.

Two ways to change the database — don't mix them up

You want	Do this
Schema that must replay on fresh environments	A new file in app/migrations/NNNN_*.sql, applied by sf deploy
Something executed right now (data seed, one-off fix)	sf d1 execute "..."
Executed now and recorded so deploy won't replay it	sf d1 migrate apply --sql-file ./migrations/0004_x.sql
Read-only inspection	sf d1 select "..."

sf d1 migrate apply plans first and gates destructive changes behind --accept.

Inspecting and recovering

sf d1 schema                       # dump the DDL
sf d1 inspect                      # structured schema introspection
sf d1 select "SELECT COUNT(*) FROM leads"
sf d1 migrate list                 # what's applied
sf d1 history                       # Time Travel bookmarks
sf d1 restore --minutes-ago 30 --yes

Time Travel is real point-in-time recovery — the answer to "I just ran the wrong UPDATE". Note that restoring is itself destructive: it rolls the whole database back, discarding everything after that point. Check sf d1 history first, and rehearse on a non-production environment when the stakes are high.

The Data button in the desktop app browses the same database, scoped to the environment selected in that app view.

Performance: this is the section that matters

A D1 database executes queries serially. One slow query doesn't slow one page — it stalls every request in the app behind it. Middleware that queries the database on every page turns an unindexed table scan into a site-wide outage. Two obligations follow.

1. Index every hot column

Every column used in a WHERE, a JOIN, or a correlated subquery needs an index. The highest-risk pattern is a correlated aggregate:

-- Cost multiplies by the number of outer rows. Index big_table.fk or don't write this.
SELECT p.*, (SELECT COUNT(*) FROM orders WHERE product_id = p.id) AS order_count
FROM products p;

Verify with the query planner rather than intuition:

sf d1 select "EXPLAIN QUERY PLAN SELECT * FROM leads WHERE status = 'new'"

A SCAN on a table beyond a few hundred rows means a missing index — add a CREATE INDEX migration. Data grows after launch. A query that's fine at 100 rows takes seconds at 100,000, and it will be fine right up until it isn't.

2. Query through the request session

Reads can be served by regional read replicas, which multiplies read throughput. Scaffolds ship a d1Session() middleware (app/src/lib/db.ts) registered in index.tsx; it opens one session per request and tracks a bookmark in a cookie so every visitor reads their own writes.

// ✅ Default for ~95% of routes — same API as c.env.DB
const { results } = await c.get('db').prepare('SELECT * FROM products').all();

Situation	Use
Catalog pages, product pages, carts, account pages, admin CRUD	c.get('db')
Checkout, payment, inventory decrement, uniqueness or redemption limits, webhooks reacting to another actor's writes	c.env.DB.withSession('first-primary')
Scheduled handlers, queue consumers, /_spike/operations/* routes	plain c.env.DB

And two hard rules: never create a session at module scope (per-request only), and never share a bookmark between users — cookie only, never a shared store.

Replication multiplies read throughput; it does not fix slow queries, because every replica also executes serially. Indexes first, replicas for scale.

Debugging note: sf d1 select and the desktop app's Data browser always read the primary, so they can look fresher than a replica-served page.

Writes to shared data

When multiple actors mutate the same row — an appointment both a customer and staff can change, an order, an inventory count — ad-hoc UPDATEs race and lose writes. Put the write behind a transactional route under /_spike/operations/<name> that enforces the invariant in one place, and guard concurrency with a version column:

UPDATE bookings SET slot = ?, version = version + 1
WHERE id = ? AND version = ?

A stale caller gets zero rows changed — return a 409 — instead of silently clobbering someone. These routes must verify their caller (the build fails if one doesn't, since an unauthenticated operation route is a write path anyone can POST), and once published they're callable by your agents as tools, which is how agents change shared data without improvising SQL.

The companion store

Every app also has a KV namespace at c.env.KV:

await c.env.KV.put('feature:new-checkout', 'on', { expirationTtl: 3600 });
const flag = await c.env.KV.get('feature:new-checkout');

Reach for KV for hot key lookups, cached values, sessions, and flags. It's eventually consistent — writes take seconds to propagate — so anything needing queries, relations, or read-after-write consistency belongs in the database.

Next

• Files and assets — what does not belong in the database.
• Durable Objects — when you need per-room consistency instead of a table.
• Environments — how migrations behave across staging and production.