Cloudflare D1 Knowledge Patch

Cloudflare D1

Use this skill when designing, implementing, reviewing, or debugging Cloudflare D1 databases, Workers bindings, Wrangler workflows, replication, placement, or recovery. Start with the quick reference, then open the task-specific reference instead of assuming general SQLite behavior applies to D1.

Reference index

Breaking and compatibility-sensitive behavior

Use D1-specific write authorization

HTTP API writes require an API token with D1:Edit. A token with D1:Read and Edit for another Cloudflare product is not sufficient.

Bind only supported JavaScript values

• Booleans are permanently stored as SQLite integers and read back as 0 or 1.
• ArrayBuffer values and views become BLOBs and read back as arrays.
• undefined raises D1_TYPE_ERROR.
• BigInt is unsupported. Although D1 stores signed 64-bit integers, binding round trips are safe only through Number.MAX_SAFE_INTEGER.

Do not promise round-trip preservation of the original JavaScript type.

Use positional placeholders

D1 supports anonymous ? and ordered ?NNN parameters, not named parameters. Ordered placeholders refer to the matching bind() argument:

const stmt = env.DB
  .prepare("SELECT * FROM jobs WHERE state = ?2 AND id = ?1")
  .bind(42, "ready");

Do not treat exec() as a batch abstraction

exec() takes unbound statements separated by newlines. It stops at the first failure, throws query and error details, and does not run later statements. Reserve it for maintenance or one-shot work; use prepared statements for application data.

Respect creation-time data-location decisions

A database jurisdiction can be set only at creation and cannot be added or changed later. If both a jurisdiction and location hint are supplied, the jurisdiction wins and the hint is ignored.

Keep local and remote targets explicit

Pages local development cannot connect to remote D1. Supply a project-root Wrangler binding with preview_database_id equal to the binding name. Pass --local to local execute and migration commands; omitting it targets the remote database.

Query and result quick reference

Choose the intended result mode

• run<T>() is an alias for all<T>().
• raw({ columnNames: true }) returns arrays with a leading column-name array and omits metadata.
• first(columnName) returns a scalar, returns null for no rows, and throws D1_ERROR when the column is absent.
• first() does not add LIMIT 1; add the limit in SQL when it matters.
• D1Result.results is [] for a query with no rows and null when results do not apply.

Use documented SQLite features only

D1 includes FTS5 and fts5vocab, JSON functions and operators, and SQLite math functions. Do not infer availability of other extensions from SQLite compatibility alone.

Treat PRAGMAs as transaction-scoped

Only the documented PRAGMA subset is supported, and its effects apply to the current transaction. PRAGMA defer_foreign_keys = on delays validation; it does not disable constraints or ON DELETE CASCADE actions.

Run PRAGMA optimize; after schema changes such as creating an index.

Read consistency quick reference

Use Sessions for sequential consistency

Create a session with env.DB.withSession() and route the logical sequence of queries through it. Carry session.getBookmark() into a later withSession(bookmark) when sequential consistency must cross session boundaries.

withSession() is equivalent to withSession("first-unconstrained"), so the first query can run on a primary or replica. Use withSession("first-primary") when the first query must observe the latest primary database version.

Keep Sessions and REST boundaries clear

Sessions are available through the Workers binding, not the REST API. They remain usable when replication is disabled. D1 creates and routes replicas; applications select consistency through the session constraint and bookmark.

Interpret serving metadata correctly

Remote results expose served_by_region and served_by_primary whether or not Sessions or replication are in use. Both values are undefined in local wrangler dev. timings.sql_duration_ms measures SQL execution only and excludes network time.

Migration quick reference

Set both migrations_dir and migrations_pattern when migrations live in nested directories:

{
  "d1_databases": [{
    "binding": "DB",
    "database_name": "my-database",
    "database_id": "<UUID>",
    "migrations_dir": "migrations",
    "migrations_pattern": "migrations/*/migration.sql"
  }]
}

The pattern is relative to the Wrangler configuration, must start with migrations_dir, uses * for one path segment and ** for any number, and defaults to ${migrations_dir}/*.sql. Applied names are relative to migrations_dir. wrangler d1 migrations create still creates top-level files, so use the ORM generator for nested-only layouts.

Recovery and transfer quick reference

Prefer Time Travel for supported databases

Production-backend databases have Time Travel automatically. Workers Paid retains 30 days and Workers Free retains 7 days. Confirm version: production with wrangler d1 info.

A restore overwrites the database, cancels in-flight work, and returns the pre-restore bookmark. Save it for undo. Restore does not erase older bookmarks inside the retention window, but it cannot clone or fork a database.

Convert SQLite files before importing

D1 does not import a raw .sqlite3 file. Dump it to SQL; remove BEGIN TRANSACTION, COMMIT, and any CREATE TABLE _cf_KV statement; then execute the SQL remotely. Each import file is limited to 5 GiB.

Plan exports as blocking operations

An export blocks other database requests. Any virtual table, including FTS5, prevents export until removed; recreate it afterward. Exports can target the whole database or one table and can select schema-only or data-only output.

Retry and debugging quick reference

D1 automatically retries classified read-only SELECT, EXPLAIN, and WITH queries up to two times after retryable errors. Inspect meta.total_attempts. If a retried attempt modifies data, D1 rolls it back. Applications must still retry other business-level idempotent operations deliberately.

Catch ordinary Error objects and read message. When supporting Wrangler before 3.1.1, fall back to cause.message.

Retry lost connections and transient infrastructure failures. Optimize, spread load, reduce operation size, or shard for timeouts, overload, isolate-memory resets, and CPU-limit resets. Resolve capacity errors by deleting data, upgrading the plan, or sharding rather than blindly retrying.