TypeORM 的迁移系统如何工作?如何创建、运行和管理数据库迁移

迁移系统是 TypeORM 中用于管理数据库结构变更的重要工具,它允许开发者以版本化的方式追踪和应用数据库结构的变化,确保团队协作时数据库结构的一致性。

迁移基础概念

什么是迁移

迁移是数据库结构变更的脚本,用于:

• 创建或删除表
• 添加或删除列
• 修改列类型
• 创建或删除索引
• 添加或删除外键约束

每个迁移都有唯一的版本号和时间戳,确保迁移可以按顺序执行。

迁移文件结构

typescript
import { MigrationInterface, QueryRunner, Table } from 'typeorm';

export class CreateUserTable1234567890123 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // 执行迁移:创建表、添加列等
    await queryRunner.createTable(
      new Table({
        name: 'user',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'name',
            type: 'varchar',
          },
          {
            name: 'email',
            type: 'varchar',
            isUnique: true,
          },
          {
            name: 'createdAt',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
          },
        ],
      }),
      true
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // 回滚迁移:删除表、移除列等
    await queryRunner.dropTable('user');
  }
}

创建迁移

使用 CLI 创建迁移

bash
# 创建新迁移
npm run typeorm migration:generate -- -n CreateUserTable

# 或者使用 npx
npx typeorm migration:generate -n CreateUserTable

# 创建空迁移
npm run typeorm migration:create -- -n CreateUserTable

配置 DataSource

typescript
import { DataSource } from 'typeorm';

export const AppDataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: ['src/entity/**/*.ts'],
  migrations: ['src/migration/**/*.ts'],
  subscribers: ['src/subscriber/**/*.ts'],
  synchronize: false, // 生产环境必须设为 false
  logging: true,
});

运行迁移

使用 CLI 运行迁移

bash
# 运行所有待执行的迁移
npm run typeorm migration:run

# 回滚最后一次迁移
npm run typeorm migration:revert

# 显示迁移状态
npm run typeorm migration:show

# 清空数据库(慎用)
npm run typeorm schema:drop

在代码中运行迁移

typescript
import { DataSource } from 'typeorm';

async function runMigrations() {
  const dataSource = new DataSource({
    type: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'root',
    password: 'password',
    database: 'myapp',
    entities: ['src/entity/**/*.ts'],
    migrations: ['src/migration/**/*.ts'],
  });

  await dataSource.initialize();

  // 运行所有待执行的迁移
  await dataSource.runMigrations();

  // 回滚最后一次迁移
  // await dataSource.undoLastMigration();

  await dataSource.destroy();
}

runMigrations().catch(console.error);

迁移操作示例

创建表

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.createTable(
    new Table({
      name: 'user',
      columns: [
        {
          name: 'id',
          type: 'int',
          isPrimary: true,
          isGenerated: true,
          generationStrategy: 'increment',
        },
        {
          name: 'name',
          type: 'varchar',
          length: '100',
        },
        {
          name: 'email',
          type: 'varchar',
          length: '255',
          isUnique: true,
        },
        {
          name: 'age',
          type: 'int',
          nullable: true,
        },
        {
          name: 'isActive',
          type: 'boolean',
          default: true,
        },
        {
          name: 'createdAt',
          type: 'timestamp',
          default: 'CURRENT_TIMESTAMP',
        },
        {
          name: 'updatedAt',
          type: 'timestamp',
          default: 'CURRENT_TIMESTAMP',
          onUpdate: 'CURRENT_TIMESTAMP',
        },
      ],
      indices: [
        {
          name: 'IDX_USER_EMAIL',
          columnNames: ['email'],
        },
      ],
    }),
    true
  );
}

添加列

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.addColumn(
    'user',
    new TableColumn({
      name: 'avatar',
      type: 'varchar',
      length: '255',
      isNullable: true,
    })
  );
}

public async down(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.dropColumn('user', 'avatar');
}

修改列

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.changeColumn(
    'user',
    'name',
    new TableColumn({
      name: 'name',
      type: 'varchar',
      length: '200', // 修改长度
    })
  );
}

public async down(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.changeColumn(
    'user',
    'name',
    new TableColumn({
      name: 'name',
      type: 'varchar',
      length: '100',
    })
  );
}

创建索引

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.createIndex(
    'user',
    new TableIndex({
      name: 'IDX_USER_EMAIL',
      columnNames: ['email'],
      isUnique: true,
    })
  );
}

