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

1. Disable synchronize: Must set synchronize: false in production
2. Backup strategy: Backup database before executing migrations
3. Test environment: Verify migrations in test environment first
4. Rollback plan: Prepare rollback plan
5. Monitor logs: Monitor migration execution logs
6. 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.