Back to Details

db-migrations

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Migrations Guide

数据库迁移指南

Step 1: Generate Migrations

步骤1：生成迁移脚本

bash
bun run db:generate
This generates:
  • packages/database/migrations/0046_meaningless_file_name.sql
And updates:
  • packages/database/migrations/meta/_journal.json
  • packages/database/src/core/migrations.json
  • docs/development/database-schema.dbml
bash
bun run db:generate
执行该命令会生成：
  • packages/database/migrations/0046_meaningless_file_name.sql
并更新以下文件：
  • packages/database/migrations/meta/_journal.json
  • packages/database/src/core/migrations.json
  • docs/development/database-schema.dbml

Step 2: Optimize Migration SQL Filename

步骤2：优化迁移SQL文件名

Rename auto-generated filename to be meaningful:
0046_meaningless_file_name.sql
→ 
0046_user_add_avatar_column.sql
将自动生成的无意义文件名修改为具有明确含义的名称：
0046_meaningless_file_name.sql
→ 
0046_user_add_avatar_column.sql

Step 3: Use Idempotent Clauses (Defensive Programming)

步骤3：使用幂等子句（防御式编程）

Always use defensive clauses to make migrations idempotent (safe to re-run):
始终使用防御式子句确保迁移具备幂等性（可安全重复执行）：

CREATE TABLE

创建表（CREATE TABLE）

sql
-- ✅ Good
CREATE TABLE IF NOT EXISTS "agent_eval_runs" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);

-- ❌ Bad
CREATE TABLE "agent_eval_runs" (...);
sql
-- ✅ 推荐
CREATE TABLE IF NOT EXISTS "agent_eval_runs" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);

-- ❌ 不推荐
CREATE TABLE "agent_eval_runs" (...);

ALTER TABLE - Columns

修改表 - 列（ALTER TABLE - Columns）

sql
-- ✅ Good
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatar" text;
ALTER TABLE "posts" DROP COLUMN IF EXISTS "deprecated_field";

-- ❌ Bad
ALTER TABLE "users" ADD COLUMN "avatar" text;
sql
-- ✅ 推荐
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatar" text;
ALTER TABLE "posts" DROP COLUMN IF EXISTS "deprecated_field";

-- ❌ 不推荐
ALTER TABLE "users" ADD COLUMN "avatar" text;

ALTER TABLE - Foreign Key Constraints

修改表 - 外键约束（ALTER TABLE - Foreign Key Constraints）

PostgreSQL has no 
ADD CONSTRAINT IF NOT EXISTS
. Use 
DROP IF EXISTS
+ 
ADD
:
sql
-- ✅ Good: Drop first, then add (idempotent)
ALTER TABLE "agent_eval_datasets" DROP CONSTRAINT IF EXISTS "agent_eval_datasets_user_id_users_id_fk";
ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

-- ❌ Bad: Will fail if constraint already exists
ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
PostgreSQL 不支持 
ADD CONSTRAINT IF NOT EXISTS
，可先执行
DROP IF EXISTS
再执行
ADD
：
sql
-- ✅ 推荐：先删除再添加（具备幂等性）
ALTER TABLE "agent_eval_datasets" DROP CONSTRAINT IF EXISTS "agent_eval_datasets_user_id_users_id_fk";
ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

-- ❌ 不推荐：如果约束已存在会执行失败
ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

DROP TABLE / INDEX

删除表 / 索引（DROP TABLE / INDEX）

sql
-- ✅ Good
DROP TABLE IF EXISTS "old_table";
CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" ("email");
CREATE UNIQUE INDEX IF NOT EXISTS "users_email_unique" ON "users" USING btree ("email");

-- ❌ Bad
DROP TABLE "old_table";
CREATE INDEX "users_email_idx" ON "users" ("email");
sql
-- ✅ 推荐
DROP TABLE IF EXISTS "old_table";
CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" ("email");
CREATE UNIQUE INDEX IF NOT EXISTS "users_email_unique" ON "users" USING btree ("email");

-- ❌ 不推荐
DROP TABLE "old_table";
CREATE INDEX "users_email_idx" ON "users" ("email");

Step 4: Regenerate Client After SQL Edits

步骤4：编辑SQL后重新生成客户端

After modifying the generated SQL (e.g., adding 
IF NOT EXISTS
), regenerate the client:
bash
bun run db:generate:client
修改生成的SQL文件后（例如添加
IF NOT EXISTS
），需重新生成客户端：
bash
bun run db:generate:client