ch09-d1

對應 09. D1 入門：邊緣上的 SQLite

在 GitHub 上檢視·6 個檔案·7.5 KB

取得並執行

這個範例可以獨立 clone 執行，不依賴其他章節。

npx degit mrsuner/cloudflare-serverless-platform-manuals/examples/ch09-d1
cd ch09-d1
npm install

可用指令

npm run dev	# wrangler dev
npm run deploy	# wrangler deploy
npm run typecheck	# tsc --noEmit
npm run cf-typegen	# wrangler types --env-interface CloudflareBindings
npm run db:apply	# wrangler d1 migrations apply linkforge-demo --local

說明

ch09 — D1 behaviour probes

Companion example for docs/09-d1.md.

Two tables with identical data; only one has an index. Everything runs locally — no Cloudflare account.

npm install
npx wrangler d1 migrations apply linkforge-demo   # note: defaults to LOCAL
npm run dev

B=localhost:8787
curl -s "$B/seed?n=2000"
curl -s "$B/rowsread"
curl -s "$B/explain"
curl -s "$B/meta"
curl -s "$B/types"
curl -s "$B/first"
curl -s "$B/tx"
curl -s "$B/exec"

Verified findings

2026-07-28 · wrangler@4.114.0 · workerd@1.20260722.1

rows_read: 1 vs 2000 for the same logical query

{"indexed":{"rows_read":1,"found":1},"unindexed":{"rows_read":2000,"found":1}}

{"indexed":  [{"detail":"SEARCH links USING INDEX idx_links_tenant_slug (tenant_id=? AND slug=?)"}],
 "unindexed":[{"detail":"SCAN links_unindexed"}]}

rows_read bills scanned rows, not returned rows. SCAN in a query plan is money burning.

first() does not add LIMIT 1

{"noLimit_rows_read":2000,"withLimit_rows_read":1}

The CLI defaults to local

$ npx wrangler d1 migrations apply linkforge-demo

Resource location: local

Use --remote if you want to access the remote instance.
...
🌀 Executing on local database linkforge-demo (...) from .wrangler/state/v3/d1:
🌀 To execute on your remote database, add a --remote flag to your wrangler command.

No --local was passed. CI migrations must pass --remote explicitly or production never changes.

No interactive transactions; batch() really is atomic

{"begin":{"threw":"D1_ERROR: To execute a transaction, please use the state.storage.transaction() or state.storage.transactionSync() APIs instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements..."},
 "savepoint":{"threw":"(same)"},
 "batchRollback":{"ok":{"error":"D1_ERROR: UNIQUE constraint failed: links.tenant_id, links.slug",
                        "before":2000,"after":2000,"firstRowSurvived":false}}}

The first INSERT in the batch would have succeeded; the second violated the unique index and rolled the whole batch back. Note the error text points at a Durable Object API that does not exist on a D1 binding.

Type conversions

{"undefinedBind":{"threw":"D1_TYPE_ERROR: Type 'undefined' not supported for value 'undefined'"},
 "nullBind":{"ok":{"v":null}},
 "booleanBind":{"ok":{"v":1}},
 "blobBind":{"ok":{"v":[1,2,3]}},
 "bigintBind":{"threw":"D1_TYPE_ERROR: Type 'bigint' not supported for value '10'"},
 "storedBoolean":{"ok":{"is_active":1}}}

undefined throws (spread an object with a missing optional key into .bind() and you get this), booleans come back as 0/1, a Uint8Array comes back as a plain integer array, and BigInt is rejected outright.

exec() splits on newlines, not semicolons

{"exec":{"ok":{"count":1,"duration":0}}}

exec("SELECT 1; SELECT 2") reports count: 1.

The local database is a real SQLite file

.wrangler/state/v3/d1/miniflare-D1DatabaseObject/<hash>.sqlite

Stop wrangler dev, then sqlite3 <that file> and .schema works.

meta is a cost receipt

{"served_by":"miniflare.db","duration":0,"changes":0,"last_row_id":2000,
 "changed_db":false,"size_after":253952,"rows_read":1,"rows_written":0}

