Skip to content
Flowchart Learning

Migrations

Chapter 30: Migrations & Schema Management

Section titled “Chapter 30: Migrations & Schema Management”

Managing Database Schema Changes

Section titled “Managing Database Schema Changes”

30.1 Migration Overview

Section titled “30.1 Migration Overview”

Migrations provide a systematic way to evolve your database schema over time.

                    Migration Lifecycle
    ================================================================================


    Development Flow:


    +------------------+     +------------------+     +------------------+
    |  Entity Changes  | --> | Generate Migration| --> | Review SQL      |
    +------------------+     +------------------+     +------------------+
                                                           |
                                                           v
    +------------------+     +------------------+     +------------------+
    |  Production     | <-- | Test Migration   | <-- | Run Migration    |
    |  Deployment     |     | Locally          |     | Locally          |
    +------------------+     +------------------+     +------------------+


    Migration Files:


    migrations/
    |-- 1700000001-CreateUserTable.ts
    |-- 1700000002-AddEmailToUser.ts
    |-- 1700000003-CreatePostTable.ts
    |-- 1700000004-AddIndexToUserEmail.ts
    +-- 1700000005-AddSoftDelete.ts


    Each migration has:
    - up(): Apply changes
    - down(): Revert changes


    ================================================================================

30.2 Setting Up Migrations

Section titled “30.2 Setting Up Migrations”

Configuration

Section titled “Configuration”
src/config/database.config.ts
import { DataSource } from 'typeorm';
import { config } from 'dotenv';


config();


export default new DataSource({
  type: 'postgres',
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT, 10) || 5432,
  username: process.env.DB_USERNAME || 'postgres',
  password: process.env.DB_PASSWORD || 'postgres',
  database: process.env.DB_DATABASE || 'myapp',
  entities: ['src/**/*.entity{.ts,.js}'],
  migrations: ['src/migrations/*{.ts,.js}'],
  synchronize: false,  // NEVER true in production
  logging: true,
});


// package.json scripts
{
  "scripts": {
    "typeorm": "typeorm-ts-node-commonjs",
    "migration:generate": "npm run typeorm -- migration:generate -d src/config/database.config.ts",
    "migration:run": "npm run typeorm -- migration:run -d src/config/database.config.ts",
    "migration:revert": "npm run typeorm -- migration:revert -d src/config/database.config.ts",
    "migration:show": "npm run typeorm -- migration:show -d src/config/database.config.ts"
  }
}

NestJS Integration

Section titled “NestJS Integration”
src/app.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConfigModule, ConfigService } from '@nestjs/config';


@Module({
  imports: [
    ConfigModule.forRoot({
      isGlobal: true,
    }),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get('DB_HOST', 'localhost'),
        port: configService.get('DB_PORT', 5432),
        username: configService.get('DB_USERNAME', 'postgres'),
        password: configService.get('DB_PASSWORD', 'postgres'),
        database: configService.get('DB_DATABASE', 'myapp'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: false,  // Use migrations instead
        migrationsRun: true,  // Auto-run migrations on startup
        migrations: [__dirname + '/migrations/*{.ts,.js}'],
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

30.3 Creating Migrations

Section titled “30.3 Creating Migrations”

Generate Migration from Entity Changes

Section titled “Generate Migration from Entity Changes”
Terminal window
# Generate migration from entity changes
npm run migration:generate src/migrations/AddEmailToUser


# Output:
# Migration AddEmailToUser has been generated successfully.

Create Empty Migration

Section titled “Create Empty Migration”
Terminal window
# Create empty migration file
npm run typeorm migration:create src/migrations/CustomMigration


# Output:
# src/migrations/1700000001-CustomMigration.ts

Migration File Structure

Section titled “Migration File Structure”
src/migrations/1700000001-CreateUserTable.ts
import { MigrationInterface, QueryRunner, Table, TableIndex } from 'typeorm';


export class CreateUserTable1700000001 implements MigrationInterface {
  // Apply migration
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Create table
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'name',
            type: 'varchar',
            length: '100',
            isNullable: false,
          },
          {
            name: 'email',
            type: 'varchar',
            length: '255',
            isUnique: true,
            isNullable: false,
          },
          {
            name: 'password',
            type: 'varchar',
            length: '255',
            isNullable: false,
          },
          {
            name: 'isActive',
            type: 'boolean',
            default: true,
          },
          {
            name: 'createdAt',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
          },
          {
            name: 'updatedAt',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
            onUpdate: 'CURRENT_TIMESTAMP',
          },
        ],
      }),
      true,  // Create foreign keys if defined
    );


    // Create index
    await queryRunner.createIndex(
      'users',
      new TableIndex({
        name: 'IDX_USER_EMAIL',
        columnNames: ['email'],
        isUnique: true,
      }),
    );
  }


  // Revert migration
  public async down(queryRunner: QueryRunner): Promise<void> {
    // Drop index first
    await queryRunner.dropIndex('users', 'IDX_USER_EMAIL');


    // Drop table
    await queryRunner.dropTable('users');
  }
}

