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: synchronize is hardcoded false for every environment, not just production (backend/src/app.module.ts) - migrations are the only schema-management mechanism here, full stop. In production (and the Docker/homelab deployments), migrations also run unconditionally on every boot, via the backend's own Dockerfile CMD - not as a separate manual step someone might forget. A SKIP_MIGRATIONS=true env var exists as a narrow escape hatch for a boot where the schema is already known to be correct (e.g. right after a raw DB restore), mirroring n8n's N8N_SKIP_MIGRATIONS.

Quick Start

Check Migration Status

See which migrations have run and which are pending:

pnpm run migration-pg:show

Run Pending Migrations

Apply all pending migrations to your database:

pnpm run migration-pg:run

Rollback Last Migration

Revert the most recently applied migration:

pnpm run migration-pg:revert

Creating Migrations

Method 1: Auto-Generate from Entity Changes (Recommended)

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

Workflow:

Modify an entity file (e.g., add a column)

Generate migration from changes:

pnpm run migration-pg:generate -- src/migrations/pg/AddEmailVerification

Review the generated migration file
Test on development database:
```
pnpm run migration-pg:run
```
Commit migration to Git

Example:

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

# TypeORM generates: src/migrations/pg/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:

pnpm run migration-pg:create -- src/migrations/pg/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/pg/*{.ts,.js}'],
  synchronize: false, // NEVER true with migrations!
});

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

The runtime configuration for your application:

TypeOrmModule.forRoot({
  // ... database settings
  autoLoadEntities: true,
  // Migrations are the only schema-management mechanism now, for
  // every environment - see the Dockerfile CMD.
  synchronize: false,
})

Unlike a typical NestJS setup, this is not conditional on NODE_ENV - synchronize: false applies identically in local development and production. There's no "dev auto-syncs, prod runs migrations" split here; every environment runs migrations the same way.

Available Scripts

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

Script	Description
`pnpm run migration-pg:show`	Show migration status (which have run)
`pnpm run migration-pg:run`	Run all pending migrations
`pnpm run migration-pg:revert`	Rollback the last executed migration
`pnpm run migration-pg:generate -- src/migrations/pg/name`	Auto-generate migration from entity changes
`pnpm run migration-pg:create -- src/migrations/pg/name`	Create empty migration template
`pnpm run migration-opensearch:run` / `:revert` / `:show` / `:create`	Same operations, for the parallel OpenSearch migration system (`src/migrations/opensearch/`)

Best Practices

1. Always Review Auto-Generated Migrations

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

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

2. Test Both Up and Down

Always test that migrations can be rolled back:

pnpm run migration-pg:run      # Apply migration
pnpm run migration-pg:revert   # Test rollback
pnpm run migration-pg: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
pnpm run migration-pg:create -- src/migrations/pg/add-user-preferences
pnpm run migration-pg:create -- src/migrations/pg/add-integration-retry

# Bad - Combined (harder to revert selectively)
pnpm run migration-pg:create -- src/migrations/pg/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/pg/
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
pnpm run migration-pg: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
pnpm run migration-pg:show

# After pulling changes
pnpm run migration-pg:run

Common Workflows

Adding a New Column

Add column to entity:

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

Generate migration:

pnpm run migration-pg:generate -- src/migrations/pg/add-email-verified

Review and test:
```
pnpm run migration-pg:run
```

Renaming a Column

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

pnpm run migration-pg:create -- src/migrations/pg/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

pnpm run migration-pg:create -- src/migrations/pg/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

Local Development

# Pull latest code
git pull

# Run any new migrations
pnpm run migration-pg:run
pnpm run migration-opensearch:run

# Start dev server
pnpm run start:dev

Production / Homelab (Docker)

There's no manual SSH-and-restart flow here - the backend's Dockerfile CMD runs both migrations unconditionally before start:prod on every boot:

[ "$SKIP_MIGRATIONS" = "true" ] || (pnpm run migration-pg:run:prod && pnpm run migration-opensearch:run:prod)
pnpm run start:prod

Deploying a new image (the GitLab CI manual-deploy-backend/ manual-deploy-api-docs jobs, or re-pulling the image on the homelab) is what triggers this - there's also a separate, explicit manual-run-production-migration CI job for running a migration ahead of a deploy, without the deploy itself.

Troubleshooting

Migration Failed - How to Recover

If a migration fails halfway through:

# 1. Check what went wrong
pnpm run migration-pg:show

# 2. Try to revert (if possible)
pnpm run migration-pg: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
pnpm run migration-pg:run

Migration Already Applied

If you see "migration already applied" error:

# Check status
pnpm run migration-pg: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
pnpm run migration-pg:generate -- src/migrations/pg/sync-schema

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

# Apply if correct
pnpm run migration-pg:run

Migration History

Initial Schema (2026-10-30)

InitialSchema

A single migration that creates the entire current schema from scratch (src/migrations/pg/1793367888341-InitialSchema.ts) - earlier, incremental migrations were consolidated into this one as part of the rewrite to run migrations unconditionally at every boot (see above).
There is a parallel, hand-rolled migration system for OpenSearch (src/migrations/opensearch/), run as its own step right after the Postgres migrations in the Dockerfile CMD.

Resources

Support

If you encounter issues with migrations:

Check the Troubleshooting section above
Review the migration file comments for context
Open an issue on GitHub with migration logs

Overview​

Quick Start​

Check Migration Status​

Run Pending Migrations​

Rollback Last Migration​

Creating Migrations​

Method 1: Auto-Generate from Entity Changes (Recommended)​

Method 2: Create Empty Migration for Manual SQL​

Migration File Structure​

Configuration​

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

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

Available Scripts​

Best Practices​

1. Always Review Auto-Generated Migrations​

2. Test Both Up and Down​

3. Use Descriptive Names​

4. One Logical Change Per Migration​

5. Never Edit Existing Migrations​

6. Commit Migrations to Git​

7. Backup Before Production Migrations​

8. Use Safe SQL Patterns​

9. Document Complex Migrations​

10. Check Migration Status Regularly​

Common Workflows​

Adding a New Column​

Renaming a Column​

Adding an Index​

Deployment​

Local Development​

Production / Homelab (Docker)​

Troubleshooting​

Migration Failed - How to Recover​

Migration Already Applied​

Entity and Database Out of Sync​

Migration History​

Initial Schema (2026-10-30)​

Resources​

Support​