D1 Migrations Can't Go Back: A Rollback Strategy for Forward-Only Schemas
# Cloudflare D1 has no down migrations. How to change schemas safely anyway: pre-written rollback SQL, idempotent backfills, the DROP COLUMN trigger trap, and dual-schema application code.
Turnstile site key が未設定のため、このフォームは送信できません。管理者は NEXT_PUBLIC_TURNSTILE_SITE_KEY を設定してください。
[ ]まだコメントはありません。
The reality of D1 migrations
Cloudflare D1's wrangler d1 migrations apply is forward-only. There is no down-migration mechanism like Rails or Prisma. Once applied, there is officially no way back.
That is no excuse for shipping irreversible schema changes blindly. Here is the operating procedure this blog actually uses.
Rule 1: write the road back before moving forward
Every schema-touching migration gets a matching rollback file in migrations/rollback/ first:
txt
migrations/ 0006_bilingual_posts.sql # the migration that gets applied rollback/ 0006_bilingual_posts.down.sql # manual rollback README.md # the runbook
Important: rollback/ is a subdirectory, so migrations apply ignores it (wrangler only reads .sql files directly under migrations/). Rollbacks run manually via d1 execute --file:
bash
wrangler d1 export my-db --remote --output backup.sql # always back up firstwrangler d1 execute my-db --remote --file migrations/rollback/0006_bilingual_posts.down.sql# remove the migration record so it can be re-applied laterwrangler d1 execute my-db --remote \ --command "DELETE FROM d1_migrations WHERE name = '0006_bilingual_posts.sql'"
Rule 2: write idempotent migrations
Design every migration to survive re-runs and partial failures:
sql
-- add columns with DEFAULT so existing rows stay validALTER TABLE posts ADD COLUMN title_en TEXT DEFAULT '';-- backfill only where emptyUPDATE posts SET title_en = CASE WHEN title_en = '' THEN title ELSE title_en END;-- guard backfills with NOT EXISTS to prevent duplicatesINSERT INTO posts_fts(rowid, title)SELECT rowid, title FROM postsWHERE NOT EXISTS (SELECT 1 FROM posts_fts WHERE posts_fts.rowid = posts.rowid);
Rule 3: know the DROP COLUMN trap
In SQLite (and therefore D1), ALTER TABLE ... DROP COLUMNfails if any trigger, view, or index references the column. Before writing a rollback that drops columns, check whether FTS sync triggers or similar reference them.
If they do, either drop the triggers, drop the column, and recreate the triggers — or use the create-new-table → copy → rename pattern. For genuinely destructive changes (type or constraint changes), start with the latter.
Rule 4: document what an irreversible rollback loses
A backfill UPDATE cannot be undone. Write what the rollback destroys in the rollback file itself — future you will be grateful:
sql
-- Rollback for 0006_bilingual_posts.sql.-- The base title/excerpt/content columns stay canonical (the app keeps-- writing them), so this rollback loses only the per-language variants.-- Take a backup before running.ALTER TABLE posts DROP COLUMN title_en;
Rule 5: make the app work on both schemas
Deploys and migrations never complete at the same instant. Application code should tolerate the new columns being present or absent:
ts
// read: fall back to the old column when the new one is emptytitleEn: row.title_en || row.title,
With this read-tolerant, write-both window, migration order stops mattering.
Takeaways
• D1 is forward-only: write rollback SQL and a runbook before touching schemas
• Make migrations idempotent — DEFAULTed adds, CASE backfills, NOT EXISTS guards
• DROP COLUMN fails under trigger references; check dependencies first
• Document what each irreversible rollback loses
• Keep the app dual-schema compatible during the transition
Turnstile site key が未設定のため、このフォームは送信できません。管理者は NEXT_PUBLIC_TURNSTILE_SITE_KEY を設定してください。