Cloudflare D1, in depth: serverless SQLite, batch transactions, the single-writer model, Sessions and Time Travel

The mental model: one SQLite file, one writer, one query at a time

Cloudflare's limits page describes each D1 database as inherently single-threaded: it processes queries one at a time. There is no parallel query execution inside a database. The throughput you get is therefore set almost entirely by how long each query takes.

The first and last rows are Cloudflare's own figures; the middle row is the same arithmetic. When requests arrive faster than the database drains them, they queue, and past a point D1 returns errors rather than waiting.

Two design rules follow. First, every hot query must be an index lookup; a missing index is a capacity problem, not just a latency problem. Second, scale out by splitting data across databases. On the paid plan an account can have 50,000 databases of up to 10 GB each (the free plan allows 10 databases of 500 MB), which is a strong hint about the intended shape: per-tenant, per-region or per-feature databases rather than one large shared one.

How a query travels

A Worker invocation calls a method on the binding. The request travels to the location that hosts the database's primary, runs, and the result comes back with metadata: how long it took, how many rows were read and written, the last inserted row ID and whether the database changed. Because the Worker may be running far from the primary, every round trip costs network latency on top of execution time. That is why the API pushes you towards sending several statements in one call rather than one call per statement.

The Worker API in practice

Declare the binding in your Wrangler configuration, then use prepare and bind for every query that takes input. Bound parameters are passed separately from the SQL text, which is what stops SQL injection; never build SQL with string concatenation.

// wrangler.jsonc (excerpt)
"d1_databases": [
  { "binding": "DB", "database_name": "shortener", "database_id": "<uuid>" }
]

// src/index.ts
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const code = new URL(request.url).pathname.slice(1);

    // first(): first row as an object, or null when there is no row
    const row = await env.DB
      .prepare("SELECT target FROM links WHERE code = ?")
      .bind(code)
      .first<{ target: string }>();
    if (row === null) return new Response("not found", { status: 404 });

    // run(): full result with meta; all() is an alias
    const res = await env.DB
      .prepare("UPDATE links SET clicks = clicks + 1 WHERE code = ?")
      .bind(code)
      .run();
    console.log(res.meta.duration, res.meta.rows_read, res.meta.changes);

    return Response.redirect(row.target, 302);
  },
};

first("column") returns one column's value directly, and raw({ columnNames: true }) returns arrays instead of objects with the column names as the first row, which is cheaper to serialise for large result sets. exec() runs raw SQL without binding; Cloudflare documents it as slower and less safe and suggests it only for maintenance tasks. Log meta.rows_read in development: it is the quickest way to see that a query is scanning a table instead of using an index.

Transactions: batch() is the unit

D1 gives you atomicity through batch(). The statements in one batch run sequentially as a single SQL transaction; if any statement fails, the error is reported for that statement and the whole sequence is rolled back. Use it rather than issuing transaction-control statements yourself.

What you do not get is an interactive transaction: you cannot read a row, decide something in JavaScript and then write inside the same transaction across several round trips. The workaround is to move the decision into SQL, so the check and the change happen in one statement, and then inspect meta.changes to learn whether the condition held.

