Migration System

/**
 * Interface that all migration classes must implement
 */
interface MigrationInterface {
  /**
   * Applies the migration (forward direction)
   * @param queryRunner - Query runner for database operations
   * @returns Promise resolving when migration is applied
   */
  up(queryRunner: QueryRunner): Promise<any>;

  /**
   * Reverts the migration (backward direction)
   * @param queryRunner - Query runner for database operations
   * @returns Promise resolving when migration is reverted
   */
  down(queryRunner: QueryRunner): Promise<any>;
}

/**
 * Migration metadata class
 */
class Migration {
  /**
   * Creates a new migration instance
   * @param id - Migration ID (timestamp or number)
   * @param timestamp - Migration timestamp
   * @param name - Migration name
   * @param instance - Migration implementation instance
   */
  constructor(
    id: number | string,
    timestamp: number,
    name: string,
    instance?: MigrationInterface
  );

  /** Unique migration identifier */
  readonly id: number | string;

  /** Migration creation timestamp */
  readonly timestamp: number;

  /** Migration name/description */
  readonly name: string;

  /** Migration implementation instance */
  readonly instance?: MigrationInterface;
}

import { MigrationInterface, QueryRunner } from "typeorm";

export class CreateUserTable1635123456789 implements MigrationInterface {
  name = "CreateUserTable1635123456789";

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE TABLE "user" (
        "id" SERIAL NOT NULL,
        "name" character varying NOT NULL,
        "email" character varying NOT NULL,
        "created_at" TIMESTAMP NOT NULL DEFAULT now(),
        "updated_at" TIMESTAMP NOT NULL DEFAULT now(),
        CONSTRAINT "UQ_USER_EMAIL" UNIQUE ("email"),
        CONSTRAINT "PK_USER" PRIMARY KEY ("id")
      )
    `);
  }

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

/**
 * Executes and manages database migrations
 */
class MigrationExecutor {
  /** Data source connection */
  readonly connection: DataSource;

  /**
   * Executes all pending migrations
   * @returns Promise resolving to array of executed migrations
   */
  executePendingMigrations(): Promise<Migration[]>;

  /**
   * Reverts the most recent migration
   * @returns Promise resolving when migration is reverted
   */
  undoLastMigration(): Promise<void>;

  /**
   * Shows migration status (executed vs pending)
   * @returns Promise resolving to boolean indicating if migrations are pending
   */
  showMigrations(): Promise<boolean>;

  /**
   * Gets all migrations from files and database
   * @returns Promise resolving to migrations array
   */
  getAllMigrations(): Promise<Migration[]>;

  /**
   * Gets executed migrations from database
   * @returns Promise resolving to executed migrations
   */
  getExecutedMigrations(): Promise<Migration[]>;

  /**
   * Gets pending migrations that haven't been executed
   * @returns Promise resolving to pending migrations
   */
  getPendingMigrations(): Promise<Migration[]>;

  /**
   * Creates migrations table if it doesn't exist
   * @returns Promise resolving when table is created
   */
  createMigrationsTableIfNotExist(): Promise<void>;

  /**
   * Loads migration files from filesystem
   * @returns Promise resolving to migration instances
   */
  loadMigrations(): Promise<Migration[]>;

  /**
   * Executes a specific migration
   * @param migration - Migration to execute
   * @returns Promise resolving when migration is executed
   */
  executeMigration(migration: Migration): Promise<void>;

  /**
   * Reverts a specific migration
   * @param migration - Migration to revert
   * @returns Promise resolving when migration is reverted
   */
  undoMigration(migration: Migration): Promise<void>;
}

/**
 * Query runner for database operations in migrations
 */
interface QueryRunner {
  /** Data source connection */
  connection: DataSource;

  /** Current database connection */
  databaseConnection: any;

  /** Whether runner is released */
  isReleased: boolean;

  /** Whether runner is in transaction */
  isTransactionActive: boolean;

  /**
   * Starts a new database transaction
   * @returns Promise resolving when transaction is started
   */
  startTransaction(): Promise<void>;

  /**
   * Commits current transaction
   * @returns Promise resolving when transaction is committed
   */
  commitTransaction(): Promise<void>;

  /**
   * Rolls back current transaction
   * @returns Promise resolving when transaction is rolled back
   */
  rollbackTransaction(): Promise<void>;

  /**
   * Executes raw SQL query
   * @param query - SQL query string
   * @param parameters - Query parameters
   * @returns Promise resolving to query results
   */
  query(query: string, parameters?: any[]): Promise<any>;

  /**
   * Creates a new database table
   * @param table - Table definition
   * @param ifNotExist - Whether to use IF NOT EXISTS clause
   * @returns Promise resolving when table is created
   */
  createTable(table: Table, ifNotExist?: boolean): Promise<void>;

  /**
   * Drops an existing database table
   * @param tableOrName - Table instance or name
   * @param ifExist - Whether to use IF EXISTS clause
   * @returns Promise resolving when table is dropped
   */
  dropTable(tableOrName: Table | string, ifExist?: boolean): Promise<void>;

  /**
   * Adds a new column to existing table
   * @param tableOrName - Table instance or name
   * @param column - Column definition
   * @returns Promise resolving when column is added
   */
  addColumn(tableOrName: Table | string, column: TableColumn): Promise<void>;

  /**
   * Drops column from existing table
   * @param tableOrName - Table instance or name
   * @param columnOrName - Column instance or name
   * @returns Promise resolving when column is dropped
   */
  dropColumn(tableOrName: Table | string, columnOrName: TableColumn | string): Promise<void>;

  /**
   * Creates a database index
   * @param tableOrName - Table instance or name
   * @param index - Index definition
   * @returns Promise resolving when index is created
   */
  createIndex(tableOrName: Table | string, index: TableIndex): Promise<void>;

  /**
   * Drops a database index
   * @param tableOrName - Table instance or name
   * @param indexOrName - Index instance or name
   * @returns Promise resolving when index is dropped
   */
  dropIndex(tableOrName: Table | string, indexOrName: TableIndex | string): Promise<void>;

  /**
   * Creates a foreign key constraint
   * @param tableOrName - Table instance or name
   * @param foreignKey - Foreign key definition
   * @returns Promise resolving when foreign key is created
   */
  createForeignKey(tableOrName: Table | string, foreignKey: TableForeignKey): Promise<void>;

  /**
   * Drops a foreign key constraint
   * @param tableOrName - Table instance or name
   * @param foreignKeyOrName - Foreign key instance or name
   * @returns Promise resolving when foreign key is dropped
   */
  dropForeignKey(tableOrName: Table | string, foreignKeyOrName: TableForeignKey | string): Promise<void>;

  /**
   * Releases the query runner and closes connection
   * @returns Promise resolving when runner is released
   */
  release(): Promise<void>;
}

/**
 * Database table definition for migrations
 */
class Table {
  constructor(options: {
    name: string;
    columns: TableColumn[];
    indices?: TableIndex[];
    foreignKeys?: TableForeignKey[];
    uniques?: TableUnique[];
    checks?: TableCheck[];
    exclusions?: TableExclusion[];
    engine?: string;
    database?: string;
    schema?: string;
  });

  name: string;
  columns: TableColumn[];
  indices: TableIndex[];
  foreignKeys: TableForeignKey[];
  uniques: TableUnique[];
  checks: TableCheck[];
  exclusions: TableExclusion[];
}

/**
 * Database column definition for migrations
 */
class TableColumn {
  constructor(options: {
    name: string;
    type: ColumnType;
    length?: string | number;
    precision?: number;
    scale?: number;
    default?: any;
    isNullable?: boolean;
    isPrimary?: boolean;
    isGenerated?: boolean;
    generationStrategy?: "increment" | "uuid" | "rowid";
    isUnique?: boolean;
    comment?: string;
    collation?: string;
    charset?: string;
  });

  name: string;
  type: ColumnType;
  length?: string | number;
  isNullable: boolean;
  isPrimary: boolean;
  default?: any;
}

/**
 * Database index definition for migrations
 */
class TableIndex {
  constructor(options: {
    name?: string;
    columnNames: string[];
    isUnique?: boolean;
    isSpatial?: boolean;
    isFulltext?: boolean;
    where?: string;
    using?: string;
  });

  name?: string;
  columnNames: string[];
  isUnique: boolean;
}

/**
 * Foreign key constraint definition for migrations
 */
class TableForeignKey {
  constructor(options: {
    name?: string;
    columnNames: string[];
    referencedTableName: string;
    referencedColumnNames: string[];
    onDelete?: "RESTRICT" | "CASCADE" | "SET NULL" | "SET DEFAULT" | "NO ACTION";
    onUpdate?: "RESTRICT" | "CASCADE" | "SET NULL" | "SET DEFAULT" | "NO ACTION";
  });

  name?: string;
  columnNames: string[];
  referencedTableName: string;
  referencedColumnNames: string[];
  onDelete?: string;
  onUpdate?: string;
}

import { MigrationInterface, QueryRunner, Table, TableColumn, TableIndex, TableForeignKey } from "typeorm";

export class CreatePostsTable1635123456790 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: "post",
        columns: [
          new TableColumn({
            name: "id",
            type: "int",
            isPrimary: true,
            isGenerated: true,
            generationStrategy: "increment"
          }),
          new TableColumn({
            name: "title",
            type: "varchar",
            length: "255"
          }),
          new TableColumn({
            name: "content",
            type: "text"
          }),
          new TableColumn({
            name: "author_id",
            type: "int"
          }),
          new TableColumn({
            name: "published_at",
            type: "timestamp",
            isNullable: true
          }),
          new TableColumn({
            name: "created_at",
            type: "timestamp",
            default: "CURRENT_TIMESTAMP"
          })
        ]
      })
    );

    await queryRunner.createIndex(
      "post",
      new TableIndex({
        name: "IDX_POST_TITLE",
        columnNames: ["title"]
      })
    );

    await queryRunner.createForeignKey(
      "post",
      new TableForeignKey({
        columnNames: ["author_id"],
        referencedTableName: "user",
        referencedColumnNames: ["id"],
        onDelete: "CASCADE"
      })
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable("post");
  }
}

# Generate a new migration based on entity changes
typeorm migration:generate -n CreateUserTable

# Create an empty migration file
typeorm migration:create -n AddIndexToUser

# Run pending migrations
typeorm migration:run

# Revert last migration
typeorm migration:revert

# Show migration status
typeorm migration:show

// Execute migrations programmatically
const dataSource = new DataSource({
  // connection options
  migrations: ["src/migrations/*.ts"],
  migrationsRun: true // Automatically run migrations on startup
});

await dataSource.initialize();

// Manual migration execution
await dataSource.runMigrations();

// Revert migrations
await dataSource.undoLastMigration();

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

export class SafeColumnAddition1635123456791 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Add nullable column first
    await queryRunner.addColumn("user", new TableColumn({
      name: "phone",
      type: "varchar",
      length: "20",
      isNullable: true
    }));

    // Populate data if needed
    await queryRunner.query(`
      UPDATE "user" SET "phone" = '' WHERE "phone" IS NULL
    `);

    // Make column non-nullable if required
    await queryRunner.changeColumn("user", "phone", new TableColumn({
      name: "phone",
      type: "varchar",
      length: "20",
      isNullable: false,
      default: "''"
    }));
  }

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

export class MigrateUserData1635123456792 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Get all users
    const users = await queryRunner.query(`SELECT * FROM "user"`);

    // Process data in batches
    for (let i = 0; i < users.length; i += 100) {
      const batch = users.slice(i, i + 100);

      for (const user of batch) {
        // Transform and update data
        const transformedData = transformUserData(user);
        await queryRunner.query(
          `UPDATE "user" SET "new_field" = $1 WHERE "id" = $2`,
          [transformedData.newField, user.id]
        );
      }
    }
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Revert data changes
    await queryRunner.query(`UPDATE "user" SET "new_field" = NULL`);
  }
}