1. Mental Model

D1 = SQLite running inside your Worker process

• Not a separate database server - zero network latency

• One logical database, replicated globally by Cloudflare

• env.DB injected at runtime via binding system

2. Basic Setup

Wrangler Config

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "my-database",
      "database_id": "<unique-ID>"
    }
  ]
}

TypeScript Support

npx wrangler types  # Generates worker-configuration.d.ts

Basic Worker Structure

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request, env): Promise<Response> {
    // env.DB is your database connection
  },
} satisfies ExportedHandler<Env>;

3. Core SQL Operations

The Prepare → Bind → Run Chain

// Basic pattern
const stmt = env.DB.prepare("SELECT * FROM users WHERE id = ?");
const result = await stmt.bind(123).run();

Multiple Placeholders

// Sequential binding
const stmt = env.DB.prepare("SELECT * FROM users WHERE age > ? AND city = ?");
const result = await stmt.bind(25, "New York").run();

// Numbered placeholders (cool!)
const stmt = env.DB.prepare(
  "SELECT * FROM logs WHERE user_id = ?1 AND created_at > ?2 OR updated_at > ?2"
);
const result = await stmt.bind(userId, yesterday).run();

Execution Methods

// .run() - Full metadata
const result = await stmt.run();
// { results: [...], meta: { changes: 1, duration: 5 }, success: true }

// .first() - Just first row
const user = await stmt.first(); // User object or null

// .raw() - Arrays instead of objects
const rows = await stmt.raw(); // [[1, "John"], [2, "Jane"]]

Type Safety

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

const user = await stmt.first<User>(); // User | null
const users = await stmt.run<User>(); // { results: User[], ... }
const rawRows = await stmt.raw<User>(); // User[][]

4. D1-Specific Patterns

Batch Operations (Performance!)

// Multiple queries in one round trip
const insertStmt = env.DB.prepare(
  "INSERT INTO users (name, email) VALUES (?, ?)"
);

const results = await env.DB.batch([
  insertStmt.bind("Alice", "alice@example.com"),
  insertStmt.bind("Bob", "bob@example.com"),
  insertStmt.bind("Charlie", "charlie@example.com"),
]);

// All succeed or all rollback (atomic transaction)

Error Handling

try {
  const result = await env.DB.prepare("SELECT * FROM users WHERE id = ?")
    .bind(userId)
    .run();
  return Response.json(result.results);
} catch (error) {
  console.error("Database error:", error);
  return new Response("Database error", { status: 500 });
}

Type Conversion Gotchas

// JavaScript booleans become numbers in D1
await stmt.bind(true).run(); // Stores as 1
const result = await stmt.first<{ active: boolean }>();
console.log(result.active); // 1, not true!

// Handle undefined
const maybeEmail = undefined;
await stmt.bind(maybeEmail ?? null).run(); // ✅ null is fine, undefined throws error

5. Performance & Consistency

Read Replication Behavior

// Without read replication: all queries hit primary region
// With read replication: reads hit local replicas, writes hit primary

// Check where query was served
const result = await env.DB.prepare("SELECT * FROM users").run();
console.log(`Served by region: ${result.meta.served_by_region}`);
console.log(`Served by primary: ${result.meta.served_by_primary}`);

Sessions API (Read Your Own Writes)

// Problem: writes go to primary, reads might hit stale replica
await env.DB.prepare("INSERT INTO posts (title) VALUES (?)")
  .bind("My Post")
  .run();
const post = await env.DB.prepare("SELECT * FROM posts WHERE title = ?")
  .bind("My Post")
  .first();
// post might be null! 😱

// Solution: Sessions API
const session = env.DB.withSession("first-primary");
await session
  .prepare("INSERT INTO posts (title) VALUES (?)")
  .bind("My Post")
  .run();
const post = await session
  .prepare("SELECT * FROM posts WHERE title = ?")
  .bind("My Post")
  .first();
// post will definitely exist ✅

// Bookmarks for continuation
const bookmark = session.getBookmark();
response.headers.set("x-d1-bookmark", bookmark);

6. Schema Management

Manual Migration Pattern

migrations/
  001-initial-schema.sql
  002-add-users-table.sql
  003-add-indexes.sql

# Local development
npx wrangler d1 execute my-db --local --file=./migrations/002-add-users-table.sql

# Production
npx wrangler d1 execute my-db --remote --file=./migrations/002-add-users-table.sql

Safe Schema Changes

-- ✅ Safe: Adding columns/tables
ALTER TABLE users ADD COLUMN email TEXT;
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);

-- ❌ Dangerous: Removing/renaming during replication
ALTER TABLE users DROP COLUMN old_field;
ALTER TABLE users RENAME COLUMN name TO full_name;

7. Real-World Integration

D1 + KV Caching Pattern

async function getUser(userId: number) {
  // Try cache first
  const cached = await env.CACHE.get(`user:${userId}`, "json");
  if (cached) return cached;

  // Cache miss - hit database
  const user = await env.DB.prepare("SELECT * FROM users WHERE id = ?")
    .bind(userId)
    .first();

  if (user) {
    // Cache for 5 minutes
    await env.CACHE.put(`user:${userId}`, JSON.stringify(user), {
      expirationTtl: 300,
    });
  }

  return user;
}

// ⚠️ KV deletion takes up to 60 seconds to propagate!
// Use TTL expiration instead of explicit deletes

Race Condition Prevention

// ❌ Bad - race condition possible
const user = await env.DB.prepare("SELECT credits FROM users WHERE id = ?")
  .bind(userId)
  .first();
if (user.credits >= price) {
  await env.DB.prepare("UPDATE users SET credits = ? WHERE id = ?")
    .bind(user.credits - price, userId)
    .run();
}

// ✅ Good - atomic operation
const result = await env.DB.prepare(
  `
  UPDATE users SET credits = credits - ? WHERE id = ? AND credits >= ?
`
)
  .bind(price, userId, price)
  .run();

if (result.meta.changes === 0) {
  throw new Error("Insufficient credits");
}

Complete Worker Example

export default {
  async fetch(request, env) {
    const url = new URL(request.url);

    try {
      if (url.pathname === "/api/users" && request.method === "GET") {
        const { results } = await env.DB.prepare(
          "SELECT id, name, email FROM users"
        ).run();
        return Response.json(results);
      }

      if (url.pathname === "/api/users" && request.method === "POST") {
        const userData = await request.json();
        const result = await env.DB.prepare(
          "INSERT INTO users (name, email) VALUES (?, ?)"
        )
          .bind(userData.name, userData.email)
          .run();

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

      return new Response("Not found", { status: 404 });
    } catch (error) {
      console.error(error);
      return new Response("Internal error", { status: 500 });
    }
  },
};

Key Commands

# Create database
npx wrangler d1 create my-database

# Execute SQL (local)
npx wrangler d1 execute my-database --local --command="SELECT * FROM users"
npx wrangler d1 execute my-database --local --file=./schema.sql

# Execute SQL (remote)
npx wrangler d1 execute my-database --remote --command="SELECT * FROM users"
npx wrangler d1 execute my-database --remote --file=./schema.sql

# Generate types
npx wrangler types

# Deploy
npx wrangler deploy

---

Remember: D1 = SQLite at the edge with global replication magic. Master the basics, then consider Drizzle for serious projects!