Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 36 ยท Storage: R2, KV and D1

Cloudflare D1: SQL Database for Workers

Learn Cloudflare D1: create a database, write migrations, query with prepare, bind, all, first and run, use batch transactions, design indexes and know D1's limits.

D1 is Cloudflare's serverless SQL database. Under the hood it is SQLite, so you already know the language if you have used SQLite: tables, indexes, joins, INSERT ... RETURNING, JSON functions. What D1 adds is hosting: you create a database in seconds, bind it to a Worker, and pay only for the rows you read and write plus storage.

D1 is not available on this site, so Worker code and wrangler commands are read-only. The SQL itself is standard SQLite and the runnable examples use the site's SQLite runner for the SQL parts.

Create a database and a migration

Terminal
npx wrangler d1 create app-db                        # prints the binding snippet with the id
npx wrangler d1 migrations create app-db create_posts
# edit migrations/0001_create_posts.sql
npx wrangler d1 migrations apply app-db --local      # local emulated copy
npx wrangler d1 migrations apply app-db --remote     # real database
npx wrangler d1 execute app-db --remote --command "SELECT COUNT(*) FROM posts"

A migration is a numbered SQL file. Wrangler remembers which ones ran, so each is applied once.

SQL
-- migrations/0001_create_posts.sql
CREATE TABLE posts (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  slug       TEXT NOT NULL UNIQUE,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL,
  author_id  INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX idx_posts_author ON posts(author_id, created_at);

Bind it with "d1_databases": [{ "binding": "DB", "database_name": "app-db", "database_id": "<id>" }].

Querying from a Worker

Always use prepared statements with bound parameters. Never build SQL by joining strings with user input.

JavaScript
export default {
  async fetch(request, env) {
    const url = new URL(request.url);

    // many rows
    const { results } = await env.DB
      .prepare("SELECT id, title FROM posts WHERE author_id = ?1 ORDER BY created_at DESC LIMIT ?2")
      .bind(7, 20)
      .all();

    // one row (or null)
    const post = await env.DB
      .prepare("SELECT * FROM posts WHERE slug = ?")
      .bind(url.searchParams.get("slug"))
      .first();

    // write
    const info = await env.DB
      .prepare("INSERT INTO posts (slug, title, body, author_id) VALUES (?, ?, ?, ?)")
      .bind("hello", "Hello", "First post", 7)
      .run();
    // info.meta.last_row_id, info.meta.changes, info.meta.duration

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

The four terminal methods:

Method Returns
.all() { results: [...rows], meta }
.first() or .first("col") The first row (or one column), or null
.run() { meta } for writes; no rows
.raw() Rows as arrays, faster for big results

Batches are transactions

env.DB.batch([stmt1, stmt2, ...]) sends several statements in one round trip and runs them as a single transaction: all succeed or all roll back. Use it whenever statements belong together, such as creating an order and its items.

JavaScript
await env.DB.batch([
  env.DB.prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)").bind(1, 30),
  env.DB.prepare("INSERT INTO order_items (order_id, sku, qty) VALUES (last_insert_rowid(), ?, ?)").bind("A1", 2),
]);

D1 does not expose interactive BEGIN ... COMMIT across separate calls; the batch is the transaction tool.

Practice the SQL

Here is the same schema in the SQLite runner, so you can write the queries your Worker would run.

SQL Posts schema and queries

Press Run (or Ctrl + Enter) to see the result here.

An update like views = views + 1 is atomic because SQLite runs it as one statement, which makes D1 fine for modest counters (unlike KV). Very high write rates on one row still queue up, because a D1 database processes writes one at a time.

Query plans and indexes

D1 bills by rows read, so a missing index costs money as well as time: a WHERE on an unindexed column scans the whole table. Check with EXPLAIN QUERY PLAN:

SQL Does this query use an index?

Press Run (or Ctrl + Enter) to see the result here.

Before the index the plan says SCAN events; afterward SEARCH events USING INDEX idx_events_user. Index the columns you filter and sort by.

Limits and good habits

  • Size and throughput. Each database has a maximum size (on the order of gigabytes) and handles writes serially. Plan to split by tenant or feature into several databases rather than one giant one.
  • Read replication. Optional read replicas bring data close to readers; use D1's session API so a user reads their own writes consistently.
  • Time Travel. D1 keeps point-in-time backups: wrangler d1 time-travel restore rolls a database back to an earlier moment.
  • Export. wrangler d1 export app-db --remote --output backup.sql gives a plain SQL dump.
  • Not for files. Store large blobs in R2 and keep their keys in a column.
  • Parameters always. .bind() prevents SQL injection; never concatenate user input.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What database engine is D1 built on?

    D1 uses SQLite semantics and syntax, so SQLite documentation and tools apply.
  2. 2. Which pattern guards against SQL injection in a Worker?

    Bound parameters keep data separate from SQL text, so input is never interpreted as SQL.
  3. 3. How do you run two related inserts so both succeed or both fail?

    batch() sends the statements together and wraps them in one transaction, rolling back on error.
  4. 4. A query that scans a huge unindexed table is costly because D1 bills for what?

    Reads are billed per row scanned. An index lets SQLite touch far fewer rows.

Summary

  • D1 is serverless SQLite bound to a Worker as env.DB; migrations live in numbered SQL files applied with Wrangler.
  • Use prepare().bind() with .all(), .first(), .run() or .raw(); never concatenate input into SQL.
  • batch() is the transaction mechanism; single-statement updates like views = views + 1 are atomic.
  • Index the columns you filter on (rows read are billed), keep blobs in R2, and use Time Travel and export for backups.

Next: Zero Trust: Replace the VPN, Protect Every App