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

1. 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