database-migrations

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Migrations

数据库迁移

Evolve your database schema safely and confidently

安全、自信地演进你的数据库Schema

When to Use This Skill

何时使用此技能

Use this skill when:

Creating or modifying database schemas
Deploying database changes to production
Planning rollback strategies
Performing data migrations

在以下场景使用此技能：

创建或修改数据库Schema
将数据库变更部署到生产环境
规划回滚策略
执行数据迁移

Critical Patterns

关键模式

Pattern 1: Development Workflow

模式1：开发工作流

When: Creating new migrations in development

Good:

bash

undefined

适用场景：在开发环境中创建新的迁移

正确做法：

bash

undefined

1. Modify schema.prisma

1. 修改schema.prisma

model User { id Int @id @default(autoincrement()) email String @unique name String }

2. Create migration

2. 创建迁移

npx prisma migrate dev --name add_user_model

Creates migration file and applies it

创建迁移文件并应用


**Why**: `migrate dev` handles everything in development.

---


**原因**：`migrate dev` 可处理开发环境中的所有相关操作。

---

Pattern 2: Production Deployment

模式2：生产环境部署

When: Deploying to production

Good:

bash

undefined

适用场景：部署到生产环境

正确做法：

bash

undefined

Deploy migrations (non-interactive)

部署迁移（非交互式）

npx prisma migrate deploy

CI/CD pipeline

CI/CD 流水线


**Bad**:
```bash


**错误做法**：
```bash

❌ Never use migrate dev in production

❌ 切勿在生产环境中使用migrate dev

npx prisma migrate dev


**Why**: `migrate deploy` is non-interactive and safe for automation.

---

npx prisma migrate dev


**原因**：`migrate deploy` 是非交互式的，适用于自动化操作，安全性更高。

---

Pattern 3: Backward Compatible Changes

模式3：向后兼容的变更

When: Adding new fields or models

Good:

prisma

// ✅ Adding optional field
model User {
  id       Int     @id
  email    String  @unique
  bio      String? // Existing rows get NULL
}

// ✅ Adding field with default
model User {
  id        Int      @id
  email     String   @unique
  role      String   @default("USER")
  createdAt DateTime @default(now())
}

Why: These changes don't break existing data or code.

适用场景：添加新字段或模型

正确做法：

prisma

// ✅ 添加可选字段
model User {
  id       Int     @id
  email    String  @unique
  bio      String? // 现有行将被设为NULL
}

// ✅ 添加带默认值的字段
model User {
  id        Int      @id
  email     String   @unique
  role      String   @default("USER")
  createdAt DateTime @default(now())
}

原因：这些变更不会破坏现有数据或代码。

Pattern 4: Safe Field Rename

模式4：安全重命名字段

When: Renaming without data loss

Good (3-step process):

prisma

// Step 1: Add new field mapping to same column
model User {
  id       Int    @id
  name     String
  fullName String @map("name")
}

// Step 2: Update app code to use fullName
// Step 3: Remove old field
model User {
  id       Int    @id
  fullName String @map("name")
}

Bad:

prisma

// ❌ Direct rename (data loss)
model User {
  id       Int    @id
  fullName String // Was "name", data lost!
}

Why: Direct rename is seen as remove + add.

适用场景：重命名字段且不丢失数据

正确做法（三步流程）：

prisma

// 步骤1：添加映射到同一列的新字段
model User {
  id       Int    @id
  name     String
  fullName String @map("name")
}

// 步骤2：更新应用代码以使用fullName
// 步骤3：移除旧字段
model User {
  id       Int    @id
  fullName String @map("name")
}

错误做法：

prisma

// ❌ 直接重命名（会丢失数据）
model User {
  id       Int    @id
  fullName String // 原字段为"name"，数据丢失！
}

原因：直接重命名会被识别为“删除旧字段+添加新字段”操作。

Pattern 5: Making Field Required

模式5：将字段设为必填

When: Converting optional to required field

Good (3-step process):

prisma

// Step 1: Add default
model User {
  id    Int     @id
  name  String? @default("")
}

bash

npx prisma migrate dev --name add_default_name

typescript

// Step 2: Backfill NULL values
await prisma.$executeRaw`UPDATE "User" SET name = '' WHERE name IS NULL`

