{"id":"d1-migration","name":"d1-migration","summary":"Cloudflare D1の移行ワークフロー:Drizzleで生成し、SQLの落とし穴を検査し、ローカルとリモートに適用し、詰まった移行を修正し、部分的な失敗を処理します。","body":"# D1 Migration Workflow\n\nGuided workflow for Cloudflare D1 database migrations using Drizzle ORM.\n\n## Standard Migration Flow\n\n### 1. Generate Migration\n\n```bash\npnpm db:generate\n```\n\nThis creates a new `.sql` file in `drizzle/` (or your configured migrations directory).\n\n### 2. Inspect the SQL (CRITICAL)\n\n**Always read the generated SQL before applying.** Drizzle sometimes generates destructive migrations for simple schema changes.\n\n#### Red Flag: Table Recreation\n\nIf you see this pattern, the migration will likely fail:\n\n```sql\nCREATE TABLE `my_table_new` (...);\nINSERT INTO `my_table_new` SELECT ..., `new_column`, ... FROM `my_table`;\n--                                      ^^^ This column doesn't exist in old table!\nDROP TABLE `my_table`;\nALTER TABLE `my_table_new` RENAME TO `my_table`;\n```\n\n**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.\n\n**Fix**: If you're only adding new columns (no type/constraint changes on existing columns), simplify to:\n\n```sql\nALTER TABLE `my_table` ADD COLUMN `new_column` TEXT DEFAULT 'value';\n```\n\nEdit the `.sql` file directly before applying.\n\n### 3. Apply to Local\n\n```bash\npnpm db:migrate:local\n# or: npx wrangler d1 migrations apply DB_NAME --local\n```\n\n### 4. Apply to Remote\n\n```bash\npnpm db:migrate:remote\n# or: npx wrangler d1 migrations apply DB_NAME --remote\n```\n\n**Always apply to BOTH local and remote before testing.** Local-only migrations cause confusing \"works locally, breaks in production\" issues.\n\n### 5. Verify\n\n```bash\n# Check local\nnpx wrangler d1 execute DB_NAME --local --command \"PRAGMA table_info(my_table)\"\n\n# Check remote\nnpx wrangler d1 execute DB_NAME --remote --command \"PRAGMA table_info(my_table)\"\n```\n\n## Fixing Stuck Migrations\n\nWhen a migration partially applied (e.g. column was added but migration wasn't recorded), wrangler retries it and fails on the duplicate column.\n\n**Symptoms**: `pnpm db:migrate` errors on a migration that looks like it should be done. `PRAGMA table_info` shows the column exists.\n\n### Diagnosis\n\n```bash\n# 1. Verify the column/table exists\nnpx wrangler d1 execute DB_NAME --remote \\\n  --command \"PRAGMA table_info(my_table)\"\n\n# 2. Check what migrations are recorded\nnpx wrangler d1 execute DB_NAME --remote \\\n  --command \"SELECT * FROM d1_migrations ORDER BY id\"\n```\n\n### Fix\n\n```bash\n# 3. Manually record the stuck migration\nnpx wrangler d1 execute DB_NAME --remote \\\n  --command \"INSERT INTO d1_migrations (name, applied_at) VALUES ('0013_my_migration.sql', datetime('now'))\"\n\n# 4. Run remaining migrations normally\npnpm db:migrate\n```\n\n### Prevention\n\n- `CREATE TABLE IF NOT EXISTS` — safe to re-run\n- `ALTER TABLE ADD COLUMN` — SQLite has no `IF NOT EXISTS` variant; check column existence first or use try/catch in application code\n- **Always inspect generated SQL** before applying (Step 2 above)\n\n## Bulk Insert Batching\n\nD1's parameter limit causes silent failures with large multi-row INSERTs. Batch into chunks:\n\n```typescript\nconst BATCH_SIZE = 10;\nfor (let i = 0; i < allRows.length; i += BATCH_SIZE) {\n  const batch = allRows.slice(i, i + BATCH_SIZE);\n  await db.insert(myTable).values(batch);\n}\n```\n\n**Why**: D1 fails when rows x columns exceeds ~100-150 parameters.\n\n## Column Naming\n\n| Context | Convention | Example |\n|---------|-----------|---------|\n| Drizzle schema | camelCase | `caseNumber: text('case_number')` |\n| Raw SQL queries | snake_case | `UPDATE cases SET case_number = ?` |\n| API responses | Match SQL aliases | `SELECT case_number FROM cases` |\n\n## New Project Setup\n\nWhen creating a D1 database for a new project, follow this order:\n\n1. **Deploy Worker first** — `npm run build && npx wrangler deploy`\n2. **Create D1 database** — `npx wrangler d1 create project-name-db`\n3. **Copy database_id** to `wrangler.jsonc` `d1_databases` binding\n4. **Redeploy** — `npx wrangler deploy`\n5. **Run migrations** — apply to both local and remote","author":"@jezweb","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/jezweb/claude-skills/tree/main/plugins/cloudflare/skills/d1-migration","license":"MIT","category":"review","lang":"en","tokens":1004,"stars":0,"calls30d":4,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":[]}}