Database Migration Manager

Detect. Generate. Migrate. Roll back. Zero downtime.

Phase 1: Detect Migration Tool

Scan the project to identify the ORM/migration tool in use. Check in this order:

Signal	Tool
INLINECODE0 or `schema.prisma` in root	Prisma
INLINECODE2 or `drizzle.config.js` or `drizzle/` dir

If no tool is detected, ask the user which they want to use. If starting fresh, recommend Drizzle for TypeScript projects, Alembic for Python, Django migrations for Django apps.

Read the existing migration history to understand current schema state before doing anything.

Phase 2: Determine the Operation

Classify what the user needs:

Operation	Description
generate	Create a new migration from schema changes
run

If the user's request implies multiple operations (e.g., "migrate production safely"), chain them: backup -> diff -> generate -> run.

Phase 3: Execute by Tool

Prisma

CODEBLOCK0

Prisma rollback: Prisma has no built-in rollback command. To roll back:

1. Create a new migration that reverses the changes
Or restore from backup
For failed migrations: INLINECODE15

Prisma seed setup: Ensure package.json has:
CODEBLOCK1

Drizzle

CODEBLOCK2

Drizzle rollback: Drizzle generates SQL files. To roll back:

1. Write a new migration that reverses changes
Or delete the migration file and re-push (dev only)
For production: always use generated SQL files, never INLINECODE17

Drizzle seed: Create a seed.ts file and run with tsx drizzle/seed.ts or add a script to package.json.

Knex

CODEBLOCK3

Knex migration template:
CODEBLOCK4

TypeORM

CODEBLOCK5

Alembic (Python/SQLAlchemy)

CODEBLOCK6

Django

CODEBLOCK7

Raw SQL

When no ORM is detected and the project uses raw SQL:

1. Create a migrations/ directory if it doesn't exist
Name files with timestamp prefix: INLINECODE22
Each migration file should have -- UP and -- DOWN sections
Track applied migrations in a _migrations table

Raw SQL migration template:
CODEBLOCK8

Migration tracking table:
CODEBLOCK9

Phase 4: Database Backup

Always back up before running migrations in production. Offer to back up before any destructive operation.

Postgres

CODEBLOCK10

MySQL

CODEBLOCK11

SQLite

CODEBLOCK12

Phase 5: Schema Diff Between Environments

Compare schemas to detect drift between environments.

Prisma

CODEBLOCK13

Drizzle

CODEBLOCK14

Generic SQL Diff (Postgres)

CODEBLOCK15

Generic SQL Diff (MySQL)

CODEBLOCK16

For richer diffs, use migra (Postgres):
CODEBLOCK17

Phase 6: Seed Data

When creating seed scripts, follow these principles:

1. Idempotent: Seeds must be safe to run multiple times (use upserts or check-before-insert)
Environment-aware: Different seed data for dev vs. staging vs. production
Realistic: Use realistic data shapes, not lorem ipsum
Referential integrity: Insert in dependency order (users before posts, etc.)
Deterministic: Use fixed IDs or consistent generation for reproducibility

Seed Script Structure

CODEBLOCK18

Python Seed Pattern (Alembic/Django)

CODEBLOCK19

CODEBLOCK20

Phase 7: Zero-Downtime Migrations

Use the expand-contract pattern for any schema change that could break running application code. This is mandatory for production deployments with rolling updates or blue-green deploys.

When to Use Zero-Downtime Patterns

Change	Safe Without Pattern?	Zero-Downtime Required?
Add nullable column	Yes	No
Add column with default

Yes (Postgres 11+, MySQL 8.0.12+) | No | | Add NOT NULL column | No | Yes | | Rename column | No | Yes | | Change column type | No | Yes | | Drop column | No | Yes | | Add index | Depends on size | Use CONCURRENTLY | | Add foreign key | No (locks table) | Yes |

The Expand-Contract Pattern

Step 1: Expand (Deploy migration + new code that writes to both)
CODEBLOCK21

Step 2: Backfill (Run as background job, batch processing)
CODEBLOCK22

Step 3: Contract (Deploy code that only reads from new column)
CODEBLOCK23

Safe Index Creation

CODEBLOCK24

Safe Foreign Key Addition (Postgres)

CODEBLOCK25

Rename Column Pattern

Never rename directly. Instead:

1. Add new column
Deploy code that writes to both old and new
Backfill new column from old
Deploy code that reads from new column only
Drop old column after bake period

Multi-Deploy Migration Checklist

For any breaking schema change, split across multiple deploys:

- [ ] Deploy 1: Add new column/table (expand)
[ ] Deploy 2: Update writes to populate both old and new
[ ] Deploy 3: Backfill existing data
[ ] Deploy 4: Switch reads to new column/table
[ ] Deploy 5: Stop writing to old column
[ ] Deploy 6: Drop old column/table (contract) — after bake period

Safety Rules

