drizzle-migrations

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Drizzle ORM Database Migrations (TypeScript)

Drizzle ORM 数据库迁移（TypeScript）

Migration-first database development workflow using Drizzle ORM for TypeScript/JavaScript projects.

基于Drizzle ORM的TypeScript/JavaScript项目优先使用迁移的数据库开发工作流。

When to Use This Skill

何时使用此技能

Use this skill when:

Working with Drizzle ORM in TypeScript/JavaScript projects
Need to create or modify database schema
Want migration-first development workflow
Setting up new database tables or columns
Need to ensure schema consistency across environments

在以下场景中使用此技能：

在TypeScript/JavaScript项目中使用Drizzle ORM
需要创建或修改数据库架构
希望采用优先使用迁移的开发工作流
搭建新的数据库表或列
需要确保不同环境间的架构一致性

Core Principle: Migration-First Development

核心原则：优先迁移开发

Critical Rule: Schema changes ALWAYS start with migrations, never code-first.

关键规则：架构变更始终从迁移开始，绝不是代码优先。

Why Migration-First?

为什么优先迁移？

✅ SQL migrations are the single source of truth
✅ Prevents schema drift between environments
✅ Enables rollback and versioning
✅ Forces explicit schema design decisions
✅ TypeScript types generated from migrations
✅ CI/CD can validate schema changes

✅ SQL迁移是唯一的可信来源
✅ 防止不同环境间的架构漂移
✅ 支持回滚和版本控制
✅ 强制明确的架构设计决策
✅ 从迁移生成TypeScript类型
✅ CI/CD可以验证架构变更

Anti-Pattern (Code-First)

反模式（代码优先）

❌ WRONG: Writing TypeScript schema first

typescript

// DON'T DO THIS FIRST
export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
});

❌ 错误做法：先编写TypeScript架构

typescript

// 不要先这么做
export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
});

Correct Pattern (Migration-First)

正确模式（优先迁移）

✅ CORRECT: Write SQL migration first

sql

-- drizzle/0001_add_users_table.sql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

✅ 正确做法：先编写SQL迁移

sql

-- drizzle/0001_add_users_table.sql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Complete Migration Workflow

完整迁移工作流

Step 1: Design Schema in SQL Migration

步骤1：在SQL迁移中设计架构

Create descriptive SQL migration file:

sql

