Database Migrations

Database migrations manage schema changes over time. Learn to implement migration systems that enable safe, reversible database updates.

Challenge

Design and implement a migration system that enables safe schema evolution with rollback capabilities and consistency across environments.

Explanation

Migration Concepts

Version Control: Track schema changes
Rollback: Revert to previous state
Idempotency: Safe to run multiple times
Consistency: Maintain data integrity
Automation: Integrate with deployment

Popular Tools

Sequelize: ORM with migrations
TypeORM: TypeScript ORM
Knex.js: Query builder with migrations
db-migrate: Framework-agnostic
Flyway: Java-based, also supports Node

Code Examples

1. Simple Migration System

const fs = require('fs');
const path = require('path');

class MigrationManager {
  constructor(database, migrationsDir = './migrations') {
    this.database = database;
    this.migrationsDir = migrationsDir;
    this.migrations = [];
  }

  async loadMigrations() {
    const files = fs.readdirSync(this.migrationsDir);

    this.migrations = files
      .filter(f => f.endsWith('.js'))
      .sort()
      .map(f => ({
        name: f,
        path: path.join(this.migrationsDir, f),
      }));
  }

  async getMigrationStatus() {
    const applied = await this.database.query(
      `SELECT name FROM __migrations ORDER BY applied_at`
    );

    const appliedSet = new Set(applied.map(m => m.name));
    const pending = this.migrations.filter(m => !appliedSet.has(m.name));

    return { applied, pending };
  }

  async migrate() {
    const { pending } = await this.getMigrationStatus();

    if (pending.length === 0) {
      console.log('No pending migrations');
      return;
    }

    for (const migration of pending) {
      await this.runMigration(migration);
    }
  }

  async runMigration(migration) {
    console.log(`Running migration: ${migration.name}`);

    try {
      const module = require(migration.path);

      await this.database.transaction(async (trx) => {
        await module.up(trx);
      });

      await this.database.query(
        'INSERT INTO __migrations (name, applied_at) VALUES (?, ?)',
        [migration.name, new Date()]
      );

      console.log(`✓ ${migration.name}`);
    } catch (error) {
      console.error(`✗ ${migration.name}: ${error.message}`);
      throw error;
    }
  }

  async rollback() {
    const applied = await this.database.query(
      `SELECT name FROM __migrations ORDER BY applied_at DESC LIMIT 1`
    );

    if (applied.length === 0) {
      console.log('No migrations to rollback');
      return;
    }

    const migration = applied[0];
    const migrationFile = this.migrations.find(m => m.name === migration.name);

    if (!migrationFile) {
      throw new Error(`Migration file not found: ${migration.name}`);
    }

    console.log(`Rolling back: ${migration.name}`);

    try {
      const module = require(migrationFile.path);

      await this.database.transaction(async (trx) => {
        await module.down(trx);
      });

      await this.database.query(
        'DELETE FROM __migrations WHERE name = ?',
        [migration.name]
      );

      console.log(`✓ Rolled back ${migration.name}`);
    } catch (error) {
      console.error(`✗ Rollback failed: ${error.message}`);
      throw error;
    }
  }
}

// migrations/001_initial_schema.js
module.exports = {
  up: async (knex) => {
    await knex.schema.createTable('users', (table) => {
      table.increments('id').primary();
      table.string('email').unique().notNullable();
      table.string('password').notNullable();
      table.string('name');
      table.timestamps(true, true);
    });

    await knex.schema.createTable('posts', (table) => {
      table.increments('id').primary();
      table.integer('user_id').unsigned().notNullable().references('users.id');
      table.string('title').notNullable();
      table.text('content');
      table.timestamps(true, true);
    });
  },

  down: async (knex) => {
    await knex.schema.dropTableIfExists('posts');
    await knex.schema.dropTableIfExists('users');
  },
};

2. Knex.js Migrations

module.exports = {
  development: {
    client: 'postgresql',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './db/migrations',
      extension: 'js',
    },
    seeds: {
      directory: './db/seeds',
    },
  },
  production: {
    client: 'postgresql',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './db/migrations',
    },
  },
};

