Cloudflare D1

Cloudflare D1 — managed serverless SQL database built on SQLite. Core: 10 GB per database, SQLite semantics, Time Travel (30 days), Workers/Pages integration.

Quick Start

# Create database
npx wrangler d1 create my-database

# Execute SQL
npx wrangler d1 execute my-database --remote --command="SELECT 1"

# Apply schema
npx wrangler d1 execute my-database --remote --file=./schema.sql

Binding (wrangler.jsonc)

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "my-database",
      "database_id": "<UUID>"
    }
  ]
}

TypeScript interface:

export interface Env {
  DB: D1Database;
}

Core API

Prepared Statements (recommended)

// Safe query with parameters
const { results } = await env.DB.prepare("SELECT * FROM users WHERE id = ?").bind(userId).run();

D1Database Methods

Method	Purpose	Returns
`prepare(sql)`	Create prepared statement	D1PreparedStatement
`batch([...stmts])`	Execute multiple statements atomically	D1Result[]
`exec(sql)`	Execute raw SQL (no bind)	D1ExecResult
`withSession(bookmark?)`	Create session for read replication	D1DatabaseSession

D1PreparedStatement Methods

Method	Purpose
`bind(...values)`	Bind parameters to ? placeholders
`run()`	Execute (returns D1Result)
`first(column?)`	Get first row/field
`all()`	Get all rows
`raw({columnNames?})`	Get array of arrays

D1Result Structure

{
  success: boolean,
  meta: {
    served_by: string,
    served_by_region: string,    // WEUR, ENAM, APAC...
    served_by_primary: boolean,
    duration: number,            // ms
    changes: number,
    last_row_id: number,
    rows_read: number,
    rows_written: number
  },
  results: T[]
}

Batch Operations

// Atomic execution of multiple queries
const results = await env.DB.batch([env.DB.prepare("INSERT INTO users (name) VALUES (?)").bind("Alice"), env.DB.prepare("INSERT INTO users (name) VALUES (?)").bind("Bob"), env.DB.prepare("SELECT * FROM users")]);
// results[2].results contains SELECT data

Important: Batch executes atomically — if one query fails, all roll back.

Read Replication (Sessions API)

// Use sessions for read replication
const bookmark = request.headers.get("x-d1-bookmark") ?? "first-unconstrained";
const session = env.DB.withSession(bookmark);

const { results } = await session.prepare("SELECT * FROM users").run();

// Save bookmark for next request
response.headers.set("x-d1-bookmark", session.getBookmark() ?? "");

Constraints:

first-unconstrained — any replica
first-primary — primary only

Migrations

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

# List migrations
npx wrangler d1 migrations list my-database --remote

# Apply migrations
npx wrangler d1 migrations apply my-database --remote

Files: migrations/0001_create_users_table.sql

Configuration in wrangler.jsonc:

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "my-database",
      "database_id": "<UUID>",
      "migrations_table": "d1_migrations",
      "migrations_dir": "migrations"
    }
  ]
}

Time Travel (Point-in-Time Recovery)

# Get information about restore point
npx wrangler d1 time-travel info my-database --timestamp=1699900000

# Restore database
npx wrangler d1 time-travel restore my-database --timestamp=1699900000

Workers Paid: 30 days history
Workers Free: 7 days
Restore overwrites database in place

Import/Export

# Import SQL file
npx wrangler d1 execute my-database --remote --file=./data.sql

# Export full database
npx wrangler d1 export my-database --remote --output=./backup.sql

# Export single table
npx wrangler d1 export my-database --remote --table=users --output=./users.sql

# Schema only
npx wrangler d1 export my-database --remote --output=./schema.sql --no-data

Local Development

# Local development (default)
npx wrangler dev

# Execute against local DB
npx wrangler d1 execute my-database --local --command="SELECT * FROM users"

# Work with remote DB in dev mode
# In wrangler.jsonc: "remote": true

Workers Integration

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const { pathname } = new URL(request.url);

    if (pathname === "/users") {
      const { results } = await env.DB.prepare("SELECT * FROM users LIMIT 10").run();
      return Response.json(results);
    }

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

Pages Functions Integration

// functions/api/users.ts
export async function onRequest(context) {
  const { results } = await context.env.DB.prepare("SELECT * FROM users").run();
  return Response.json(results);
}

