Database Migration Guidelines

Database migrations are a critical part of the development lifecycle as they alter the production database schema. A poorly executed migration can lead to data loss or downtime. Therefore, all migrations must be handled with extreme care, following these principles to ensure safety, consistency, and reversibility.

Database Structure

The system uses three separate PostgreSQL databases, each with its own migration directory:

Database	Purpose	Migration Directory	npm Script Prefix
app_core_db	Core operational data (resources, engagements, records)	`libs/database/src/migrations/app_core_db/`	`migration:*:core`
app_master_db	Master/reference data (labels, regions, classification codes)	`libs/database/src/migrations/app_master_db/`	`migration:*:master`
app_iam_db	Identity & Access Management (users, roles, permissions)	`libs/database/src/migrations/app_iam_db/`	`migration:*:iam`

Migration File Locations:

All migration files are stored in libs/database/src/migrations/{database}/
Entity files remain in their respective bounded contexts (e.g., apps/data-owner-bc/src/modules/resource/entities/)
TypeORM data sources are located at libs/database/src/data-source.{database}.ts

Core Migration Principles

synchronize: false in Production: The TypeORM synchronize option must be false in staging and production environments. All schema changes in these environments must be managed exclusively through migration files.
Migrations are Immutable: Once a migration file has been merged into the main branch (develop or main), it must never be edited. If a mistake is found, create a new migration file to correct it.
Small and Atomic: Each migration file should represent a single, atomic change (e.g., add one column, create one table). This simplifies reviews, debugging, and rollbacks.
Always Reversible: Every migration’s up() method must have a corresponding down() method that perfectly reverses its changes. This is non-negotiable for safe rollbacks.
Always Test Locally: Never commit a migration file without first testing it locally using both migration:run and migration:revert.

Choosing the Right Command: `generate` vs. `create`

There are two primary commands for creating migration files, each for a different purpose.

migration:generate (The Automatic Way): Use this 95% of the time. It automatically compares your entity files (.entity.ts) to the database schema and generates the necessary SQL for any changes. This is the standard for all schema changes like adding/modifying columns or tables.
migration:create (The Manual Way): Use this for special cases where you need to write custom SQL that is not related to entity changes. This includes:
- Data Migrations: Updating existing data (e.g., UPDATE "resources" SET "status" = 'active').
- Adding Complex Indexes: Creating performance-critical indexes like GIN with pg_trgm, which are best handled with explicit SQL.

Standard Workflow: Generating Migrations from Entity Changes

This is the most common workflow for day-to-day development.

1. Modify the Entity Make the necessary changes to your .entity.ts files (e.g., add a new @Column()).

2. Generate the Migration File Run the generate command from the project root. Use a descriptive name as a parameter.

# Generate migration for CORE database (app_core_db)
npm run migration:generate:core --name=AddStatusToResource

# Generate migration for MASTER database (app_master_db)
npm run migration:generate:master --name=AddNewMasterDataTable

# Generate migration for IAM database (app_iam_db)
npm run migration:generate:iam --name=AddUserPermissions

Common npm Scripts for Migrations:

The following scripts are available in package.json for each database:

{
    "scripts": {
        "migration:generate:core": "npm run typeorm:core -- migration:generate libs/database/src/migrations/app_core_db/$npm_config_name",
        "migration:generate:master": "npm run typeorm:master -- migration:generate libs/database/src/migrations/app_master_db/$npm_config_name",
        "migration:generate:iam": "npm run typeorm:iam -- migration:generate libs/database/src/migrations/app_iam_db/$npm_config_name",
        "migration:create:core": "npm run typeorm:core -- migration:create libs/database/src/migrations/app_core_db/$npm_config_name",
        "migration:create:master": "npm run typeorm:master -- migration:create libs/database/src/migrations/app_master_db/$npm_config_name",
        "migration:create:iam": "npm run typeorm:iam -- migration:create libs/database/src/migrations/app_iam_db/$npm_config_name",
        "migration:run:core": "npm run typeorm:core -- migration:run",
        "migration:run:master": "npm run typeorm:master -- migration:run",
        "migration:run:iam": "npm run typeorm:iam -- migration:run",
        "migration:run:all": "npm run migration:run:core && npm run migration:run:master && npm run migration:run:iam",
        "migration:revert:core": "npm run typeorm:core -- migration:revert",
        "migration:revert:master": "npm run typeorm:master -- migration:revert",
        "migration:revert:iam": "npm run typeorm:iam -- migration:revert"
    }
}

