Back to Details

database-migration-helper

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Migration Helper

数据库迁移助手

Create and manage database migrations safely with proper rollback support.
安全地创建和管理数据库迁移，提供完善的回滚支持。

Quick Start

快速开始

Create migration files with up/down functions, test locally, backup before production, run migrations incrementally.
创建包含up/down函数的迁移文件，先在本地测试，生产环境执行前备份数据库，增量运行迁移。

Instructions

操作指南

Migration Structure

迁移文件结构

Basic migration:
javascript
// migrations/001_create_users_table.js
exports.up = async (db) => {
  await db.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email').unique().notNullable();
    table.string('password_hash').notNullable();
    table.timestamps(true, true);
  });
};

exports.down = async (db) => {
  await db.schema.dropTable('users');
};
基础迁移示例：
javascript
// migrations/001_create_users_table.js
exports.up = async (db) => {
  await db.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email').unique().notNullable();
    table.string('password_hash').notNullable();
    table.timestamps(true, true);
  });
};

exports.down = async (db) => {
  await db.schema.dropTable('users');
};

Creating Tables

创建数据表

With Knex:
javascript
exports.up = async (knex) => {
  await knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.integer('user_id').unsigned().notNullable();
    table.string('title', 200).notNullable();
    table.text('content');
    table.enum('status', ['draft', 'published', 'archived']).defaultTo('draft');
    table.timestamps(true, true);
    
    // Foreign key
    table.foreign('user_id').references('users.id').onDelete('CASCADE');
    
    // Indexes
    table.index('user_id');
    table.index('status');
  });
};

exports.down = async (knex) => {
  await knex.schema.dropTable('posts');
};
With raw SQL:
sql
-- migrations/001_create_users.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

-- migrations/001_create_users.down.sql
DROP TABLE IF EXISTS users;
使用Knex：
javascript
exports.up = async (knex) => {
  await knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.integer('user_id').unsigned().notNullable();
    table.string('title', 200).notNullable();
    table.text('content');
    table.enum('status', ['draft', 'published', 'archived']).defaultTo('draft');
    table.timestamps(true, true);
    
    // Foreign key
    table.foreign('user_id').references('users.id').onDelete('CASCADE');
    
    // Indexes
    table.index('user_id');
    table.index('status');
  });
};

exports.down = async (knex) => {
  await knex.schema.dropTable('posts');
};
使用原生SQL：
sql
-- migrations/001_create_users.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

-- migrations/001_create_users.down.sql
DROP TABLE IF EXISTS users;

Adding Columns

添加列

javascript
exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.string('phone', 20);
    table.boolean('is_verified').defaultTo(false);
  });
};

exports.down = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.dropColumn('phone');
    table.dropColumn('is_verified');
  });
};
javascript
exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.string('phone', 20);
    table.boolean('is_verified').defaultTo(false);
  });
};

exports.down = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.dropColumn('phone');
    table.dropColumn('is_verified');
  });
};

Modifying Columns

修改列

javascript
exports.up = async (knex) => {
  await knex.schema.alterTable('users', (table) => {
    table.string('email', 320).alter(); // Increase length
    table.string('name', 100).notNullable().alter(); // Add NOT NULL
  });
};

exports.down = async (knex) => {
  await knex.schema.alterTable('users', (table) => {
    table.string('email', 255).alter();
    table.string('name', 100).nullable().alter();
  });
};
javascript
exports.up = async (knex) => {
  await knex.schema.alterTable('users', (table) => {
    table.string('email', 320).alter(); // Increase length
    table.string('name', 100).notNullable().alter(); // Add NOT NULL
  });
};

exports.down = async (knex) => {
  await knex.schema.alterTable('users', (table) => {
    table.string('email', 255).alter();
    table.string('name', 100).nullable().alter();
  });
};

Adding Indexes

添加索引

javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.index('created_at');
    table.index(['user_id', 'created_at']); // Composite index
  });
};

exports.down = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.dropIndex('created_at');
    table.dropIndex(['user_id', 'created_at']);
  });
};
javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.index('created_at');
    table.index(['user_id', 'created_at']); // Composite index
  });
};

exports.down = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.dropIndex('created_at');
    table.dropIndex(['user_id', 'created_at']);
  });
};

Data Migrations

数据迁移

javascript
exports.up = async (knex) => {
  // Add column
  await knex.schema.table('users', (table) => {
    table.string('full_name');
  });
  
  // Migrate data
  const users = await knex('users').select('id', 'first_name', 'last_name');
  for (const user of users) {
    await knex('users')
      .where('id', user.id)
      .update({ full_name: `${user.first_name} ${user.last_name}` });
  }
  
  // Drop old columns
  await knex.schema.table('users', (table) => {
    table.dropColumn('first_name');
    table.dropColumn('last_name');
  });
};

