Skip to main content

Database Migrations

Yew Search uses TypeORM migrations to manage database schema changes in a version-controlled, reversible way.

Overview​

Migrations allow you to:

  • Track schema changes in version control (Git)
  • Apply changes incrementally in development and production
  • Rollback changes if something goes wrong
  • Collaborate safely with multiple developers
  • Avoid data loss from schema synchronization

IMPORTANT: Never use synchronize: true in production. It can drop tables and cause data loss.

Quick Start​

Check Migration Status​

See which migrations have run and which are pending:

npm run migration:show

Run Pending Migrations​

Apply all pending migrations to your database:

npm run migration:run

Rollback Last Migration​

Revert the most recently applied migration:

npm run migration:revert

Creating Migrations​

TypeORM can automatically generate migrations by comparing your entities to the database schema.

Workflow:

  1. Modify an entity file (e.g., add a column)
  2. Generate migration from changes: 
    npm run migration:generate -- src/migrations/AddEmailVerification
  3. Review the generated migration file
  4. Test on development database: 
    npm run migration:run
  5. Commit migration to Git

Example:

# After adding a new field to UserEntity
npm run migration:generate -- src/migrations/add-user-preferences

# TypeORM generates: src/migrations/1234567890-add-user-preferences.ts
# Review, test, commit

Method 2: Create Empty Migration for Manual SQL​

For complex changes or raw SQL, create an empty migration template:

npm run migration:create -- src/migrations/add-custom-indexes

Then fill in the up() and down() methods manually.

Migration File Structure​

Every migration has two methods:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddEmailVerification1234567890 implements MigrationInterface {
  // up() - Apply the change
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE "user"
        ADD COLUMN email_verified boolean DEFAULT false;
    `);
  }

  // down() - Revert the change
  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE "user"
        DROP COLUMN email_verified;
    `);
  }
}

Key Points:

  • up(): Applies your schema change (runs when executing migration)
  • down(): Reverts your schema change (runs when rolling back)
  • Transactions: TypeORM wraps migrations in transactions automatically
  • Query Runner: Provides database-agnostic API for schema changes

Configuration​

Migrations are configured in two places:

1. DataSource Configuration (backend/src/config/typeorm.config.ts)​

This file is used by the TypeORM CLI:

export default new DataSource({
  type: 'postgres',
  // ... database connection settings
  entities: [/* all entities */],
  migrations: ['src/migrations/*{.ts,.js}'],
  synchronize: false, // NEVER true with migrations!
});

2. NestJS Module (backend/src/app.module.ts)​

The runtime configuration for your application:

TypeOrmModule.forRootAsync({
  useFactory: (configService: ConfigService) => ({
    // ... database settings
    synchronize: configService.get('NODE_ENV') === 'development',
    autoLoadEntities: true,
  }),
})

IMPORTANT: Set synchronize: false in production to prevent automatic schema changes.

Available Scripts​

All migration scripts are defined in backend/package.json:

ScriptDescription
npm run migration:showShow migration status (which have run)
npm run migration:runRun all pending migrations
npm run migration:revertRollback the last executed migration
npm run migration:generate -- src/migrations/nameAuto-generate migration from entity changes
npm run migration:create -- src/migrations/nameCreate empty migration template

Best Practices​

1. Always Review Auto-Generated Migrations​

TypeORM's auto-generation is helpful but not perfect. Always review generated SQL before running:

npm run migration:generate -- src/migrations/update-user
# Open src/migrations/*-update-user.ts and review the SQL

2. Test Both Up and Down​

Always test that migrations can be rolled back:

npm run migration:run      # Apply migration
npm run migration:revert   # Test rollback
npm run migration:run      # Re-apply

3. Use Descriptive Names​

Good migration names describe the change:

  • ✅ add-user-email-verification
  • ✅ fix-timestamps-to-timestamptz
  • ✅ create-integration-indexes
  • ❌ update1
  • ❌ migration
  • ❌ fix

4. One Logical Change Per Migration​

Keep migrations focused on a single logical change:

# Good - Separate migrations
npm run migration:create -- src/migrations/add-user-preferences
npm run migration:create -- src/migrations/add-integration-retry

# Bad - Combined (harder to revert selectively)
npm run migration:create -- src/migrations/multiple-changes

5. Never Edit Existing Migrations​

Once a migration has been committed and run in any environment (dev, staging, production), never edit it.

