1# D1 API Reference
2 
3## Prepared Statements (Required for Security)
4 
5```typescript
6// ❌ NEVER: Direct string interpolation (SQL injection risk)
7const result = await env.DB.prepare(`SELECT * FROM users WHERE id = ${userId}`).all();
8 
9// ✅ CORRECT: Prepared statements with bind()
10const result = await env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(userId).all();
11 
12// Multiple parameters
13const result = await env.DB.prepare('SELECT * FROM users WHERE email = ? AND active = ?').bind(email, true).all();
14```
15 
16## Query Execution Methods
17 
18```typescript
19// .all() - Returns all rows
20const { results, success, meta } = await env.DB.prepare('SELECT * FROM users WHERE active = ?').bind(true).all();
21// results: Array of row objects; success: boolean
22// meta: { duration: number, rows_read: number, rows_written: number }
23 
24// .first() - Returns first row or null
25const user = await env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(userId).first();
26 
27// .first(columnName) - Returns single column value
28const email = await env.DB.prepare('SELECT email FROM users WHERE id = ?').bind(userId).first('email');
29// Returns string | number | null
30 
31// .run() - For INSERT/UPDATE/DELETE (no row data returned)
32const result = await env.DB.prepare('UPDATE users SET last_login = ? WHERE id = ?').bind(Date.now(), userId).run();
33// result.meta: { duration, rows_read, rows_written, last_row_id, changes }
34 
35// .raw() - Returns array of arrays (efficient for large datasets)
36const rawResults = await env.DB.prepare('SELECT id, name FROM users').raw();
37// [[1, 'Alice'], [2, 'Bob']]
38```
39 
40## Batch Operations
41 
42```typescript
43// Execute multiple queries in single round trip (atomic transaction)
44const results = await env.DB.batch([
45  env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(1),
46  env.DB.prepare('SELECT * FROM posts WHERE author_id = ?').bind(1),
47  env.DB.prepare('UPDATE users SET last_access = ? WHERE id = ?').bind(Date.now(), 1)
48]);
49// results is array: [result1, result2, result3]
50 
51// Batch with same prepared statement, different params
52const userIds = [1, 2, 3];
53const stmt = env.DB.prepare('SELECT * FROM users WHERE id = ?');
54const results = await env.DB.batch(userIds.map(id => stmt.bind(id)));
55```
56 
57## Transactions (via batch)
58 
59```typescript
60// D1 executes batch() as atomic transaction - all succeed or all fail
61const results = await env.DB.batch([
62  env.DB.prepare('INSERT INTO accounts (id, balance) VALUES (?, ?)').bind(1, 100),
63  env.DB.prepare('INSERT INTO accounts (id, balance) VALUES (?, ?)').bind(2, 200),
64  env.DB.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?').bind(50, 1),
65  env.DB.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?').bind(50, 2)
66]);
67```
68 
69## Sessions API (Paid Plans)
70 
71Long-running sessions for operations exceeding 30s timeout (up to 15 min).
72 
73```typescript
74const session = env.DB.withSession({ timeout: 600 }); // 10 min (1-900s)
75try {
76  await session.prepare('CREATE INDEX idx_large ON big_table(column)').run();
77  await session.prepare('ANALYZE').run();
78} finally {
79  session.close(); // CRITICAL: always close to prevent leaks
80}
81```
82 
83**Use cases**: Migrations, ANALYZE, large index creation, bulk transformations
84 
85## Read Replication (Paid Plans)
86 
87Routes queries to nearest replica for lower latency. Writes always go to primary.
88 
89```typescript
90interface Env {
91  DB: D1Database;          // Primary (writes)
92  DB_REPLICA: D1Database;  // Replica (reads)
93}
94 
95// Reads: use replica
96const user = await env.DB_REPLICA.prepare('SELECT * FROM users WHERE id = ?').bind(userId).first();
97 
98// Writes: use primary
99await env.DB.prepare('UPDATE users SET last_login = ? WHERE id = ?').bind(Date.now(), userId).run();
100 
101// Read-after-write: use primary for consistency (replication lag <100ms-2s)
102await env.DB.prepare('INSERT INTO posts (title) VALUES (?)').bind(title).run();
103const post = await env.DB.prepare('SELECT * FROM posts WHERE title = ?').bind(title).first(); // Primary
104```
105 
106## Error Handling
107 
108```typescript
109async function getUser(userId: number, env: Env): Promise<Response> {
110  try {
111    const result = await env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(userId).all();
112    if (!result.success) return new Response('Database error', { status: 500 });
113    if (result.results.length === 0) return new Response('User not found', { status: 404 });
114    return Response.json(result.results[0]);
115  } catch (error) {
116    return new Response('Internal error', { status: 500 });
117  }
118}
119 
120// Constraint violations
121try {
122  await env.DB.prepare('INSERT INTO users (email, name) VALUES (?, ?)').bind(email, name).run();
123} catch (error) {
124  if (error.message?.includes('UNIQUE constraint failed')) return new Response('Email exists', { status: 409 });
125  throw error;
126}
127```
128 
129## REST API (HTTP) Access
130 
131Access D1 from external services (non-Worker contexts) using Cloudflare API.
132 
133```typescript
134// Single query
135const response = await fetch(
136  `https://api.cloudflare.com/client/v4/accounts/${ACCOUNT_ID}/d1/database/${DATABASE_ID}/query`,
137  {
138    method: 'POST',
139    headers: {
140      'Authorization': `Bearer ${CLOUDFLARE_API_TOKEN}`,
141      'Content-Type': 'application/json'
142    },
143    body: JSON.stringify({
144      sql: 'SELECT * FROM users WHERE id = ?',
145      params: [userId]
146    })
147  }
148);
149 
150const { result, success, errors } = await response.json();
151// result: [{ results: [...], success: true, meta: {...} }]
152 
153// Batch queries via HTTP
154const response = await fetch(
155  `https://api.cloudflare.com/client/v4/accounts/${ACCOUNT_ID}/d1/database/${DATABASE_ID}/query`,
156  {
157    method: 'POST',
158    headers: {
159      'Authorization': `Bearer ${CLOUDFLARE_API_TOKEN}`,
160      'Content-Type': 'application/json'
161    },
162    body: JSON.stringify([
163      { sql: 'SELECT * FROM users WHERE id = ?', params: [1] },
164      { sql: 'SELECT * FROM posts WHERE author_id = ?', params: [1] }
165    ])
166  }
167);
168```
169 
170**Use cases**: Server-side scripts, CI/CD migrations, administrative tools, non-Worker integrations
171 
172## Testing & Debugging
173 
174```typescript
175// Vitest with unstable_dev
176import { unstable_dev } from 'wrangler';
177describe('D1', () => {
178  let worker: Awaited<ReturnType<typeof unstable_dev>>;
179  beforeAll(async () => { worker = await unstable_dev('src/index.ts'); });
180  afterAll(async () => { await worker.stop(); });
181  it('queries users', async () => { expect((await worker.fetch('/users')).status).toBe(200); });
182});
183 
184// Debug query performance
185const result = await env.DB.prepare('SELECT * FROM users').all();
186console.log('Duration:', result.meta.duration, 'ms');
187 
188// Query plan analysis
189const plan = await env.DB.prepare('EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?').bind(email).all();
190```
191 
192```bash
193# Inspect local database
194sqlite3 .wrangler/state/v3/d1/<database-id>.sqlite
195.tables; .schema users; PRAGMA table_info(users);
196```
197