Usage Examples:

# Generate a migration from entity changes (CORE database)
npm run migration:generate:core --name=AddCoverageTypeToResource

# Run all pending migrations for CORE database
npm run migration:run:core

# Revert the last migration for CORE database
npm run migration:revert:core

# Run migrations for all databases
npm run migration:run:all

# Create a manual migration for adding indexes (CORE database)
npm run migration:create:core --name=AddSearchIndexes

# Generate migration for MASTER database
npm run migration:generate:master --name=AddRegionData

# Generate migration for IAM database
npm run migration:generate:iam --name=AddRoleHierarchy

3. CRITICAL: Review the Generated File Manually inspect the new migration file. Verify that the SQL in both the up() and down() methods is correct and logical.

4. Test the Migration Locally Verify that the script works in both directions.

# Test the 'up()' method
npm run migration:run:core

# Test the 'down()' method
npm run migration:revert:core

5. Commit and Push Commit both the modified entity file and the new migration file together.

Running Specific Migration Files

Sometimes you need to run or re-run a specific migration file rather than running all pending migrations. This is useful when:

Testing a specific migration before deploying
Re-running a migration with the --force flag
Manually managing migration order
Debugging migration issues

Using the run-migration Scripts

We provide three convenience scripts, one for each database:

# For CORE database
npm run migration:run:file:core --name=1234567890123-CreateResourcesTable

# For MASTER database
npm run migration:run:file:master --name=1234567890123-CreateLabels

# For IAM database
npm run migration:run:file:iam --name=1234567890123-CreateUsers

Features of these scripts:

✅ Validates the migration file exists before execution
✅ Checks if migration was already executed (prevents duplicates)
✅ Shows all available migrations if target not found
✅ Supports --force flag to re-run completed migrations
✅ Uses transactions with automatic rollback on errors
✅ Provides detailed console output with status indicators

Examples

Example 1: Run a specific migration

npm run migration:run:file:core --name=1234567890123-CreateResourcesTable

Output:

🚀 Running migration: 1234567890123-CreateResourcesTable
✅ Database connection established
✅ Migration found: 1234567890123-CreateResourcesTable
✅ Migration completed successfully!
🔌 Database connection closed

Example 2: Re-run an already-executed migration with —force

npm run migration:run:file:core --name=1234567890123-CreateResourcesTable --force

This will re-execute the migration even if it was previously run.

Example 3: Partial name matching

You can use a partial migration name:

npm run migration:run:file:core --name=CreateResources

The script will find the first migration containing “CreateResources” in its name.

Example 4: List all available migrations

If you specify a migration that doesn’t exist, the script will show all available migrations:

npm run migration:run:file:core --name=invalid

# Output shows all available migrations:
# Available migrations:
#   - 1234567890123-CreateResourcesTable
#   - 1234567890124-AddCoverageType
#   - 1234567890125-CreateEngagements

Script Implementation

The scripts are located at:

scripts/run-migration.ts (for core database)
scripts/run-migration-master.ts (for master database)
scripts/run-migration-iam.ts (for IAM database)

Each script:

Loads the DataSource configuration for its database
Initializes the database connection
Validates the migration exists in the list of loaded migrations
Checks if the migration was already executed
Uses QueryRunner with transactions for safe execution
Handles errors with automatic rollback
Provides feedback to the user