Note served_by is present at runtime but absent from D1Meta in the generated types (it is reachable through the Record<string, unknown> intersection). Production adds served_by_region, served_by_colo, served_by_primary, timings and total_attempts.

原始碼

• wrangler.jsonc
• package.json
• src/index.ts
• migrations/0001_init.sql
• migrations/0002_no_index_table.sql
• tsconfig.json

wrangler.jsonc

{
  "$schema": "./node_modules/wrangler/config-schema.json",
  "name": "ch09-d1",
  "main": "src/index.ts",
  "compatibility_date": "2026-07-24",
  "observability": { "enabled": true },
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "linkforge-demo",
      "database_id": "00000000-0000-0000-0000-0000000d1000",
      "migrations_dir": "./migrations"
    }
  ]
}

package.json

{ "name": "ch09-d1", "private": true, "type": "module",
  "scripts": { "dev": "wrangler dev", "deploy": "wrangler deploy", "typecheck": "tsc --noEmit",
    "cf-typegen": "wrangler types --env-interface CloudflareBindings",
    "db:apply": "wrangler d1 migrations apply linkforge-demo --local" },
  "devDependencies": { "typescript": "^5.9.0", "wrangler": "^4.114.0" } }

src/index.ts

// ---------------------------------------------------------------------------
// Chapter 09 — D1 behaviour probes.
// ---------------------------------------------------------------------------
const t = async (fn: () => Promise<unknown>): Promise<unknown> => {
  try { return { ok: await fn() }; }
  catch (e) { return { threw: String(e).slice(0, 220) }; }
};