// Create migration
// npx knex migrate:make create_users_table

// db/migrations/001_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id');
    table.string('email').unique();
    table.string('password');
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

// Run migrations
// npx knex migrate:latest
// npx knex migrate:rollback

3. Sequelize Migrations

module.exports = {
  development: {
    username: 'postgres',
    password: 'password',
    database: 'myapp_dev',
    host: 'localhost',
    dialect: 'postgres',
  },
};

// migrations/20240101000000-create-users.js
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      email: {
        allowNull: false,
        type: Sequelize.STRING,
        unique: true,
      },
      password: {
        allowNull: false,
        type: Sequelize.STRING,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('users');
  },
};

// Run migrations
// npx sequelize-cli db:migrate
// npx sequelize-cli db:migrate:undo

4. TypeORM Migrations

import { MigrationInterface, QueryRunner, Table } from 'typeorm';

export class CreateUsers1234567890000 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'integer',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'email',
            type: 'varchar',
            isUnique: true,
            isNullable: false,
          },
          {
            name: 'password',
            type: 'varchar',
            isNullable: false,
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()',
          },
        ],
      })
    );
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('users');
  }
}

// Run migrations
// typeorm migration:run
// typeorm migration:revert

5. Safe Schema Changes

// Migration with data transformation
exports.up = async function(knex) {
  // Step 1: Add new column
  await knex.schema.alterTable('users', (table) => {
    table.string('first_name');
    table.string('last_name');
  });

  // Step 2: Migrate data
  await knex.raw(`
    UPDATE users
    SET first_name = split_part(name, ' ', 1),
        last_name = split_part(name, ' ', 2)
    WHERE name IS NOT NULL
  `);

  // Step 3: Drop old column (in separate migration)
};

exports.down = async function(knex) {
  // Add name column back if needed
  // Copy data back
  // Drop first_name and last_name
};

// Zero-downtime migration pattern
exports.up = async function(knex) {
  // 1. Add new column with default
  await knex.schema.alterTable('users', (table) => {
    table.string('email_verified').defaultTo('false');
  });

  // 2. Backfill data
  await knex('users').update({ email_verified: 'true' });

  // 3. Application can be deployed with dual-write logic
  // 4. After rollover period, drop old column
};

6. Migration Validation

class MigrationValidator {
  async validateMigration(migration) {
    const errors = [];

    // Check if migration has up and down methods
    if (typeof migration.up !== 'function') {
      errors.push('Migration must have an "up" method');
    }
    if (typeof migration.down !== 'function') {
      errors.push('Migration must have a "down" method');
    }

    // Warn about data loss operations
    const upCode = migration.up.toString();
    if (upCode.includes('dropTable') || upCode.includes('dropColumn')) {
      console.warn('Migration includes data-destructive operations');
    }

    return errors;
  }

  async testMigration(database, migration) {
    try {
      // Test up
      await database.transaction(async (trx) => {
        await migration.up(trx);
        throw new Error('Rollback for testing');
      });
    } catch (err) {
      if (err.message !== 'Rollback for testing') {
        throw err;
      }
    }

    console.log('Migration test passed');
  }
}

Best Practices

Naming and Organization
- Use timestamps for naming
- One change per migration
- Clear, descriptive names
- Version control migrations
Reversibility
- Always write down migrations
- Test rollback scenarios
- Avoid irreversible changes
- Plan for rollback time
Safety
- Test migrations in staging
- Use transactions
- Backup before running
- Document data changes
Performance
- Avoid locking tables
- Use separate migrations for big changes
- Index new columns
- Monitor migration duration
Deployment
- Run migrations before deployment
- Automate migration execution
- Monitor migration status
- Document migration history

Real-World Applications

Application Updates: Schema evolution
Feature Development: Database changes for new features
Performance Optimization: Index additions
Data Fixes: Correcting data issues
Multi-Environment: Consistent schema across envs