D1 Migration Workflow

Guided workflow for Cloudflare D1 database migrations using Drizzle ORM.

Standard Migration Flow

1. Generate Migration

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:

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:

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

Edit the .sql file directly before applying.

3. Apply to Local

pnpm db:migrate:local

# or: npx wrangler d1 migrations apply DB_NAME --local

4. Apply to Remote

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

# 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

# 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

# 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:

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:

• Deploy Worker first — npm run build && npx wrangler deploy

• Create D1 database — npx wrangler d1 create project-name-db

• Copy database_id to wrangler.jsonc d1_databases binding

• Redeploy — npx wrangler deploy

• Run migrations — apply to both local and remote