SQL & Databases with D1 — Persistence Your Worker Won't Forget - Ultimate Web Development Series

In Chapter 8 you shipped a Worker that held notes in a JavaScript array. It works for ten seconds until the isolate restarts and every note vanishes. Real backends need persistence — data that lives longer than any single process.

The universal tool for this is a database, and the universal language for talking to one is SQL. This chapter gives you the SQL you need (six queries cover 95% of real work) and then wires up Cloudflare D1 — a SQLite database hosted at the edge, free, and wired into your Worker with two config lines.

By the end your notes API will be durable: create a note, redeploy the Worker, fetch the note back. Restart-proof.

What a Database Actually Is

A database is a program that stores and retrieves structured data fast, safely, and from many clients at once. The structure is a set of tables, each with rows and columns — think a spreadsheet with rules about what kinds of values each column can hold.

Loading diagram…

Figure 1 — A small three-table schema. Every row in notes points at a row in users via user_id; every row in tags points at a row in notes via note_id. That's the whole shape of nearly every app — entities in tables, relationships as ID references.

Four ideas that make up a real database:

Table — a named collection of rows with the same columns. users, notes, orders.
Row — one record. One user, one note, one order.
Column — a named field on every row, with a type. id INTEGER, email TEXT, created_at TEXT.
Primary key — a column (or group) that uniquely identifies a row. Usually an auto-incrementing id or a UUID.

Everything else in SQL is just operations on tables with those properties.

SQL — Six Queries You'll Actually Write

SQL is a declarative language: you say what you want, the database figures out how. These six shapes cover 95% of a typical backend's queries.

1. CREATE TABLE — define the shape

CREATE TABLE users (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  email      TEXT    UNIQUE NOT NULL,
  name       TEXT,
  created_at TEXT    DEFAULT (datetime('now'))
);

Read that top to bottom: "make a users table with four columns. id is a unique auto-incrementing integer and is this row's identity. email is text, must be unique, can't be null. name is text, optional. created_at defaults to the current UTC timestamp."

CREATE TABLE runs once, at migration time. You don't re-create tables on every request.

2. INSERT — add a new row

INSERT INTO users (email, name) VALUES ('w@example.com', 'William');

You omit id (auto-incremented) and created_at (has a default). The database fills them in.

3. SELECT — read rows

-- all columns, all rows
SELECT * FROM users;

-- specific columns
SELECT id, email FROM users;

-- filter rows
SELECT * FROM users WHERE email = 'w@example.com';

-- sort + limit (pagination)
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 40;

SELECT is the single query you'll write most often. Three knobs control everything:

WHERE — filter rows by condition
ORDER BY — sort
LIMIT / OFFSET — paginate

4. UPDATE — modify existing rows

UPDATE users SET name = 'William Claude' WHERE id = 42;

UPDATE without WHERE updates every row — a common catastrophe. Always include a WHERE clause when modifying.

5. DELETE — remove rows

DELETE FROM users WHERE id = 42;

Same footgun: DELETE without WHERE wipes the table. The senior-engineer trick is to run the equivalent SELECT first, verify the row count matches what you expect, then switch to DELETE.

6. JOIN — combine related tables

SELECT notes.id, notes.title, users.email
FROM notes
JOIN users ON notes.user_id = users.id
WHERE users.email = 'w@example.com';

"For every row in notes whose user_id matches a row in users, give me these columns." That's how you answer "show me all notes belonging to William." Two tables, one result set.

Primary Keys, Foreign Keys, Indexes

Three schema concepts that separate "works" from "works when you have 100K rows."

Primary key — the row's identity

Every table should have one. Either an auto-incrementing integer (id INTEGER PRIMARY KEY AUTOINCREMENT) or a UUID-ish string (id TEXT PRIMARY KEY). Primary keys are indexed automatically — lookups by id are O(log n), effectively instant even at a million rows.

Foreign key — "this column points at that table's id"

CREATE TABLE notes (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title      TEXT    NOT NULL,
  body       TEXT,
  created_at TEXT    DEFAULT (datetime('now'))
);

user_id REFERENCES users(id) declares: "this column must match a row in users." ON DELETE CASCADE says: "if that user is deleted, delete their notes too." Without it, you'd get orphaned notes pointing at nothing.

Index — the reason your queries stay fast

A B-tree index on a column makes WHERE col = value lookups fast. Without one, the database scans every row (a "full table scan"). Create indexes on columns you filter or sort by:

CREATE INDEX idx_notes_user_id ON notes(user_id);
CREATE INDEX idx_notes_created_at ON notes(created_at DESC);

Rule of thumb: id columns are auto-indexed. Any other column you use in a WHERE or ORDER BY with real data volume should have one too. Don't over-index — each index costs write speed — but don't under-index either. "Why is this query slow?" 80% of the time the answer is "add an index."

What D1 Is

