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