Back to Details

d1-migration

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

D1 Migration Workflow

D1 迁移工作流

Guided workflow for Cloudflare D1 database migrations using Drizzle ORM.
使用 Drizzle ORM 进行 Cloudflare D1 数据库迁移的指导工作流。

Standard Migration Flow

标准迁移流程

1. Generate Migration

1. 生成迁移文件

bash
pnpm db:generate
This creates a new 
.sql
file in 
drizzle/
(or your configured migrations directory).
bash
pnpm db:generate
该命令会在 
drizzle/
目录（或你配置的迁移目录）下生成一个新的 
.sql
文件。

2. Inspect the SQL (CRITICAL)

2. 检查 SQL（关键步骤）

Always read the generated SQL before applying. Drizzle sometimes generates destructive migrations for simple schema changes.
务必在应用迁移前阅读生成的 SQL。Drizzle 有时会为简单的 schema 变更生成破坏性的迁移脚本。

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.
如果你看到如下模式的 SQL，迁移大概率会执行失败：
sql
CREATE TABLE `my_table_new` (...);
INSERT INTO `my_table_new` SELECT ..., `new_column`, ... FROM `my_table`;
--                                      ^^^ 该列在旧表中不存在！
DROP TABLE `my_table`;
ALTER TABLE `my_table_new` RENAME TO `my_table`;
原因：在 Drizzle schema 中修改列的 
default
值会触发全表重建，INSERT SELECT 语句会引用旧表中不存在的新列。
修复方案：如果你只是新增列（没有修改现有列的类型/约束），可以将 SQL 简化为：
sql
ALTER TABLE `my_table` ADD COLUMN `new_column` TEXT DEFAULT 'value';
在应用迁移前直接编辑生成的 
.sql
文件即可。

3. Apply to Local

3. 应用到本地环境

bash
pnpm db:migrate:local
bash
pnpm db:migrate:local

or: npx wrangler d1 migrations apply DB_NAME --local

或者: npx wrangler d1 migrations apply DB_NAME --local

undefined
undefined

4. Apply to Remote

4. 应用到远程环境

bash
pnpm db:migrate:remote
bash
pnpm db:migrate:remote

or: npx wrangler d1 migrations apply DB_NAME --remote

或者: 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

5. 验证迁移结果

bash
undefined
bash
undefined

Check local

检查本地环境

npx wrangler d1 execute DB_NAME --local --command "PRAGMA table_info(my_table)"
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)"
undefined
npx wrangler d1 execute DB_NAME --remote --command "PRAGMA table_info(my_table)"
undefined

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.
当迁移部分执行时（例如列已经新增但迁移记录没有被写入），wrangler 会重试该迁移，然后因为列重复而报错。
症状：
pnpm db:migrate
在一个看起来已经执行完成的迁移上报错，执行 
PRAGMA table_info
可以看到对应列已经存在。

Diagnosis

诊断步骤

bash
undefined
bash
undefined

1. Verify the column/table exists

1. 确认列/表已存在

npx wrangler d1 execute DB_NAME --remote
--command "PRAGMA table_info(my_table)"
npx wrangler d1 execute DB_NAME --remote
--command "PRAGMA table_info(my_table)"

2. Check what migrations are recorded

2. 检查已记录的迁移列表

npx wrangler d1 execute DB_NAME --remote
--command "SELECT * FROM d1_migrations ORDER BY id"
undefined
npx wrangler d1 execute DB_NAME --remote
--command "SELECT * FROM d1_migrations ORDER BY id"
undefined

Fix

修复方案

bash
undefined
bash
undefined

3. Manually record the stuck migration

3. 手动录入卡住的迁移记录

npx wrangler d1 execute DB_NAME --remote
--command "INSERT INTO d1_migrations (name, applied_at) VALUES ('0013_my_migration.sql', datetime('now'))"
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

4. 正常执行剩余的迁移

pnpm db:migrate
undefined
pnpm db:migrate
undefined

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)
  • CREATE TABLE IF NOT EXISTS
    — 可安全重跑
  • ALTER TABLE ADD COLUMN
    — SQLite 没有对应的 
    IF NOT EXISTS
    语法，需要提前检查列是否存在，或者在应用代码中使用 try/catch 捕获异常
  • 应用迁移前务必检查生成的 SQL（参考上述步骤2）

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.
D1 的参数限制会导致大规模多行 INSERT 语句静默失败，建议拆分成多个批次执行：
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);
}
原因：当行数×列数超过约100-150个参数时，D1 会执行失败。

Column Naming

列命名规范

ContextConventionExample
Drizzle schemacamelCase
caseNumber: text('case_number')
Raw SQL queriessnake_case
UPDATE cases SET case_number = ?
API responsesMatch SQL aliases
SELECT case_number FROM cases
上下文命名规范示例
Drizzle schema驼峰命名（camelCase）
caseNumber: text('case_number')
原生 SQL 查询蛇形命名（snake_case）
UPDATE cases SET case_number = ?
API 响应匹配 SQL 别名
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
为新项目创建 D1 数据库时，请遵循以下顺序：
  1. 先部署 Worker — 
    npm run build && npx wrangler deploy
  2. 创建 D1 数据库 — 
    npx wrangler d1 create project-name-db
  3. 复制 
    database_id
     到 
    wrangler.jsonc
    的 
    d1_databases
    绑定配置中
  4. 重新部署 — 
    npx wrangler deploy
  5. 运行迁移 — 同时应用到本地和远程环境