Skip to content

d1.md

Latest commit

 

History

History
164 lines (124 loc) · 4.84 KB

d1.md

File metadata and controls

164 lines (124 loc) · 4.84 KB
Raw

D1 — SQL / relational storage

What it is

A namespace-level SQL database, accessed in the worker through env.<DB> with prepared statements. Schema is managed via files in the migrations/ directory; apply them explicitly with wdl d1 migrations — no rollback.

When to use

  • Relational data: foreign keys, JOINs, multi-row queries.
  • Persistent application state shared across requests.

For small key-value lookups, prefer KV — see kv.md. For large blobs use R2 — see r2.md.

Wrangler configuration

[[d1_databases]]
binding = "DB"
database_name = "main"
migrations_dir = "migrations"

Or in wrangler.jsonc:

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "main",
      "migrations_dir": "migrations"
    }
  ]
}

binding is the name exposed at runtime (env.DB). database_id, when present, takes precedence as the platform database reference; otherwise database_name is used. These values are platform-local D1 names or aliases, not Cloudflare UUIDs.

Setup

Create the database before the first deploy:

wdl d1 create main

Then add SQL migration files under migrations/:

migrations/
  0001_create_users.sql
  0002_add_email_index.sql

The filename is the migration id. Apply:

wdl d1 migrations status main      # see what is pending
wdl d1 migrations apply main       # forward-only, no rollback

Both migrations_dir and an explicit --dir must stay inside the project root.

Once applied, a migration is immutable. Never rename or edit an already-applied file — the CLI tracks by filename, and a rename is treated as a brand-new migration and executed again.

Reading and writing in the Worker

export default {
  async fetch(request, env, ctx) {
    // SELECT
    const { results } = await env.DB
      .prepare("SELECT id, name FROM users WHERE active = ?")
      .bind(1)
      .all();

    // INSERT
    await env.DB
      .prepare("INSERT INTO users (name, email) VALUES (?, ?)")
      .bind("Alice", "alice@example.com")
      .run();

    // single row
    const row = await env.DB
      .prepare("SELECT * FROM users WHERE id = ?")
      .bind(42)
      .first();

    return Response.json({ results });
  },
};

Use .bind(...) for every user-controlled value. Never concatenate value strings into SQL — D1 supports prepared statements precisely to prevent SQL injection.

Inspecting from the CLI

wdl d1 list
wdl d1 execute main --sql "SELECT name FROM sqlite_master WHERE type='table'"
wdl d1 execute main --file query.sql
wdl d1 execute main --sql "SELECT * FROM users WHERE id = ?" --params '[42]'
wdl d1 execute main --sql "DELETE FROM tmp" --mode run   # all | raw | run | exec

wdl d1 execute can both read and write. Before a destructive statement, run a read-only version first (e.g. SELECT COUNT(*) before a DELETE).

Pass exactly one of --sql or --file (even --sql "" conflicts with --file). The SQL provided by the selected source must be non-empty.

--mode picks the result shape (all / raw / run / exec); exec runs a batch of statements and does not accept --params — the CLI rejects that combination locally.

D1 requests are bounded before execution: the binary query body is capped at 8 MiB; a decoded request can contain at most 1000 SQL statements and 8 MiB of aggregate SQL plus params; each statement returns at most 65,536 rows (Cloudflare D1 compatibility — overflow maps to limit-exceeded); result bodies are protected by the platform default 16 MiB aggregate cap. Multi-statement exec() runs in one SQLite transaction; if a later statement fails, the statements already executed in that exec() call are rolled back.

Deleting a database

wdl d1 delete <name>           # prompts for confirmation by default; add --yes only after confirming with the user

Deleting a worker does not delete D1 data. To delete the data, use wdl d1 delete. Confirm explicitly with the user — there is no undo.

Anti-patterns

  • ❌ Renaming an applied migration file. It gets applied again.
  • ❌ Editing the contents of an applied migration. It will not re-run; schema and file diverge.
  • ❌ Concatenating value strings into SQL. Always .bind(...).
  • ❌ Skipping wdl d1 create before the first deploy. A [[d1_databases]] entry does not auto-create the database.
  • ❌ Assuming database_id is ignored. The CLI matches database_id first and falls back to database_name only when it is absent.

End-to-end examples

../examples/d1-demo — minimal D1 + migration setup. ../examples/inspection-demo combines D1 with KV / R2 / assets.

Related

  • secrets.md — runtime secrets a D1 query may need (e.g. an encryption key).
  • deploy.md — the pre- and post-deploy checklist, and when to run D1 migrations.