1# D1 Configuration
2 
3## wrangler.jsonc Setup
4 
5```jsonc
6{
7  "name": "your-worker-name",
8  "main": "src/index.ts",
9  "compatibility_date": "2025-01-01", // Use current date for new projects
10  "d1_databases": [
11    {
12      "binding": "DB",                    // Env variable name
13      "database_name": "your-db-name",    // Human-readable name
14      "database_id": "your-database-id",  // UUID from dashboard/CLI
15      "migrations_dir": "migrations"      // Optional: default is "migrations"
16    },
17    // Read replica (paid plans only)
18    {
19      "binding": "DB_REPLICA",
20      "database_name": "your-db-name",
21      "database_id": "your-database-id"   // Same ID, different binding
22    },
23    // Multiple databases
24    {
25      "binding": "ANALYTICS_DB",
26      "database_name": "analytics-db",
27      "database_id": "yyy-yyy-yyy"
28    }
29  ]
30}
31```
32 
33## TypeScript Types
34 
35```typescript
36interface Env { DB: D1Database; ANALYTICS_DB?: D1Database; }
37 
38export default {
39  async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
40    const result = await env.DB.prepare('SELECT * FROM users').all();
41    return Response.json(result.results);
42  }
43}
44```
45 
46## Migrations
47 
48File structure: `migrations/0001_initial_schema.sql`, `0002_add_posts.sql`, etc.
49 
50### Example Migration
51 
52```sql
53-- migrations/0001_initial_schema.sql
54CREATE TABLE IF NOT EXISTS users (
55  id INTEGER PRIMARY KEY AUTOINCREMENT,
56  email TEXT UNIQUE NOT NULL,
57  name TEXT NOT NULL,
58  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
59  updated_at TEXT DEFAULT CURRENT_TIMESTAMP
60);
61 
62CREATE INDEX idx_users_email ON users(email);
63 
64CREATE TABLE IF NOT EXISTS posts (
65  id INTEGER PRIMARY KEY AUTOINCREMENT,
66  user_id INTEGER NOT NULL,
67  title TEXT NOT NULL,
68  content TEXT,
69  published BOOLEAN DEFAULT 0,
70  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
71  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
72);
73 
74CREATE INDEX idx_posts_user_id ON posts(user_id);
75CREATE INDEX idx_posts_published ON posts(published);
76```
77 
78### Running Migrations
79 
80```bash
81# Create new migration file
82wrangler d1 migrations create <db-name> add_users_table
83# Creates: migrations/0001_add_users_table.sql
84 
85# Apply migrations
86wrangler d1 migrations apply <db-name> --local     # Apply to local DB
87wrangler d1 migrations apply <db-name> --remote    # Apply to production DB
88 
89# List applied migrations
90wrangler d1 migrations list <db-name> --remote
91 
92# Direct SQL execution (bypasses migration tracking)
93wrangler d1 execute <db-name> --remote --command="SELECT * FROM users"
94wrangler d1 execute <db-name> --local --file=./schema.sql
95```
96 
97**Migration tracking**: Wrangler creates `d1_migrations` table automatically to track applied migrations
98 
99## Indexing Strategy
100 
101```sql
102-- Index frequently queried columns
103CREATE INDEX idx_users_email ON users(email);
104 
105-- Composite indexes for multi-column queries
106CREATE INDEX idx_posts_user_published ON posts(user_id, published);
107 
108-- Covering indexes (include queried columns)
109CREATE INDEX idx_users_email_name ON users(email, name);
110 
111-- Partial indexes for filtered queries
112CREATE INDEX idx_active_users ON users(email) WHERE active = 1;
113 
114-- Check if query uses index
115EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?;
116```
117 
118## Drizzle ORM
119 
120```typescript
121// drizzle.config.ts
122export default {
123  schema: './src/schema.ts', out: './migrations', dialect: 'sqlite', driver: 'd1-http',
124  dbCredentials: { accountId: process.env.CLOUDFLARE_ACCOUNT_ID!, databaseId: process.env.D1_DATABASE_ID!, token: process.env.CLOUDFLARE_API_TOKEN! }
125} satisfies Config;
126 
127// schema.ts
128import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
129export const users = sqliteTable('users', {
130  id: integer('id').primaryKey({ autoIncrement: true }),
131  email: text('email').notNull().unique(),
132  name: text('name').notNull()
133});
134 
135// worker.ts
136import { drizzle } from 'drizzle-orm/d1';
137import { users } from './schema';
138export default {
139  async fetch(request: Request, env: Env) {
140    const db = drizzle(env.DB);
141    return Response.json(await db.select().from(users));
142  }
143}
144```
145 
146## Import & Export
147 
148```bash
149# Export full database (schema + data)
150wrangler d1 export <db-name> --remote --output=./backup.sql
151 
152# Export data only (no schema)
153wrangler d1 export <db-name> --remote --no-schema --output=./data-only.sql
154 
155# Export with foreign key constraints preserved
156# (Default: foreign keys are disabled during export for import compatibility)
157 
158# Import SQL file
159wrangler d1 execute <db-name> --remote --file=./backup.sql
160 
161# Limitations
162# - BLOB data may not export correctly (use R2 for binary files)
163# - Very large exports (>1GB) may timeout (split into chunks)
164# - Import is NOT atomic (use batch() for transactional imports in Workers)
165```
166 
167## Plan Tiers
168 
169| Feature | Free (Workers Free) | Paid (Workers Paid) |
170|---------|---------------------|---------------------|
171| Rows read | 5 million / day | First 25 billion / month included |
172| Rows written | 100,000 / day | First 50 million / month included |
173| Storage | 5 GB (total) | First 5 GB included |
174| Database size | 500 MB | 10 GB |
175| Batch size | 1,000 statements | 10,000 statements |
176| Time Travel | 7 days | 30 days |
177| Read replicas | ❌ | ✅ |
178| Sessions API | ❌ | ✅ (up to 15 min) |
179| Pricing | Free | $5/mo + usage |
180 
181**Usage pricing** (paid plans, beyond included allowances): $0.001 per million rows read + $1.00 per million rows written + $0.75/GB-mo storage
182 
183## Local Development
184 
185```bash
186wrangler dev --persist-to=./.wrangler/state  # Persist across restarts
187# Local DB: .wrangler/state/v3/d1/<database-id>.sqlite
188sqlite3 .wrangler/state/v3/d1/<database-id>.sqlite  # Inspect
189 
190# Local dev uses free tier limits by default
191```
192