exports.down = async (knex) => {
  // Add old columns
  await knex.schema.table('users', (table) => {
    table.string('first_name');
    table.string('last_name');
  });
  
  // Migrate data back
  const users = await knex('users').select('id', 'full_name');
  for (const user of users) {
    const [firstName, ...lastNameParts] = user.full_name.split(' ');
    await knex('users')
      .where('id', user.id)
      .update({
        first_name: firstName,
        last_name: lastNameParts.join(' ')
      });
  }
  
  // Drop new column
  await knex.schema.table('users', (table) => {
    table.dropColumn('full_name');
  });
};
javascript
exports.up = async (knex) => {
  // Add column
  await knex.schema.table('users', (table) => {
    table.string('full_name');
  });
  
  // Migrate data
  const users = await knex('users').select('id', 'first_name', 'last_name');
  for (const user of users) {
    await knex('users')
      .where('id', user.id)
      .update({ full_name: `${user.first_name} ${user.last_name}` });
  }
  
  // Drop old columns
  await knex.schema.table('users', (table) => {
    table.dropColumn('first_name');
    table.dropColumn('last_name');
  });
};

exports.down = async (knex) => {
  // Add old columns
  await knex.schema.table('users', (table) => {
    table.string('first_name');
    table.string('last_name');
  });
  
  // Migrate data back
  const users = await knex('users').select('id', 'full_name');
  for (const user of users) {
    const [firstName, ...lastNameParts] = user.full_name.split(' ');
    await knex('users')
      .where('id', user.id)
      .update({
        first_name: firstName,
        last_name: lastNameParts.join(' ')
      });
  }
  
  // Drop new column
  await knex.schema.table('users', (table) => {
    table.dropColumn('full_name');
  });
};

Foreign Keys

外键

javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.foreign('user_id')
      .references('id')
      .inTable('users')
      .onDelete('CASCADE')
      .onUpdate('CASCADE');
  });
};

exports.down = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.dropForeign('user_id');
  });
};
javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.foreign('user_id')
      .references('id')
      .inTable('users')
      .onDelete('CASCADE')
      .onUpdate('CASCADE');
  });
};

exports.down = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.dropForeign('user_id');
  });
};

Renaming Tables/Columns

重命名表/列

javascript
exports.up = async (knex) => {
  await knex.schema.renameTable('posts', 'articles');
  
  await knex.schema.table('articles', (table) => {
    table.renameColumn('content', 'body');
  });
};

exports.down = async (knex) => {
  await knex.schema.table('articles', (table) => {
    table.renameColumn('body', 'content');
  });
  
  await knex.schema.renameTable('articles', 'posts');
};
javascript
exports.up = async (knex) => {
  await knex.schema.renameTable('posts', 'articles');
  
  await knex.schema.table('articles', (table) => {
    table.renameColumn('content', 'body');
  });
};

exports.down = async (knex) => {
  await knex.schema.table('articles', (table) => {
    table.renameColumn('body', 'content');
  });
  
  await knex.schema.renameTable('articles', 'posts');
};

Migration Tools

迁移工具

Knex.js:
bash
undefined
Knex.js：
bash
undefined

Create migration

Create migration

npx knex migrate:make create_users_table
npx knex migrate:make create_users_table

Run migrations

Run migrations

npx knex migrate:latest
npx knex migrate:latest

Rollback last batch

Rollback last batch

npx knex migrate:rollback
npx knex migrate:rollback

Rollback all

Rollback all

npx knex migrate:rollback --all
npx knex migrate:rollback --all

Check status

Check status

npx knex migrate:status

**TypeORM:**
```bash
npx knex migrate:status

**TypeORM：**
```bash

Generate migration

Generate migration

npm run typeorm migration:generate -- -n CreateUsersTable
npm run typeorm migration:generate -- -n CreateUsersTable

Run migrations

Run migrations

npm run typeorm migration:run
npm run typeorm migration:run

Revert last migration

Revert last migration

npm run typeorm migration:revert

**Prisma:**
```bash
npm run typeorm migration:revert

**Prisma：**
```bash

Create migration

Create migration

npx prisma migrate dev --name create_users_table
npx prisma migrate dev --name create_users_table

Apply migrations

Apply migrations

npx prisma migrate deploy
npx prisma migrate deploy

Reset database

Reset database

npx prisma migrate reset

**Sequelize:**
```bash
npx prisma migrate reset

**Sequelize：**
```bash

Create migration

Create migration

npx sequelize-cli migration:generate --name create-users-table
npx sequelize-cli migration:generate --name create-users-table

Run migrations

Run migrations

npx sequelize-cli db:migrate
npx sequelize-cli db:migrate

Undo last migration

Undo last migration

npx sequelize-cli db:migrate:undo
undefined
npx sequelize-cli db:migrate:undo
undefined

Best Practices

最佳实践

1. Always include rollback:
javascript
// Every migration must have down()
exports.down = async (knex) => {
  // Reverse the changes
};
2. Make migrations idempotent:
javascript
exports.up = async (knex) => {
  const exists = await knex.schema.hasTable('users');
  if (!exists) {
    await knex.schema.createTable('users', (table) => {
      // ...
    });
  }
};
3. Test migrations:
bash
undefined
1. 始终包含回滚逻辑：
javascript
// Every migration must have down()
exports.down = async (knex) => {
  // Reverse the changes
};
2. 确保迁移具有幂等性：
javascript
exports.up = async (knex) => {
  const exists = await knex.schema.hasTable('users');
  if (!exists) {
    await knex.schema.createTable('users', (table) => {
      // ...
    });
  }
};
3. 测试迁移：
bash
undefined

