D1: SQLite at the edge

You have eight D1 databases — jobs, policynews, citizentech, ceoai, podsmith, maison-laurier, consistency-calendar, respondentvoice-db — and an agent wrote the code that reads and writes all of them. Each one is a SQLite database that Cloudflare runs on your behalf, reachable from a Worker with no connection string and no password. That convenience hides a few sharp edges: a hard 10 GB ceiling, one query at a time, and a local copy that quietly diverges from the real thing. This article is about reading that code and telling which of those edges you are close to.

What a D1 database actually is

Cloudflare describes D1 as a managed, serverless database with SQLite’s SQL semantics. The important structural fact is buried in the limits page: each individual D1 database is backed by a single Durable Object, and it is single-threaded — it processes queries one at a time, in one primary location.

So D1 is not SQLite running in all 300-odd Cloudflare locations. It is one small SQLite database in one place, that your Workers reach quickly from anywhere. That single-threaded detail sets your throughput ceiling, and Cloudflare spells the arithmetic out: if your average query takes 1 ms you can run roughly 1,000 queries per second; at 100 ms you can run about 10. Beyond that, requests queue, and when the queue fills the database returns an overloaded error.

The binding: no connection string

A binding is an entry in your Wrangler config that injects a live object onto env when the Worker starts.

// wrangler.jsonc
{
  "d1_databases": [
    {
      "binding": "DB",                    // the name you use in code: env.DB
      "database_name": "respondentvoice-db",
      "database_id": "<unique-ID-for-your-database>"
    }
  ]
}

There is no host, port, username, password or connection pool. That removes two whole classes of bug you may have met on the FastAPI side — leaked credentials and pool exhaustion — but it also means the binding name in code and the config are the only link. If env.DB is undefined in production, the binding is missing from the deployed config, not from the code.

One real limit does apply: a single Worker invocation can hold at most six simultaneous connections to D1, and can issue at most 1,000 queries per invocation on Workers Paid (50 on Free).

Prepared statements and bind() — never string concatenation

Here is the version agents still emit surprisingly often:

// BAD: the value is pasted into the SQL the database will parse
const id = new URL(request.url).searchParams.get("id");
const row = await env.DB
  .prepare(`SELECT * FROM responses WHERE respondent_id = '${id}'`)
  .first();

An id of x' OR 1=1 -- now returns every row. The fix is a bound parameter — the value travels separately from the SQL text, so it can never be parsed as SQL. Cloudflare’s docs say this directly: binding parameters using bind() prevents SQL injection attacks.

// GOOD: ? is a placeholder; the value is passed to bind()
const row = await env.DB
  .prepare("SELECT id, answer, created_at FROM responses WHERE respondent_id = ?")
  .bind(id)                 // one bind() argument per ? , in order
  .first();                 // null if no row matched

The rule to grep for is blunt: any backtick, + or %s inside a prepare() call is a bug unless what is being interpolated is a hard-coded table name you wrote yourself. Note also the cap of 100 bound parameters per query — a WHERE id IN (?, ?, ...) built from a large array will start failing once the array passes 100 items.

What the result objects give you

run() returns a D1Result with success, results and a meta object. first() returns one row or null. all() returns every row as objects. raw() returns arrays of values with no metadata, which is cheaper for large exports.

The meta object is the most under-used diagnostic in D1:

const res = await env.DB
  .prepare("SELECT id, title FROM articles WHERE topic = ? ORDER BY published_at DESC LIMIT 20")
  .bind(topic)
  .run();

console.log(res.meta.rows_read, res.results.length, res.meta.duration);
// rows_read=48213, results=20  → this query is scanning the table

rows_read is rows examined, not rows returned. D1 bills on rows read and rows written, so a full scan of a 5,000-row table counts as 5,000 rows read even when it returns one. A large gap between rows_read and the length of results is the single clearest smell that a query needs an index.

Batches, and the transaction you do not have

An agent asked to import survey responses will often write this:

// BAD: 400 statements = 400 round trips, and no atomicity
for (const r of responses) {
  await env.DB.prepare("INSERT INTO responses (respondent_id, answer) VALUES (?, ?)")
    .bind(r.respondentId, r.answer)
    .run();
}

db.batch() sends an array of prepared statements in one call:

// GOOD: one round trip, and one transaction
const stmt = env.DB.prepare("INSERT INTO responses (respondent_id, answer) VALUES (?, ?)");
const batch = responses.map((r) => stmt.bind(r.respondentId, r.answer));
await env.DB.batch(batch);   // rolls the whole sequence back if any statement fails

