Drizzle Orm D1

# Drizzle ORM for Cloudflare D1

**Status**: Production Ready ✅
**Last Updated**: 2025-12-14
**Latest Version**: drizzle-orm@0.44.7, drizzle-kit@0.31.7
**Dependencies**: cloudflare-d1, cloudflare-worker-base

---

## Quick Start (10 Minutes)

### 1. Install Drizzle

```bash
bun add drizzle-orm drizzle-kit
```

### 2. Configure Drizzle Kit

Create `drizzle.config.ts`:

```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!,
  },
});
```

### 3. Define 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' }),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
```

### 4. Generate & Apply Migrations

```bash
bunx drizzle-kit generate                           # Generate SQL
bunx wrangler d1 migrations apply my-database --local   # Apply local
bunx wrangler d1 migrations apply my-database --remote  # Apply prod
```

### 5. Query in Worker

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

export default {
  async fetch(request: Request, env: { DB: D1Database }): Promise<Response> {
    const db = drizzle(env.DB);
    const allUsers = await db.select().from(users).all();
    return Response.json(allUsers);
  },
};
```

---

## Critical Rules

### Always Do

| Rule | Why |
|------|-----|
| Use `drizzle-kit generate` for migrations | Never write SQL manually |
| Test migrations locally first | `--local` before `--remote` |
| 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 has no native date type |
| Use `.$defaultFn()` for dynamic defaults | Not `.default()` for functions |

### Never Do

| Rule | Why |
|------|-----|
| Use SQL `BEGIN TRANSACTION` | D1 requires batch API (Error #1) |
| Mix `drizzle-kit migrate` and `wrangler apply` | Use Wrangler only |
| Use `drizzle-kit push` for production | Use `generate` + `apply` |
| Commit credentials in drizzle.config.ts | Use env vars |
| Use `.default()` for function calls | Use `.$defaultFn()` instead |

---

## Top 5 Critical Errors

| # | Error | Solution |
|---|-------|----------|
| 1 | `D1_ERROR: Cannot use BEGIN TRANSACTION` | Use `db.batch([...])` instead of `db.transaction()` |
| 2 | `FOREIGN KEY constraint failed` | Define cascading: `.references(() => users.id, { onDelete: 'cascade' })` |
| 3 | `env.DB is undefined` | Ensure binding in `wrangler.jsonc` matches `env.DB` |
| 4 | `No such module "wrangler"` | Use `import { drizzle } from 'drizzle-orm/d1'` |
| 5 | `Type instantiation excessively deep` | Use `InferSelectModel<typeof users>` for explicit types |

**See**: `references/error-catalog.md` for all 12 errors with complete solutions.

---

## Common Patterns Summary

| Pattern | Use Case | Template |
|---------|----------|----------|
| **CRUD Operations** | Basic database operations | `templates/basic-queries.ts` |
| **Relations & Joins** | Nested queries, manual joins | `templates/relations-queries.ts` |
| **Batch Operations** | Transactions (D1 batch API) | `templates/transactions.ts` |
| **Schema Design** | Naming, indexes, soft deletes | `references/schema-patterns.md` |

---

## Configuration Summary

| File | Purpose | Template |
|------|---------|----------|
| `drizzle.config.ts` | Drizzle Kit configuration | `templates/drizzle.config.ts` |
| `wrangler.jsonc` | D1 binding setup | `references/wrangler-setup.md` |
| `package.json` | npm scripts for migrations | `templates/package.json` |

**npm scripts:**
```json
{
  "db:generate": "drizzle-kit generate",
  "db:migrate:local": "wrangler d1 migrations apply my-database --local",
  "db:migrate:remote": "wrangler d1 migrations apply my-database --remote"
}
```

---

## Migration Workflow

| Step | Command | Notes |
|------|---------|-------|
| 1. Edit schema | Edit `src/db/schema.ts` | Make changes |
| 2. Generate | `npm run db:generate` | Creates SQL migration |
| 3. Test local | `npm run db:migrate:local` | Verify locally |
| 4. Deploy code | `npm run deploy` | Push to Cloudflare |
| 5. Apply prod | `npm run db:migrate:remote` | Apply migration |

**See**: `references/migration-workflow.md` for complete workflow.

---

## TypeScript Type Inference

```typescript
import { InferSelectModel, InferInsertModel } from 'drizzle-orm';
import { users } from './db/schema';

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;
```

---

## When to Load References

| Reference | Load When... |
|-----------|--------------|
| `references/error-catalog.md` | Debugging D1 errors, transaction failures, binding issues |
| `references/schema-patterns.md` | Designing schemas, naming conventions, indexes, soft deletes |
| `references/migration-workflow.md` | Setting up or troubleshooting migrations |
| `references/query-builder-api.md` | Complex queries, operators, joins syntax |
| `references/wrangler-setup.md` | Configuring wrangler.jsonc for D1 |
| `references/common-errors.md` | Quick error lookup |

---

## Bundled Resources

**Templates**: `basic-schema.ts`, `basic-queries.ts`, `transactions.ts`, `relations-queries.ts`, `prepared-statements.ts`, `drizzle.config.ts`, `package.json`

**References**: `error-catalog.md`, `schema-patterns.md`, `migration-workflow.md`, `query-builder-api.md`, `wrangler-setup.md`, `common-errors.md`, `links-to-official-docs.md`

---

## Dependencies

```json
{
  "dependencies": {
    "drizzle-orm": "^0.44.7"
  },
  "devDependencies": {
    "drizzle-kit": "^0.31.7"
  }
}
```

---

## Official Documentation

- **Drizzle ORM**: https://orm.drizzle.team/
- **Drizzle with D1**: https://orm.drizzle.team/docs/connect-cloudflare-d1
- **Drizzle Kit**: https://orm.drizzle.team/docs/kit-overview
- **GitHub**: https://github.com/drizzle-team/drizzle-orm

---

**Token Savings**: ~65% (comprehensive patterns in references)
**Error Prevention**: 100% (all 12 documented issues)
**Ready for production!** ✅