export default {
  async fetch(request: Request, env: CloudflareBindings): Promise<Response> {
    const url = new URL(request.url);
    const db = env.DB;

    switch (url.pathname) {
      // Seed both tables with identical data.
      case "/seed": {
        const n = Number(url.searchParams.get("n") ?? 2000);
        await db.exec("DELETE FROM links");
        await db.exec("DELETE FROM links_unindexed");
        const now = Date.now();
        for (let batch = 0; batch < n; batch += 100) {
          const stmts = [];
          for (let i = batch; i < Math.min(batch + 100, n); i++) {
            stmts.push(
              db.prepare(
                "INSERT INTO links (tenant_id, slug, url, is_active, created_at) VALUES (?, ?, ?, ?, ?)",
              ).bind("t1", `s${i}`, `https://example.com/${i}`, 1, now),
              db.prepare(
                "INSERT INTO links_unindexed (tenant_id, slug, url) VALUES (?, ?, ?)",
              ).bind("t1", `s${i}`, `https://example.com/${i}`),
            );
          }
          await db.batch(stmts);
        }
        return Response.json({ seeded: n });
      }

      // The headline cost lesson: same logical query, two rows_read values.
      case "/rowsread": {
        const slug = url.searchParams.get("slug") ?? "s1999";
        const indexed = await db
          .prepare("SELECT * FROM links WHERE tenant_id = ? AND slug = ?")
          .bind("t1", slug)
          .all();
        const unindexed = await db
          .prepare("SELECT * FROM links_unindexed WHERE tenant_id = ? AND slug = ?")
          .bind("t1", slug)
          .all();
        return Response.json({
          indexed: { rows_read: indexed.meta.rows_read, found: indexed.results.length },
          unindexed: { rows_read: unindexed.meta.rows_read, found: unindexed.results.length },
        });
      }

      // EXPLAIN QUERY PLAN is the official way to check index usage.
      case "/explain": {
        const a = await db
          .prepare("EXPLAIN QUERY PLAN SELECT * FROM links WHERE tenant_id = ? AND slug = ?")
          .bind("t1", "s1").all();
        const b = await db
          .prepare("EXPLAIN QUERY PLAN SELECT * FROM links_unindexed WHERE tenant_id = ? AND slug = ?")
          .bind("t1", "s1").all();
        return Response.json({ indexed: a.results, unindexed: b.results });
      }

      // The full meta object, and what the docs do/don't list.
      case "/meta": {
        const r = await db.prepare("SELECT * FROM links LIMIT 1").all();
        return Response.json({ meta: r.meta, metaKeys: Object.keys(r.meta).sort() });
      }

      // Type round-trips: boolean, blob, undefined, bigint.
      case "/types": {
        const out: Record<string, unknown> = {};
        out.undefinedBind = await t(() =>
          db.prepare("SELECT ? AS v").bind(undefined).first(),
        );
        out.nullBind = await t(() => db.prepare("SELECT ? AS v").bind(null).first());
        out.booleanBind = await t(() => db.prepare("SELECT ? AS v").bind(true).first());
        out.blobBind = await t(() =>
          db.prepare("SELECT ? AS v").bind(new Uint8Array([1, 2, 3])).first(),
        );
        out.bigintBind = await t(() => db.prepare("SELECT ? AS v").bind(10n).first());
        out.storedBoolean = await t(() =>
          db.prepare("SELECT is_active FROM links LIMIT 1").first(),
        );
        return Response.json(out);
      }

      // first() does NOT add LIMIT 1 for you — watch rows_read.
      case "/first": {
        const withoutLimit = await db.prepare("SELECT * FROM links_unindexed WHERE tenant_id = ?").bind("t1").run();
        const stmt = db.prepare("SELECT * FROM links_unindexed WHERE tenant_id = ? LIMIT 1").bind("t1");
        const withLimit = await stmt.run();
        return Response.json({
          noLimit_rows_read: withoutLimit.meta.rows_read,
          withLimit_rows_read: withLimit.meta.rows_read,
        });
      }

      // Transactions: what actually happens.
      case "/tx": {
        const out: Record<string, unknown> = {};
        out.begin = await t(() => db.prepare("BEGIN TRANSACTION").run());
        out.savepoint = await t(() => db.prepare("SAVEPOINT sp1").run());
        out.batchRollback = await t(async () => {
          const before = (await db.prepare("SELECT COUNT(*) AS c FROM links").first<{ c: number }>())!.c;
          try {
            await db.batch([
              db.prepare("INSERT INTO links (tenant_id, slug, url, created_at) VALUES (?,?,?,?)")
                .bind("t1", "tx-ok", "https://ok", Date.now()),
              // Violates the unique index -> whole batch must roll back.
              db.prepare("INSERT INTO links (tenant_id, slug, url, created_at) VALUES (?,?,?,?)")
                .bind("t1", "s0", "https://dup", Date.now()),
            ]);
          } catch (e) {
            const after = (await db.prepare("SELECT COUNT(*) AS c FROM links").first<{ c: number }>())!.c;
            const orphan = await db.prepare("SELECT 1 FROM links WHERE slug = 'tx-ok'").first();
            return { error: String(e).slice(0, 120), before, after, firstRowSurvived: orphan !== null };
          }
          return "batch unexpectedly succeeded";
        });
        return Response.json(out);
      }

      case "/exec": {
        return Response.json({
          exec: await t(() => db.exec("SELECT 1; SELECT 2")),
        });
      }

      default:
        return new Response("try /seed /rowsread /explain /meta /types /first /tx /exec\n", { status: 404 });
    }
  },
} satisfies ExportedHandler<CloudflareBindings>;

migrations/0001_init.sql

-- Chapter 09 demo schema.
CREATE TABLE IF NOT EXISTS links (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id   TEXT    NOT NULL,
  slug        TEXT    NOT NULL,
  url         TEXT    NOT NULL,
  is_active   INTEGER NOT NULL DEFAULT 1,   -- SQLite has no BOOLEAN
  created_at  INTEGER NOT NULL,
  note        TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_links_tenant_slug ON links(tenant_id, slug);

migrations/0002_no_index_table.sql

-- Same shape, deliberately without an index, to show rows_read.
CREATE TABLE IF NOT EXISTS links_unindexed (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id   TEXT    NOT NULL,
  slug        TEXT    NOT NULL,
  url         TEXT    NOT NULL
);

tsconfig.json

{ "compilerOptions": { "target": "esnext", "lib": ["esnext"], "module": "esnext",
  "moduleResolution": "bundler", "types": ["./worker-configuration.d.ts"],
  "strict": true, "skipLibCheck": true, "noEmit": true, "isolatedModules": true },
  "include": ["src/**/*.ts", "worker-configuration.d.ts"] }