Claude Code with Cloudflare D1: SQLite at the Edge
Why D1 without CLAUDE.md generates server-style SQL that crashes at the edge
Cloudflare D1 is SQLite running inside a Worker. It is not a TCP database behind a connection pool. There is no persistent socket. Every query goes over an HTTP binding that D1 exposes as env.DB, and the Worker runtime enforces strict limits on what SQL can run, how parameters must be passed, and how transactions must be structured.
Claude Code does not have this context by default. Its training data includes far more Postgres and MySQL usage than D1, so without explicit constraints, it generates code shaped by those runtimes. The patterns that emerge from that default are wrong in ways that are not immediately obvious.
The most common failure is parameter passing via string interpolation. Claude reaches for:
const result = await env.DB.prepare(`SELECT * FROM users WHERE id = ${userId}`).first();
This breaks for two reasons. First, Cloudflare's SQL injection scanner flags interpolated values and the Worker throws before the query executes. Second, D1's prepared statement interface is designed around .bind(), and bypassing it removes the safety layer. The correct form is:
const result = await env.DB.prepare("SELECT * FROM users WHERE id = ?").bind(userId).first();
The second most common failure is writing queries that use Postgres syntax. Claude defaults to $1, $2 parameter placeholders, RETURNING * on every mutation, SERIAL for auto-increment columns, and jsonb for JSON storage. None of these exist in SQLite. D1's dialect is SQLite 3.x with a small subset of extensions. Using Postgres syntax produces a runtime error the first time the Worker handles a request.
The third failure is expecting connection pooling to exist. Claude sometimes generates code that opens a database connection in a module-level variable, expecting it to be reused across requests. D1 is stateless. The binding env.DB is handed to the Worker for the duration of a single request, and there is no persistent connection to cache.
This guide covers the CLAUDE.md configuration that fixes all three failure modes. If you are setting up Claude Code for a Workers project from scratch, the Claude Code with Cloudflare Workers guide covers the surrounding runtime configuration. For a general SQLite guide that shares many of the dialect rules below, Claude Code with SQLite is a useful companion.
The D1 CLAUDE.md template
The CLAUDE.md at your project root is read at the start of every session. For a D1-backed Workers project it needs to declare: the binding name and where it comes from, the prepared statement pattern with mandatory .bind(), the migrations workflow, the SQLite dialect constraints that differ from Postgres, the db.batch() pattern for multi-statement atomicity, the Drizzle ORM driver config if applicable, and the hard rules that block the interpolation and Postgres-syntax patterns Claude generates without guidance.
# Cloudflare D1 database rules
## Stack
- Cloudflare Workers (ESM format)
- Cloudflare D1 (SQLite at the edge, accessed via HTTP binding)
- Wrangler 3.x CLI for migrations and local dev
- TypeScript 5.x strict mode
- Drizzle ORM 0.31+ (optional, see Drizzle section)
## D1 binding
- The D1 database is accessed via `env.DB` inside a Worker handler
- Binding declared in wrangler.toml, NOT imported or instantiated in code
- `env.DB` is a D1Database instance, available only in request scope
- NEVER attempt to open a database connection with a connection string
- NEVER cache `env.DB` in a module-level variable
- Type the binding in the Env interface: `DB: D1Database`
## Prepared statements (MANDATORY)
- ALWAYS use prepared statements: `env.DB.prepare("SQL with ? placeholders")`
- ALWAYS chain `.bind(...params)` before `.first()`, `.all()`, or `.run()`
- NEVER use string interpolation or template literals for query parameters
- NEVER concatenate user input into a query string
- Correct: `env.DB.prepare("SELECT * FROM users WHERE id = ?").bind(id).first()`
- FORBIDDEN: `env.DB.prepare(\`SELECT * FROM users WHERE id = ${id}\`).first()`
## Result methods
- `.first()`: returns the first row as an object, or null if no rows
- `.all()`: returns `{ results: Row[], meta: Meta }` (use `.results` to get the array)
- `.run()`: for INSERT / UPDATE / DELETE, returns `{ success, meta }`, no rows
- `.first<T>()`, `.all<T>()`: generic overloads for typed results
## SQLite dialect rules (D1 uses SQLite 3.x)
- Parameters: use `?` not `$1/$2` (no Postgres-style positional params)
- Auto-increment: use `INTEGER PRIMARY KEY` not `SERIAL` or `BIGSERIAL`
- JSON: store as `TEXT` with `JSON_EXTRACT()` or use Drizzle's json() column (NOT jsonb)
- RETURNING: NOT supported by D1, do NOT add `RETURNING *` to mutations
- Boolean: store as INTEGER (0/1), SQLite has no native BOOLEAN type
- Timestamps: store as `TEXT` (ISO 8601) or `INTEGER` (Unix seconds), no TIMESTAMPTZ
- Upsert: use `INSERT OR REPLACE` or `INSERT ... ON CONFLICT DO UPDATE SET`
- Full-text search: use FTS5 virtual tables, not Postgres tsvector
## Migrations
- Create: `wrangler d1 migrations create <db-name> <description>`
- Apply local: `wrangler d1 migrations apply <db-name>`
- Apply remote: `wrangler d1 migrations apply <db-name> --remote`
- Migration files live in migrations/ at project root as numbered SQL files
- NEVER write raw SQL schema changes directly to D1, always go through migrations
- Each migration file: one schema change, idempotent where possible (IF NOT EXISTS)
## Batching (atomicity)
- Use `env.DB.batch([...statements])` to run multiple statements atomically
- Each item in the array is a prepared+bound statement
- Batch is all-or-nothing: if any statement fails, none are committed
- Correct batch:
await env.DB.batch([
env.DB.prepare("INSERT INTO orders (id, user_id) VALUES (?, ?)").bind(orderId, userId),
env.DB.prepare("UPDATE inventory SET qty = qty - 1 WHERE product_id = ?").bind(productId),
]);
- NEVER run sequential `.run()` calls for operations that must be atomic, use batch
## Local development
- `wrangler dev` uses a local SQLite mirror automatically
- Local and remote D1 are separate databases, apply migrations to both
- `wrangler dev --remote` points at the production D1, use with caution
- Seed local D1: `wrangler d1 execute <db-name> --local --file=seed.sql`
- Inspect local D1: `wrangler d1 execute <db-name> --local --command "SELECT * FROM users"`
## Hard rules
- NEVER use string interpolation for query parameters, no exceptions
- NEVER use Postgres parameter style ($1, $2), use ? only
- NEVER add RETURNING * to any mutation query
- NEVER use SERIAL, BIGSERIAL, BOOLEAN, TIMESTAMPTZ, use INTEGER / TEXT equivalents
- NEVER run sequential .run() calls for atomic operations, use db.batch()
- NEVER cache env.DB at module scope, it is request-scoped
- ALWAYS await every D1 call, it returns a Promise
Three rules here prevent the majority of D1 failures Claude generates without them.
The no-interpolation rule is the single most impactful entry. Cloudflare's runtime has an active SQL injection scanner on D1 queries. Interpolated values trigger it, and the Worker throws a D1_ERROR before the query reaches the SQLite engine. String interpolation also defeats the purpose of prepared statements, which compile the query plan once and reuse it across requests. The .bind() method passes parameters safely in a separate channel from the SQL text.
The no-Postgres-syntax rule matters because D1's error messages for dialect mismatches are not always clear. RETURNING * is not supported is clear. near "jsonb": syntax error takes a moment to diagnose if you do not know D1 uses SQLite. The rule prevents Claude from generating an entire schema in Postgres SQL that then fails migration-by-migration when applied to D1.
The no-sequential-run-for-atomic-operations rule addresses a subtle correctness issue. If Claude generates two sequential .run() calls that together form a logical transaction (insert order, decrement inventory), and the Worker crashes or the second call fails, the database is left in an inconsistent state. db.batch() wraps them in a single atomic operation that either fully completes or fully rolls back.
wrangler.toml binding setup
The D1 binding connects your Worker code to the database. It is declared in wrangler.toml and injected into the Worker's env at runtime. Claude sometimes generates code that tries to import a D1 client or pass a connection string, which is not how D1 works.
The correct wrangler.toml configuration:
name = "my-worker"
main = "src/index.ts"
compatibility_date = "2024-09-23"
[[d1_databases]]
binding = "DB"
database_name = "my-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
The binding value is the property name on env. If binding = "DB", your Worker code uses env.DB. If you name it binding = "USERS_DB", the code uses env.USERS_DB. Claude will use env.DB consistently if you match the name in CLAUDE.md. The database_id comes from wrangler d1 create <db-name>, which outputs it to stdout and records it in your Cloudflare account.
For the TypeScript type, declare the binding in your Env interface:
export interface Env {
DB: D1Database;
// other bindings: KV, R2, Queue, etc.
}
export default {
async fetch(request: Request, env: Env): Promise<Response> {
const user = await env.DB
.prepare("SELECT * FROM users WHERE email = ?")
.bind("user@example.com")
.first<User>();
if (!user) return new Response("Not found", { status: 404 });
return Response.json(user);
},
};
Add the Env interface declaration to CLAUDE.md so Claude generates it consistently:
## TypeScript Env interface
interface Env {
DB: D1Database;
}
Always declare this in src/types.ts and import it in handler files.
Worker fetch signature: async fetch(request: Request, env: Env): Promise<Response>
Schema migrations via wrangler d1 migrations
D1 migrations are numbered SQL files in a migrations/ directory. Wrangler tracks which migrations have been applied using a d1_migrations table it manages internally. The workflow prevents Claude from writing schema changes inline in application code, which cannot be rolled back or audited.
The commands:
# Create a new migration file (generates migrations/0001_create_users.sql)
wrangler d1 migrations create my-db create_users
# Apply to local SQLite mirror
wrangler d1 migrations apply my-db
# Apply to remote (production) D1
wrangler d1 migrations apply my-db --remote
# List applied and pending migrations
wrangler d1 migrations list my-db
A migration file is plain SQL:
-- migrations/0001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
Note the SQLite idioms: INTEGER PRIMARY KEY AUTOINCREMENT instead of SERIAL, TEXT for timestamps with strftime() for the default, and IF NOT EXISTS to make the migration safe to re-run during development.
Add a migrations policy to CLAUDE.md:
## Migrations policy
- Schema changes go in migrations/ only, never in application code
- One logical change per migration file (table create, column add, index add)
- Use IF NOT EXISTS and IF EXISTS in all DDL for idempotency
- Apply local after creation: wrangler d1 migrations apply my-db
- Apply remote before deploying code that depends on the schema change
- Never modify a migration file after it has been applied to remote D1
- To undo a change: create a new migration that reverses it
Prepared statements and the .bind() rule
The prepared statement pattern in D1 has three parts: prepare the SQL with ? placeholders, bind the parameters with .bind(), and execute with the appropriate result method. Claude must be shown all three parts together to generate them correctly.
Common patterns for CLAUDE.md:
## Query patterns
### Fetch one row
const user = await env.DB
.prepare("SELECT id, email, name FROM users WHERE id = ?")
.bind(userId)
.first<User>();
// Returns User | null
### Fetch multiple rows
const { results } = await env.DB
.prepare("SELECT id, email FROM users WHERE active = ? ORDER BY created_at DESC LIMIT ?")
.bind(1, 50)
.all<Pick<User, "id" | "email">>();
// results is User[] (empty array if no rows)
### Insert
const { success, meta } = await env.DB
.prepare("INSERT INTO users (email, name, created_at) VALUES (?, ?, ?)")
.bind(email, name, new Date().toISOString())
.run();
// meta.last_row_id is the new row's ID
### Update
await env.DB
.prepare("UPDATE users SET name = ? WHERE id = ?")
.bind(newName, userId)
.run();
### Delete
await env.DB
.prepare("DELETE FROM users WHERE id = ?")
.bind(userId)
.run();
### Multiple params: bind left-to-right matching ?
await env.DB
.prepare("SELECT * FROM orders WHERE user_id = ? AND status = ? AND created_at > ?")
.bind(userId, "pending", cutoffDate)
.first<Order>();
One detail Claude frequently misses: .all() returns { results, meta, success }, not a plain array. The query results are in .results. Claude often writes const users = await env.DB.prepare(...).bind(...).all() and then tries to iterate users directly, which iterates over the wrapper object's keys, not the rows. CLAUDE.md should show the destructuring pattern explicitly.
Batching with db.batch()
db.batch() is D1's mechanism for running multiple statements in a single round-trip with atomicity. Each statement in the batch array must be a prepared and bound statement. The batch returns an array of results in the same order as the input array.
// Insert an order and decrement inventory atomically
const results = await env.DB.batch([
env.DB
.prepare("INSERT INTO orders (id, user_id, product_id, qty) VALUES (?, ?, ?, ?)")
.bind(orderId, userId, productId, qty),
env.DB
.prepare("UPDATE inventory SET qty = qty - ? WHERE product_id = ? AND qty >= ?")
.bind(qty, productId, qty),
]);
const [orderResult, inventoryResult] = results;
if (!inventoryResult.success) {
// The batch rolled back atomically, handle the failure
return Response.json({ error: "Insufficient inventory" }, { status: 409 });
}
Add the batch policy to CLAUDE.md:
## db.batch() policy
- Use batch for any operation that touches more than one table
- Use batch when two writes must both succeed or both fail
- Each array element must be env.DB.prepare("...").bind(...)
- Do NOT pass raw strings to batch, statements must be prepared
- Batch returns D1Result[] in input order, destructure to check each result
- NEVER chain multiple sequential .run() calls for operations that belong together
One pattern Claude tends to generate incorrectly is building the batch array with a loop and forgetting to call .bind() on each statement. The batch method will throw if it receives an unbound prepared statement. If your batch has variable-length input (batch-insert ten rows from an array), build the array explicitly:
const statements = items.map((item) =>
env.DB
.prepare("INSERT INTO line_items (order_id, product_id, qty, price) VALUES (?, ?, ?, ?)")
.bind(item.orderId, item.productId, item.qty, item.price)
);
await env.DB.batch(statements);
Common SQLite vs Postgres gotchas
D1 developers coming from a Postgres background encounter a consistent set of dialect differences. Claude generates Postgres syntax by default, so these belong explicitly in CLAUDE.md.
## SQLite vs Postgres dialect differences for D1
### Parameters
- SQLite: ? (positional, unnamed)
- Postgres: $1, $2 (positional, numbered)
- D1 always uses ?
### Auto-increment
- SQLite: INTEGER PRIMARY KEY AUTOINCREMENT
- Postgres: SERIAL or BIGSERIAL
- D1: INTEGER PRIMARY KEY AUTOINCREMENT
### Boolean
- SQLite: INTEGER (0 or 1), no BOOLEAN type
- Postgres: BOOLEAN (true/false)
- D1: store 0/1, cast in application code
### Timestamps
- SQLite: TEXT (ISO 8601) or INTEGER (Unix epoch)
- Postgres: TIMESTAMP WITH TIME ZONE
- D1: TEXT with strftime() for defaults, compare as strings
### JSON
- SQLite: TEXT, query with json_extract() or json_each()
- Postgres: jsonb with -> and ->> operators
- D1: TEXT. Example: json_extract(metadata, '$.plan')
### RETURNING
- Postgres: INSERT INTO ... RETURNING id
- D1: NOT SUPPORTED. Use meta.last_row_id after INSERT, or SELECT separately
### Case sensitivity
- SQLite string comparisons are case-insensitive by default (LIKE)
- Use GLOB for case-sensitive matching, or COLLATE BINARY
### Upsert
- SQLite: INSERT OR REPLACE INTO ... or INSERT ... ON CONFLICT(col) DO UPDATE SET ...
- Postgres: INSERT ... ON CONFLICT DO UPDATE SET ...
- D1 supports both SQLite forms
### LIMIT/OFFSET
- Both SQLite and Postgres use LIMIT/OFFSET, no difference
The RETURNING difference is the most disruptive because it changes how you retrieve a newly inserted row's ID. In Postgres you append RETURNING id and get the ID back in the result set. In D1 you call .run() and read meta.last_row_id. Claude will add RETURNING id to every INSERT it generates unless the rule explicitly forbids it.
Drizzle ORM integration with D1
Drizzle ORM has a dedicated D1 driver that wraps env.DB and provides a type-safe query builder. The configuration is different from Drizzle's Postgres or LibSQL drivers, and Claude often generates the wrong driver when it sees a Drizzle project without D1-specific context.
The correct drizzle.config.ts for D1:
import { defineConfig } from "drizzle-kit";
export default defineConfig({
schema: "./src/schema.ts",
out: "./migrations",
dialect: "sqlite",
driver: "d1-http",
dbCredentials: {
accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
databaseId: process.env.CLOUDFLARE_DATABASE_ID!,
token: process.env.CLOUDFLARE_D1_TOKEN!,
},
});
The dialect: "sqlite" and driver: "d1-http" pair is mandatory. Using dialect: "postgresql" or driver: "libsql" generates incorrect migrations and query types.
Inside a Worker, the Drizzle instance is created from the binding:
import { drizzle } from "drizzle-orm/d1";
import * as schema from "./schema";
export default {
async fetch(request: Request, env: Env): Promise<Response> {
const db = drizzle(env.DB, { schema });
const users = await db.select().from(schema.users).limit(10);
return Response.json(users);
},
};
A schema file using Drizzle's SQLite column types:
// src/schema.ts
import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core";
export const users = sqliteTable("users", {
id: integer("id").primaryKey({ autoIncrement: true }),
email: text("email").notNull().unique(),
name: text("name").notNull(),
createdAt: text("created_at").notNull()
.$defaultFn(() => new Date().toISOString()),
});
export const orders = sqliteTable("orders", {
id: integer("id").primaryKey({ autoIncrement: true }),
userId: integer("user_id").notNull().references(() => users.id),
status: text("status").notNull().default("pending"),
createdAt: text("created_at").notNull()
.$defaultFn(() => new Date().toISOString()),
});
Note the column types: integer not serial, text not timestamp, integer references instead of typed foreign keys. Claude defaults to Drizzle's Postgres column types (pgTable, serial, timestamp) unless the D1 schema conventions are shown explicitly.
Add a Drizzle section to CLAUDE.md:
## Drizzle ORM with D1
### Config (drizzle.config.ts)
- dialect: "sqlite" (NOT "postgresql")
- driver: "d1-http"
- out: "./migrations" (same directory as wrangler migrations)
### Schema (src/schema.ts)
- Import from "drizzle-orm/sqlite-core"
- Use sqliteTable, integer, text, real, blob
- NEVER import pgTable, serial, timestamp, jsonb, these are Postgres-only
### Instance
- Create per-request: const db = drizzle(env.DB, { schema })
- NEVER create at module scope
### Migrations
- Generate: npx drizzle-kit generate
- Apply: wrangler d1 migrations apply <db-name> (applies the SQL Drizzle generated)
- Or: npx drizzle-kit migrate (with d1-http driver via API)
### Query patterns
db.select().from(schema.users).where(eq(schema.users.id, id)).get()
db.insert(schema.users).values({ email, name }).run()
db.update(schema.users).set({ name }).where(eq(schema.users.id, id)).run()
db.delete(schema.users).where(eq(schema.users.id, id)).run()
For a broader look at Drizzle ORM patterns that complement the D1-specific rules above, Claude Code with Drizzle covers the general Drizzle conventions and schema patterns.
Local dev vs production D1
One of the most useful aspects of D1 development is the local SQLite mirror that wrangler dev provides. It lets you iterate on schema and queries without touching the production database. The gap Claude often does not understand is that local and remote D1 are entirely separate databases, and migrations must be applied to both independently.
## Local vs remote D1 workflow
### Local dev (default)
- wrangler dev starts a local SQLite mirror in .wrangler/state/v3/d1/
- Migrations: wrangler d1 migrations apply <db-name> --local
- Seed: wrangler d1 execute <db-name> --local --file=seed.sql
- Inspect: wrangler d1 execute <db-name> --local --command "SELECT * FROM users LIMIT 5"
### Remote (production)
- wrangler dev --remote connects to real D1, use only for integration testing
- Deploy: wrangler d1 migrations apply <db-name> --remote BEFORE wrangler deploy
- Inspect production: wrangler d1 execute <db-name> --remote --command "..."
### Order of operations for a schema change
1. wrangler d1 migrations create <db-name> <description>
2. Write the SQL in the generated migration file
3. wrangler d1 migrations apply <db-name> (local)
4. Test the change locally with wrangler dev
5. wrangler d1 migrations apply <db-name> --remote
6. wrangler deploy
### Environment separation
- Use separate databases for dev, staging, production
- Separate wrangler.toml entries or wrangler.toml environments:
[env.staging]
[[env.staging.d1_databases]]
binding = "DB"
database_name = "my-db-staging"
database_id = "staging-database-id"
### Hard rules
- NEVER run wrangler d1 migrations apply --remote in a CI step before tests pass
- NEVER use --remote for seeding local development data
- ALWAYS apply remote migrations before deploying code that requires the new schema
The most common production incident from skipping this workflow is deploying Worker code that references a table or column that does not yet exist in remote D1, because the migration was applied locally but not remotely before wrangler deploy. The deploy succeeds, the Worker starts, and the first request to hit the new code path throws a D1 error at runtime.
Permission hooks for wrangler commands
Wrangler commands that touch remote D1 are irreversible in some cases. Dropping a table in a migration cannot be undone. A destructive seed script can corrupt production data. Permission hooks in .claude/settings.local.json gate the commands that matter.
{
"permissions": {
"allow": [
"Bash(wrangler dev*)",
"Bash(wrangler d1 migrations create*)",
"Bash(wrangler d1 migrations apply * --local*)",
"Bash(wrangler d1 execute * --local*)",
"Bash(wrangler d1 migrations list*)",
"Bash(npx drizzle-kit generate*)"
],
"deny": [
"Bash(wrangler d1 migrations apply * --remote*)",
"Bash(wrangler d1 execute * --remote*)",
"Bash(wrangler deploy*)"
]
}
}
Local dev commands run without prompting. Any command that touches remote D1 or deploys to production requires explicit confirmation. This prevents Claude from running wrangler d1 migrations apply my-db --remote as part of an automated schema generation workflow without the developer seeing the prompt.
The deny list for wrangler deploy is a deliberate choice. Production deploys should always be a conscious action, not a step Claude triggers automatically at the end of a code generation session.
Building a D1 Worker that Claude gets right by default
The D1 CLAUDE.md in this guide produces Workers code where parameters are always passed through .bind() rather than string interpolation, schema changes go through numbered migration files, multi-table writes use db.batch() for atomicity, the Drizzle config uses the sqlite dialect with the d1-http driver, local and remote D1 are treated as separate environments with explicit migration steps for each, and SQLite dialect rules are enforced so Postgres-specific syntax never appears in schema files or queries.
The underlying principle is identical to any database integration with Claude Code. A D1 project without a CLAUDE.md produces queries with string interpolation, schemas with Postgres column types, and atomic operations implemented as sequential .run() calls. A project with the configuration above has one correct path through every database interaction. Claude generates the right code because the right code is specified, not because the model has deep D1 knowledge by default.
For the full Workers setup that surrounds the D1 layer, Claude Code with Cloudflare Workers covers the fetch handler, KV, R2, and Queue bindings that typically live alongside a D1 database. For the Supabase alternative if you need a Postgres-backed edge solution, Claude Code with Supabase covers the same CLAUDE.md approach for that stack. Claudify includes a D1-specific CLAUDE.md template, pre-configured with all binding patterns, migration workflows, the no-interpolation and no-Postgres-syntax rules, db.batch() conventions, and Drizzle d1-http driver configuration shown in this guide.
More like this
Ready to upgrade your Claude Code setup?
Get Claudify