-- schema: the database itself refuses a negative balance
CREATE TABLE accounts (
  id      TEXT PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
CREATE TABLE transfers (src TEXT, dst TEXT, amount INTEGER);

// Move 10 credits from a to b. If a would go negative, the CHECK fails,
// the batch reports the error and both updates are rolled back.
try {
  await env.DB.batch([
    env.DB.prepare("UPDATE accounts SET balance = balance - ?1 WHERE id = ?2").bind(10, "a"),
    env.DB.prepare("UPDATE accounts SET balance = balance + ?1 WHERE id = ?2").bind(10, "b"),
    env.DB.prepare("INSERT INTO transfers (src, dst, amount) VALUES (?1, ?2, ?3)").bind("a", "b", 10),
  ]);
} catch (err) {
  if (String(err).includes("CHECK constraint failed")) {
    return new Response("insufficient funds", { status: 409 });
  }
  throw err;   // overload, timeout: retry with backoff upstream
}

The invariant lives in the schema, so it holds no matter which code path writes, and the all-or-nothing behaviour of the batch does the rest. For updates that depend on a value read earlier, use optimistic concurrency: keep a version column, write with UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?, and retry from the read when meta.changes is zero.

Batches also cut round trips. Keep them short: while one runs, nothing else in that database does.

Schema changes and migrations

Wrangler manages migrations as numbered SQL files in a migrations/ directory and records which ones have been applied in a d1_migrations table; both names can be changed in the binding configuration.

npx wrangler d1 migrations create shortener add_clicks_index
# edit migrations/0002_add_clicks_index.sql
npx wrangler d1 migrations list  shortener --remote   # what is not yet applied
npx wrangler d1 migrations apply shortener --local    # test against local state
npx wrangler d1 migrations apply shortener --remote   # then production

Two habits prevent most migration incidents. The first is expand and contract: your Worker and your schema deploy separately, so for a while the old code runs against the new schema. Add the new column or table first, deploy code that writes both shapes, backfill, switch reads, and only then drop the old shape. The second is respecting the single writer: a migration that rewrites a large table blocks every other query for its duration, and a statement that runs past the 30 second query limit fails. Backfill in bounded chunks keyed on the primary key instead.

If a migration has to violate foreign keys temporarily, Cloudflare documents PRAGMA defer_foreign_keys = true at the top of the migration, so the check runs at the end instead of statement by statement.

Read replication and the Sessions API

Read replication is off until you enable it for a database. Once on, D1 keeps replicas in other regions and may serve reads from them. Replicas lag the primary, so a naive client could write a row and then fail to read it back. The Sessions API prevents that by attaching a bookmark to every query: a bookmark names a version of the database, and a replica serves a query only once it has reached at least that version.

// Continue the client's session if it sent a bookmark; otherwise any instance may answer first.
const bookmark = request.headers.get("x-d1-bookmark") ?? "first-unconstrained";
const session = env.DB.withSession(bookmark);

const { results, meta } = await session
  .prepare("SELECT id, title FROM notes WHERE owner = ? ORDER BY id DESC LIMIT 20")
  .bind(userId)
  .run();
console.log(meta.served_by_region, meta.served_by_primary);

const response = Response.json(results);
response.headers.set("x-d1-bookmark", session.getBookmark() ?? "");
return response;

Within a session D1 guarantees sequential consistency: monotonic reads, monotonic writes, writes follow reads, and read-your-own-writes. Pass "first-primary" when the first query must see the very latest data, for example right after a payment. Returning the bookmark to the client and sending it back on the next request is what extends these guarantees across requests; forget that step and a user can see their own update disappear on refresh.

Worked example: the index that bought 50 times the capacity

A link shortener stores 2 million rows in links(code, target, owner, clicks, created_at) with code as the primary key. The redirect path is fast. The dashboard query, SELECT code, clicks FROM links WHERE owner = ? ORDER BY created_at DESC LIMIT 50, is not: logging shows rows_read of about 2,000,000 and a duration around 150 ms.

Because the database is single-threaded, that one query caps the whole database at roughly seven of these per second, and redirects queue behind it. The plan confirms the scan:

EXPLAIN QUERY PLAN
SELECT code, clicks FROM links WHERE owner = ? ORDER BY created_at DESC LIMIT 50;
-- SCAN links
-- USE TEMP B-TREE FOR ORDER BY

CREATE INDEX idx_links_owner_created ON links(owner, created_at DESC);
-- SEARCH links USING INDEX idx_links_owner_created (owner=?)

After the migration, rows_read drops to about 50 and duration to a few milliseconds: roughly a fifty-fold gain in the queries the database can absorb, without changing plans or databases. The cost is a slower write path and more storage, because every insert now updates the index too.

Time Travel and backups

D1 keeps a history of changes that lets you restore a database to any minute within the retention window: 30 days on paid plans and 7 on free. Restores work on databases on the current storage system (check that wrangler d1 info reports the production version).

npx wrangler d1 time-travel info shortener              # current bookmark
npx wrangler d1 time-travel restore shortener --timestamp=1759460400
npx wrangler d1 time-travel restore shortener --bookmark=<bookmark>

A restore overwrites the database in place, so it is not an undo for one bad row: everything written after the restore point is lost. Record the current bookmark before you restore so you can go back. Restores are rate limited to 10 per 10 minutes per database. For retention beyond the window, export to object storage such as R2 on a schedule.

Limits that shape the design

The parameter limit is the one teams trip on most. A multi-row insert with five columns can carry at most 20 rows per statement; build several statements and send them in one batch().

Failure modes

• Overload errors under traffic spikes. One slow query plus a burst means the queue fills and requests fail. Fix the slow query first, then shard. Retrying immediately makes it worse; use backoff with jitter.
• Read-after-write anomalies. Replication is on but bookmarks are not passed between requests, so a user reads from a replica that has not caught up.
• Lost updates. Code reads a value, changes it in JavaScript and writes it back in a separate call. Use conditional updates or version columns.
• Local and remote drift. Migrations applied with --local but never with --remote, or the other way round. Run migrations list --remote in CI before deploy.
• Destructive restore. Someone restores to fix one table and discards a day of writes everywhere else. Save the current bookmark and copy out any rows written after the restore point that you need to keep, then restore.

D1 or something else

D1 fits the general pattern described in edge compute architecture: compute runs near users, while the state it depends on has a home location you must design around.

What to do next

1. Create a database, add the binding, and write one endpoint using prepare().bind().first().
2. Log meta.duration and meta.rows_read for every query in staging; any hot query that reads far more rows than it returns needs an index.
3. Run EXPLAIN QUERY PLAN on your top five queries and remove every SCAN on a large table.
4. Replace read-modify-write code with conditional updates inside batch().
5. Set up Wrangler migrations, apply with --local in CI tests and check migrations list --remote before each deploy.
6. Decide the shard key (tenant, region or feature) before the first database approaches a few GB.
7. If you enable read replication, thread the bookmark through every request with the Sessions API.
8. Practise a Time Travel restore on a throwaway test database, and write down the bookmark-first runbook.