D1 is Cloudflare's managed SQLite database. Three things worth knowing:

It's SQLite. Same engine that runs on every iPhone and Android, the most-deployed database on earth. Battle-tested, dead simple.
Hosted at the edge. Your Worker's D1 binding talks to a primary replica; reads can be served from edge replicas.
Free tier. 5 GB storage, 5 million reads/day, 100K writes/day. Most personal and small-SaaS projects never pay a cent.

The trade-off vs Postgres / MySQL: D1 has the SQLite feature set (no FULL OUTER JOIN, no stored procedures). In exchange you get zero operations, near-free pricing, and edge-close reads. For the shape of app we're building, it's the right pick.

Creating a D1 Database

cd my-notes-api
wrangler d1 create my-notes-db

Output looks like:

✅ Successfully created DB 'my-notes-db'

[[d1_databases]]
binding = "DB"                     # <- how your Worker references it
database_name = "my-notes-db"
database_id = "a1b2c3d4-..."

Copy that block and paste it into wrangler.jsonc (or wrangler.toml). Your Worker now has a database available as env.DB.

Writing Your First Migration

A migration is an SQL file that sets up (or evolves) your schema. Create a folder + file:

mkdir -p migrations
cat > migrations/0001_init.sql <<'EOF'
CREATE TABLE IF NOT EXISTS notes (
  id         TEXT    PRIMARY KEY,
  title      TEXT    NOT NULL,
  body       TEXT    DEFAULT '',
  created_at TEXT    DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_notes_created_at ON notes(created_at DESC);
EOF

Run it against your remote D1:

wrangler d1 execute my-notes-db --remote --file=migrations/0001_init.sql

For local dev against a local D1 replica:

wrangler d1 execute my-notes-db --local --file=migrations/0001_init.sql

You now have a notes table on the edge, ready to hold data.

Querying D1 From Your Worker

D1 exposes a prepare → bind → execute API on the binding. Here's the core pattern:

// env.DB is the binding defined in wrangler.jsonc
async fetch(request, env) {
  // Read one row
  const note = await env.DB
    .prepare("SELECT * FROM notes WHERE id = ?")
    .bind(id)
    .first();

  // Read many rows
  const { results } = await env.DB
    .prepare("SELECT * FROM notes ORDER BY created_at DESC LIMIT 20")
    .all();

  // Write
  await env.DB
    .prepare("INSERT INTO notes (id, title, body) VALUES (?, ?, ?)")
    .bind("nt_abc", "Groceries", "milk, eggs")
    .run();
}

Three methods:

.first() — first row or null. For "get one by id."
.all() — object with .results (array of rows). For "get a list."
.run() — object with .success / .meta. For INSERT / UPDATE / DELETE where you don't need returned rows.

Loading diagram…

Figure 2 — The D1 query flow. Prepare the SQL, bind the arguments, execute. The binding is literally just RPC to Cloudflare's storage — your Worker doesn't hold a DB connection.

Replacing the In-Memory Array

Let's upgrade Ch 8's Worker to use D1. Here's the refactored routes:

// src/index.js — Ch 9 version, now backed by D1

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

    if (method === "OPTIONS") return cors(new Response(null));

    try {
      // GET /api/notes
      if (pathname === "/api/notes" && method === "GET") {
        const { results } = await env.DB
          .prepare("SELECT * FROM notes ORDER BY created_at DESC LIMIT 100")
          .all();
        return cors(json(results));
      }

      // POST /api/notes
      if (pathname === "/api/notes" && method === "POST") {
        const body = await request.json();
        if (!body.title) {
          return cors(json({ error: "validation_failed", field: "title" }, 400));
        }
        const id = "nt_" + crypto.randomUUID().slice(0, 8);
        await env.DB
          .prepare("INSERT INTO notes (id, title, body) VALUES (?, ?, ?)")
          .bind(id, String(body.title).slice(0, 200), String(body.body ?? ""))
          .run();
        const note = await env.DB
          .prepare("SELECT * FROM notes WHERE id = ?")
          .bind(id)
          .first();
        return cors(json(note, 201));
      }

      // GET /api/notes/:id
      const m = pathname.match(/^\/api\/notes\/([^\/]+)$/);
      if (m) {
        const id = m[1];
        if (method === "GET") {
          const note = await env.DB
            .prepare("SELECT * FROM notes WHERE id = ?")
            .bind(id)
            .first();
          return cors(note ? json(note) : json({ error: "not_found" }, 404));
        }
        if (method === "DELETE") {
          const result = await env.DB
            .prepare("DELETE FROM notes WHERE id = ?")
            .bind(id)
            .run();
          if (result.meta.changes === 0) {
            return cors(json({ error: "not_found" }, 404));
          }
          return cors(new Response(null, { status: 204 }));
        }
      }

      return cors(json({ error: "not_found" }, 404));
    } catch (err) {
      console.error("DB error:", err);
      return cors(json({ error: "internal" }, 500));
    }
  },
};

