Schema design and migrations in D1

After this lesson you'll be able to version a D1 schema with wrangler's migrations workflow, model a foreign-key relationship correctly, and use the Sessions API to avoid stale reads from D1's read replicas.

D1 is SQLite, and SQLite gives you real foreign keys and joins — but D1 layers two things on top that don't exist in a plain local SQLite file: a migrations workflow for versioning schema changes across environments, and read replication, which means a query can be served by a copy of your database that hasn't yet caught up to your most recent write. Both matter as soon as you have more than one table or more than one deploy.

How migrations work

A migration is a plain .sql file in a migrations/ directory. Wrangler tracks which ones have already run in a bookkeeping table (d1_migrations by default) inside the database itself, so re-running apply is safe — it only executes files it hasn't seen before, in filename order.

# create a new, empty, sequentially-numbered migration file
npx wrangler d1 migrations create my-db "create users and posts tables"

# see which migrations haven't been applied yet
npx wrangler d1 migrations list my-db --local
npx wrangler d1 migrations list my-db --remote

# apply pending migrations
npx wrangler d1 migrations apply my-db --local   # your local dev database
npx wrangler d1 migrations apply my-db --remote  # the real, deployed database

The create command just scaffolds a numbered, empty file for you to fill in — the number prefix is what guarantees ordering. apply is the command that actually runs SQL, and it always needs an explicit --local or --remote: local applies against the SQLite file Wrangler uses for wrangler dev, remote applies against the production database over the network. There's no implicit default, which is deliberate — you don't want a habit of typing one flag to accidentally run against the other environment.

Foreign keys and relational schema design

D1 enforces foreign key constraints as if PRAGMA foreign_keys = ON were set for every query — you don't need to turn it on yourself, and you can't turn it off for an entire connection since every D1 query runs inside its own implicit transaction. (You can suspend checking for the duration of one transaction with PRAGMA defer_foreign_keys = ON if you need to insert rows out of order, as long as the constraint is satisfied by the time the transaction commits.)

Declare a foreign key with the standard SQLite syntax: FOREIGN KEY (column) REFERENCES table(column), optionally with ON DELETE/ON UPDATE actions (CASCADE, SET NULL, RESTRICT). Design-wise, treat this like any relational database: put the "many" side's foreign key on the "many" table (a post belongs to one user, so posts.user_id references users.id, not the other way around), and index foreign key columns explicitly — SQLite does not automatically create an index on a foreign key column the way some databases do, and every join or cascading delete on that column will do a full table scan without one.

Worked example

migrations/0001_create_users_and_posts.sql:

CREATE TABLE users (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  email      TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE posts (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id    INTEGER NOT NULL,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- foreign key columns aren't indexed automatically — add this yourself
CREATE INDEX idx_posts_user_id ON posts(user_id);

Apply it locally, then to production:

npx wrangler d1 migrations apply my-db --local
npx wrangler d1 migrations apply my-db --remote

A join from a Worker, fetching a user's posts newest-first:

const { results } = await env.DB.prepare(
  `SELECT posts.id, posts.title, posts.created_at, users.email
   FROM posts
   JOIN users ON users.id = posts.user_id
   WHERE users.email = ?
   ORDER BY posts.created_at DESC`
).bind("ada@example.com").all();

Read replication and the Sessions API

D1 can serve reads from regional replicas instead of always hitting the primary, which cuts latency for read-heavy Workers running far from the primary's location. The catch: a replica can lag slightly behind the primary, so a naive read right after a write can miss data you just wrote — the classic replication read-your-own-writes problem.

D1's Sessions API solves this with a bookmark: every D1 query returns a bookmark representing the database's state at that point. Start a session, and every query in it takes the previous query's bookmark as a minimum staleness requirement — D1 routes the next query to any replica that has caught up to at least that bookmark, or to the primary if no replica has. This gives you sequential, read-your-writes consistency within a session without forcing every query to the primary.

// Start a session. "first-primary" means the first query in the session
// goes to the primary — use this right after a write you must see reflected.
const session = env.DB.withSession("first-primary");

await session.prepare(
  "INSERT INTO posts (user_id, title, body) VALUES (?, ?, ?)"
).bind(userId, title, body).run();

// Subsequent reads in the same session are guaranteed to see that write,
// even if they're served by a replica.
const { results } = await session
  .prepare("SELECT * FROM posts WHERE user_id = ? ORDER BY created_at DESC")
  .bind(userId)
  .all();

For read-only requests where you don't care about seeing the very latest write, use env.DB.withSession("first-unconstrained") (or omit the argument) — it lets the first query land on whichever replica is fastest, trading a small staleness window for lower latency. Pick "first-primary" when the request just performed a write and must read it back, and "first-unconstrained" for everything else.