drizzle-orm-d1

What this skill does

# Drizzle ORM for Cloudflare D1

**Status**: Production Ready ✅
**Last Updated**: 2025-10-24
**Latest Version**: [email protected], [email protected]
**Dependencies**: cloudflare-d1, cloudflare-worker-base

---

## Quick Start (10 Minutes)

### 1. Install Drizzle

```bash
npm install drizzle-orm
npm install -D drizzle-kit

# Or with pnpm
pnpm add drizzle-orm
pnpm add -D drizzle-kit
```

**Why Drizzle?**
- Type-safe queries with full TypeScript inference
- SQL-like syntax (no magic, no abstraction overhead)
- Serverless-ready (works perfectly with D1)
- Zero dependencies (except database driver)
- Excellent DX with IDE autocomplete
- Migrations that work with Wrangler

### 2. Configure Drizzle Kit

Create `drizzle.config.ts` in your project root:

```typescript
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './migrations',
  dialect: 'sqlite',
  driver: 'd1-http',
  dbCredentials: {
    accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
    databaseId: process.env.CLOUDFLARE_DATABASE_ID!,
    token: process.env.CLOUDFLARE_D1_TOKEN!,
  },
});
```

**CRITICAL**:
- `dialect: 'sqlite'` - D1 is SQLite-based
- `driver: 'd1-http'` - For remote database access via HTTP API
- Use environment variables for credentials (never commit these!)

### 3. Configure Wrangler

Update `wrangler.jsonc`:

```jsonc
{
  "name": "my-worker",
  "main": "src/index.ts",
  "compatibility_date": "2025-10-11",
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "my-database",
      "database_id": "your-database-id",
      "preview_database_id": "local-db",
      "migrations_dir": "./migrations"  // ← Points to Drizzle migrations!
    }
  ]
}
```

**Why this matters:**
- `migrations_dir` tells Wrangler where to find SQL migration files
- Drizzle generates migrations in `./migrations` (from drizzle.config.ts `out`)
- Wrangler can apply Drizzle-generated migrations with `wrangler d1 migrations apply`

### 4. Define Your Schema

Create `src/db/schema.ts`:

```typescript
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { relations } from 'drizzle-orm';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
});

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  createdAt: integer('created_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
});

// Define relations for type-safe joins
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));
```

**Key Points:**
- Use `integer` for auto-incrementing IDs
- Use `integer` with `mode: 'timestamp'` for dates (D1 doesn't have native date type)
- Use `.$defaultFn()` for dynamic defaults (not `.default()` for functions)
- Define relations separately for type-safe joins

### 5. Generate & Apply Migrations

```bash
# Step 1: Generate SQL migration from schema
npx drizzle-kit generate

# Step 2: Apply to local database (for testing)
npx wrangler d1 migrations apply my-database --local

# Step 3: Apply to production database
npx wrangler d1 migrations apply my-database --remote
```

**Why this workflow:**
- `drizzle-kit generate` creates versioned SQL files in `./migrations`
- Test locally first with `--local` flag
- Apply to production only after local testing succeeds
- Wrangler reads the migrations and applies them to D1

### 6. Query in Your Worker

Create `src/index.ts`:

```typescript
import { drizzle } from 'drizzle-orm/d1';
import { users, posts } from './db/schema';
import { eq } from 'drizzle-orm';

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const db = drizzle(env.DB);

    // Type-safe select with full inference
    const allUsers = await db.select().from(users);

    // Select with where clause
    const user = await db
      .select()
      .from(users)
      .where(eq(users.email, '[email protected]'))
      .get(); // .get() returns first result or undefined

    // Insert with returning
    const [newUser] = await db
      .insert(users)
      .values({ email: '[email protected]', name: 'New User' })
      .returning();

    // Update
    await db
      .update(users)
      .set({ name: 'Updated Name' })
      .where(eq(users.id, 1));

    // Delete
    await db
      .delete(users)
      .where(eq(users.id, 1));

    return Response.json({ allUsers, user, newUser });
  },
};
```

**CRITICAL**:
- Use `.get()` for single results (returns first or undefined)
- Use `.all()` for all results (returns array)
- Import operators from `drizzle-orm`: `eq`, `gt`, `lt`, `and`, `or`, etc.
- `.returning()` works with D1 (returns inserted/updated rows)

---

## The Complete Setup Process

### Step 1: Install Dependencies

```bash
# Core dependencies
npm install drizzle-orm

# Dev dependencies
npm install -D drizzle-kit @cloudflare/workers-types

# Optional: For local development with SQLite
npm install -D better-sqlite3
```

### Step 2: Environment Variables

Create `.env` (never commit this!):

```bash
# Get these from Cloudflare dashboard
CLOUDFLARE_ACCOUNT_ID=your-account-id
CLOUDFLARE_DATABASE_ID=your-database-id
CLOUDFLARE_D1_TOKEN=your-api-token
```

**How to get these:**
1. Account ID: Cloudflare dashboard → Account Home → Account ID
2. Database ID: Run `wrangler d1 create my-database` (output includes ID)
3. API Token: Cloudflare dashboard → My Profile → API Tokens → Create Token

### Step 3: Project Structure

```
my-project/
├── drizzle.config.ts          # Drizzle Kit configuration
├── wrangler.jsonc             # Wrangler configuration
├── src/
│   ├── index.ts               # Worker entry point
│   └── db/
│       └── schema.ts          # Database schema
├── migrations/                # Generated by drizzle-kit
│   ├── meta/
│   │   └── _journal.json
│   └── 0001_initial_schema.sql
└── package.json
```

### Step 4: Configure TypeScript

Update `tsconfig.json`:

```json
{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ESNext",
    "lib": ["ES2022"],
    "types": ["@cloudflare/workers-types"],
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "moduleResolution": "bundler",
    "resolveJsonModule": true,
    "isolatedModules": true
  }
}
```

---

## Critical Rules

### Always Do

✅ **Use `drizzle-kit generate` for migrations** - Never write SQL manually
✅ **Test migrations locally first** - Always use `--local` flag before `--remote`
✅ **Define relations in schema** - For type-safe joins and nested queries
✅ **Use `.get()` for single results** - Returns first row or undefined
✅ **Use `db.batch()` for transactions** - D1 doesn't support SQL BEGIN/COMMIT
✅ **Use `integer` with `mode: 'timestamp'` for dates** - D1 doesn't have native date type
✅ **Use `.$defaultFn()` for dynamic defaults** - Not `.default()` for functions
✅ **Set `migrations_dir` in wrangler.jsonc** - Points to `./migrations`
✅ **Use environment variables for credentials** - Never commit API keys
✅ **Import operators from drizzle-orm** - `eq`, `gt`, `and`, `or`, etc.

### Never Do

❌ **Never use SQL `BEGIN TRANSACTION`** - D1 requires batch API (see Known Issue #1)
❌ **Never mix `wrangler d1 migrations apply` and `drizzle-kit migrate`** - Use Wrangler only
❌ **Never use `drizzle-kit push` for production** - Use `generate` + `apply` workflow
❌ **Never forget to apply migrations loc