Notice what changed and what didn't: the route handlers are nearly identical to Ch 8's. Only the storage layer swapped. That's the benefit of clean separation — you can replace the persistence tier without rewriting every route.

Local Dev Against a Local D1

When you run wrangler dev, the Worker uses a local SQLite file, not your real D1 (so you can develop offline and not corrupt prod).

# Apply migrations to the local DB
wrangler d1 execute my-notes-db --local --file=migrations/0001_init.sql

# Run the Worker locally
wrangler dev

# Hit it
curl -X POST http://localhost:8787/api/notes \
  -H "Content-Type: application/json" \
  -d '{"title":"Local note","body":"persists across wrangler dev restarts"}'

curl http://localhost:8787/api/notes

Local state persists between wrangler dev runs (stored in .wrangler/state/). When you're ready, wrangler deploy pushes your code — your production data is already in the remote D1 you migrated earlier.

Quick SQL From the Command Line

You don't always need to write a SELECT in code. D1 gives you an ad-hoc shell:

# Remote
wrangler d1 execute my-notes-db --remote --command="SELECT COUNT(*) FROM notes"

# Local
wrangler d1 execute my-notes-db --local --command="SELECT * FROM notes LIMIT 5"

This is priceless for debugging — "is that row actually in the DB?" → one command, see for yourself.

Relationships — When You Need More Than One Table

Let's extend the schema to include users. A note belongs to a user.

-- migrations/0002_users.sql
CREATE TABLE IF NOT EXISTS users (
  id         TEXT    PRIMARY KEY,
  email      TEXT    UNIQUE NOT NULL,
  name       TEXT,
  created_at TEXT    DEFAULT (datetime('now'))
);

-- Add user_id to notes
ALTER TABLE notes ADD COLUMN user_id TEXT REFERENCES users(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_notes_user_id ON notes(user_id);

Now a "list my notes" query joins the two:

const { results } = await env.DB
  .prepare(`
    SELECT notes.*
    FROM notes
    JOIN users ON notes.user_id = users.id
    WHERE users.email = ?
    ORDER BY notes.created_at DESC
    LIMIT 100
  `)
  .bind(userEmail)
  .all();

Same prepare/bind/all pattern — SQL just got richer. Multi-line strings with template literals keep it readable.

Transactions — `batch` for "All or Nothing"

Some operations need to be atomic: either all the changes land, or none of them do. D1 supports this with batch:

await env.DB.batch([
  env.DB.prepare("INSERT INTO users (id, email) VALUES (?, ?)").bind(userId, email),
  env.DB.prepare("INSERT INTO notes (id, user_id, title) VALUES (?, ?, ?)").bind(noteId, userId, "Welcome"),
  env.DB.prepare("UPDATE audit_log SET last_signup_at = ?").bind(now),
]);

Either all three statements commit, or none do. Use this for operations where a partial failure would leave inconsistent state (signups, order placement, point transfers).

The Six Sins to Avoid

A compressed list of the footguns you'll otherwise learn the hard way:

| Sin | Fix | |---|---| | String-concat user input into SQL | Use ? + .bind() | | UPDATE / DELETE without WHERE | Always include a filter; SELECT first to verify | | Missing IF NOT EXISTS on migrations | Add it to every CREATE TABLE / CREATE INDEX | | No index on a filtered column | Add CREATE INDEX after noticing slow queries | | SELECT * in hot paths | Name only the columns you need | | Treating null like = NULL | Use IS NULL / IS NOT NULL |

Exercise — Make Your Notes Survive

Take the Worker from Ch 8 and:

wrangler d1 create my-notes-db and paste the binding block into wrangler.jsonc.
Write migrations/0001_init.sql with the notes table and a created_at index.
Apply it: wrangler d1 execute my-notes-db --local --file=migrations/0001_init.sql (and again with --remote).
Replace the in-memory notes = [] with the D1 queries above.
wrangler dev. Create a note. Stop the server. Restart. Fetch it back — it's still there. That's the whole point.
wrangler deploy. Your production notes now survive anything short of Cloudflare deleting your account.

Next Steps

You can now store and retrieve structured data. The one thing D1 isn't good for is big files (images, videos, PDFs) — those belong in object storage, and that's Ch 10's job.

Get comfortable with wrangler d1 execute --local --command="..." for ad-hoc poking. It's the fastest debugging tool in the stack.
Read the next chapter — Ch 10: Object Storage with R2, where you'll upload images to Cloudflare's S3-compatible bucket and serve them globally.

← Ch 8: Your First Cloudflare Worker Ch 10: Object Storage with R2→

Ship your apps faster

When you're ready to publish your Swift app to the App Store, Simple App Shipper handles metadata, screenshots, TestFlight, and submissions — all in one place.

Try Simple App Shipper