cf-d1

name	cf-d1
description	Generate D1 SQLite database schemas, queries, and edge-native patterns. Use when the user wants to create, query, or manage Cloudflare D1 databases with Workers or Pages.
argument-hint	[operation]
disable-model-invocation	true
allowed-tools	Read, Write, Edit, Glob, Grep, Bash(npx wrangler *), Bash(wrangler *), Bash(npm *)
user-invocable	true

Instructions

You are a Cloudflare D1 expert. Generate production-ready D1 database schemas, queries, migrations, and integration patterns.

Step 1: Gather requirements

Determine from user input or $ARGUMENTS:

• Operation: schema design, query writing, migration creation, seed data
• Data model: tables, relationships, constraints, indexes
• Access pattern: read-heavy, write-heavy, mixed
• Integration: raw D1 API, Drizzle ORM, Prisma with D1 adapter
• Scale: read replication needs, location hints

Step 2: Create database and configure binding

# Create a D1 database
npx wrangler d1 create my-app-db

# Output includes database_id to add to wrangler.toml

# List databases
npx wrangler d1 list

# Get database info
npx wrangler d1 info my-app-db

wrangler.toml binding:

name = "my-worker"
main = "src/index.ts"
compatibility_date = "2024-12-01"

[[d1_databases]]
binding = "DB"
database_name = "my-app-db"
database_id = "xxxx-xxxx-xxxx-xxxx"

# Multiple databases
[[d1_databases]]
binding = "ANALYTICS_DB"
database_name = "analytics-db"
database_id = "yyyy-yyyy-yyyy-yyyy"

Step 3: Generate schema with migrations

Create migration files:

# Create a new migration
npx wrangler d1 migrations create my-app-db init-schema

# This creates: migrations/0001_init-schema.sql

Schema design (migrations/0001_init-schema.sql):

-- Enable WAL mode for better concurrent read performance
PRAGMA journal_mode = WAL;

-- Users table
CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  avatar_url TEXT,
  role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role) WHERE is_active = 1;

-- Posts table with foreign key
CREATE TABLE IF NOT EXISTS posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  content TEXT NOT NULL,
  excerpt TEXT,
  author_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  published_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_status ON posts(status, published_at DESC);
CREATE INDEX idx_posts_slug ON posts(slug);

-- Tags table (many-to-many)
CREATE TABLE IF NOT EXISTS tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- Comments table
CREATE TABLE IF NOT EXISTS comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_comments_post ON comments(post_id, created_at DESC);
CREATE INDEX idx_comments_user ON comments(user_id);

-- Trigger to update updated_at timestamp
CREATE TRIGGER update_users_timestamp
  AFTER UPDATE ON users
  FOR EACH ROW
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = OLD.id;
END;

CREATE TRIGGER update_posts_timestamp
  AFTER UPDATE ON posts
  FOR EACH ROW
BEGIN
  UPDATE posts SET updated_at = datetime('now') WHERE id = OLD.id;
END;

Apply migrations:

# Apply migrations locally (for development)
npx wrangler d1 migrations apply my-app-db --local

# Apply migrations to remote (production)
npx wrangler d1 migrations apply my-app-db --remote

# List applied migrations
npx wrangler d1 migrations list my-app-db

Step 4: Generate D1 client API queries

interface Env {
  DB: D1Database;
}

// --- CRUD Operations ---

// Create (INSERT)
async function createUser(db: D1Database, user: { email: string; name: string; passwordHash: string }) {
  const result = await db
    .prepare("INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?) RETURNING *")
    .bind(user.email, user.name, user.passwordHash)
    .first();
  return result;
}

// Read single (SELECT ... first())
async function getUserById(db: D1Database, id: string) {
  return db.prepare("SELECT id, email, name, role, created_at FROM users WHERE id = ?").bind(id).first();
}

// Read multiple (SELECT ... all())
async function getPublishedPosts(db: D1Database, limit = 20, offset = 0) {
  const { results } = await db
    .prepare(`
      SELECT p.id, p.title, p.slug, p.excerpt, p.published_at,
             u.name AS author_name, u.avatar_url AS author_avatar
      FROM posts p
      JOIN users u ON p.author_id = u.id
      WHERE p.status = 'published'
      ORDER BY p.published_at DESC
      LIMIT ? OFFSET ?
    `)
    .bind(limit, offset)
    .all();
  return results;
}

// Update
async function updatePost(db: D1Database, id: number, data: { title?: string; content?: string; status?: string }) {
  const fields: string[] = [];
  const values: unknown[] = [];

  if (data.title) { fields.push("title = ?"); values.push(data.title); }
  if (data.content) { fields.push("content = ?"); values.push(data.content); }
  if (data.status) {
    fields.push("status = ?");
    values.push(data.status);
    if (data.status === "published") {
      fields.push("published_at = datetime('now')");
    }
  }

  values.push(id);

  return db
    .prepare(`UPDATE posts SET ${fields.join(", ")} WHERE id = ? RETURNING *`)
    .bind(...values)
    .first();
}