Binding in Pages: Settings → Functions → D1 Database Bindings

JSON Queries

-- Extract value from JSON
SELECT json_extract(data, '$.name') as name FROM users;

-- JSON array expansion
SELECT value FROM json_each('[1,2,3]');

-- Validate JSON
SELECT json_valid('{"key": "value"}');

Generated Columns

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data TEXT,
  name TEXT GENERATED ALWAYS AS (json_extract(data, '$.name')) STORED
);

Foreign Keys

-- Enable foreign keys (at start of transaction)
PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

Indexes

-- Create index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Rule: Create indexes on columns used in WHERE, JOIN, ORDER BY.

Wrangler Commands Reference

Command	Description
`d1 create <name>`	Create database
`d1 delete <name>`	Delete database
`d1 list`	List databases
`d1 info <name>`	Database info
`d1 execute <name>`	Execute SQL
`d1 export <name>`	Export database
`d1 migrations create`	Create migration
`d1 migrations list`	List migrations
`d1 migrations apply`	Apply migrations
`d1 time-travel info`	Time Travel info
`d1 time-travel restore`	Restore database
`d1 insights`	Query metrics

Flags:

--local — local DB (wrangler dev)
--remote — production DB

Limits

Parameter	Paid	Free
Databases per account	50,000	10
Max database size	10 GB	500 MB
Max storage per account	1 TB	5 GB
Time Travel	30 days	7 days
Max row size	2 MB	2 MB
Max SQL statement	100 KB	100 KB
Bound parameters	100	100
Queries per invocation	1000	50

Pricing (Workers Paid)

Metric	Included	Overage
Rows read	25B/month	$0.001/M
Rows written	50M/month	$1.00/M
Storage	5 GB	$0.75/GB-mo

Free plan: 5M reads/day, 100K writes/day, 5 GB total.

Environments

{
  "env": {
    "staging": {
      "d1_databases": [
        {
          "binding": "DB",
          "database_name": "staging-db",
          "database_id": "<STAGING_UUID>"
        }
      ]
    },
    "production": {
      "d1_databases": [
        {
          "binding": "DB",
          "database_name": "production-db",
          "database_id": "<PROD_UUID>"
        }
      ]
    }
  }
}

Deploy: npx wrangler deploy --env production

Location Hints

npx wrangler d1 create my-database --location=weur
# weur/eeur - Europe, wnam/enam - North America, apac - Asia Pacific, oc - Oceania

Jurisdictions (Data Locality)

npx wrangler d1 create eu-database --jurisdiction=eu
# eu - European Union, fedramp - FedRAMP compliance

Important: Jurisdiction cannot be changed after creation.

Error Handling

try {
  const result = await env.DB.prepare("SELECT * FROM nonexistent").run();
} catch (e) {
  // e.message contains D1_ERROR
  console.error("D1 Error:", e.message);
  // Example: "D1_ERROR: no such table: nonexistent"
}

Testing with Miniflare

import { Miniflare } from "miniflare";

const mf = new Miniflare({
  d1Databases: {
    DB: "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
  },
});

const db = await mf.getD1Database("DB");
const { results } = await db.prepare("SELECT 1").run();

ORM Support

Drizzle ORM

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

const db = drizzle(env.DB);
const users = await db.select().from(usersTable);

Prisma ORM

import { PrismaD1 } from "@prisma/adapter-d1";
import { PrismaClient } from "./generated/prisma";

const adapter = new PrismaD1(env.DB);
const prisma = new PrismaClient({ adapter });

Prohibitions

DO NOT use exec() with user input — no parameterization
DO NOT rely on --local flag as default in wrangler 3.33+
DO NOT store .sqlite3 files directly — use SQL dump only
DO NOT use alpha databases (deprecated)
DO NOT exceed 2 MB per row
DO NOT run long-running transactions

References

references/binding.md — binding configuration
references/api.md — full API reference
references/migrations.md — migration system
references/time-travel.md — Point-in-Time Recovery
references/replication.md — Read Replication and Sessions API
references/pricing.md — billing and limits

Related Skills

cloudflare-workers — D1 works through Workers bindings
cloudflare-pages - Pages Functions support D1 bindings
cloudflare-r2 — Can export D1 to R2 via Workflows

cloudflare-d1