prisma

// Step 3: Make required
model User {
  id    Int    @id
  name  String @default("")
}

Why: Multi-step prevents errors from existing NULL values.

适用场景：将可选字段转换为必填字段

正确做法（三步流程）：

prisma

// 步骤1：添加默认值
model User {
  id    Int     @id
  name  String? @default("")
}

bash

npx prisma migrate dev --name add_default_name

typescript

// 步骤2：回填NULL值
await prisma.$executeRaw`UPDATE "User" SET name = '' WHERE name IS NULL`

prisma

// 步骤3：设为必填
model User {
  id    Int    @id
  name  String @default("")
}

原因：多步骤操作可避免现有NULL值引发的错误。

Code Examples

代码示例

Example 1: Adding a New Model

示例1：添加新模型

prisma

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

bash

undefined

prisma

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

bash

undefined

Create migration

创建迁移

npx prisma migrate dev --name add_post_model

Applies migration and generates Prisma Client

应用迁移并生成Prisma Client

undefined

undefined

Example 2: Safe Data Migration

示例2：安全的数据迁移

typescript

// Backfill default values before making field required
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function backfillUserNames() {
  // Update all NULL names to empty string
  await prisma.$executeRaw`
    UPDATE "User"
    SET name = COALESCE(name, '')
    WHERE name IS NULL
  `;

  console.log('Backfill complete');
}

backfillUserNames()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

For comprehensive examples and detailed implementations, see the references/ folder.

typescript

// 在将字段设为必填之前回填默认值
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function backfillUserNames() {
  // 将所有NULL名称更新为空字符串
  await prisma.$executeRaw`
    UPDATE "User"
    SET name = COALESCE(name, '')
    WHERE name IS NULL
  `;

  console.log('回填完成');
}

backfillUserNames()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

如需完整示例和详细实现，请查看 references/ 文件夹。

Production Checklist

生产环境检查清单

Before deployment:

Test migration in staging (with production data copy)
Backup production database
Review migration SQL
Have rollback plan ready

Deployment:

bash

undefined

部署前：

在预发布环境测试迁移（使用生产环境数据副本）
备份生产环境数据库
审核迁移SQL
准备好回滚方案

部署流程：

bash

undefined

1. Backup

1. 备份

pg_dump production_db > backup_$(date +%Y%m%d).sql

2. Deploy code (if backward compatible)

2. 部署代码（如果是向后兼容的）

git push production main

3. Run migrations

3. 执行迁移

npx prisma migrate deploy

4. Verify

4. 验证

npx prisma migrate status

---

npx prisma migrate status

---

Best Practices

最佳实践

Development:

✅ Use
```
prisma migrate dev
```
for iterative changes
✅ Keep migrations small and focused
✅ Review generated SQL before committing

Production:

✅ Always backup before migration
✅ Test in staging first
✅ Use
```
prisma migrate deploy
```
only
✅ Have rollback plan

Safety:

❌ Never delete deployed migrations
❌ Never modify committed migrations
❌ Never use
```
migrate reset
```
in production

开发阶段：

✅ 使用
```
prisma migrate dev
```
进行迭代式变更
✅ 保持迁移内容小而聚焦
✅ 提交前审核生成的SQL

生产环境：

✅ 迁移前始终备份数据库
✅ 先在预发布环境测试
✅ 仅使用
```
prisma migrate deploy
```
✅ 准备回滚方案

安全注意事项：

❌ 切勿删除已部署的迁移
❌ 切勿修改已提交的迁移
❌ 切勿在生产环境中使用
```
migrate reset
```

Progressive Disclosure

进阶参考

For detailed implementations:

Prisma Workflows - Dev workflow, production deployment, reset commands
Migration Strategies - Safe renames, data migrations, breaking changes

如需详细实现：

Prisma工作流 - 开发工作流、生产环境部署、重置命令
迁移策略 - 安全重命名、数据迁移、破坏性变更

References

参考资料

Prisma Workflows
Migration Strategies
Prisma Migrate Docs

Maintained by dsmj-ai-toolkit

Prisma Workflows
Migration Strategies
Prisma Migrate Docs

Maintained by dsmj-ai-toolkit