cloudflare-d1

What this skill does

# Cloudflare D1 Database

**Status**: Production Ready ✅ | **Last Verified**: 2025-01-15

## Table of Contents
1. [What Is D1?](#what-is-d1)
2. [Quick Start](#quick-start-5-minutes)
3. [Critical Rules](#critical-rules)
4. [D1 API Methods](#d1-api-methods)
5. [Top 5 Use Cases](#top-5-use-cases)
6. [Migrations Best Practices](#migrations-best-practices)
7. [Common Patterns](#common-patterns)
8. [SQLite Type Affinity](#sqlite-type-affinity)
9. [Top 5 Errors Prevented](#top-5-errors-prevented)

---

## What Is D1?

Cloudflare D1 is **serverless SQLite** on the edge:
- SQL database without servers
- Global distribution
- Zero cold starts
- Standard SQLite syntax
- Read replication for global performance

---

## 🆕 New in 2025

D1 received major updates throughout 2025:

### Performance (January 2025)
- **40-60% latency reduction** globally (P50 query times)
- Optimized SQLite engine for edge execution
- Reduced cold start impact for databases <100 MB

### Reliability (September 2025)
- **Automatic query retries**: Read queries retry up to 2x on transient failures
- Transparent to application code (logged in `wrangler tail`)

### Scalability (April 2025)
- **Read Replication (Public Beta)**: Deploy read replicas globally
- Up to 2x read throughput for read-heavy workloads
- Sessions API for read-write separation

### Compliance (November 2025)
- **Data Localization**: Specify EU/US jurisdiction for GDPR/data sovereignty
- Configure via `--jurisdiction` flag or wrangler.jsonc

### ⚠️ Breaking Change (February 10, 2025)
- **Free tier hard limits enforced**: 10 DBs, 500 MB each, 50 queries/invocation
- Exceeding limits = 429 errors (previously warnings only)
- **Action**: Review usage with `wrangler d1 list` and upgrade if needed

**Full details**: Load `references/2025-features.md`

---

## Quick Start (5 Minutes)

### 1. Create Database

```bash
bunx wrangler d1 create my-database
```

Save the `database_id` from output!

### 2. Configure Binding

Add to `wrangler.jsonc`:

```jsonc
{
  "name": "my-worker",
  "main": "src/index.ts",
  "compatibility_date": "2025-10-11",
  "d1_databases": [
    {
      "binding": "DB",                    // env.DB
      "database_name": "my-database",
      "database_id": "<UUID>",
      "preview_database_id": "local-db"
    }
  ]
}
```

### 3. Create Migration

```bash
bunx wrangler d1 migrations create my-database create_users
```

Edit `migrations/0001_create_users.sql`:

```sql
CREATE TABLE IF NOT EXISTS users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

PRAGMA optimize;
```

### 4. Apply Migration

```bash
# Local
bunx wrangler d1 migrations apply my-database --local

# Production
bunx wrangler d1 migrations apply my-database --remote
```

### 5. Query from Worker

```typescript
import { Hono } from 'hono';

type Bindings = {
  DB: D1Database;
};

const app = new Hono<{ Bindings: Bindings }>();

app.get('/users/:email', async (c) => {
  const { results } = await c.env.DB.prepare(
    'SELECT * FROM users WHERE email = ?'
  )
    .bind(c.req.param('email'))
    .all();

  return c.json(results);
});

export default app;
```

**Load `references/setup-guide.md` for complete walkthrough.**

---

## Critical Rules

### Always Do ✅

1. **Use prepared statements** with `.bind()` (never string concatenation)
2. **Create indexes** for WHERE/JOIN/ORDER BY columns
3. **Use migrations** for schema changes (never manual SQL)
4. **Batch queries** for multiple operations (.batch())
5. **Run PRAGMA optimize** after schema changes
6. **Handle errors** explicitly (try/catch)
7. **Use INTEGER for timestamps** (Date.now())
8. **Test locally** before deploying migrations
9. **Use read replicas** for global read performance
10. **Validate input** before SQL queries

### Never Do ❌

1. **Never concatenate** user input into SQL
2. **Never commit database_id** to public repos
3. **Never skip migrations** for schema changes
4. **Never use VARCHAR** (use TEXT instead)
5. **Never skip indexes** for filtered columns
6. **Never ignore** SQLite type affinity rules
7. **Never use SELECT *** without LIMIT
8. **Never run migrations** without testing locally
9. **Never exceed** 1MB per row
10. **Never use DATETIME** (use INTEGER for timestamps)

---

## D1 API Methods

### prepare() - Execute Queries

```typescript
// Single result
const { results } = await env.DB.prepare(
  'SELECT * FROM users WHERE email = ?'
)
  .bind(email)
  .all();

// First result only
const user = await env.DB.prepare(
  'SELECT * FROM users WHERE user_id = ?'
)
  .bind(userId)
  .first();

// Raw results (faster)
const { results } = await env.DB.prepare(
  'SELECT username FROM users'
)
  .raw();  // Returns arrays instead of objects
```

### batch() - Multiple Queries

```typescript
const results = await env.DB.batch([
  env.DB.prepare('INSERT INTO users (email, username, created_at) VALUES (?, ?, ?)')
    .bind('[email protected]', 'user1', Date.now()),
  env.DB.prepare('INSERT INTO users (email, username, created_at) VALUES (?, ?, ?)')
    .bind('[email protected]', 'user2', Date.now()),
  env.DB.prepare('SELECT COUNT(*) as count FROM users')
]);

console.log('Users count:', results[2].results[0].count);
```

**All queries execute in single transaction** (all succeed or all fail).

### exec() - Run SQL String

```typescript
// For migrations/setup only
await env.DB.exec(`
  CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
  );
  CREATE INDEX idx_email ON users(email);
`);
```

**NEVER use for queries with user input!**

**Load `references/query-patterns.md` for complete API reference.**

---

## Top 5 Use Cases

### Use Case 1: User CRUD

```typescript
// Create
app.post('/users', async (c) => {
  const { email, username } = await c.req.json();

  const { results } = await c.env.DB.prepare(
    'INSERT INTO users (email, username, created_at) VALUES (?, ?, ?) RETURNING *'
  )
    .bind(email, username, Date.now())
    .all();

  return c.json(results[0]);
});

// Read
app.get('/users/:id', async (c) => {
  const user = await c.env.DB.prepare(
    'SELECT * FROM users WHERE user_id = ?'
  )
    .bind(c.req.param('id'))
    .first();

  if (!user) {
    return c.json({ error: 'Not found' }, 404);
  }

  return c.json(user);
});

// Update
app.patch('/users/:id', async (c) => {
  const { username } = await c.req.json();

  await c.env.DB.prepare(
    'UPDATE users SET username = ?, updated_at = ? WHERE user_id = ?'
  )
    .bind(username, Date.now(), c.req.param('id'))
    .run();

  return c.json({ success: true });
});

// Delete
app.delete('/users/:id', async (c) => {
  await c.env.DB.prepare(
    'DELETE FROM users WHERE user_id = ?'
  )
    .bind(c.req.param('id'))
    .run();

  return c.json({ success: true });
});
```

### Use Case 2: Batch Operations

```typescript
app.post('/users/bulk', async (c) => {
  const users = await c.req.json();  // Array of users

  const statements = users.map(user =>
    c.env.DB.prepare(
      'INSERT INTO users (email, username, created_at) VALUES (?, ?, ?)'
    ).bind(user.email, user.username, Date.now())
  );

  const results = await c.env.DB.batch(statements);

  return c.json({ inserted: results.length });
});
```

### Use Case 3: Read Replication (Global Reads)

```typescript
// Configure read replica (any region)
const session = c.env.DB.withSession({
  preferredRegion: 'auto'  // or 'weur', 'wnam', 'enam', 'apac'
});

// Read from nearest replica
const { results } = await session.prepare(
  'SELECT * FROM users WHERE email = ?'
)
  .bind(email)
  .all();

// Check which region served request
console.log('Served by:', results[0].served_by_region);
```

**Load `references/read-replication.md` for complete guide.**

### Use Case 4: Transactions with Batch

```typescript
// Transfer credits between users (atomic)
const results = await c.en