Special Workflow: Manually Creating Migrations (e.g., for Indexes)

Use this workflow when you need to add things like performance indexes, which require custom SQL.

1. Create an Empty Migration File Use the create command with a descriptive name.

# Create a manual migration for CORE database
npm run migration:create:core --name=AddSearchIndexesToResource

# Create a manual migration for MASTER database
npm run migration:create:master --name=AddCustomIndexes

# Create a manual migration for IAM database
npm run migration:create:iam --name=AddPermissionIndexes

2. Write the SQL Manually Open the new, empty migration file. In the up() method, add the SQL commands to create the indexes. In the down() method, add the corresponding DROP INDEX commands.

// Example: Adding a GIN index

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`CREATE EXTENSION IF NOT EXISTS pg_trgm;`);
    await queryRunner.query(
        `CREATE INDEX "idx_gin_trgm_resources_first_name" ON "resources" USING gin (first_name gin_trgm_ops)`
    );
}

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

3. Test and Commit Follow the same local testing (run and revert) and commit procedures as the standard workflow.

Important Note: TypeORM Limitations with GIN Indexes

TypeORM does not natively support GIN (Generalized Inverted Index) indexes through entity decorators. While TypeORM can handle standard B-tree indexes via @Index(), advanced indexes like:

GIN indexes with pg_trgm (trigram) operators
GiST (Generalized Search Tree) indexes
BRIN (Block Range Index) indexes
Full-text search vector indexes

These must be created manually using the migration:create workflow (not migration:generate).

Why? These specialized indexes require custom SQL syntax that TypeORM’s schema generator cannot produce automatically. They are performance-critical for advanced queries and full-text search.

Best Practice:

Use migration:generate for entity changes (columns, tables, relationships)
Use migration:create for custom indexes and performance-critical SQL
Always write the down() method to drop these indexes for reversibility

Example: Creating a GIN Index Manually

public async up(queryRunner: QueryRunner): Promise<void> {
    // Ensure the pg_trgm extension is available
    await queryRunner.query(`CREATE EXTENSION IF NOT EXISTS pg_trgm;`);

    // Create the GIN trigram index
    await queryRunner.query(`
        CREATE INDEX "idx_gin_trgm_resources_reference_number"
        ON "resources"
        USING gin ("reference_number" gin_trgm_ops)
    `);
}

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

Final Example: Complete Migration File for Search Indexes

Below is a complete, best-practice example of a migration file created to add GIN indexes for both full-text search (tsvector) and partial search (pg_trgm).

