d1-migration

# D1 Migration Workflow

Guided workflow for Cloudflare D1 database migrations using Drizzle ORM.

## Standard Migration Flow

### 1. Generate Migration

```bash
pnpm db:generate
```

This creates a new `.sql` file in `drizzle/` (or your configured migrations directory).

### 2. Inspect the SQL (CRITICAL)

**Always read the generated SQL before applying.** Drizzle sometimes generates destructive migrations for simple schema changes.

#### Red Flag: Table Recreation

If you see this pattern, the migration will likely fail:

```sql
CREATE TABLE `my_table_new` (...);
INSERT INTO `my_table_new` SELECT ..., `new_column`, ... FROM `my_table`;
--                                      ^^^ This column doesn't exist in old table!
DROP TABLE `my_table`;
ALTER TABLE `my_table_new` RENAME TO `my_table`;
```

**Cause**: Changing a column's `default` value in Drizzle schema triggers full table recreation. The INSERT SELECT references the new column from the old table.

**Fix**: If you're only adding new columns (no type/constraint changes on existing columns), simplify to:

```sql
ALTER TABLE `my_table` ADD COLUMN `new_column` TEXT DEFAULT 'value';
```

Edit the `.sql` file directly before applying.

### 3. Apply to Local

```bash
pnpm db:migrate:local
# or: npx wrangler d1 migrations apply DB_NAME --local
```

### 4. Apply to Remote

```bash
pnpm db:migrate:remote
# or: npx wrangler d1 migrations apply DB_NAME --remote
```

**Always apply to BOTH local and remote before testing.** Local-only migrations cause confusing "works locally, breaks in production" issues.

### 5. Verify

```bash
# Check local
npx wrangler d1 execute DB_NAME --local --command "PRAGMA table_info(my_table)"

# Check remote
npx wrangler d1 execute DB_NAME --remote --command "PRAGMA table_info(my_table)"
```

## Fixing Stuck Migrations

When a migration partially applied (e.g. column was added but migration wasn't recorded), wrangler retries it and fails on the duplicate column.

**Symptoms**: `pnpm db:migrate` errors on a migration that looks like it should be done. `PRAGMA table_info` shows the column exists.

### Diagnosis

```bash
# 1. Verify the column/table exists
npx wrangler d1 execute DB_NAME --remote \
  --command "PRAGMA table_info(my_table)"

# 2. Check what migrations are recorded
npx wrangler d1 execute DB_NAME --remote \
  --command "SELECT * FROM d1_migrations ORDER BY id"
```

### Fix

```bash
# 3. Manually record the stuck migration
npx wrangler d1 execute DB_NAME --remote \
  --command "INSERT INTO d1_migrations (name, applied_at) VALUES ('0013_my_migration.sql', datetime('now'))"

# 4. Run remaining migrations normally
pnpm db:migrate
```

### Prevention

- `CREATE TABLE IF NOT EXISTS` — safe to re-run
- `ALTER TABLE ADD COLUMN` — SQLite has no `IF NOT EXISTS` variant; check column existence first or use try/catch in application code
- **Always inspect generated SQL** before applying (Step 2 above)

## Bulk Insert Batching

D1's parameter limit causes silent failures with large multi-row INSERTs. Batch into chunks:

```typescript
const BATCH_SIZE = 10;
for (let i = 0; i < allRows.length; i += BATCH_SIZE) {
  const batch = allRows.slice(i, i + BATCH_SIZE);
  await db.insert(myTable).values(batch);
}
```

**Why**: D1 fails when rows x columns exceeds ~100-150 parameters.

## Column Naming

| Context | Convention | Example |
|---------|-----------|---------|
| Drizzle schema | camelCase | `caseNumber: text('case_number')` |
| Raw SQL queries | snake_case | `UPDATE cases SET case_number = ?` |
| API responses | Match SQL aliases | `SELECT case_number FROM cases` |

## New Project Setup

When creating a D1 database for a new project, follow this order:

1. **Deploy Worker first** — `npm run build && npx wrangler deploy`
2. **Create D1 database** — `npx wrangler d1 create project-name-db`
3. **Copy database_id** to `wrangler.jsonc` `d1_databases` binding
4. **Redeploy** — `npx wrangler deploy`
5. **Run migrations** — apply to both local and remote