Cloudflare D1: save drafts with SQL

The reading tool we built in part four gives you an estimate, but it cannot save your work. That is fine for a quick calculation. It is less useful when you are editing something you want to return to tomorrow.

Let’s give the tool a draft library: save a title and some text, list recent drafts, reopen one, and update it without accidentally overwriting an edit from another tab.

We’ll store those drafts in Cloudflare D1, Cloudflare’s managed SQL database built on SQLite. Along the way, we’ll work through the decisions behind a save button: what to store, who can read it, and how to keep one edit from erasing another.

We’ll extend the reading-api Hono project from the previous two articles. Keep its src/analyze.ts, existing routes, and static-assets configuration. This chapter builds and tests the storage API locally; wiring a draft library into the page can follow once that API behaves correctly.

• Choose what to store.
• Create a local database and schema.
• Save and retrieve drafts, then try the API.
• Handle two tabs editing the same draft.
• Understand indexes, costs, and deployment.

What belongs in D1?

A draft library needs to answer: show this reader’s latest drafts, newest first. Later we might filter by language, add tags, or count drafts by month. SQL lets us express those questions directly.

D1 lets the Worker ask those questions through a binding such as env.DB. There is no database password in the browser and no connection pool to maintain in the Worker. The browser calls our API; our API decides which SQL to run.

The other Cloudflare storage products solve different problems:

D1 uses SQLite’s SQL dialect and has its own service limits. Each database has a primary that handles writes, so a Worker running near the reader may still need to reach a database in another region. We’ll return to read replicas later.

For our draft library, one table is enough. Store the original text, its language, and the chosen reading speed. Calculate the reading estimate when needed using analyzeReading. Storing both the text and its derived word count would give us two values to keep in sync whenever the calculation changes.

Start with a local database

In the existing project’s wrangler.jsonc, add this binding to the top-level configuration. Keep the existing settings, including assets and vars:

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "reading-drafts",
      "database_id": "00000000-0000-0000-0000-000000000000"
    }
  ]
}

The all-zero ID is a local placeholder. Wrangler runs D1 locally without a remote database and keeps its state under .wrangler. Restarting the dev server preserves your drafts; deleting that directory removes them. Keep .wrangler/ out of Git.

We also need a temporary identity for testing. Add this line to .dev.vars, and make sure .dev.vars is ignored by Git:

LOCAL_DRAFTS="true"

This flag enables a fixed test author for the local walkthrough. It does not authenticate anyone. Use test text, bind the dev server to 127.0.0.1, and keep the flag out of remote settings. Without it, the draft routes return 503; the reading-time API continues to work.

Generate the binding types, then create the first migration:

npx wrangler types --env-interface CloudflareBindings
npx wrangler d1 migrations create reading-drafts create_drafts

Wrangler creates a numbered SQL file under migrations/. Put the following in that file; in a new migration directory it will be 0001_create_drafts.sql:

