Database Migrations

This project uses the Expand and Contract pattern for database migrations to support zero-downtime deployments.

Overview

The expand and contract pattern splits database changes into two phases:

• Pre-deploy (Expand): Additive, non-breaking changes that are backwards compatible with the current API version
• Post-deploy (Contract): Breaking changes that require the new API version to be deployed first

This allows the database and API to be updated independently while maintaining compatibility during the deployment window.

Migration Folders

• pre-deploy/ - Migrations that run before the API is deployed
• post-deploy/ - Migrations that run after the API is deployed

Note: Root folder migrations (not in pre-deploy or post-deploy) are legacy migrations created before the expand-and-contract pattern was introduced. These run during migration:run:init only.

Developer Workflow

1. Make Changes to Database Entities

Modify the TypeORM entity files in src/**/*.entity.ts as needed.

2. Generate Migrations

Run the migration generator:

npm run migration:generate

This creates the same autogenerated migration in both pre-deploy/ and post-deploy/ folders with a timestamp prefix.

3. Analyze and Adjust Migrations

This is the critical step. You MUST analyze the generated migrations and determine:

• Which changes are safe to run before the API deployment (pre-deploy)
• Which changes require the new API to be running first (post-deploy)
• Whether manual adjustments are needed for zero-downtime compatibility

Example Scenarios

Adding a new field (Pre-deploy only)

When adding a new nullable column or a column with a default value:

// pre-deploy/migration.ts
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`ALTER TABLE "workspace" ADD "description" varchar NULL`);
}

// post-deploy/migration.ts
// DELETE the generated migration - no post-deploy changes needed

The new column can be added before the API deployment since the old API will simply ignore it.

Dropping a field (Post-deploy only)

When removing a column:

// pre-deploy/migration.ts
// DELETE the generated migration - no pre-deploy changes needed

// post-deploy/migration.ts
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`ALTER TABLE "workspace" DROP COLUMN "legacy_field"`);
}

The column must only be dropped after the new API is deployed, since the old API may still be reading from it.

Renaming a field (Expand then Contract)

Renaming requires both phases to maintain zero-downtime. Use a database trigger to keep columns synchronized automatically:

// pre-deploy/migration.ts (Expand)
public async up(queryRunner: QueryRunner): Promise<void> {
  // Add new column
  await queryRunner.query(`ALTER TABLE "workspace" ADD "display_name" varchar NULL`);
  // Copy existing data
  await queryRunner.query(`UPDATE "workspace" SET "display_name" = "name"`);
  // Create trigger to keep columns in sync during transition
  await queryRunner.query(`
    CREATE OR REPLACE FUNCTION sync_workspace_name()
    RETURNS TRIGGER AS $$
    BEGIN
      IF NEW.display_name IS NOT NULL THEN
        NEW.name := NEW.display_name;
      ELSIF NEW.name IS NOT NULL THEN
        NEW.display_name := NEW.name;
      END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
  `);
  await queryRunner.query(`
    CREATE TRIGGER workspace_name_sync
    BEFORE INSERT OR UPDATE ON "workspace"
    FOR EACH ROW EXECUTE FUNCTION sync_workspace_name();
  `);
}

// post-deploy/migration.ts (Contract)
public async up(queryRunner: QueryRunner): Promise<void> {
  // Remove trigger and old column
  await queryRunner.query(`DROP TRIGGER workspace_name_sync ON "workspace"`);
  await queryRunner.query(`DROP FUNCTION sync_workspace_name()`);
  await queryRunner.query(`ALTER TABLE "workspace" DROP COLUMN "name"`);
}

How the trigger works:

The trigger intercepts every INSERT and UPDATE on the table and automatically copies the value between columns:

Deployment timeline:

1. Pre-deploy migration runs → Trigger is active, both columns exist
2. Rolling deployment begins → Mix of old and new API instances, trigger keeps data in sync
3. Rolling deployment completes → All instances are new API
4. Post-deploy migration runs → Trigger and old column are removed

New API code changes:

The new API should read from and write to display_name only. The trigger handles backward compatibility with old API instances—no dual-write logic needed in application code.

Migration Scripts

npm run migration:run:init

Runs all migrations from both pre-deploy and post-deploy folders. Use this for:

• Initial database setup
• Development environments
• Fresh database instances

npm run migration:run:pre-deploy

Runs only migrations in the pre-deploy/ folder. Use this:

• Before deploying a new API version
• As part of your CI/CD pipeline, before the rolling update begins

npm run migration:run:post-deploy

Runs only migrations in the post-deploy/ folder. Use this:

• After the new API version is fully deployed
• As part of your CI/CD pipeline, after the rolling update completes

Reverting Migrations

npm run migration:revert

This reverts the last executed migration from either folder (based on the combined migration history in the database).

Important behaviors:

• Reverts one migration at a time - run multiple times to revert multiple migrations
• Uses the combined data-source that sees all migrations
• The revert order follows the execution timestamp, not the folder structure
• Always test revert scripts in development before relying on them in production

Recommendation: After reverting, you may need to also revert the corresponding entity changes and regenerate migrations to keep everything in sync.