d1-database

Overview

D1 is Cloudflare's serverless SQLite database — runs at the edge alongside Workers. Zero connection overhead (it's in the same data center as your Worker), SQLite query syntax, automatic replication, and pay-per-query pricing. Perfect for read-heavy workloads, content sites, and applications where latency matters.

When to Use

• Building on Cloudflare Workers and need a database
• Read-heavy applications (blogs, content sites, APIs)
• Want SQLite simplicity with global distribution
• Serverless applications with no connection pooling headaches
• Edge-first applications where database latency matters

Instructions

Setup

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

# Add to wrangler.toml

# wrangler.toml
[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "xxxx-xxxx-xxxx"

Schema and Migrations

# Create migration
npx wrangler d1 migrations create my-database init

-- migrations/0001_init.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES users(id),
  published BOOLEAN DEFAULT FALSE,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published);

# Apply migrations
npx wrangler d1 migrations apply my-database

Queries in Workers

// src/index.ts — Cloudflare Worker with D1
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const url = new URL(request.url);

    if (url.pathname === "/api/posts" && request.method === "GET") {
      const { results } = await env.DB.prepare(
        `SELECT posts.*, users.name as author_name
         FROM posts
         JOIN users ON posts.author_id = users.id
         WHERE posts.published = TRUE
         ORDER BY posts.created_at DESC
         LIMIT ?`
      ).bind(20).all();

      return Response.json({ posts: results });
    }

    if (url.pathname === "/api/posts" && request.method === "POST") {
      const body = await request.json();
      const result = await env.DB.prepare(
        "INSERT INTO posts (title, content, author_id) VALUES (?, ?, ?) RETURNING *"
      ).bind(body.title, body.content, body.authorId).first();

      return Response.json({ post: result }, { status: 201 });
    }

    return new Response("Not found", { status: 404 });
  },
};

With Drizzle ORM

// src/db/schema.ts — Drizzle schema for D1
import { sqliteTable, text, integer } 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(),
});

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id").notNull().references(() => users.id),
  published: integer("published", { mode: "boolean" }).default(false),
});

// src/index.ts — Using Drizzle with D1
import { drizzle } from "drizzle-orm/d1";
import { eq } from "drizzle-orm";
import * as schema from "./db/schema";

export default {
  async fetch(request: Request, env: Env) {
    const db = drizzle(env.DB, { schema });

    const publishedPosts = await db.query.posts.findMany({
      where: eq(schema.posts.published, true),
      with: { author: true },
      orderBy: (posts, { desc }) => [desc(posts.id)],
      limit: 20,
    });

    return Response.json({ posts: publishedPosts });
  },
};

Examples

Example 1: Build a blog API on the edge

User prompt: "Create a blog API with Cloudflare Workers and D1 for posts and comments."

The agent will create D1 schema, migrations, CRUD endpoints in a Worker, and Drizzle ORM integration.

Example 2: User authentication with D1

User prompt: "Store user accounts in D1 with email/password auth."

The agent will create users table, password hashing with Web Crypto API, session management, and auth middleware.

Guidelines

• SQLite syntax — D1 is SQLite under the hood
• Prepared statements with .bind() — prevent SQL injection
• .first() for single row, .all() for multiple — query methods
• Migrations via Wrangler — version-controlled schema changes
• Read replicas are automatic — D1 replicates globally
• Write latency is higher — writes go to primary; reads are fast everywhere
• 5MB row limit — not for large blobs
• Free tier: 5M reads, 100K writes/day — generous for small apps
• Drizzle ORM recommended — type-safe queries with D1 adapter
• Local development — wrangler dev uses local SQLite