db-migration-helper

DB Migration Helper

Analyze model changes and generate safe database migration SQL.

Overview

Compare entity/model definitions in the code with the current database schema, detect structural changes (new tables, added/removed columns, type changes), and generate forward-compatible migration SQL. Supports MySQL, PostgreSQL, SQLite.

When to Use

• User wants to create database migrations
• User modified model/entity definitions
• User mentions migration, schema change, or sync
• User says "生成迁移" / "create migration"
• User inputs /db-migration-helper

When NOT to Use:

• User only wants to view database structure
• User wants data migration (not schema)
• User uses ORM auto-migration
• User wants to generate seed data
• User wants to backup/restore database

Core Pattern

Step 1: Detect project type and ORM

Detection file	ORM/Framework	Migration method
prisma/schema.prisma	Prisma	Generate SQL diff
alembic/	SQLAlchemy + Alembic	Generate Alembic migration
migrations/	Generic	Scan existing migrations to infer
*.entity.ts / *.model.ts	TypeORM / Sequelize	Extract from decorators
schema.rb / db/migrate/	Rails	Rails migration

Step 2: Extract current Schema

# Prisma (v2.18+，旧命令 introspect 已更名为 db pull，统一用 db pull)
npx prisma db pull 2>/dev/null

# 通用 — 从代码提取
grep -r "CREATE TABLE\|@Entity\|@Table\|model " --include="*.ts" --include="*.py" --include="*.go" -l

Extract:

• Table names and column definitions
• Column types, constraints (NOT NULL, DEFAULT, UNIQUE)
• Indexes and foreign keys

Step 3: Compare changes

Compare model definitions in the code with the existing schema (or the last migration), detect:

Change type	Risk level	Description
New table	Low	Direct CREATE TABLE
New column (with DEFAULT)	Low	ALTER TABLE ADD COLUMN
New column (without DEFAULT)	Medium	Existing data needs to be handled
Drop column	High	May lose data, requires confirmation
Change column type	High	May be incompatible
Add index	Low	CREATE INDEX
Drop index	Low	DROP INDEX

Step 4: Generate migration file

Use the templates/migration.sql template, generate:

1. Up migration — forward change SQL
2. Down rollback — reverse rollback SQL
3. Risk assessment — flag high-risk operations

-- Migration: 20260603_add_user_avatar
-- Risk: LOW

-- Up
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(500);
CREATE INDEX idx_users_email ON users(email);

-- Down
DROP INDEX idx_users_email;
ALTER TABLE users DROP COLUMN avatar_url;

Quick Reference

/db-migration-helper                    # 检测变更，生成迁移
/db-migration-helper --dry-run          # 只预览 SQL 不执行
/db-migration-helper --name add_user    # 指定迁移名称

Parameter	Description	Default value
--dry-run	Preview only, do not execute	false
--name	Migration file name	Auto-generated
--output	Output directory	./migrations/

Common Mistakes

Mistake	Correct approach	Reason
Not generating down rollback	Always generate rollback SQL	Need to roll back if problems occur
Dropping columns without backup	Back up data before dropping columns	Data loss is unrecoverable
Changing type with ALTER COLUMN	PostgreSQL can directly ALTER COLUMN ... TYPE ... USING; MySQL uses create new column → migrate data → drop old column	MySQL changing type directly may lose data, PG's USING is an atomic conversion
Not adding indexes	Add indexes for query fields	Affects query performance
Migration files have no name	Use descriptive naming	Facilitates team collaboration and tracing
Not checking foreign key dependencies	Check relationships between tables first	Dropping a referenced column will fail