import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddSearchIndexesToResource1759221182216 implements MigrationInterface {
    name = 'AddSearchIndexesToResource1759221182216';

    public async up(queryRunner: QueryRunner): Promise<void> {
        // --- For Full-Text Search ---
        // Creates a GIN index on the pre-existing `search_vector` column.
        await queryRunner.query(`
            CREATE INDEX "idx_gin_resources_search_vector"
            ON "resources"
            USING GIN (search_vector);
        `);

        // --- For Partial Search (Trigram) ---
        // 1. Ensure the pg_trgm extension is available.
        await queryRunner.query(`CREATE EXTENSION IF NOT EXISTS pg_trgm;`);

        // 2. Create GIN trigram indexes on frequently searched text columns.
        await queryRunner.query(
            `CREATE INDEX "idx_gin_trgm_resources_reference_number" ON "resources" USING gin ("reference_number" gin_trgm_ops)`,
        );
        await queryRunner.query(
            `CREATE INDEX "idx_gin_trgm_resources_national_id" ON "resources" USING gin ("national_id" gin_trgm_ops)`,
        );
        await queryRunner.query(
            `CREATE INDEX "idx_gin_trgm_resources_passport_id" ON "resources" USING gin ("passport_id" gin_trgm_ops)`,
        );
        await queryRunner.query(
            `CREATE INDEX "idx_gin_trgm_resources_first_name" ON "resources" USING gin ("first_name" gin_trgm_ops)`,
        );
        await queryRunner.query(
            `CREATE INDEX "idx_gin_trgm_resources_last_name" ON "resources" USING gin ("last_name" gin_trgm_ops)`,
        );
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP INDEX IF EXISTS "idx_gin_resources_search_vector"`);
        await queryRunner.query(`DROP INDEX IF EXISTS "idx_gin_trgm_resources_reference_number"`);
        await queryRunner.query(`DROP INDEX IF EXISTS "idx_gin_trgm_resources_national_id"`);
        await queryRunner.query(`DROP INDEX IF EXISTS "idx_gin_trgm_resources_passport_id"`);
        await queryRunner.query(`DROP INDEX IF EXISTS "idx_gin_trgm_resources_first_name"`);
        await queryRunner.query(`DROP INDEX IF EXISTS "idx_gin_trgm_resources_last_name"`);
    }
}

Handling a Failed Migration

If a migration fails during deployment, follow this emergency procedure:

Do not attempt to fix it on the server.
Roll back the failed migration: Run npm run migration:revert:core (or the appropriate database: core, master, or iam). This executes the down() method of the last migration.
Redeploy the PREVIOUS version of the code.
Restore the service by restarting the application.
Investigate the issue in a development environment.

Migration Workflows by Environment

Development Workflow

Goal: Fast iteration with frequent schema changes and testing.

Process:

Modify Entity: Update .entity.ts file with new column, relationship, or constraint
Generate Migration: npm run migration:generate:core --name=DescriptiveChangeName
Review: Examine the generated SQL in the migration file
Test Up: npm run migration:run:core — Verify the migration applies without errors
Test Down: npm run migration:revert:core — Verify rollback works perfectly
Commit: Commit BOTH entity and migration file together
Push: Push to feature branch for code review

Key Points:

Run migrations frequently during development
Always test both directions (up and down)
Never edit a committed migration file (create a corrective migration instead)
Share migrations with team via version control

Example:

# Edit resource.entity.ts to add a new field
# Then generate migration
npm run migration:generate:core --name=AddCoverageTypeToResource

# Review the generated SQL file
# Then test it
npm run migration:run:core      # Test UP
npm run migration:revert:core   # Test DOWN

# Commit and push
git add apps/data-owner-bc/src/modules/resource/entities/resource.entity.ts
git add libs/database/src/migrations/app_core_db/1759221182216-AddCoverageTypeToResource.ts
git commit -m "feat(resource): add coverage_type field"

Production Workflow

Goal: Safe, audited, reversible schema changes with zero downtime.

Process:

Planning Phase (Before merging to main/develop):
- Review all migrations in the PR
- Verify data backup exists
- Test migrations in staging environment
- Ensure all migrations are reversible
Deployment Phase:
- Deploy new code to production
- Run pending migrations: npm run migration:run:core (or npm run migration:run:all for all databases)
- Monitor for errors and verify data integrity
- Confirm application starts successfully
Verification Phase:
- Test affected features with real production data
- Monitor application logs for any issues
- Check query performance with new indexes
Rollback Plan (if needed):
- Revert migrations: npm run migration:revert:core (or the appropriate database)
- Redeploy previous code version
- Notify team of rollback

Deployment Checklist:

□ All migrations merged to main/develop branch
□ Tested in staging environment
□ Database backup created
□ Rollback plan documented
□ Team notified of deployment window
□ Code deployed to production
□ Run: npm run migration:run:core (or npm run migration:run:all)
□ Verify application health
□ Test critical features
□ Monitor logs for 30 minutes
□ Confirm with team
□ Update deployment status

Data Migration Workflow

Goal: Safely transform or backfill existing data when schema changes.

When Needed:

Renaming or splitting columns
Changing data types (string to number)
Backfilling new required columns
Migrating data between services
Data cleanup (trimming, normalization)

Process:

Create Manual Migration: npm run migration:create:core --name=MigrateResourceData

Write SQL in up() Method:

public async up(queryRunner: QueryRunner): Promise<void> {
    // Step 1: Add new column (if needed)
    await queryRunner.query(`ALTER TABLE "resources" ADD "new_column" VARCHAR;`);

    // Step 2: Backfill data with transformation logic
    await queryRunner.query(`
        UPDATE "resources"
        SET "new_column" = UPPER("old_column")
        WHERE "new_column" IS NULL;
    `);

    // Step 3: Add constraints (if needed)
    await queryRunner.query(`
        ALTER TABLE "resources"
        ALTER COLUMN "new_column" SET NOT NULL;
    `);
}

Write Rollback in down() Method:

public async down(queryRunner: QueryRunner): Promise<void> {
    // Reverse the changes in opposite order
    await queryRunner.query(`
        ALTER TABLE "resources"
        ALTER COLUMN "new_column" DROP NOT NULL;
    `);
    await queryRunner.query(`ALTER TABLE "resources" DROP COLUMN "new_column";`);
}

Test Thoroughly:

npm run migration:run:core
# Verify data is correct
npm run migration:revert:core
# Verify original state restored
npm run migration:run:core

Commit and Deploy:
- Commit migration file
- Deploy to production with same process as schema migrations
- Verify data integrity after deployment

Data Migration Examples:

// Example 1: Backfill Required Field
public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
        UPDATE "resources"
        SET "coverage_type" = 'UNKNOWN'
        WHERE "coverage_type" IS NULL;
    `);

    await queryRunner.query(`
        ALTER TABLE "resources"
        ALTER COLUMN "coverage_type" SET NOT NULL;
    `);
}