Cloudflare’s docs state that batched statements are SQL transactions, that D1 operates in auto-commit, and that each statement in the list will execute and commit sequentially and non-concurrently. What you do not get is an interactive transaction — you cannot read a row, branch in JavaScript, and write inside the same transaction. If agent-written code appears to assume that (read a balance, compute, write it back), it has a race condition. Fold the decision into the SQL (UPDATE ... WHERE version = ? and check meta.changes), or move that state into a Durable Object.

Per-statement limits still apply inside a batch: 100 KB of SQL text, 100 bound parameters, 2 MB per row, and 30 seconds for the whole call.

Local versus remote — the confusion that eats an afternoon

This is the one that generates the most “but it worked locally”. Wrangler keeps a separate local SQLite file for development, persisted between runs of wrangler dev.

# seeds the LOCAL file only — production never sees this
npx wrangler d1 execute policynews --local --file=./seed.sql

# seeds the real database
npx wrangler d1 execute policynews --remote --file=./seed.sql

Same story for migrations: --local, --remote and --preview are all available on d1 migrations list and d1 migrations apply. Always pass one explicitly, so it is obvious in your shell history which database you hit. Cloudflare warns that changes made against a remote database cannot be undone — though Time Travel softens that, restoring a database to any minute within the last 30 days on Workers Paid (7 days on Free), up to 10 restores per 10 minutes.

npx wrangler d1 time-travel info policynews --timestamp=2026-09-24T14:00:00Z
npx wrangler d1 time-travel restore policynews --bookmark=<bookmark-from-above>

Migrations

npx wrangler d1 migrations create jobs "add applied_at to applications"
# → migrations/0003_add_applied_at_to_applications.sql
npx wrangler d1 migrations list jobs --remote
npx wrangler d1 migrations apply jobs --remote

Applied migrations are recorded in a d1_migrations table inside the database itself. That gives you one hard rule: never edit a migration that has already been applied. It will not re-run, so your local and remote schemas drift apart silently. Fix forward with a new file. If an agent hands you a diff that changes 0002_*.sql, that is a smell.

Indexes

Indexes are how you stop paying for full scans.

-- before: SCAN responses  (rows_read grows with the table)
-- after:  SEARCH responses USING INDEX idx_responses_respondent (respondent_id=?)
CREATE INDEX IF NOT EXISTS idx_responses_respondent ON responses(respondent_id);

EXPLAIN QUERY PLAN SELECT * FROM responses WHERE respondent_id = 'r-1042';
SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');

SCAN in the plan is bad news; SEARCH ... USING INDEX is what you want. Indexes cost a little on every write, but Cloudflare’s guidance is that the reduction in rows read almost always more than offsets it.

Read replication

Turning on read replication adds read-only copies of a database in other regions. Two things matter. First, all writes still go to the primary — it improves read latency only. Second, it does nothing at all unless your code uses the Sessions API:

// newest data required (a dashboard right after a write)
const session = env.DB.withSession("first-primary");

// slightly stale reads fine (a public news list)
const session2 = env.DB.withSession();   // "first-unconstrained"
const rows = await session2.prepare("SELECT id, title FROM articles LIMIT 20").all();

Passing the previous session’s bookmark back in (via a header such as x-d1-bookmark) gives sequential consistency: monotonic reads, read-your-own-writes, and writes that follow reads. Without it, a user can write a row and then read a lagging replica that has not seen it. Also note: disabling replication takes up to 24 hours for replicas to stop serving.

When D1 is the wrong choice

What to check in your own code

• Grep every prepare( call for backticks, + or ${ — any interpolated value is an injection bug. It should be ? plus bind().
• Grep for await inside a for loop around .run(). Replace with db.batch().
• Log meta.rows_read next to results.length on your slowest dashboard query. If the ratio is large, run EXPLAIN QUERY PLAN and add an index.
• Check your Wrangler config: does every binding name used in code exist in d1_databases, and is the database_id the production one?
• Search your shell history for wrangler d1 without --local or --remote. Make the flag explicit from now on.
• Look for code that reads a value, computes in JavaScript, then writes it back. That is not transactional on D1.
• Check the size of each of your eight databases in the dashboard. Anything above about 7 GB needs a plan before it hits 10.
• If read replication is on anywhere, confirm the Worker actually calls withSession(...) — otherwise you are paying nothing and gaining nothing.