30.4 Migration Operations

Section titled “30.4 Migration Operations”

Table Operations

Section titled “Table Operations”
import { MigrationInterface, QueryRunner, Table, TableColumn, TableIndex, TableForeignKey } from 'typeorm';


export class UserMigration1700000001 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Create table
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          { name: 'id', type: 'int', isPrimary: true, isGenerated: true },
          { name: 'name', type: 'varchar', length: '100' },
          { name: 'email', type: 'varchar', length: '255', isUnique: true },
        ],
      }),
      true,
    );


    // Add column
    await queryRunner.addColumn(
      'users',
      new TableColumn({
        name: 'phone',
        type: 'varchar',
        length: '20',
        isNullable: true,
      }),
    );


    // Modify column
    await queryRunner.changeColumn(
      'users',
      'name',
      new TableColumn({
        name: 'name',
        type: 'varchar',
        length: '200',  // Increased length
        isNullable: false,
      }),
    );


    // Drop column
    await queryRunner.dropColumn('users', 'phone');


    // Create index
    await queryRunner.createIndex(
      'users',
      new TableIndex({
        name: 'IDX_USER_NAME',
        columnNames: ['name'],
      }),
    );


    // Create foreign key
    await queryRunner.createForeignKey(
      'posts',
      new TableForeignKey({
        name: 'FK_POST_AUTHOR',
        columnNames: ['authorId'],
        referencedTableName: 'users',
        referencedColumnNames: ['id'],
        onDelete: 'CASCADE',
      }),
    );
  }


  public async down(queryRunner: QueryRunner): Promise<void> {
    // Drop foreign key
    await queryRunner.dropForeignKey('posts', 'FK_POST_AUTHOR');


    // Drop index
    await queryRunner.dropIndex('users', 'IDX_USER_NAME');


    // Drop table
    await queryRunner.dropTable('users');
  }
}

Data Operations

Section titled “Data Operations”
export class SeedDataMigration1700000002 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Insert data
    await queryRunner.query(`
      INSERT INTO users (name, email, password, role)
      VALUES
        ('Admin', 'admin@example.com', '$2b$10$...', 'admin'),
        ('User', 'user@example.com', '$2b$10$...', 'user')
    `);


    // Update data
    await queryRunner.query(`
      UPDATE users
      SET role = 'moderator'
      WHERE email = 'user@example.com'
    `);


    // Using parameterized queries
    await queryRunner.query(
      'INSERT INTO roles (name) VALUES ($1)',
      ['admin'],
    );
  }


  public async down(queryRunner: QueryRunner): Promise<void> {
    // Delete data
    await queryRunner.query(`
      DELETE FROM users
      WHERE email IN ('admin@example.com', 'user@example.com')
    `);
  }
}

30.5 Running Migrations

Section titled “30.5 Running Migrations”

Run All Pending Migrations

Section titled “Run All Pending Migrations”
Terminal window
# Run migrations
npm run migration:run


# Output:
# query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = ...
# query: CREATE TABLE "users" ...
# Migration CreateUserTable1700000001 has been executed successfully.

Revert Last Migration

Section titled “Revert Last Migration”
Terminal window
# Revert last migration
npm run migration:revert