Run migration

Run migration

npm run migrate
npm run migrate

Test application

Test application

npm test
npm test

Rollback

Rollback

npm run migrate:rollback
npm run migrate:rollback

Run again

Run again

npm run migrate

**4. Backup before production:**
```bash
npm run migrate

**4. 生产环境前备份数据库：**
```bash

PostgreSQL

PostgreSQL

pg_dump dbname > backup.sql
pg_dump dbname > backup.sql

MySQL

MySQL

mysqldump dbname > backup.sql
mysqldump dbname > backup.sql

Then run migration

Then run migration

npm run migrate

**5. Run migrations incrementally:**
```bash
npm run migrate

**5. 增量运行迁移：**
```bash

Don't run all at once in production

Don't run all at once in production

Run one migration at a time

Run one migration at a time

npx knex migrate:up 001_create_users_table.js
npx knex migrate:up 001_create_users_table.js

Verify

Verify

Then next migration

Then next migration

npx knex migrate:up 002_create_posts_table.js
undefined
npx knex migrate:up 002_create_posts_table.js
undefined

Common Patterns

常见模式

Add column with default:
javascript
exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.boolean('is_active').defaultTo(true);
  });
};
Add enum column:
javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.enum('status', ['draft', 'published', 'archived'])
      .defaultTo('draft');
  });
};
Add timestamp columns:
javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.timestamps(true, true); // created_at, updated_at
  });
};
Add JSON column:
javascript
exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.json('metadata');
  });
};
添加带默认值的列：
javascript
exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.boolean('is_active').defaultTo(true);
  });
};
添加枚举列：
javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.enum('status', ['draft', 'published', 'archived'])
      .defaultTo('draft');
  });
};
添加时间戳列：
javascript
exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.timestamps(true, true); // created_at, updated_at
  });
};
添加JSON列：
javascript
exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.json('metadata');
  });
};

Handling Large Tables

大表处理

Add index without locking (PostgreSQL):
sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
Add column with default (PostgreSQL 11+):
sql
-- Fast: doesn't rewrite table
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;
Batch data migration:
javascript
exports.up = async (knex) => {
  const batchSize = 1000;
  let offset = 0;
  
  while (true) {
    const users = await knex('users')
      .select('id', 'email')
      .limit(batchSize)
      .offset(offset);
    
    if (users.length === 0) break;
    
    for (const user of users) {
      await knex('users')
        .where('id', user.id)
        .update({ email_lower: user.email.toLowerCase() });
    }
    
    offset += batchSize;
  }
};
添加索引时避免锁表（PostgreSQL）：
sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
添加带默认值的列（PostgreSQL 11+）：
sql
-- Fast: doesn't rewrite table
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;
批量数据迁移：
javascript
exports.up = async (knex) => {
  const batchSize = 1000;
  let offset = 0;
  
  while (true) {
    const users = await knex('users')
      .select('id', 'email')
      .limit(batchSize)
      .offset(offset);
    
    if (users.length === 0) break;
    
    for (const user of users) {
      await knex('users')
        .where('id', user.id)
        .update({ email_lower: user.email.toLowerCase() });
    }
    
    offset += batchSize;
  }
};

Migration Checklist

迁移检查清单

Before creating:
  • Understand the change needed
  • Plan rollback strategy
  • Consider data migration
  • Check for dependencies
In migration:
  • Include up and down functions
  • Add appropriate indexes
  • Set constraints
  • Handle existing data
Before running:
  • Test locally
  • Test rollback
  • Backup database
  • Plan maintenance window
After running:
  • Verify changes
  • Test application
  • Monitor performance
  • Document changes
创建迁移前：
  • 明确所需的变更内容
  • 规划回滚策略
  • 考虑数据迁移需求
  • 检查依赖关系
编写迁移时：
  • 包含up和down函数
  • 添加合适的索引
  • 设置约束条件
  • 处理现有数据
运行迁移前：
  • 本地测试
  • 测试回滚
  • 备份数据库
  • 规划维护窗口
运行迁移后：
  • 验证变更
  • 测试应用
  • 监控性能
  • 记录变更

Troubleshooting

故障排除

Migration fails:
  • Check error message
  • Verify database connection
  • Check for syntax errors
  • Ensure dependencies exist
Can't rollback:
  • Check down() function
  • Verify rollback logic
  • May need manual intervention
  • Restore from backup if needed
Performance issues:
  • Add indexes after data load
  • Use CONCURRENTLY for indexes
  • Batch large data migrations
  • Run during low traffic
迁移失败：
  • 查看错误信息
  • 验证数据库连接
  • 检查语法错误
  • 确保依赖项存在
无法回滚：
  • 检查down()函数
  • 验证回滚逻辑
  • 可能需要手动干预
  • 必要时从备份恢复
性能问题：
  • 数据加载完成后再添加索引
  • 对索引使用CONCURRENTLY
  • 批量处理大型数据迁移
  • 在低流量时段运行