# D1

> Write Cloudflare D1 code and schemas for SQLite-backed relational data, prepared statements, bindings, migrations, typed queries, tenant boundaries, indexes, pagination, and idempotent writes. Use when Workers need SQL data on Cloudflare.

- Skill: `zllovesuki/d1` (Agent Skill)
- Install (CLI): `npx skillmds@latest add zllovesuki/d1`
- Raw SKILL.md: https://api.skillmd.com/api/skills/zllovesuki/d1/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: zllovesuki (https://skillmd.com/u/zllovesuki)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/zllovesuki/d1

---

# D1

Use this skill for Cloudflare D1 relational data. D1 is best when records are queryable with SQL and can be modeled with clear boundaries.

## Data modeling rules

- Design around natural boundaries: tenant, workspace, user, project, region, or product domain.
- Avoid one giant catch-all database if the workload naturally partitions.
- Put large blobs in R2 and store keys/metadata in D1.
- Add indexes for every production query pattern.
- Use pagination; do not return unbounded result sets.
- Use Durable Objects when you need serialized per-entity coordination around writes.
- Use Hyperdrive if the true source of truth must remain an existing Postgres/MySQL database.

## Binding and Env

```ts
export interface Env {
  DB: D1Database;
}
```

```jsonc
{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "app-prod",
      "database_id": "REPLACE_WITH_DATABASE_ID"
    }
  ]
}
```

## Prepared statement pattern

```ts
type Todo = {
  id: string;
  tenant_id: string;
  title: string;
  completed: number;
  created_at: string;
};

export async function listTodos(env: Env, tenantId: string, limit = 50) {
  const result = await env.DB.prepare(
    `SELECT id, tenant_id, title, completed, created_at
     FROM todos
     WHERE tenant_id = ?
     ORDER BY created_at DESC
     LIMIT ?`
  ).bind(tenantId, limit).all<Todo>();

  return result.results;
}
```

## Insert with idempotency

```ts
export async function createTodo(env: Env, input: { tenantId: string; title: string }) {
  const id = crypto.randomUUID();
  const now = new Date().toISOString();

  await env.DB.prepare(
    `INSERT INTO todos (id, tenant_id, title, completed, created_at)
     VALUES (?, ?, ?, 0, ?)`
  ).bind(id, input.tenantId, input.title, now).run();

  return { id, createdAt: now };
}
```

## Schema template

```sql
CREATE TABLE IF NOT EXISTS todos (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  title TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_todos_tenant_created
  ON todos (tenant_id, created_at DESC);
```

## Query safety

- Always use `prepare().bind()` for user input.
- Do not pass `undefined` as a bound value; convert to `null` or omit the column.
- Model booleans as integers unless a helper converts them consistently.
- Keep SQL in small functions with typed results.
- Log query purpose and correlation ID, not raw PII-heavy statements.

## Migration workflow

- Keep migrations in version control.
- Test against a fresh local database and a database with previous migrations.
- Never deploy code that requires a migration before the migration is safely applied.
- For destructive changes, use expand/migrate/contract: add new columns/tables, backfill, update code, then remove old shape later.

## Anti-patterns

- `SELECT *` in production API responses.
- No tenant predicate in multi-tenant queries.
- Dynamic SQL string concatenation from user input.
- R2 object data duplicated into D1 as large blobs.
- Assuming D1 alone solves high-contention coordination without a Durable Object or idempotency design.