# Output:
# Migration CreateUserTable1700000001 has been reverted successfully.

Show Migration Status

Section titled “Show Migration Status”
Terminal window
# Show migration status
npm run migration:show


# Output:
# [X] CreateUserTable1700000001
# [ ] AddEmailToUser1700000002
# [ ] CreatePostTable1700000003

30.6 Programmatic Migration Control

Section titled “30.6 Programmatic Migration Control”

Migration Service

Section titled “Migration Service”
src/migrations/migration.service.ts
import { Injectable, InjectDataSource } from '@nestjs/common';
import { DataSource } from 'typeorm';


@Injectable()
export class MigrationService {
  constructor(
    @InjectDataSource()
    private dataSource: DataSource,
  ) {}


  async runMigrations(): Promise<void> {
    const migrations = await this.dataSource.runMigrations();
    console.log(`Executed ${migrations.length} migrations`);
  }


  async revertLastMigration(): Promise<void> {
    await this.dataSource.undoLastMigration();
    console.log('Reverted last migration');
  }


  async showMigrations(): Promise<void> {
    const migrations = await this.dataSource.showMigrations();
    console.log('Pending migrations:', migrations);
  }


  async getExecutedMigrations(): Promise<any[]> {
    return this.dataSource.query(
      'SELECT * FROM migrations ORDER BY id DESC'
    );
  }
}

Migration Controller

Section titled “Migration Controller”
src/migrations/migration.controller.ts
import { Controller, Post, Get, UseGuards } from '@nestjs/common';
import { MigrationService } from './migration.service';
import { AdminGuard } from '../auth/guards/admin.guard';


@Controller('migrations')
@UseGuards(AdminGuard)  // Only admins can manage migrations
export class MigrationController {
  constructor(private migrationService: MigrationService) {}


  @Post('run')
  async runMigrations() {
    await this.migrationService.runMigrations();
    return { message: 'Migrations executed successfully' };
  }


  @Post('revert')
  async revertMigration() {
    await this.migrationService.revertLastMigration();
    return { message: 'Last migration reverted successfully' };
  }


  @Get('status')
  async getMigrationStatus() {
    const executed = await this.migrationService.getExecutedMigrations();
    return { executed };
  }
}

30.7 Best Practices

Section titled “30.7 Best Practices”

Migration Naming Convention

Section titled “Migration Naming Convention”
                    Migration Naming Convention
    ================================================================================


    Format: {timestamp}-{description}.ts


    Examples:
    - 1700000001-CreateUserTable.ts
    - 1700000002-AddEmailColumnToUser.ts
    - 1700000003-CreateIndexOnUserEmail.ts
    - 1700000004-AddForeignKeyPostToUser.ts
    - 1700000005-SeedDefaultRoles.ts


    Naming Guidelines:
    - Use PascalCase for description
    - Start with action verb (Create, Add, Update, Remove, Drop)
    - Be descriptive but concise
    - Include affected entity/table name


    ================================================================================

Migration Guidelines

Section titled “Migration Guidelines”
// Good: Small, focused migration
export class AddEmailToUser1700000001 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn(
      'users',
      new TableColumn({
        name: 'email',
        type: 'varchar',
        length: '255',
        isUnique: true,
      }),
    );
  }


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


// Bad: Multiple unrelated changes in one migration
export class MultipleChanges1700000002 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Don't do this - multiple unrelated changes
    await queryRunner.addColumn('users', ...);
    await queryRunner.createTable('posts', ...);
    await queryRunner.createIndex('comments', ...);
  }
}

Safe Migration Patterns

Section titled “Safe Migration Patterns”
// Safe: Add nullable column first, then populate, then make non-null
export class SafeAddColumnMigration1700000001 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Step 1: Add nullable column
    await queryRunner.addColumn(
      'users',
      new TableColumn({
        name: 'status',
        type: 'varchar',
        length: '20',
        isNullable: true,  // Start as nullable
      }),
    );


    // Step 2: Populate existing rows
    await queryRunner.query(`
      UPDATE users SET status = 'active' WHERE status IS NULL
    `);


    // Step 3: Make non-null (in separate migration or after verification)
    // This could be a separate migration for safety
  }
}