public async down(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.dropIndex('user', 'IDX_USER_EMAIL');
}

添加外键

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.createForeignKey(
    'post',
    new TableForeignKey({
      columnNames: ['authorId'],
      referencedColumnNames: ['id'],
      referencedTableName: 'user',
      onDelete: 'CASCADE',
    })
  );
}

public async down(queryRunner: QueryRunner): Promise<void> {
  const table = await queryRunner.getTable('post');
  const foreignKey = table.foreignKeys.find(
    fk => fk.columnNames.indexOf('authorId') !== -1
  );
  await queryRunner.dropForeignKey('post', foreignKey);
}

执行原生 SQL

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`
    CREATE TRIGGER update_user_timestamp 
    BEFORE UPDATE ON user
    FOR EACH ROW
    SET NEW.updatedAt = CURRENT_TIMESTAMP
  `);
}

public async down(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`DROP TRIGGER update_user_timestamp`);
}

数据迁移

迁移现有数据

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  // 添加新列
  await queryRunner.addColumn(
    'user',
    new TableColumn({
      name: 'fullName',
      type: 'varchar',
      length: '200',
      isNullable: true,
    })
  );

  // 迁移数据
  await queryRunner.query(`
    UPDATE user 
    SET fullName = CONCAT(firstName, ' ', lastName)
  `);

  // 删除旧列
  await queryRunner.dropColumn('user', 'firstName');
  await queryRunner.dropColumn('user', 'lastName');
}

批量插入数据

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  const users = [
    { name: 'John', email: 'john@example.com' },
    { name: 'Jane', email: 'jane@example.com' },
  ];

  for (const user of users) {
    await queryRunner.query(
      `INSERT INTO user (name, email) VALUES (?, ?)`,
      [user.name, user.email]
    );
  }
}

迁移最佳实践

1. 版本控制

typescript
// 迁移文件命名格式: {timestamp}-{name}.ts
// 例如: 1234567890123-CreateUserTable.ts

export class CreateUserTable1234567890123 implements MigrationInterface {
  // 迁移内容
}

2. 可逆性

确保每个迁移都可以完全回滚:

typescript
export class AddUserAvatar1234567890123 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn('user', new TableColumn({
      name: 'avatar',
      type: 'varchar',
      isNullable: true,
    }));
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropColumn('user', 'avatar');
  }
}

3. 事务支持

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.startTransaction();
  
  try {
    await queryRunner.createTable(/* ... */);
    await queryRunner.addColumn(/* ... */);
    await queryRunner.commitTransaction();
  } catch (err) {
    await queryRunner.rollbackTransaction();
    throw err;
  }
}

4. 环境区分

typescript
export class AddProductionIndex1234567890123 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // 只在生产环境执行
    if (process.env.NODE_ENV === 'production') {
      await queryRunner.createIndex(/* ... */);
    }
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    if (process.env.NODE_ENV === 'production') {
      await queryRunner.dropIndex(/* ... */);
    }
  }
}

常见问题

1. 迁移冲突

当多个开发者同时创建迁移时可能出现冲突:

bash
# 解决方法:重新生成迁移
npm run typeorm migration:generate -- -n FixMigrationConflict

2. 数据丢失风险

在修改列类型或删除列前备份数据:

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  // 备份数据
  await queryRunner.query(`CREATE TABLE user_backup AS SELECT * FROM user`);
  
  // 执行迁移
  await queryRunner.changeColumn(/* ... */);
}

3. 性能问题

对于大数据表的迁移,考虑分批处理:

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  const batchSize = 1000;
  let offset = 0;
  
  while (true) {
    const users = await queryRunner.query(
      `SELECT id FROM user LIMIT ${batchSize} OFFSET ${offset}`
    );
    
    if (users.length === 0) break;
    
    for (const user of users) {
      await queryRunner.query(`UPDATE user SET ... WHERE id = ?`, [user.id]);
    }
    
    offset += batchSize;
  }
}

生产环境建议

1. 禁用 synchronize: 生产环境必须设置 synchronize: false
2. 备份策略: 执行迁移前备份数据库
3. 测试环境: 先在测试环境验证迁移
4. 回滚计划: 准备好回滚方案
5. 监控日志: 监控迁移执行日志
6. 分步执行: 对于大型迁移,分步骤执行

TypeORM 的迁移系统提供了强大而灵活的数据库结构管理能力,掌握迁移系统的使用对于维护大型应用的数据库结构至关重要。