CREATE TABLE drafts (
  id TEXT PRIMARY KEY NOT NULL,
  owner_id TEXT NOT NULL,
  title TEXT NOT NULL,
  text TEXT NOT NULL,
  language TEXT NOT NULL CHECK (language IN ('en', 'ar')),
  words_per_minute INTEGER NOT NULL
    CHECK (words_per_minute BETWEEN 80 AND 600),
  version INTEGER NOT NULL DEFAULT 1,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX drafts_owner_updated
  ON drafts (owner_id, updated_at DESC, id DESC);

Each row has a server-generated ID and an owner. Timestamps are Unix milliseconds from Date.now(). The version starts at 1; each successful update increments it so we can detect edits based on an older copy.

The database checks the allowed language and reading-speed range. The API will also validate the title and text before writing. Database constraints protect stored data; API validation gives the reader a useful error message.

Apply the migration locally:

npx wrangler d1 migrations apply reading-drafts --local

A migration is a recorded schema change. Wrangler tracks which files it has applied. When the schema changes later, create a new migration instead of editing one that has already run elsewhere. Commit migration files with the code that needs them.

Keep ownership on the server

A draft ID identifies a row. It does not grant access to that row.

Every read, update, and delete below includes the owner in its WHERE clause. In a deployed application, the server must derive that owner from a verified session. Accepting ownerId from JSON or a query parameter would let a caller choose whose drafts to access.

Start src/drafts.ts with the router, the row type, and the local-author middleware:

import { Hono } from "hono";
import { bodyLimit } from "hono/body-limit";
import { readDraft, type DraftEnv } from "./draft-input";

export const drafts = new Hono<DraftEnv>();

type DraftRow = {
  id: string;
  title: string;
  text: string;
  language: "en" | "ar";
  wordsPerMinute: number;
  version: number;
  createdAt: number;
  updatedAt: number;
};

drafts.use("*", async (c, next) => {
  c.header("Cache-Control", "no-store");
  if (c.env.LOCAL_DRAFTS !== "true") {
    return c.json(
      { error: "Drafts are disabled until authentication is configured." },
      503,
    );
  }
  c.set("ownerId", "local-author");
  await next();
});

drafts.use("*", bodyLimit({
  maxSize: 64 * 1024,
  onError: (c) => c.json({ error: "Keep the request body within 64 KiB." }, 413),
}));

The extra LOCAL_DRAFTS property keeps the local flag typed even when generated bindings omit it, as they would when generated without your .dev.vars.

The body limit rejects requests over 64 KiB before our handler parses the JSON. Cache-Control: no-store tells browsers and shared caches not to store these responses. Keep the parent app’s request-ID logging and generic error handler from part three; do not log draft text.

We will reuse analyzeReading to validate the text, language, and speed. The small middleware below adds title validation and checks the optional version. Put it in src/draft-input.ts before continuing.

import { createMiddleware } from "hono/factory";
import { analyzeReading } from "./analyze";

export type DraftInput = {
  title: string;
  text: string;
  language: "en" | "ar";
  wordsPerMinute: number;
  version?: number;
};

export type DraftEnv = {
  Bindings: CloudflareBindings & { LOCAL_DRAFTS?: string };
  Variables: { ownerId: string; draft: DraftInput };
};

export const readDraft = createMiddleware<DraftEnv>(async (c, next) => {
  const mediaType = c.req.header("Content-Type")
    ?.split(";")[0]?.trim().toLowerCase();

  if (mediaType !== "application/json") {
    return c.json({ error: "Use Content-Type: application/json." }, 415);
  }

  let input: unknown;
  try {
    input = await c.req.json();
  } catch (error) {
    if (error instanceof SyntaxError) {
      return c.json({ error: "Send valid JSON." }, 400);
    }
    throw error;
  }

  if (!input || typeof input !== "object" || Array.isArray(input)) {
    return c.json({ error: "Send a JSON object." }, 400);
  }
  const data = input as Record<string, unknown>;
  const title = typeof data.title === "string" ? data.title.trim() : "";
  if (!title || title.length > 120) {
    return c.json({ error: "Use a title of 1–120 UTF-16 code units." }, 400);
  }

  const analysis = analyzeReading(data, 200);
  if (!analysis.ok) return c.json({ error: analysis.error }, 400);

  const version = data.version;
  if (version !== undefined && (
    typeof version !== "number" || !Number.isSafeInteger(version) || version < 1
  )) {
    return c.json({ error: "Use a positive integer version." }, 400);
  }

  c.set("draft", {
    title,
    text: data.text as string, // Validated by analyzeReading.
    language: analysis.value.language as DraftInput["language"],
    wordsPerMinute: analysis.value.wordsPerMinute,
    version,
  });
  await next();
});

The default speed is 200 words per minute. Text keeps the previous article’s 10,000 UTF-16 code-unit limit; a title is limited to 120. The two type assertions rely on the runtime checks in analyzeReading.

Save a draft

Append this handler to src/drafts.ts:

drafts.post("/", readDraft, async (c) => {
  const draft = c.get("draft");
  const id = crypto.randomUUID();
  const now = Date.now();

  await c.env.DB.prepare(`
    INSERT INTO drafts
      (id, owner_id, title, text, language, words_per_minute, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `).bind(
    id, c.get("ownerId"), draft.title, draft.text,
    draft.language, draft.wordsPerMinute, now, now,
  ).run();

  return c.json({ id, version: 1 }, 201);
});

prepare() contains the SQL; bind() supplies its values. If the title contains an apostrophe or text that looks like SQL, it remains a title. Do not interpolate user input into the statement. Placeholders represent values, not table names or sort directions; keep those fixed or select them from an explicit allowlist.

We await the insert before returning 201 Created. A save should not be reported as successful while the database write is still pending. Background work with waitUntil() is useful for other tasks, but it would give this response the wrong meaning.

The ID and timestamps come from the server. Any ownerId or id in the request body is ignored. This create endpoint generates a new ID for every successful request, so submitting the same request twice creates two drafts. If the write succeeds but the response is lost, retrying the request can create a duplicate. Before adding automatic retries to a save button, give each intended create a stable idempotency key and enforce its uniqueness in the database.

List recent drafts and reopen one

Add these two handlers to the same file:

drafts.get("/", async (c) => {
  const { results } = await c.env.DB.prepare(`
    SELECT id, title, language, version, updated_at AS updatedAt
    FROM drafts
    WHERE owner_id = ?
    ORDER BY updated_at DESC, id DESC
    LIMIT 20
  `).bind(c.get("ownerId")).all();

  return c.json({ drafts: results });
});

drafts.get("/:id", async (c) => {
  const draft = await c.env.DB.prepare(`
    SELECT id, title, text, language, words_per_minute AS wordsPerMinute,
           version, created_at AS createdAt, updated_at AS updatedAt
    FROM drafts
    WHERE id = ? AND owner_id = ?
  `).bind(c.req.param("id"), c.get("ownerId")).first<DraftRow>();

  if (!draft) return c.json({ error: "Draft not found." }, 404);
  return c.json(draft);
});

The list returns metadata for the latest 20 drafts, leaving the full text for the individual-draft endpoint. For a full library, add cursor pagination using the same updated_at and id ordering. An edit can move a row between pages, so this live ordering is not a fixed snapshot of the library.

The methods have different return shapes: .all() returns a result object containing a results array; .first() returns a row or null. The generic type on .first<DraftRow>() describes the expected row to TypeScript. It does not check the database schema at runtime.

An ID belonging to another reader gets the same 404 response as a nonexistent ID.

Make your first save

In src/index.ts, import the router and mount it after the existing global middleware. Keep the other routes and error handler:

import { drafts } from "./drafts";

// After the existing global middleware:
app.route("/api/drafts", drafts);

The previous article’s assets.run_worker_first setting already includes /api/*, so draft requests reach Hono. Start the local Worker:

npx wrangler dev --ip 127.0.0.1 --port 8787

Create a draft:

curl -i http://127.0.0.1:8787/api/drafts \
  -H 'Content-Type: application/json' \
  --data '{
    "title": "Notes for the D1 article",
    "text": "A saved draft should still be here tomorrow.",
    "language": "en",
    "wordsPerMinute": 200
  }'

Expect 201 and JSON containing an id and "version": 1. Copy that ID into a shell variable, then list and reopen the draft:

DRAFT_ID="paste-the-returned-id-here"
curl http://127.0.0.1:8787/api/drafts
curl "http://127.0.0.1:8787/api/drafts/$DRAFT_ID"

Stop Wrangler, start it again with the same command, and repeat the GET. The draft should still be there. That is the first useful difference from keeping data in a variable inside the Worker.

Protect a save from an older tab

Open the same draft in two tabs. Edit its opening paragraph in the first, save, then change the title in the second and save again. A plain update would replace the newer paragraph with the older text still held by the second tab.

We can catch that with one condition: save only if the stored version still matches the version I loaded.

Append the update handler:

drafts.put("/:id", readDraft, async (c) => {
  const draft = c.get("draft");
  if (draft.version === undefined) {
    return c.json({ error: "Include the version you loaded." }, 400);
  }

  const saved = await c.env.DB.prepare(`
    UPDATE drafts
    SET title = ?, text = ?, language = ?, words_per_minute = ?,
        updated_at = ?, version = version + 1
    WHERE id = ? AND owner_id = ? AND version = ?
    RETURNING id, version
  `).bind(
    draft.title, draft.text, draft.language, draft.wordsPerMinute,
    Date.now(), c.req.param("id"), c.get("ownerId"), draft.version,
  ).first<{ id: string; version: number }>();

  if (saved) return c.json(saved);

  const existing = await c.env.DB.prepare(`
    SELECT id FROM drafts WHERE id = ? AND owner_id = ?
  `).bind(c.req.param("id"), c.get("ownerId")).first();

  if (!existing) return c.json({ error: "Draft not found." }, 404);
  return c.json(
    { error: "This draft changed. Load it again before saving." },
    409,
  );
});

This PUT replaces the editable fields, so the client sends the whole draft and its current version. Use the returned version for the next save. The UPDATE checks ownership and version in the same statement that writes the changes. A separate “read version, then update” sequence would leave a gap for another request to write between the two operations.

RETURNING gives us the new version when the update matches. If nothing matched, a second, owner-scoped lookup distinguishes a missing draft from a conflict. That lookup chooses the response; the conditional update already prevented the stale write.

On 409 Conflict, keep the reader’s unsaved text. Load the current draft and offer a way to compare or reconcile the changes. Retrying automatically with the newer version would defeat the protection we just added.

With the Worker still running, update the draft you created earlier:

curl -i -X PUT "http://127.0.0.1:8787/api/drafts/$DRAFT_ID" \
  -H 'Content-Type: application/json' \
  --data '{
    "title": "Notes for the D1 article",
    "text": "A saved draft should survive a restart and a second tab.",
    "language": "en",
    "wordsPerMinute": 200,
    "version": 1
  }'

The response should contain "version": 2. Run that exact command again and expect 409: version 1 is now outdated. Reopen the draft and check that the saved text has not changed.

Delete a draft

Append the last handler to src/drafts.ts:

drafts.delete("/:id", async (c) => {
  const result = await c.env.DB.prepare(`
    DELETE FROM drafts WHERE id = ? AND owner_id = ?
  `).bind(c.req.param("id"), c.get("ownerId")).run();

  if (result.meta.changes === 0) {
    return c.json({ error: "Draft not found." }, 404);
  }
  return c.body(null, 204);
});

Here meta.changes tells us whether the delete affected a row. Deletion is unconditional once ownership matches: it removes the current draft even if another tab edited it recently. If your UI promises “delete only the version I saw,” add a version condition here too.

Try the remaining error cases before building the save button:

Delete the test draft when finished:

curl -i -X DELETE "http://127.0.0.1:8787/api/drafts/$DRAFT_ID"

Expect 204 with no response body, then 404 when you try to reopen it. In a future browser UI, insert returned titles and text using safe text or form-value APIs, not innerHTML.

Make the index match the question

The list query asks for one owner’s drafts ordered by update time and ID. The index follows that same order:

CREATE INDEX drafts_owner_updated
  ON drafts (owner_id, updated_at DESC, id DESC);

This lets SQLite find the owner’s entries and walk them in the requested order. Without a suitable index, even a query returning only 20 rows may scan many rows and sort the matches first.

I tested that list query against 10,000 local drafts, split evenly across 100 authors. With no owner/date index, D1 reported 10,100 rows read. After adding the index, it reported 20. Both runs returned exactly the same rows in the same order.

D1 reported more reads than there are drafts in this fixture; the plan also shows a temporary sorting structure. meta.rows_read counts read work, not distinct draft IDs. To find out how much the index improves response times, measure complete requests in your deployed application.

node index-lab.mjs

Inspect the plan locally:

npx wrangler d1 execute reading-drafts --local --command \
  "EXPLAIN QUERY PLAN
   SELECT id, title, language, version, updated_at
   FROM drafts WHERE owner_id = 'local-author'
   ORDER BY updated_at DESC, id DESC LIMIT 20;"

Look for USING INDEX drafts_owner_updated. The indexed plan should avoid the full scan and temporary sort. For production measurements, inspect meta.rows_read and time the whole awaited D1 call: the metadata’s SQL duration excludes network time.

D1’s usage charges count rows read and written, as well as storage. Rows returned are not the same as rows read. Indexes can reduce reads, but maintaining them adds storage and can add billed writes. Add indexes for queries you actually run, then measure. The pricing documentation explains these counters.

As of November 2026, the Free plan includes 5 million rows read and 100,000 rows written per day. Reaching either daily limit causes queries to fail until it resets or you upgrade. Its 5 GB total storage allowance is spread across databases: an individual Free database is limited to 500 MB. On Workers Paid, the per-database limit is 10 GB. Check the current pricing and limits before planning around those numbers.

Keep local, preview, and production data separate

Our placeholder ID has never pointed to a remote database. When you are ready to test remotely, create a real D1 database and use the configuration returned by Wrangler:

npx wrangler d1 create reading-drafts-preview

A database is not tied to a particular code deployment. Binding a preview to the production database would let preview code change real data. Give preview and production separate database IDs, and make the migration target explicit.

For example, a dedicated wrangler.preview-migrations.jsonc can name only the preview database:

{
  "$schema": "node_modules/wrangler/config-schema.json",
  "d1_databases": [
    {
      "binding": "PREVIEW_DB",
      "database_name": "reading-drafts-preview",
      "database_id": "replace-with-the-real-preview-database-id",
      "migrations_dir": "migrations"
    }
  ]
}

Then the remote migration command has a clear destination:

npx wrangler d1 migrations apply PREVIEW_DB --remote \
  --config wrangler.preview-migrations.jsonc

For the branch previews from part four, configure the Worker’s previews.d1_databases binding with binding: "DB" and that same preview database ID. The separate migration config only targets schema changes; it does not wire the Worker to the database. Follow Cloudflare’s preview-resource setup for the full configuration. Previews sharing an ID also share migrations; use a dedicated database when testing an incompatible schema change.

Before exposing drafts remotely, replace the local-author middleware with verified session authentication. Set ownerId from that session, return 401 for unauthenticated requests, and retain the owner filters. Cookie-based sessions also need appropriate CSRF protection for mutations. Never copy LOCAL_DRAFTS=true into remote settings to get past the 503.

When changing a live schema, keep old and new code compatible during rollout. Adding a nullable column, deploying code that understands it, and backfilling separately is easier to recover from than immediately dropping a column still used by the old Worker. Rolling back Worker code does not roll back a D1 migration.

What happens when the app grows?

Each D1 database instance processes queries one at a time. A slow scan occupies it longer and delays other work. Keep queries short, measure their row counts, and avoid turning one HTTP request into dozens of database round trips.

If one operation must make several related writes, D1’s batch() executes prepared statements sequentially in a transaction and rolls the sequence back if a statement fails. Separate awaited calls do not become one transaction just because they happen in the same request. A statement that updates zero rows still succeeds in SQL. If a batch includes a conditional update, do not assume a version mismatch will roll the other statements back. Our update needs only one statement.

For geographically distributed readers, D1 offers read replication. Enable it on the database and use the Sessions API to route reads to replicas. Our plain binding queries continue to use the primary, and writes always go there.

A D1 session keeps successive reads from going backwards in database history. It neither identifies the reader nor groups the queries into a transaction. Pass its bookmark to a later request to preserve that continuity. If a request must begin with the primary’s current data, start with withSession("first-primary"). Switching every query to a fresh unconstrained session would not give a reader reliable read-after-write behavior across requests.

For recovery, Time Travel is enabled automatically on remote D1 databases. The recovery window is seven days on Free and 30 days on Paid. Restoring a database affects the whole database, including valid writes after the chosen point. For a reader-facing undo feature, store draft revisions in the application. Use SQL exports when you need an independent copy, and test your restore process before depending on it.

You can now save a draft, restart the Worker, and reopen it. The remaining work for a public draft library is concrete: verify the reader’s identity, connect the save and load controls, and give conflicts a UI that preserves unsaved text.

What would your version need next: revision history, tags, or sharing? Send me the query you are trying to write, or share this article with someone adding storage to a Worker.