// Example 2: Split Column
public async up(queryRunner: QueryRunner): Promise<void> {
    // Add new columns
    await queryRunner.query(`ALTER TABLE "resources" ADD "first_name" VARCHAR;`);
    await queryRunner.query(`ALTER TABLE "resources" ADD "last_name" VARCHAR;`);

    // Split full_name into first and last
    await queryRunner.query(`
        UPDATE "resources"
        SET
            "first_name" = SPLIT_PART("full_name", ' ', 1),
            "last_name" = SPLIT_PART("full_name", ' ', 2)
        WHERE "full_name" IS NOT NULL;
    `);
}

// Example 3: Data Type Conversion
public async up(queryRunner: QueryRunner): Promise<void> {
    // Create temporary column with new type
    await queryRunner.query(`ALTER TABLE "resources" ADD "age_temp" INTEGER;`);

    // Copy data with type conversion
    await queryRunner.query(`
        UPDATE "resources"
        SET "age_temp" = CAST("age" AS INTEGER)
        WHERE "age" ~ '^[0-9]+$'; -- Only convert valid numeric strings
    `);

    // Replace old column with new one
    await queryRunner.query(`ALTER TABLE "resources" DROP COLUMN "age";`);
    await queryRunner.query(`ALTER TABLE "resources" RENAME COLUMN "age_temp" TO "age";`);
}

Database Architecture Overview — Three-database architecture
Database Optimization for Large-Scale Tables — Partial indexes and archival strategies
Entity & DTO Principles — Entity definition standards

Database Migration Guidelines

Database Structure

Core Migration Principles

Choosing the Right Command: generate vs. create

Standard Workflow: Generating Migrations from Entity Changes

Running Specific Migration Files

Using the run-migration Scripts

Examples

Example 1: Run a specific migration

Example 2: Re-run an already-executed migration with —force

Example 3: Partial name matching

Example 4: List all available migrations

Script Implementation

Special Workflow: Manually Creating Migrations (e.g., for Indexes)

Important Note: TypeORM Limitations with GIN Indexes

Final Example: Complete Migration File for Search Indexes

Handling a Failed Migration

Migration Workflows by Environment

Development Workflow

Production Workflow

Data Migration Workflow

Related Documentation

Choosing the Right Command: `generate` vs. `create`