// Delete
async function deletePost(db: D1Database, id: number) {
  return db.prepare("DELETE FROM posts WHERE id = ?").bind(id).run();
}

Step 5: Batch and transaction operations

// Batch operations (multiple statements in one round-trip)
async function createPostWithTags(
  db: D1Database,
  post: { title: string; slug: string; content: string; authorId: string },
  tagIds: number[]
) {
  const statements: D1PreparedStatement[] = [
    db.prepare(
      "INSERT INTO posts (title, slug, content, author_id, status) VALUES (?, ?, ?, ?, 'draft') RETURNING id"
    ).bind(post.title, post.slug, post.content, post.authorId),
  ];

  // Note: batch() executes statements sequentially but in a single transaction
  // The post ID from the first statement is not available to subsequent statements
  // Use a known ID pattern or two-step approach for dependent inserts

  const results = await db.batch(statements);
  const postId = (results[0].results?.[0] as { id: number })?.id;

  if (postId && tagIds.length > 0) {
    const tagStatements = tagIds.map((tagId) =>
      db.prepare("INSERT INTO post_tags (post_id, tag_id) VALUES (?, ?)").bind(postId, tagId)
    );
    await db.batch(tagStatements);
  }

  return postId;
}

// Exec for raw SQL (useful for seeds and migrations)
async function seedDatabase(db: D1Database) {
  await db.exec(`
    INSERT INTO tags (name, slug) VALUES ('JavaScript', 'javascript');
    INSERT INTO tags (name, slug) VALUES ('TypeScript', 'typescript');
    INSERT INTO tags (name, slug) VALUES ('Cloudflare', 'cloudflare');
    INSERT INTO tags (name, slug) VALUES ('Workers', 'workers');
  `);
}

Step 6: Full-text search with FTS5

-- migrations/0002_add-fts.sql

-- Create FTS5 virtual table for posts
CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(
  title,
  content,
  content='posts',
  content_rowid='id',
  tokenize='porter unicode61'
);

-- Populate FTS index from existing data
INSERT INTO posts_fts(rowid, title, content)
  SELECT id, title, content FROM posts;

-- Triggers to keep FTS in sync
CREATE TRIGGER posts_fts_insert AFTER INSERT ON posts BEGIN
  INSERT INTO posts_fts(rowid, title, content) VALUES (NEW.id, NEW.title, NEW.content);
END;

CREATE TRIGGER posts_fts_delete AFTER DELETE ON posts BEGIN
  INSERT INTO posts_fts(posts_fts, rowid, title, content)
    VALUES('delete', OLD.id, OLD.title, OLD.content);
END;

CREATE TRIGGER posts_fts_update AFTER UPDATE ON posts BEGIN
  INSERT INTO posts_fts(posts_fts, rowid, title, content)
    VALUES('delete', OLD.id, OLD.title, OLD.content);
  INSERT INTO posts_fts(rowid, title, content)
    VALUES (NEW.id, NEW.title, NEW.content);
END;

// Full-text search query
async function searchPosts(db: D1Database, query: string, limit = 20) {
  const { results } = await db
    .prepare(`
      SELECT p.id, p.title, p.slug, p.excerpt,
             highlight(posts_fts, 0, '<mark>', '</mark>') AS title_highlighted,
             snippet(posts_fts, 1, '<mark>', '</mark>', '...', 32) AS content_snippet,
             rank
      FROM posts_fts
      JOIN posts p ON posts_fts.rowid = p.id
      WHERE posts_fts MATCH ?
      ORDER BY rank
      LIMIT ?
    `)
    .bind(query, limit)
    .all();
  return results;
}

Step 7: JSON support

// Store and query JSON data
async function createSetting(db: D1Database, key: string, value: object) {
  await db
    .prepare("INSERT OR REPLACE INTO settings (key, value) VALUES (?, json(?))")
    .bind(key, JSON.stringify(value))
    .run();
}

async function getSettingField(db: D1Database, key: string, field: string) {
  return db
    .prepare("SELECT json_extract(value, ?) AS field_value FROM settings WHERE key = ?")
    .bind(`$.${field}`, key)
    .first();
}

// Query JSON arrays
async function getUsersByPreference(db: D1Database, preference: string) {
  const { results } = await db
    .prepare(`
      SELECT u.id, u.name
      FROM users u, json_each(u.preferences, '$.tags') AS t
      WHERE t.value = ?
    `)
    .bind(preference)
    .all();
  return results;
}

Step 8: Drizzle ORM integration

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "sqlite",
});

// src/db/schema.ts
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";

