Database Migrations

APSO uses TypeORM migrations to manage database schema changes safely and reproducibly.

Overview

Migrations are versioned SQL scripts that modify your database schema. They ensure:

Reproducibility — Same schema across all environments
Version control — Track changes in git
Rollback — Undo changes if needed
Team collaboration — Share schema changes

Migration Workflow

1. Modify Schema

Edit your .apsorc file:


{
  "entities": {
    "Project": {
      "fields": {
        "name": { "type": "string", "required": true },
        "priority": { "type": "number", "default": 0 }  // New field
      }
    }
  }
}

2. Regenerate Code


npm run apso:generate

3. Generate Migration


npm run db:migrate:create

This creates a migration file like src/migrations/1705000000000-AddProjectPriority.ts:


import { MigrationInterface, QueryRunner } from 'typeorm';
 
export class AddProjectPriority1705000000000 implements MigrationInterface {
  name = 'AddProjectPriority1705000000000';
 
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE "projects"
      ADD "priority" integer NOT NULL DEFAULT 0
    `);
  }
 
  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE "projects"
      DROP COLUMN "priority"
    `);
  }
}

4. Run Migration


npm run db:migrate

Commands Reference

Command	Description
`npm run db:migrate`	Run pending migrations
`npm run db:migrate:create`	Generate migration from schema diff
`npm run db:migrate:status`	Show migration status
`npm run db:migrate:rollback`	Rollback last migration
`npm run db:reset`	Drop database and re-run all migrations

Migration Types

Auto-Generated

When you run db:migrate:create, APSO compares your current schema with the database and generates the necessary SQL:


npm run db:migrate:create
# Creates: src/migrations/1705000000000-AutoMigration.ts

Manual Migrations

For complex changes, create a migration manually:


npm run db:migrate:create -- --name AddIndexes

Then edit the generated file:


export class AddIndexes1705000000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE INDEX "idx_projects_status" ON "projects" ("status");
      CREATE INDEX "idx_tasks_project_id" ON "tasks" ("project_id");
    `);
  }
 
  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DROP INDEX "idx_projects_status";
      DROP INDEX "idx_tasks_project_id";
    `);
  }
}

Environment-Specific Migrations

Development

In development, you can use synchronize: true for rapid iteration:


{
  "database": {
    "synchronize": true  // Auto-sync schema (dev only!)
  }
}

Warning: Never enable synchronize in production. It can cause data loss.

Production

Always use migrations in production:


# Build the application
npm run build
 
# Run migrations
npm run db:migrate
 
# Start the server
npm run start:prod

Migration Best Practices

1. Always Create Down Migrations

Every up should have a corresponding down:


public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`ALTER TABLE "users" ADD "avatar_url" varchar`);
}
 
public async down(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`ALTER TABLE "users" DROP COLUMN "avatar_url"`);
}

2. Test Migrations Before Deploying


# Run migrations
npm run db:migrate
 
# Verify schema
npm run db:migrate:status
 
# Rollback to test down migration
npm run db:migrate:rollback
 
# Re-run
npm run db:migrate

3. Handle Data Migrations

For migrations that require data transformation:


export class MigrateUserNames1705000000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Add new columns
    await queryRunner.query(`
      ALTER TABLE "users"
      ADD "first_name" varchar,
      ADD "last_name" varchar
    `);
 
    // Migrate data
    await queryRunner.query(`
      UPDATE "users"
      SET
        "first_name" = split_part("name", ' ', 1),
        "last_name" = split_part("name", ' ', 2)
    `);
 
    // Remove old column
    await queryRunner.query(`
      ALTER TABLE "users" DROP COLUMN "name"
    `);
  }
 
  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE "users"
      ADD "name" varchar
    `);
 
    await queryRunner.query(`
      UPDATE "users"
      SET "name" = concat("first_name", ' ', "last_name")
    `);
 
    await queryRunner.query(`
      ALTER TABLE "users"
      DROP COLUMN "first_name",
      DROP COLUMN "last_name"
    `);
  }
}

4. Use Transactions

Wrap complex migrations in transactions:


public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.startTransaction();
  try {
    await queryRunner.query(`...`);
    await queryRunner.query(`...`);
    await queryRunner.commitTransaction();
  } catch (error) {
    await queryRunner.rollbackTransaction();
    throw error;
  }
}

Troubleshooting

Migration Already Exists

If a migration name conflicts:


# Delete the conflicting migration file
rm src/migrations/1705000000000-Conflict.ts
 
# Regenerate with new timestamp
npm run db:migrate:create

Database Out of Sync

If your database doesn’t match expected state:


# Check current status
npm run db:migrate:status
 
# Force sync (development only)
npm run db:reset

Failed Migration

If a migration fails partway through:


# Check what ran
npm run db:migrate:status
 
# Fix the migration file manually
# Then re-run
npm run db:migrate

CI/CD Integration

Run migrations as part of your deployment:


# GitHub Actions example
- name: Run migrations
  run: npm run db:migrate
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}