1. Never run migrate reset, push, or db push in production — these are dev-only commands
Always back up before migrating production — offer this proactively
Never drop columns or tables without confirming with the user — even if the schema change implies it
Review generated SQL before applying — auto-generated migrations can be destructive
Test migrations on a staging copy of production data — operations that take milliseconds on dev can lock for minutes on large tables
Monitor during production migrations — watch replication lag, CPU, lock contention
Keep migrations small and focused — one concern per migration file
Never skip the down/rollback function — every up needs a corresponding down
Use transactions where supported — Postgres DDL is transactional, MySQL is not (most DDL auto-commits)
Check for active connections before dropping — INLINECODE30

Error Recovery

If a migration fails mid-way:

1. Check migration status — which migrations applied, which failed
Do NOT re-run blindly — understand what partially applied
For Prisma: npx prisma migrate resolve --rolled-back <name> to mark as rolled back
For Alembic: alembic stamp <last_good_revision> to reset tracking
For Django: Fix the issue, then python3 manage.py migrate again (Django tracks per-migration)
For Knex: Check knex_migrations table, manually remove failed entry if needed
Restore from backup if the database is in an inconsistent state

Environment Variable Detection

Read database connection from the project's environment:

Variable	Common In
INLINECODE35	Prisma, Drizzle, general
INLINECODE36 + `DB_PORT` + `DB_NAME` + `DB_USER` + INLINECODE40

Check .env, .env.local, .env.development, and .env.production for these values. Never log or display connection strings — they contain credentials.

数据库迁移管理器

检测。生成。迁移。回滚。零停机。

第一阶段：检测迁移工具

扫描项目以识别正在使用的ORM/迁移工具。按以下顺序检查：

信号	工具
prisma/schema.prisma 或根目录下的 schema.prisma	Prisma
drizzle.config.ts 或 drizzle.config.js 或 drizzle/ 目录

如果未检测到任何工具，询问用户想要使用哪个。如果是全新开始，推荐 TypeScript 项目使用 Drizzle，Python 项目使用 Alembic，Django 应用使用 Django 迁移。

在执行任何操作前，先读取现有的迁移历史以了解当前模式状态。

第二阶段：确定操作

分类用户需求：

操作	描述
generate	从模式变更创建新迁移
run

如果用户的请求暗示多个操作（例如，安全地迁移生产环境），则链式执行：备份 -> 差异比较 -> 生成 -> 运行。

第三阶段：按工具执行

Prisma

bash

从模式变更生成迁移

npx prisma migrate dev --name <迁移名称>

在生产环境应用（无交互提示）

npx prisma migrate deploy

重置数据库（仅开发环境——会销毁数据）

npx prisma migrate reset

检查迁移状态

npx prisma migrate status

模式变更后生成客户端

npx prisma generate

种子数据

npx prisma db seed

从现有数据库拉取模式

npx prisma db pull

推送模式而不生成迁移文件（仅开发环境）

npx prisma db push

Prisma 回滚：Prisma 没有内置的回滚命令。要回滚：

1. 创建一个反向变更的新迁移
或者从备份恢复
对于失败的迁移：npx prisma migrate resolve --rolled-back <迁移名称>

Prisma 种子设置：确保 package.json 包含：
json
{
prisma: {
seed: tsx prisma/seed.ts
}
}

Drizzle

bash

从模式变更生成迁移

npx drizzle-kit generate

应用迁移

npx drizzle-kit migrate

直接推送模式（仅开发环境——有破坏性）

npx drizzle-kit push

从现有数据库拉取模式

npx drizzle-kit pull

打开 Drizzle Studio（可视化浏览器）

npx drizzle-kit studio

检查当前状态

npx drizzle-kit check

Drizzle 回滚：Drizzle 生成 SQL 文件。要回滚：

1. 编写一个反向变更的新迁移
或者删除迁移文件并重新推送（仅开发环境）
对于生产环境：始终使用生成的 SQL 文件，绝不使用 push

Drizzle 种子：创建一个 seed.ts 文件并使用 tsx drizzle/seed.ts 运行，或向 package.json 添加脚本。

Knex

bash

创建新的迁移文件

npx knex migrate:make <迁移名称>

运行待处理的迁移

npx knex migrate:latest

回滚上一批

npx knex migrate:rollback

回滚所有迁移

npx knex migrate:rollback --all

检查迁移状态

npx knex migrate:status

运行种子文件

npx knex seed:run

创建新的种子文件

npx knex seed:make <种子名称>

Knex 迁移模板：
js
exports.up = function(knex) {
return knex.schema.createTable(表名, (table) => {
table.increments(id).primary();
table.timestamps(true, true);
});
};

exports.down = function(knex) {
return knex.schema.dropTable(表名);
};

TypeORM

bash

从实体变更生成迁移

npx typeorm migration:generate -d src/data-source.ts src/migrations/<迁移名称>

创建空迁移

npx typeorm migration:create src/migrations/<迁移名称>

运行待处理的迁移

npx typeorm migration:run -d src/data-source.ts

回滚上一次迁移

npx typeorm migration:revert -d src/data-source.ts

显示迁移和状态

npx typeorm migration:show -d src/data-source.ts