export const users = sqliteTable("users", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  passwordHash: text("password_hash").notNull(),
  role: text("role", { enum: ["user", "admin", "moderator"] }).notNull().default("user"),
  isActive: integer("is_active", { mode: "boolean" }).notNull().default(true),
  createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
});

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  slug: text("slug").notNull().unique(),
  content: text("content").notNull(),
  authorId: text("author_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  status: text("status", { enum: ["draft", "published", "archived"] }).notNull().default("draft"),
  publishedAt: text("published_at"),
  createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
});

// src/db/index.ts
import { drizzle } from "drizzle-orm/d1";
import * as schema from "./schema";

export function createDb(d1: D1Database) {
  return drizzle(d1, { schema });
}

// Usage in Worker
import { eq, desc } from "drizzle-orm";
import { createDb } from "./db";
import { users, posts } from "./db/schema";

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const db = createDb(env.DB);

    // Select with joins
    const publishedPosts = await db
      .select({
        id: posts.id,
        title: posts.title,
        authorName: users.name,
      })
      .from(posts)
      .innerJoin(users, eq(posts.authorId, users.id))
      .where(eq(posts.status, "published"))
      .orderBy(desc(posts.publishedAt))
      .limit(20);

    return Response.json(publishedPosts);
  },
};

Step 9: Query performance optimization

// Use EXPLAIN QUERY PLAN to analyze queries
async function analyzeQuery(db: D1Database, sql: string) {
  const { results } = await db.prepare(`EXPLAIN QUERY PLAN ${sql}`).all();
  return results;
}

// Efficient pagination with cursor-based approach (avoid OFFSET for large datasets)
async function getPostsCursor(db: D1Database, cursor?: string, limit = 20) {
  let query = `
    SELECT id, title, slug, published_at
    FROM posts
    WHERE status = 'published'
  `;
  const params: unknown[] = [];

  if (cursor) {
    query += " AND published_at < ?";
    params.push(cursor);
  }

  query += " ORDER BY published_at DESC LIMIT ?";
  params.push(limit + 1); // Fetch one extra to determine if there are more

  const stmt = db.prepare(query);
  const { results } = await stmt.bind(...params).all();

  const hasMore = results.length > limit;
  const items = results.slice(0, limit);
  const nextCursor = hasMore ? (items[items.length - 1] as { published_at: string }).published_at : null;

  return { items, nextCursor, hasMore };
}

Step 10: Local development and backups

# Execute SQL against local D1
npx wrangler d1 execute my-app-db --local --command "SELECT * FROM users LIMIT 5"

# Execute SQL against remote D1
npx wrangler d1 execute my-app-db --remote --command "SELECT COUNT(*) FROM users"

# Execute SQL from a file
npx wrangler d1 execute my-app-db --local --file ./seed.sql

# Export database (backup)
npx wrangler d1 export my-app-db --remote --output ./backup.sql

# Time travel (point-in-time recovery)
# Available via Cloudflare Dashboard: D1 -> Database -> Time Travel
# Restore to any point within the last 30 days (paid plan)

Best practices

• Use parameterized queries (.bind()) to prevent SQL injection -- never concatenate user input
• Create indexes for columns used in WHERE, JOIN, and ORDER BY clauses
• Use batch() for multiple related operations to reduce round-trips and ensure atomicity
• Use cursor-based pagination instead of OFFSET for large datasets
• Use first() instead of all() when expecting a single row
• Store dates as TEXT in ISO 8601 format using datetime('now') for SQLite compatibility
• Use FTS5 for text search rather than LIKE '%term%' (which cannot use indexes)
• Set location hints for databases that are primarily accessed from specific regions
• Use Drizzle ORM for type-safe queries in larger applications
• Keep migrations small and incremental (one concern per migration file)

Anti-patterns to avoid

• Do NOT use exec() for user-provided input (no parameter binding, SQL injection risk)
• Do NOT create too many indexes (each index increases write latency and storage)
• Do NOT use SELECT * in production queries (specify needed columns explicitly)
• Do NOT use D1 for high-write-throughput workloads (SQLite is single-writer)
• Do NOT store large binary blobs in D1 (use R2 and store the key/URL in D1)
• Do NOT skip migrations and modify the schema manually
• Do NOT use OFFSET for deep pagination (performance degrades linearly with offset value)
• Do NOT ignore meta.changes and meta.duration in query results (useful for monitoring)

Cost optimization

• Free tier: 5 million rows read/day, 100,000 rows written/day, 5GB storage
• Paid plan: $0.001 per million rows read, $1.00 per million rows written
• Optimize queries to read fewer rows (use indexes, limit result sets)
• Use batch() to combine multiple reads into a single call (counts as one billable operation)
• Cache frequently-read data in KV to reduce D1 read operations
• Use read replicas for read-heavy workloads to distribute load
• Monitor row reads/writes in the Cloudflare Dashboard under D1 Analytics
• Avoid full table scans by ensuring queries hit indexes (use EXPLAIN QUERY PLAN)