// Safe: Create index concurrently (PostgreSQL)
export class SafeCreateIndexMigration1700000002 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // CONCURRENT doesn't lock the table
    await queryRunner.query(`
      CREATE INDEX CONCURRENTLY "IDX_USER_EMAIL" ON "users" ("email")
    `);
  }


  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DROP INDEX CONCURRENTLY "IDX_USER_EMAIL"
    `);
  }
}

30.8 Production Migration Strategy

Section titled “30.8 Production Migration Strategy”
                    Production Migration Strategy
    ================================================================================


    1. Development Phase:
       - Make entity changes
       - Generate migration
       - Review generated SQL
       - Test locally


    2. Staging Phase:
       - Run migration on staging database
       - Verify application works
       - Performance test if needed


    3. Production Phase:
       - Backup database
       - Run migration during low-traffic period
       - Monitor for errors
       - Have rollback plan ready


    CI/CD Pipeline:


    +------------------+     +------------------+     +------------------+
    |    Code Commit   | --> |    Run Tests     | --> | Build Container  |
    +------------------+     +------------------+     +------------------+
                                                           |
                                                           v
    +------------------+     +------------------+     +------------------+
    |    Production   | <-- | Staging Deploy   | <-- | Staging Tests    |
    |    Deployment   |     | & Migration      |     |                  |
    +------------------+     +------------------+     +------------------+
           |
           v
    +------------------+
    | Run Migration    |
    | (with backup)    |
    +------------------+


    ================================================================================

Production Migration Script

Section titled “Production Migration Script”
scripts/migrate-production.ts
import { DataSource } from 'typeorm';
import { config } from 'dotenv';
import * as fs from 'fs';


config();


async function runProductionMigration() {
  const dataSource = new DataSource({
    type: 'postgres',
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT, 10),
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    entities: ['dist/**/*.entity{.ts,.js}'],
    migrations: ['dist/migrations/*{.ts,.js}'],
    synchronize: false,
  });


  try {
    // Connect to database
    await dataSource.initialize();
    console.log('Connected to database');


    // Check pending migrations
    const pendingMigrations = await dataSource.showMigrations();


    if (!pendingMigrations) {
      console.log('No pending migrations');
      return;
    }


    // Create backup (if supported)
    console.log('Creating backup...');
    // Backup logic here


    // Run migrations
    console.log('Running migrations...');
    const migrations = await dataSource.runMigrations();


    console.log(`Executed ${migrations.length} migrations:`);
    migrations.forEach(m => console.log(`  - ${m}`));


    // Verify migrations
    const remainingPending = await dataSource.showMigrations();
    if (remainingPending) {
      throw new Error('Some migrations were not executed');
    }


    console.log('All migrations executed successfully');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  } finally {
    await dataSource.destroy();
  }
}


runProductionMigration();

30.9 Complete Migration Example

Section titled “30.9 Complete Migration Example”
src/migrations/1700000001-InitialSetup.ts
import {
  MigrationInterface,
  QueryRunner,
  Table,
  TableIndex,
  TableForeignKey,
  TableColumn,
} from 'typeorm';


export class InitialSetup1700000001 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Create users table
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'name',
            type: 'varchar',
            length: '100',
            isNullable: false,
          },
          {
            name: 'email',
            type: 'varchar',
            length: '255',
            isUnique: true,
            isNullable: false,
          },
          {
            name: 'password',
            type: 'varchar',
            length: '255',
            isNullable: false,
          },
          {
            name: 'isActive',
            type: 'boolean',
            default: true,
          },
          {
            name: 'createdAt',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
          },
          {
            name: 'updatedAt',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
            onUpdate: 'CURRENT_TIMESTAMP',
          },
        ],
      }),
      true,
    );


    // Create posts table
    await queryRunner.createTable(
      new Table({
        name: 'posts',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'title',
            type: 'varchar',
            length: '200',
            isNullable: false,
          },
          {
            name: 'content',
            type: 'text',
            isNullable: false,
          },
          {
            name: 'authorId',
            type: 'int',
            isNullable: false,
          },
          {
            name: 'isPublished',
            type: 'boolean',
            default: false,
          },
          {
            name: 'createdAt',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
          },
          {
            name: 'updatedAt',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
            onUpdate: 'CURRENT_TIMESTAMP',
          },
        ],
      }),
      true,
    );


    // Create foreign key
    await queryRunner.createForeignKey(
      'posts',
      new TableForeignKey({
        name: 'FK_POST_AUTHOR',
        columnNames: ['authorId'],
        referencedTableName: 'users',
        referencedColumnNames: ['id'],
        onDelete: 'CASCADE',
      }),
    );


    // Create indexes
    await queryRunner.createIndex(
      'users',
      new TableIndex({
        name: 'IDX_USER_EMAIL',
        columnNames: ['email'],
        isUnique: true,
      }),
    );


    await queryRunner.createIndex(
      'posts',
      new TableIndex({
        name: 'IDX_POST_AUTHOR',
        columnNames: ['authorId'],
      }),
    );


    await queryRunner.createIndex(
      'posts',
      new TableIndex({
        name: 'IDX_POST_CREATED',
        columnNames: ['createdAt'],
      }),
    );
  }


  public async down(queryRunner: QueryRunner): Promise<void> {
    // Drop indexes
    await queryRunner.dropIndex('posts', 'IDX_POST_CREATED');
    await queryRunner.dropIndex('posts', 'IDX_POST_AUTHOR');
    await queryRunner.dropIndex('users', 'IDX_USER_EMAIL');


    // Drop foreign key
    await queryRunner.dropForeignKey('posts', 'FK_POST_AUTHOR');


    // Drop tables
    await queryRunner.dropTable('posts');
    await queryRunner.dropTable('users');
  }
}


// src/migrations/1700000002-AddSoftDelete.ts
export class AddSoftDelete1700000002 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Add deletedAt to users
    await queryRunner.addColumn(
      'users',
      new TableColumn({
        name: 'deletedAt',
        type: 'timestamp',
        isNullable: true,
      }),
    );


    // Add deletedAt to posts
    await queryRunner.addColumn(
      'posts',
      new TableColumn({
        name: 'deletedAt',
        type: 'timestamp',
        isNullable: true,
      }),
    );


    // Create index for soft delete queries
    await queryRunner.createIndex(
      'users',
      new TableIndex({
        name: 'IDX_USER_DELETED',
        columnNames: ['deletedAt'],
      }),
    );
  }


  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropIndex('users', 'IDX_USER_DELETED');
    await queryRunner.dropColumn('posts', 'deletedAt');
    await queryRunner.dropColumn('users', 'deletedAt');
  }
}

30.10 Summary

Section titled “30.10 Summary”
                    Migration Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Command            | Purpose                                   |
    |    -------------------|------------------------------------------|
    |    migration:generate | Generate from entity changes              |
    |    migration:create   | Create empty migration                    |
    |    migration:run      | Execute pending migrations                |
    |    migration:revert   | Undo last migration                       |
    |    migration:show     | List migration status                     |
    |                                                                   |
    |    QueryRunner Methods | Description                              |
    |    -------------------|------------------------------------------|
    |    createTable        | Create new table                          |
    |    dropTable          | Drop table                                |
    |    addColumn          | Add column to table                       |
    |    dropColumn         | Remove column                             |
    |    changeColumn       | Modify column                             |
    |    createIndex        | Create index                              |
    |    dropIndex          | Drop index                                |
    |    createForeignKey   | Create foreign key                        |
    |    dropForeignKey     | Drop foreign key                          |
    |    query              | Execute raw SQL                           |
    |                                                                   |
    |    Best Practices     | Description                              |
    |    -------------------|------------------------------------------|
    |    Small migrations   | One change per migration                  |
    |    Always have down   | Make migrations reversible                |
    |    Test locally       | Verify before production                  |
    |    Backup first       | Always backup before migration            |
    |    Version control    | Track migrations in git                   |
    |                                                                   |
    +------------------------------------------------------------------+

Next Chapter

Section titled “Next Chapter”

Chapter 31: Query Optimization Strategies

Last Updated: February 2026