D1 + Durable Object Storage Best Practices

Use this skill for Cloudflare storage design and query implementation with:

• D1 Worker bindings (env.DB)
• Durable Objects SQLite storage (ctx.storage.sql)
• Drizzle ORM on D1 (drizzle-orm/d1)

Quick Reference Selector

• D1/SQLite query tuning, indexes, and schema checks -> references/sqlite-d1-best-practices.md
• D1/DO error normalization and tagging -> references/storage-error-classification.md
• Retry behavior and idempotent write rules -> references/storage-retry-idempotency.md
• SQLite-backed Durable Object SQL, transactions, lifecycle, and PITR -> references/durable-object-sqlite-patterns.md

When to Use

• Adding or refactoring D1 queries
• Building multi-statement write flows
• Porting raw SQL to Drizzle (or mixed raw SQL + Drizzle)
• Defining retry/error handling for D1 or Durable Object storage
• Reviewing performance regressions due to DB round trips

Non-Negotiable Rules

1. Prefer prepare + bind and batch

• Use env.DB.prepare(...).bind(...) for normal D1 queries.
• Use env.DB.batch([...]) (or Drizzle db.batch([...])) for related statements to reduce round trips.
• Avoid env.DB.exec() for normal app paths; reserve it for one-shot admin/maintenance jobs.

2. Treat D1 transactions as unsupported in app code

• Do not use SQL BEGIN TRANSACTION, COMMIT, or SAVEPOINT in D1 Worker paths.
• Do not use Drizzle db.transaction(...) on D1.
• Use batch for atomic multi-statement units.
• If code must run on D1, assume there is no user-managed transaction support.

3. Use Durable Objects for serialized coordination

• Use SQLite-backed Durable Objects for per-entity coordination/serialization.
• Persist important state in storage, not only in memory (objects can be evicted/restarted).
• Prefer the SQLite backend for new Durable Object classes.

4. Design for D1 limits and throughput

• A single D1 database processes queries one-at-a-time; optimize query duration.
• Add indexes for high-volume lookup/filter columns.
• Chunk large updates/deletes (for example 500-1000 rows per batch).
• Retry only when idempotent and when the error is retryable.

5. Use SQLite-backed Durable Object storage correctly

• Only SQLite-backed DO classes can use ctx.storage.sql and PITR.
• In DO SQL paths, do not execute BEGIN TRANSACTION / COMMIT / SAVEPOINT with sql.exec().
• Use ctx.storage.transactionSync() for synchronous SQL-only transaction blocks.
• Use ctx.storage.transaction() for async KV-style transaction flows when needed.
• Initialize critical state with ctx.blockConcurrencyWhile(...) and persist data needed after eviction.
• For full teardown, call both ctx.storage.deleteAlarm() and ctx.storage.deleteAll().

Recommended Workflow

1. Confirm data placement:

• D1 for shared relational data.
• Durable Object SQLite for single-key/per-entity coordination and strongly-consistent object-local state.

2. Build statements with parameters:

• Standard API: prepare().bind()
• Drizzle: query builder with placeholders/typed values

3. Collapse round trips:

• Convert related statements into a single batch call.

4. Enforce transaction rule:

• No SQL BEGIN/COMMIT/SAVEPOINT on D1 paths.
• No Drizzle db.transaction() for D1.

5. Validate production safety:

• Add indexes for read paths.
• Make write retries idempotent.
• Add chunking for large write/migration operations.

6. Apply SQLite/D1 performance rules:

• Use workload-first schema/query design (read/write mix and hottest queries first).
• Validate index coverage with PRAGMA index_list, PRAGMA index_info, and sqlite_master.
• Run PRAGMA optimize after schema/index changes.
• Prefer cursor pagination and avoid function-wrapped predicates on indexed columns.
• Review index count regularly; do not over-index write-heavy tables.
• See the SQLite checklist in references/sqlite-d1-best-practices.md.

7. If using Durable Objects SQLite:

• Keep DO SQL access on ctx.storage.sql only for SQLite-backed classes.
• Use sql.exec(..., bindings) for parameterized SQL and inspect rowsRead / rowsWritten on cursors for cost/perf signals.
• Use PITR bookmarks for recovery workflows (production only; not supported in local development).
• See references/durable-object-sqlite-patterns.md.

Core Patterns

Standard D1 API (env.DB.batch)

const insertUser = env.DB
  .prepare("INSERT INTO users (id, email) VALUES (?, ?)")
  .bind(id, email);

const insertProfile = env.DB
  .prepare("INSERT INTO profiles (user_id, display_name) VALUES (?, ?)")
  .bind(id, displayName);

await env.DB.batch([insertUser, insertProfile]);

Drizzle on D1 (db.batch)

import { drizzle } from "drizzle-orm/d1";

const db = drizzle(env.DB);

await db.batch([
  db.insert(users).values({ id, email }),
  db.insert(profiles).values({ userId: id, displayName }),
]);

Avoid on D1

// Do not do this on D1:
await db.transaction(async (tx) => {
  // ...
});

References

• D1 Database API
• D1 Query Best Practices
• D1 Retry Best Practices
• D1 Limits + Throughput
• D1 Import/Export Note (BEGIN/COMMIT removal)
• Durable Objects SQLite Storage API
• Durable Objects Storage Best Practices
• Drizzle Batch API
• Storage Error Classification (D1 + DO)
• Storage Retries and Idempotency (D1 + DO)
• SQLite and D1 Best Practices
• Durable Object SQLite Patterns