d1-database

What this skill does

# D1 Database

D1 is Cloudflare's native serverless SQL database built on SQLite. Run queries at the edge with automatic replication and low latency.

## FIRST: Create Database

```bash
# Create D1 database
wrangler d1 create my-database

# Output includes database_id - add to wrangler.jsonc
```

Add binding to `wrangler.jsonc`:

```jsonc
{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "my-database",
      "database_id": "<DATABASE_ID>",
      "migrations_dir": "./migrations"
    }
  ]
}
```

Generate TypeScript types:

```bash
wrangler types
```

## When to Use

| Use Case | Why D1 |
|----------|--------|
| Relational data | Structured tables with relationships |
| Complex queries | JOINs, aggregations, full SQL support |
| User data | Accounts, profiles, settings |
| Content management | Posts, comments, metadata |
| E-commerce | Products, orders, inventory |
| Analytics | Event tracking with SQL queries |

**When NOT to use D1:**
- Simple key-value data → Use Workers KV
- Large files/objects → Use R2
- Real-time coordination → Use Durable Objects
- High-frequency writes → Use Queues + D1

## Quick Reference

| Operation | Method | Returns |
|-----------|--------|---------|
| Execute query | `db.prepare(sql).bind(...).run()` | `{ success: boolean, meta: {...} }` |
| Get all rows | `db.prepare(sql).bind(...).all()` | `{ results: T[], success: boolean }` |
| Get first row | `db.prepare(sql).bind(...).first()` | `T \| null` |
| Get single value | `db.prepare(sql).bind(...).first('column')` | `any \| null` |
| Batch queries | `db.batch([stmt1, stmt2, ...])` | `Array<D1Result>` |
| Raw query | `db.prepare(sql).bind(...).raw()` | `Array<Array<any>>` |

## Minimal Example

```typescript
// src/index.ts
interface Env {
  DB: D1Database;
}

export default {
  async fetch(request: Request, env: Env) {
    // Get all users
    const { results } = await env.DB
      .prepare("SELECT * FROM users WHERE active = ?")
      .bind(true)
      .all<{ id: number; name: string; email: string }>();

    return Response.json(results);
  }
} satisfies ExportedHandler<Env>;
```

## CRUD Operations

```typescript
interface Env {
  DB: D1Database;
}

type User = {
  id: number;
  name: string;
  email: string;
  created_at: string;
};

type NewUser = Omit<User, "id" | "created_at">;

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

    // CREATE - Insert new user
    if (pathname === "/users" && request.method === "POST") {
      const user: NewUser = await request.json();
      
      const result = await env.DB
        .prepare("INSERT INTO users (name, email) VALUES (?, ?)")
        .bind(user.name, user.email)
        .run();

      if (!result.success) {
        return Response.json({ error: "Failed to create user" }, { status: 500 });
      }

      return Response.json({ 
        id: result.meta.last_row_id,
        message: "User created" 
      }, { status: 201 });
    }

    // READ - Get user by ID
    if (pathname.startsWith("/users/") && request.method === "GET") {
      const id = pathname.split("/")[2];
      
      const user = await env.DB
        .prepare("SELECT * FROM users WHERE id = ?")
        .bind(id)
        .first<User>();

      if (!user) {
        return Response.json({ error: "User not found" }, { status: 404 });
      }

      return Response.json(user);
    }

    // READ - Get all users (with pagination)
    if (pathname === "/users" && request.method === "GET") {
      const page = parseInt(url.searchParams.get("page") || "1");
      const limit = parseInt(url.searchParams.get("limit") || "10");
      const offset = (page - 1) * limit;

      const { results } = await env.DB
        .prepare("SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?")
        .bind(limit, offset)
        .all<User>();

      return Response.json({
        users: results,
        page,
        limit
      });
    }

    // UPDATE - Update user
    if (pathname.startsWith("/users/") && request.method === "PUT") {
      const id = pathname.split("/")[2];
      const updates: Partial<NewUser> = await request.json();

      const result = await env.DB
        .prepare("UPDATE users SET name = ?, email = ? WHERE id = ?")
        .bind(updates.name, updates.email, id)
        .run();

      if (result.meta.changes === 0) {
        return Response.json({ error: "User not found" }, { status: 404 });
      }

      return Response.json({ message: "User updated" });
    }

    // DELETE - Delete user
    if (pathname.startsWith("/users/") && request.method === "DELETE") {
      const id = pathname.split("/")[2];

      const result = await env.DB
        .prepare("DELETE FROM users WHERE id = ?")
        .bind(id)
        .run();

      if (result.meta.changes === 0) {
        return Response.json({ error: "User not found" }, { status: 404 });
      }

      return Response.json({ message: "User deleted" });
    }

    return Response.json({ error: "Not found" }, { status: 404 });
  }
} satisfies ExportedHandler<Env>;
```

## Migrations Workflow

D1 uses SQL migration files to manage schema changes over time.

### Create Migration

```bash
# Create new migration file
wrangler d1 migrations create my-database create_users_table

# Creates: migrations/0001_create_users_table.sql
```

Edit the generated migration file:

```sql
-- migrations/0001_create_users_table.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
```

### Apply Migrations

```bash
# Apply locally for testing
wrangler d1 migrations apply my-database --local

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

# List pending migrations
wrangler d1 migrations list my-database --local
```

### Migration Best Practices

1. **One migration per change**: Create separate migrations for each schema change
2. **Test locally first**: Always run `--local` before `--remote`
3. **Never edit applied migrations**: Create new migrations to fix issues
4. **Use transactions**: Migrations run in transactions by default
5. **Add indexes**: Include indexes in migrations for query performance

Example migration with multiple tables:

```sql
-- migrations/0002_add_posts.sql
CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  published BOOLEAN DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);
```

## Batch Operations

Execute multiple statements in a single transaction for better performance and atomicity.

```typescript
interface Env {
  DB: D1Database;
}

type Post = {
  title: string;
  content: string;
  userId: number;
};

export default {
  async fetch(request: Request, env: Env) {
    const posts: Post[] = await request.json();

    // Prepare all statements
    const statements = posts.map(post =>
      env.DB
        .prepare("INSERT INTO posts (title, content, user_id) VALUES (?, ?, ?)")
        .bind(post.title, post.content, post.userId)
    );

    // Execute as a batch (all or nothing)
    try {
      const results = await env.DB.batch(statements);
      
      const insertedIds = results.map(r => r.meta.last_row_id);
      
      return Response.json({
        message: "Posts created",
        ids: insertedIds,
        count: results.length
      });
    } catch (error) {
      return Response.json(
        { error: "Batch insert failed", details: error.message },
        { status: 500 }
      );
    }
  }
} satisfies ExportedHandler<Env>;
```

**Batch benefits:**
- All statements succeed or all fail (atomic)
- Single round-trip to database
- Better p