Alembic (Python/SQLAlchemy)

bash

从模型变更自动生成迁移

alembic revision --autogenerate -m 变更描述

创建空迁移

alembic revision -m 变更描述

应用所有待处理的迁移

alembic upgrade head

升级到特定修订版本

alembic upgrade <修订版本ID>

回滚一步

alembic downgrade -1

回滚到特定修订版本

alembic downgrade <修订版本ID>

显示当前修订版本

alembic current

显示迁移历史

alembic history

显示所有分支头

alembic heads

Django

bash

从模型变更生成迁移

python3 manage.py makemigrations

为特定应用生成

python3 manage.py makemigrations <应用名称>

应用所有待处理的迁移

python3 manage.py migrate

应用特定应用的迁移

python3 manage.py migrate <应用名称> <迁移编号>

回滚到特定迁移

python3 manage.py migrate <应用名称> <迁移编号>

回滚应用的所有迁移

python3 manage.py migrate <应用名称> zero

显示迁移状态

python3 manage.py showmigrations

生成SQL而不应用

python3 manage.py sqlmigrate <应用名称> <迁移编号>

创建空迁移

python3 manage.py makemigrations --empty <应用名称>

原始 SQL

当未检测到ORM且项目使用原始SQL时：

1. 如果不存在，创建 migrations/ 目录
使用时间戳前缀命名文件：YYYYMMDDHHMMSS描述.sql
每个迁移文件应包含 -- UP 和 -- DOWN 部分
在 migrations 表中跟踪已应用的迁移

原始 SQL 迁移模板：
sql
-- UP
CREATE TABLE IF NOT EXISTS example (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

-- DOWN
DROP TABLE IF EXISTS example;

迁移跟踪表：
sql
CREATE TABLE IF NOT EXISTS _migrations (
id SERIAL PRIMARY KEY,
filename VARCHAR(255) NOT NULL UNIQUE,
applied_at TIMESTAMP DEFAULT NOW()
);

第四阶段：数据库备份

在生产环境运行迁移前始终进行备份。 在任何破坏性操作前主动提供备份。

Postgres

bash

完整数据库转储（压缩）

pgdump -Fc -f backup$(date +%Y%m%d%H%M%S).dump $DATABASEURL

仅模式

pgdump --schema-only -f schema$(date +%Y%m%d%H%M%S).sql $DATABASEURL

特定表

pgdump -t 表名 -f tablebackup.dump $DATABASE_URL

从转储恢复

pgrestore -d $DATABASEURL backup.dump

从SQL恢复

psql $DATABASE_URL < backup.sql

角色和全局对象（完整恢复所需）

pg_dumpall --globals-only -f globals.sql

MySQL

bash

完整数据库转储

mysqldump -u $DBUSER -p $DBNAME > backup$(date +%Y%m%d%H%M%S).sql

仅模式

mysqldump -u $DB_USER -p --no-data $DB

db-migrate数据库迁移管理

db-migrate

Database Migration Manager

Phase 1: Detect Migration Tool

Phase 2: Determine the Operation

Phase 3: Execute by Tool

Prisma

Drizzle

Knex

TypeORM

Alembic (Python/SQLAlchemy)

Django

Raw SQL

Phase 4: Database Backup

Postgres

MySQL

SQLite

Phase 5: Schema Diff Between Environments

Prisma

Drizzle

Generic SQL Diff (Postgres)

Generic SQL Diff (MySQL)

Phase 6: Seed Data

Seed Script Structure

Python Seed Pattern (Alembic/Django)

Phase 7: Zero-Downtime Migrations

When to Use Zero-Downtime Patterns

The Expand-Contract Pattern

Safe Index Creation

Safe Foreign Key Addition (Postgres)

Rename Column Pattern

Multi-Deploy Migration Checklist

Safety Rules

Error Recovery

Environment Variable Detection

数据库迁移管理器

第一阶段：检测迁移工具

第二阶段：确定操作

第三阶段：按工具执行

Prisma

从模式变更生成迁移

在生产环境应用（无交互提示）

重置数据库（仅开发环境——会销毁数据）

检查迁移状态

模式变更后生成客户端

种子数据

从现有数据库拉取模式

推送模式而不生成迁移文件（仅开发环境）

Drizzle

从模式变更生成迁移

应用迁移

直接推送模式（仅开发环境——有破坏性）

从现有数据库拉取模式

打开 Drizzle Studio（可视化浏览器）

检查当前状态

Knex

创建新的迁移文件

运行待处理的迁移

回滚上一批

回滚所有迁移

检查迁移状态

运行种子文件

创建新的种子文件

TypeORM

从实体变更生成迁移

创建空迁移

运行待处理的迁移

回滚上一次迁移

显示迁移和状态

Alembic (Python/SQLAlchemy)

从模型变更自动生成迁移

创建空迁移

应用所有待处理的迁移

升级到特定修订版本

回滚一步

回滚到特定修订版本

显示当前修订版本

显示迁移历史

显示所有分支头

Django