How does TypeORM's migration system work? How to create, run, and manage database migrations - 面试题

The migration system is an important tool in TypeORM for managing database structure changes. It allows developers to track and apply database structure changes in a versioned manner, ensuring database structure consistency during team collaboration.

Migration Basic Concepts

What is Migration

Migrations are scripts for database structure changes used to:

Create or drop tables
Add or drop columns
Modify column types
Create or drop indexes
Add or drop foreign key constraints

Each migration has a unique version number and timestamp, ensuring migrations can be executed in order.

Migration File Structure

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

export class CreateUserTable1234567890123 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Execute migration: create tables, add columns, etc.
    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> {
    // Rollback migration: drop tables, remove columns, etc.
    await queryRunner.dropTable('user');
  }
}

Creating Migrations

Creating Migrations Using CLI

bash
# Create new migration
npm run typeorm migration:generate -- -n CreateUserTable

# Or use npx
npx typeorm migration:generate -n CreateUserTable

# Create empty migration
npm run typeorm migration:create -- -n CreateUserTable

Configuring 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, // Must be false in production
  logging: true,
});

Running Migrations

Running Migrations Using CLI

bash
# Run all pending migrations
npm run typeorm migration:run

# Rollback last migration
npm run typeorm migration:revert

# Show migration status
npm run typeorm migration:show

# Drop database (use with caution)
npm run typeorm schema:drop

Running Migrations in Code

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();

  // Run all pending migrations
  await dataSource.runMigrations();

  // Rollback last migration
  // await dataSource.undoLastMigration();

  await dataSource.destroy();
}

runMigrations().catch(console.error);

Migration Operation Examples

Creating Tables

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
  );
}

Adding Columns

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');
}

Modifying Columns

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

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

Creating Indexes

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');
}

Adding Foreign Keys

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);
}

Executing Native 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`);
}

Data Migration

Migrating Existing Data

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

  // Migrate data
  await queryRunner.query(`
    UPDATE user 
    SET fullName = CONCAT(firstName, ' ', lastName)
  `);

  // Drop old columns
  await queryRunner.dropColumn('user', 'firstName');
  await queryRunner.dropColumn('user', 'lastName');
}

Batch Inserting Data

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]
    );
  }
}

Migration Best Practices

1. Version Control

typescript
// Migration file naming format: {timestamp}-{name}.ts
// Example: 1234567890123-CreateUserTable.ts

export class CreateUserTable1234567890123 implements MigrationInterface {
  // Migration content
}

2. Reversibility

Ensure each migration can be completely rolled back:

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. Transaction Support

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. Environment Differentiation

typescript
export class AddProductionIndex1234567890123 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Only execute in production environment
    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(/* ... */);
    }
  }
}

Common Issues

1. Migration Conflicts

Conflicts may occur when multiple developers create migrations simultaneously:

bash
# Solution: regenerate migration
npm run typeorm migration:generate -- -n FixMigrationConflict

2. Data Loss Risk

Backup data before modifying column types or dropping columns:

typescript
public async up(queryRunner: QueryRunner): Promise<void> {
  // Backup data
  await queryRunner.query(`CREATE TABLE user_backup AS SELECT * FROM user`);
  
  // Execute migration
  await queryRunner.changeColumn(/* ... */);
}

3. Performance Issues

For migrations on large tables, consider batch processing:

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;
  }
}

Production Environment Recommendations

Disable synchronize: Must set synchronize: false in production
Backup strategy: Backup database before executing migrations
Test environment: Verify migrations in test environment first
Rollback plan: Prepare rollback plan
Monitor logs: Monitor migration execution logs
Step-by-step execution: Execute large migrations in steps

TypeORM's migration system provides powerful and flexible database structure management capabilities. Mastering the migration system is crucial for maintaining database structure in large applications.