1# D1 Patterns & Best Practices
2 
3## Pagination
4 
5```typescript
6async function getUsers({ page, pageSize }: { page: number; pageSize: number }, env: Env) {
7  const offset = (page - 1) * pageSize;
8  const [countResult, dataResult] = await env.DB.batch([
9    env.DB.prepare('SELECT COUNT(*) as total FROM users'),
10    env.DB.prepare('SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?').bind(pageSize, offset)
11  ]);
12  return { data: dataResult.results, total: countResult.results[0].total, page, pageSize, totalPages: Math.ceil(countResult.results[0].total / pageSize) };
13}
14```
15 
16## Conditional Queries
17 
18```typescript
19async function searchUsers(filters: { name?: string; email?: string; active?: boolean }, env: Env) {
20  const conditions: string[] = [], params: (string | number | boolean | null)[] = [];
21  if (filters.name) { conditions.push('name LIKE ?'); params.push(`%${filters.name}%`); }
22  if (filters.email) { conditions.push('email = ?'); params.push(filters.email); }
23  if (filters.active !== undefined) { conditions.push('active = ?'); params.push(filters.active ? 1 : 0); }
24  const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
25  return await env.DB.prepare(`SELECT * FROM users ${whereClause}`).bind(...params).all();
26}
27```
28 
29## Bulk Insert
30 
31```typescript
32async function bulkInsertUsers(users: Array<{ name: string; email: string }>, env: Env) {
33  const stmt = env.DB.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
34  const batch = users.map(user => stmt.bind(user.name, user.email));
35  return await env.DB.batch(batch);
36}
37```
38 
39## Caching with KV
40 
41```typescript
42async function getCachedUser(userId: number, env: { DB: D1Database; CACHE: KVNamespace }) {
43  const cacheKey = `user:${userId}`;
44  const cached = await env.CACHE?.get(cacheKey, 'json');
45  if (cached) return cached;
46  const user = await env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(userId).first();
47  if (user) await env.CACHE?.put(cacheKey, JSON.stringify(user), { expirationTtl: 300 });
48  return user;
49}
50```
51 
52## Query Optimization
53 
54```typescript
55// ✅ Use indexes in WHERE clauses
56const users = await env.DB.prepare('SELECT * FROM users WHERE email = ?').bind(email).all();
57 
58// ✅ Limit result sets
59const recentPosts = await env.DB.prepare('SELECT * FROM posts ORDER BY created_at DESC LIMIT 100').all();
60 
61// ✅ Use batch() for multiple independent queries
62const [user, posts, comments] = await env.DB.batch([
63  env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(userId),
64  env.DB.prepare('SELECT * FROM posts WHERE user_id = ?').bind(userId),
65  env.DB.prepare('SELECT * FROM comments WHERE user_id = ?').bind(userId)
66]);
67 
68// ❌ Avoid N+1 queries
69for (const post of posts) {
70  const author = await env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(post.user_id).first(); // Bad: multiple round trips
71}
72 
73// ✅ Use JOINs instead
74const postsWithAuthors = await env.DB.prepare(`
75  SELECT posts.*, users.name as author_name
76  FROM posts
77  JOIN users ON posts.user_id = users.id
78`).all();
79```
80 
81## Multi-Tenant SaaS
82 
83```typescript
84// Each tenant gets own database
85export default {
86  async fetch(request: Request, env: { [key: `TENANT_${string}`]: D1Database }) {
87    const tenantId = request.headers.get('X-Tenant-ID');
88    const data = await env[`TENANT_${tenantId}`].prepare('SELECT * FROM records').all();
89    return Response.json(data.results);
90  }
91}
92```
93 
94## Session Storage
95 
96```typescript
97async function createSession(userId: number, token: string, env: Env) {
98  const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000).toISOString();
99  return await env.DB.prepare('INSERT INTO sessions (user_id, token, expires_at) VALUES (?, ?, ?)').bind(userId, token, expiresAt).run();
100}
101 
102async function validateSession(token: string, env: Env) {
103  return await env.DB.prepare('SELECT s.*, u.email FROM sessions s JOIN users u ON s.user_id = u.id WHERE s.token = ? AND s.expires_at > CURRENT_TIMESTAMP').bind(token).first();
104}
105```
106 
107## Analytics/Events
108 
109```typescript
110async function logEvent(event: { type: string; userId?: number; metadata: object }, env: Env) {
111  return await env.DB.prepare('INSERT INTO events (type, user_id, metadata) VALUES (?, ?, ?)').bind(event.type, event.userId || null, JSON.stringify(event.metadata)).run();
112}
113 
114async function getEventStats(startDate: string, endDate: string, env: Env) {
115  return await env.DB.prepare('SELECT type, COUNT(*) as count FROM events WHERE timestamp BETWEEN ? AND ? GROUP BY type ORDER BY count DESC').bind(startDate, endDate).all();
116}
117```
118 
119## Read Replication Pattern (Paid Plans)
120 
121```typescript
122interface Env { DB: D1Database; DB_REPLICA: D1Database; }
123 
124export default {
125  async fetch(request: Request, env: Env) {
126    if (request.method === 'GET') {
127      // Reads: use replica for lower latency
128      const users = await env.DB_REPLICA.prepare('SELECT * FROM users WHERE active = 1').all();
129      return Response.json(users.results);
130    }
131    
132    if (request.method === 'POST') {
133      const { name, email } = await request.json();
134      const result = await env.DB.prepare('INSERT INTO users (name, email) VALUES (?, ?)').bind(name, email).run();
135      
136      // Read-after-write: use primary for consistency (replication lag <100ms-2s)
137      const user = await env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(result.meta.last_row_id).first();
138      return Response.json(user, { status: 201 });
139    }
140  }
141}
142```
143 
144**Use replicas for**: Analytics dashboards, search results, public queries (eventual consistency OK)  
145**Use primary for**: Read-after-write, financial transactions, authentication (consistency required)
146 
147## Sessions API Pattern (Paid Plans)
148 
149```typescript
150// Migration with long-running session (up to 15 min)
151async function runMigration(env: Env) {
152  const session = env.DB.withSession({ timeout: 600 }); // 10 min
153  try {
154    await session.prepare('CREATE INDEX idx_users_email ON users(email)').run();
155    await session.prepare('CREATE INDEX idx_posts_user ON posts(user_id)').run();
156    await session.prepare('ANALYZE').run();
157  } finally {
158    session.close(); // Always close to prevent leaks
159  }
160}
161 
162// Bulk transformation with batching
163async function transformLargeDataset(env: Env) {
164  const session = env.DB.withSession({ timeout: 900 }); // 15 min max
165  try {
166    const BATCH_SIZE = 1000;
167    let offset = 0;
168    while (true) {
169      const rows = await session.prepare('SELECT id, data FROM legacy LIMIT ? OFFSET ?').bind(BATCH_SIZE, offset).all();
170      if (rows.results.length === 0) break;
171      const updates = rows.results.map(row => 
172        session.prepare('UPDATE legacy SET new_data = ? WHERE id = ?').bind(transform(row.data), row.id)
173      );
174      await session.batch(updates);
175      offset += BATCH_SIZE;
176    }
177  } finally { session.close(); }
178}
179```
180 
181## Time Travel & Backups
182 
183```bash
184wrangler d1 time-travel restore <db-name> --timestamp="2024-01-15T14:30:00Z"  # Point-in-time
185wrangler d1 time-travel info <db-name>  # List restore points (7 days free, 30 days paid)
186wrangler d1 export <db-name> --remote --output=./backup.sql  # Full export
187wrangler d1 export <db-name> --remote --no-schema --output=./data.sql  # Data only
188wrangler d1 execute <db-name> --remote --file=./backup.sql  # Import
189```
190