Instead:

  • Create a new migration to fix the issue
  • Or revert the old migration and create a new one

6. Commit Migrations to Git​

Migrations are code. Always commit them:

git add src/migrations/
git add src/**/entities/*.entity.ts  # If you changed entities
git commit -m "Add migration for user preferences"

7. Backup Before Production Migrations​

Always backup your production database before running migrations:

# PostgreSQL backup
pg_dump -U postgres -d yew > backup_$(date +%Y%m%d_%H%M%S).sql

# Run migration
npm run migration:run

# If needed, restore
psql -U postgres -d yew < backup_20260118_120000.sql

8. Use Safe SQL Patterns​

Write SQL that's safe to run multiple times when possible:

-- ❌ WRONG - Fails if column exists
ALTER TABLE user ADD COLUMN email varchar(255);

-- ✅ CORRECT - Safe to run multiple times
ALTER TABLE user ADD COLUMN IF NOT EXISTS email varchar(255);

9. Document Complex Migrations​

Add comments to explain non-obvious changes:

export class FixTimestamps1234567890 implements MigrationInterface {
  /**
   * Converting timestamp to timestamptz requires specifying the timezone
   * for existing data. We assume all timestamps are UTC (from JavaScript Date objects).
   */
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE "user"
        ALTER COLUMN created_at TYPE timestamptz USING created_at AT TIME ZONE 'UTC';
    `);
  }
}

10. Check Migration Status Regularly​

Make it a habit to check migration status:

# Before starting work
npm run migration:show

# After pulling changes
npm run migration:run

Common Workflows​

Adding a New Column​

  1. Add column to entity:

    @Column({ type: 'boolean', default: false })
    public emailVerified: boolean;

  2. Generate migration:

    npm run migration:generate -- src/migrations/add-email-verified

  3. Review and test:

    npm run migration:run

Renaming a Column​

TypeORM can't auto-detect renames, so create manually:

npm run migration:create -- src/migrations/rename-user-status

Then write the SQL:

public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`
    ALTER TABLE "user"
      RENAME COLUMN status TO account_status;
  `);
}

public async down(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`
    ALTER TABLE "user"
      RENAME COLUMN account_status TO status;
  `);
}

Adding an Index​

npm run migration:create -- src/migrations/add-user-email-index
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`
    CREATE INDEX IF NOT EXISTS idx_user_email ON "user"(email);
  `);
}

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

Deployment​

Development Environment​

# Pull latest code
git pull

# Run any new migrations
npm run migration:run

# Start dev server
npm run start:dev

Production Environment​

# Backup database first!
pg_dump -U postgres -d yew_production > backup_$(date +%Y%m%d_%H%M%S).sql

# Pull latest code
git pull

# Install dependencies (if needed)
npm install

# Run migrations
npm run migration:run

# Restart application
pm2 restart yew-backend

Troubleshooting​

Migration Failed - How to Recover​

If a migration fails halfway through:

# 1. Check what went wrong
npm run migration:show

# 2. Try to revert (if possible)
npm run migration:revert

# 3. Or manually fix database and update migrations table
psql -U postgres -d yew
SELECT * FROM migrations ORDER BY timestamp DESC;
DELETE FROM migrations WHERE timestamp = 1234567890;  -- Remove failed entry

# 4. Fix the migration SQL and try again
npm run migration:run

Migration Already Applied​

If you see "migration already applied" error:

# Check status
npm run migration:show

# If it shows as applied but didn't actually run, manually remove from migrations table
psql -U postgres -d yew
DELETE FROM migrations WHERE name = 'MigrationName1234567890';

Entity and Database Out of Sync​

If your entities don't match the database:

# Generate migration to fix differences
npm run migration:generate -- src/migrations/sync-schema

# Review carefully before running!
cat src/migrations/*-sync-schema.ts

# Apply if correct
npm run migration:run

Migration History​

Initial Migration (2026-01-18)​

fix-all-timestamps-to-timestamptz

  • Converted all timestamp columns to timestamptz across 5 tables
  • Fixed timezone inconsistency causing polling issues
  • See: src/migrations/1737225600000-fix-all-timestamps-to-timestamptz.ts

Resources​

Support​

If you encounter issues with migrations:

  1. Check the Troubleshooting section above
  2. Review the migration file comments for context
  3. Check backend/plans/migrations.md for detailed research
  4. Open an issue on GitHub with migration logs