-- drizzle/0001_create_school_calendars.sql
CREATE TABLE school_calendars (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  academic_year TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Add indexes for query performance
CREATE INDEX idx_school_calendars_school_id ON school_calendars(school_id);
CREATE INDEX idx_school_calendars_academic_year ON school_calendars(academic_year);

-- Add constraints
ALTER TABLE school_calendars
  ADD CONSTRAINT check_date_range
  CHECK (end_date > start_date);

Naming Convention:

Use sequential numbers:
```
0001_
```
,
```
0002_
```
, etc.
Descriptive names:
```
create_school_calendars
```
,
```
add_user_roles
```
Format:
```
XXXX_descriptive_name.sql
```

创建描述性的SQL迁移文件：

sql

-- drizzle/0001_create_school_calendars.sql
CREATE TABLE school_calendars (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  academic_year TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- 为查询性能添加索引
CREATE INDEX idx_school_calendars_school_id ON school_calendars(school_id);
CREATE INDEX idx_school_calendars_academic_year ON school_calendars(academic_year);

-- 添加约束
ALTER TABLE school_calendars
  ADD CONSTRAINT check_date_range
  CHECK (end_date > start_date);

命名规范:

使用连续编号：
```
0001_
```
,
```
0002_
```
等
描述性名称：
```
create_school_calendars
```
,
```
add_user_roles
```
格式：
```
XXXX_descriptive_name.sql
```

Step 2: Generate TypeScript Definitions

步骤2：生成TypeScript定义

Drizzle Kit generates TypeScript types from SQL:

bash

undefined

Drizzle Kit从SQL生成TypeScript类型：

bash

undefined

Generate TypeScript schema and snapshots

生成TypeScript架构和快照

pnpm drizzle-kit generate

Or using npm

或使用npm

npm run db:generate


**What This Creates**:
1. TypeScript schema files (if using `drizzle-kit push`)
2. Snapshot files in `drizzle/meta/XXXX_snapshot.json`
3. Migration metadata

npm run db:generate


**此操作会创建**:
1. TypeScript架构文件（如果使用`drizzle-kit push`）
2. `drizzle/meta/XXXX_snapshot.json`中的快照文件
3. 迁移元数据

Step 3: Create Schema Snapshot

步骤3：创建架构快照

Snapshots enable schema drift detection:

json

// drizzle/meta/0001_snapshot.json (auto-generated)
{
  "version": "5",
  "dialect": "postgresql",
  "tables": {
    "school_calendars": {
      "name": "school_calendars",
      "columns": {
        "id": {
          "name": "id",
          "type": "uuid",
          "primaryKey": true,
          "notNull": true,
          "default": "gen_random_uuid()"
        },
        "school_id": {
          "name": "school_id",
          "type": "uuid",
          "notNull": true
        }
      }
    }
  }
}

Snapshots in Version Control:

✅ Commit snapshots to git
✅ Enables drift detection in CI
✅ Documents schema history

快照可检测架构漂移：

json

// drizzle/meta/0001_snapshot.json（自动生成）
{
  "version": "5",
  "dialect": "postgresql",
  "tables": {
    "school_calendars": {
      "name": "school_calendars",
      "columns": {
        "id": {
          "name": "id",
          "type": "uuid",
          "primaryKey": true,
          "notNull": true,
          "default": "gen_random_uuid()"
        },
        "school_id": {
          "name": "school_id",
          "type": "uuid",
          "notNull": true
        }
      }
    }
  }
}

版本控制中的快照:

✅ 将快照提交到git
✅ 在CI中启用漂移检测
✅ 记录架构历史

Step 4: Implement TypeScript Schema

步骤4：实现TypeScript架构

Now write TypeScript schema that mirrors SQL migration:

typescript

// src/lib/db/schema/school/calendar.ts
import { pgTable, uuid, date, text, timestamp } from 'drizzle-orm/pg-core';
import { schools } from './school';

export const schoolCalendars = pgTable('school_calendars', {
  id: uuid('id').primaryKey().defaultRandom(),
  schoolId: uuid('school_id')
    .notNull()
    .references(() => schools.id, { onDelete: 'cascade' }),
  startDate: date('start_date').notNull(),
  endDate: date('end_date').notNull(),
  academicYear: text('academic_year').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Type inference
export type SchoolCalendar = typeof schoolCalendars.$inferSelect;
export type NewSchoolCalendar = typeof schoolCalendars.$inferInsert;

Key Points:

Column names match SQL exactly:
```
school_id
```
→
```
'school_id'
```
TypeScript property names use camelCase:
```
schoolId
```
Constraints and indexes defined in SQL, not TypeScript
Foreign keys reference other tables

现在编写与SQL迁移一致的TypeScript架构：

typescript

// src/lib/db/schema/school/calendar.ts
import { pgTable, uuid, date, text, timestamp } from 'drizzle-orm/pg-core';
import { schools } from './school';

export const schoolCalendars = pgTable('school_calendars', {
  id: uuid('id').primaryKey().defaultRandom(),
  schoolId: uuid('school_id')
    .notNull()
    .references(() => schools.id, { onDelete: 'cascade' }),
  startDate: date('start_date').notNull(),
  endDate: date('end_date').notNull(),
  academicYear: text('academic_year').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// 类型推断
export type SchoolCalendar = typeof schoolCalendars.$inferSelect;
export type NewSchoolCalendar = typeof schoolCalendars.$inferInsert;

关键点:

列名与SQL完全匹配：
```
school_id
```
→
```
'school_id'
```
TypeScript属性名使用驼峰式：
```
schoolId
```
约束和索引在SQL中定义，而非TypeScript
外键引用其他表

Step 5: Organize Schemas by Domain

步骤5：按领域组织架构

Structure schemas for maintainability:

src/lib/db/schema/
├── index.ts              # Export all schemas
├── school/
│   ├── index.ts
│   ├── district.ts
│   ├── holiday.ts
│   ├── school.ts
│   └── calendar.ts
├── providers.ts
├── cart.ts
└── users.ts

index.ts (export all):

typescript

// src/lib/db/schema/index.ts
export * from './school';
export * from './providers';
export * from './cart';
export * from './users';

school/index.ts:

typescript

// src/lib/db/schema/school/index.ts
export * from './district';
export * from './holiday';
export * from './school';
export * from './calendar';

结构化架构以提高可维护性：

src/lib/db/schema/
├── index.ts              # 导出所有架构
├── school/
│   ├── index.ts
│   ├── district.ts
│   ├── holiday.ts
│   ├── school.ts
│   └── calendar.ts
├── providers.ts
├── cart.ts
└── users.ts

index.ts（导出所有）:

typescript

// src/lib/db/schema/index.ts
export * from './school';
export * from './providers';
export * from './cart';
export * from './users';

school/index.ts:

typescript

// src/lib/db/schema/school/index.ts
export * from './district';
export * from './holiday';
export * from './school';
export * from './calendar';

Step 6: Add Quality Check to CI

步骤6：在CI中添加质量检查

Validate schema consistency in CI/CD:

yaml

undefined

在CI/CD中验证架构一致性：

yaml

undefined

.github/workflows/quality.yml

name: Quality Checks

on: pull_request: branches: [main, develop] push: branches: [main]

jobs: quality: runs-on: ubuntu-latest

steps:
  - uses: actions/checkout@v4

  - name: Setup Node.js
    uses: actions/setup-node@v4
    with:
      node-version: '20'
      cache: 'pnpm'

  - name: Install dependencies
    run: pnpm install --frozen-lockfile

  - name: Check database schema drift
    run: pnpm drizzle-kit check

  - name: Verify migrations (dry-run)
    run: pnpm drizzle-kit push --dry-run
    env:
      DATABASE_URL: ${{ secrets.STAGING_DATABASE_URL }}

  - name: Run type checking
    run: pnpm tsc --noEmit

  - name: Lint code
    run: pnpm lint


**CI Checks Explained**:
- `drizzle-kit check`: Validates snapshots match schema
- `drizzle-kit push --dry-run`: Tests migration without applying
- Type checking: Ensures TypeScript compiles
- Linting: Enforces code style

name: Quality Checks

on: pull_request: branches: [main, develop] push: branches: [main]

jobs: quality: runs-on: ubuntu-latest

steps:
  - uses: actions/checkout@v4

  - name: Setup Node.js
    uses: actions/setup-node@v4
    with:
      node-version: '20'
      cache: 'pnpm'

  - name: Install dependencies
    run: pnpm install --frozen-lockfile

  - name: Check database schema drift
    run: pnpm drizzle-kit check

  - name: Verify migrations (dry-run)
    run: pnpm drizzle-kit push --dry-run
    env:
      DATABASE_URL: ${{ secrets.STAGING_DATABASE_URL }}

  - name: Run type checking
    run: pnpm tsc --noEmit

  - name: Lint code
    run: pnpm lint


**CI检查说明**:
- `drizzle-kit check`: 验证快照与架构是否匹配
- `drizzle-kit push --dry-run`: 测试迁移但不实际应用
- 类型检查：确保TypeScript可以编译
- 代码检查：强制执行代码风格

Step 7: Test on Staging

步骤7：在预发布环境测试

Before production, test migration on staging:

bash

undefined

上线前，在预发布环境测试迁移：

bash

undefined

1. Run migration on staging

1. 在预发布环境运行迁移

STAGING_DATABASE_URL="..." pnpm drizzle-kit push

2. Verify schema

2. 验证架构

pnpm drizzle-kit check

3. Test affected API routes

3. 测试受影响的API路由

curl https://staging.example.com/api/schools/calendars

4. Check for data integrity issues

4. 检查数据完整性问题

Run queries to verify data looks correct

运行查询验证数据是否正确

5. Monitor logs for errors

5. 监控日志中的错误

Check application logs for migration-related errors

检查应用日志中与迁移相关的错误


**Staging Checklist**:
- [ ] Migration runs without errors
- [ ] Schema drift check passes
- [ ] API routes using new schema work correctly
- [ ] No data integrity issues
- [ ] Application logs show no errors
- [ ] Query performance acceptable


**预发布环境检查清单**:
- [ ] 迁移运行无错误
- [ ] 架构漂移检查通过
- [ ] 使用新架构的API路由正常工作
- [ ] 无数据完整性问题
- [ ] 应用日志无错误
- [ ] 查询性能可接受

Common Migration Patterns

常见迁移模式

Adding a Column

添加列

sql

-- drizzle/0005_add_user_phone.sql
ALTER TABLE users
ADD COLUMN phone TEXT;

-- Add index if querying by phone
CREATE INDEX idx_users_phone ON users(phone);

TypeScript:

typescript

export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
  phone: text('phone'), // New column
});

sql

-- drizzle/0005_add_user_phone.sql
ALTER TABLE users
ADD COLUMN phone TEXT;

-- 如果按手机号查询则添加索引
CREATE INDEX idx_users_phone ON users(phone);

TypeScript:

typescript

export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
  phone: text('phone'), // 新列
});

Creating a Junction Table

创建关联表

sql

-- drizzle/0006_create_provider_specialties.sql
CREATE TABLE provider_specialties (
  provider_id UUID NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
  specialty_id UUID NOT NULL REFERENCES specialties(id) ON DELETE CASCADE,
  PRIMARY KEY (provider_id, specialty_id)
);

CREATE INDEX idx_provider_specialties_provider ON provider_specialties(provider_id);
CREATE INDEX idx_provider_specialties_specialty ON provider_specialties(specialty_id);

TypeScript:

typescript

export const providerSpecialties = pgTable('provider_specialties', {
  providerId: uuid('provider_id')
    .notNull()
    .references(() => providers.id, { onDelete: 'cascade' }),
  specialtyId: uuid('specialty_id')
    .notNull()
    .references(() => specialties.id, { onDelete: 'cascade' }),
}, (table) => ({
  pk: primaryKey(table.providerId, table.specialtyId),
}));

sql

-- drizzle/0006_create_provider_specialties.sql
CREATE TABLE provider_specialties (
  provider_id UUID NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
  specialty_id UUID NOT NULL REFERENCES specialties(id) ON DELETE CASCADE,
  PRIMARY KEY (provider_id, specialty_id)
);

CREATE INDEX idx_provider_specialties_provider ON provider_specialties(provider_id);
CREATE INDEX idx_provider_specialties_specialty ON provider_specialties(specialty_id);

TypeScript:

typescript

export const providerSpecialties = pgTable('provider_specialties', {
  providerId: uuid('provider_id')
    .notNull()
    .references(() => providers.id, { onDelete: 'cascade' }),
  specialtyId: uuid('specialty_id')
    .notNull()
    .references(() => specialties.id, { onDelete: 'cascade' }),
}, (table) => ({
  pk: primaryKey(table.providerId, table.specialtyId),
}));

Modifying Column Type

修改列类型

sql

-- drizzle/0007_change_price_to_decimal.sql
ALTER TABLE services
ALTER COLUMN price TYPE DECIMAL(10, 2);

TypeScript:

typescript

import { decimal } from 'drizzle-orm/pg-core';

export const services = pgTable('services', {
  id: uuid('id').primaryKey(),
  name: text('name').notNull(),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
});

sql

-- drizzle/0007_change_price_to_decimal.sql
ALTER TABLE services
ALTER COLUMN price TYPE DECIMAL(10, 2);

TypeScript:

typescript

import { decimal } from 'drizzle-orm/pg-core';

export const services = pgTable('services', {
  id: uuid('id').primaryKey(),
  name: text('name').notNull(),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
});

Adding Constraints

添加约束

sql

-- drizzle/0008_add_email_constraint.sql
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

ALTER TABLE users
ADD CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

sql

-- drizzle/0008_add_email_constraint.sql
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

ALTER TABLE users
ADD CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

Configuration

配置

drizzle.config.ts

typescript

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/lib/db/schema/index.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

typescript

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/lib/db/schema/index.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

package.json Scripts

package.json 脚本

json

{
  "scripts": {
    "db:generate": "drizzle-kit generate:pg",
    "db:push": "drizzle-kit push:pg",
    "db:studio": "drizzle-kit studio",
    "db:check": "drizzle-kit check:pg",
    "db:up": "drizzle-kit up:pg"
  }
}

json

{
  "scripts": {
    "db:generate": "drizzle-kit generate:pg",
    "db:push": "drizzle-kit push:pg",
    "db:studio": "drizzle-kit studio",
    "db:check": "drizzle-kit check:pg",
    "db:up": "drizzle-kit up:pg"
  }
}

Migration Testing Workflow

迁移测试工作流

Local Testing

本地测试

bash

undefined

bash

undefined

1. Create migration

1. 创建迁移

echo "CREATE TABLE test (...)" > drizzle/0009_test.sql

2. Generate TypeScript

2. 生成TypeScript

pnpm db:generate

3. Push to local database

3. 推送到本地数据库

pnpm db:push

4. Verify schema

4. 验证架构

pnpm db:check

5. Test in application

5. 在应用中测试

pnpm dev

Manually test affected features

手动测试受影响的功能

6. Run tests

6. 运行测试

pnpm test

undefined

pnpm test

undefined

Rollback Strategy

回滚策略

sql

-- drizzle/0010_add_feature.sql (up migration)
CREATE TABLE new_feature (...);

-- drizzle/0010_add_feature_down.sql (down migration)
DROP TABLE new_feature;

Apply rollback:

bash

undefined

sql

-- drizzle/0010_add_feature.sql（升级迁移）
CREATE TABLE new_feature (...);

-- drizzle/0010_add_feature_down.sql（回滚迁移）
DROP TABLE new_feature;

执行回滚：

bash

undefined

Manually run down migration

手动运行回滚迁移

psql $DATABASE_URL -f drizzle/0010_add_feature_down.sql

undefined

psql $DATABASE_URL -f drizzle/0010_add_feature_down.sql

undefined

Best Practices

最佳实践

Do's

建议做法

✅ Write SQL migrations first
✅ Use descriptive migration names
✅ Add indexes for foreign keys
✅ Include constraints in migrations
✅ Test migrations on staging before production
✅ Commit snapshots to version control
✅ Organize schemas by domain
✅ Use
```
drizzle-kit check
```
in CI

✅ 先编写SQL迁移
✅ 使用描述性的迁移名称
✅ 为外键添加索引
✅ 在迁移中包含约束
✅ 上线前在预发布环境测试迁移
✅ 将快照提交到版本控制
✅ 按领域组织架构
✅ 在CI中使用
```
drizzle-kit check
```

Don'ts

禁止做法

❌ Never write TypeScript schema before SQL migration
❌ Don't skip staging testing
❌ Don't modify old migrations (create new ones)
❌ Don't forget to add indexes
❌ Don't use
```
drizzle-kit push
```
in production (use proper migrations)
❌ Don't commit generated files without snapshots

❌ 绝不要在SQL迁移前编写TypeScript架构
❌ 不要跳过预发布环境测试
❌ 不要修改旧迁移（创建新的迁移）
❌ 不要忘记添加索引
❌ 不要在生产环境使用
```
drizzle-kit push
```
（使用正式迁移）
❌ 不要在没有快照的情况下提交生成的文件

Troubleshooting

故障排除

Schema Drift Detected

检测到架构漂移

Error:

Schema drift detected

Solution:

bash

undefined

错误：

Schema drift detected

解决方案:

bash

undefined

Check what changed

检查变更内容

pnpm drizzle-kit check

Regenerate snapshots

重新生成快照

pnpm drizzle-kit generate

Review changes and commit

查看变更并提交

git add drizzle/meta/ git commit -m "Update schema snapshots"

undefined

git add drizzle/meta/ git commit -m "更新架构快照"

undefined

Migration Fails on Staging

预发布环境迁移失败

Error: Migration fails with data constraint violation

Solution:

Rollback migration
Create data migration script
Run data migration first
Then run schema migration

sql

-- First: Migrate data
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Then: Add constraint
ALTER TABLE users
ALTER COLUMN status SET NOT NULL;

错误：迁移因数据约束冲突失败

解决方案:

回滚迁移
创建数据迁移脚本
先运行数据迁移
再运行架构迁移

sql

undefined

TypeScript Types Out of Sync

首先：迁移数据

Error: TypeScript types don't match database

Solution:

bash

undefined

UPDATE users SET status = 'active' WHERE status IS NULL;

Regenerate everything

然后：添加约束

pnpm db:generate pnpm tsc --noEmit

ALTER TABLE users ALTER COLUMN status SET NOT NULL;

undefined

If still broken, check schema files

TypeScript类型不同步

Ensure column names match SQL exactly

—

undefined

错误：TypeScript类型与数据库不匹配

解决方案:

bash

undefined

Related Skills

重新生成所有内容

```
universal-data-database-migration
```
- Universal migration patterns
```
toolchains-typescript-data-drizzle
```
- Drizzle ORM usage patterns
```
toolchains-typescript-core
```
- TypeScript best practices

universal-debugging-verification-before-completion

- Verification workflows

pnpm db:generate pnpm tsc --noEmit

—

如果仍然有问题，检查架构文件

—

确保列名与SQL完全匹配

—

undefined

—