database-migration-helper

name	database-migration-helper
description	Create and manage database migrations safely with rollback support. Use when modifying database schema, adding indexes, or managing database changes.

Database Migration Helper

Create and manage database migrations safely with proper rollback support.

Quick Start

Create migration files with up/down functions, test locally, backup before production, run migrations incrementally.

Instructions

Migration Structure

Basic migration:

// migrations/001_create_users_table.js
exports.up = async (db) => {
  await db.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email').unique().notNullable();
    table.string('password_hash').notNullable();
    table.timestamps(true, true);
  });
};

exports.down = async (db) => {
  await db.schema.dropTable('users');
};

Creating Tables

With Knex:

exports.up = async (knex) => {
  await knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.integer('user_id').unsigned().notNullable();
    table.string('title', 200).notNullable();
    table.text('content');
    table.enum('status', ['draft', 'published', 'archived']).defaultTo('draft');
    table.timestamps(true, true);
    
    // Foreign key
    table.foreign('user_id').references('users.id').onDelete('CASCADE');
    
    // Indexes
    table.index('user_id');
    table.index('status');
  });
};

exports.down = async (knex) => {
  await knex.schema.dropTable('posts');
};

With raw SQL:

-- migrations/001_create_users.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

-- migrations/001_create_users.down.sql
DROP TABLE IF EXISTS users;

Adding Columns

exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.string('phone', 20);
    table.boolean('is_verified').defaultTo(false);
  });
};

exports.down = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.dropColumn('phone');
    table.dropColumn('is_verified');
  });
};

Modifying Columns

exports.up = async (knex) => {
  await knex.schema.alterTable('users', (table) => {
    table.string('email', 320).alter(); // Increase length
    table.string('name', 100).notNullable().alter(); // Add NOT NULL
  });
};

exports.down = async (knex) => {
  await knex.schema.alterTable('users', (table) => {
    table.string('email', 255).alter();
    table.string('name', 100).nullable().alter();
  });
};

Adding Indexes

exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.index('created_at');
    table.index(['user_id', 'created_at']); // Composite index
  });
};

exports.down = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.dropIndex('created_at');
    table.dropIndex(['user_id', 'created_at']);
  });
};

Data Migrations

exports.up = async (knex) => {
  // Add column
  await knex.schema.table('users', (table) => {
    table.string('full_name');
  });
  
  // Migrate data
  const users = await knex('users').select('id', 'first_name', 'last_name');
  for (const user of users) {
    await knex('users')
      .where('id', user.id)
      .update({ full_name: `${user.first_name} ${user.last_name}` });
  }
  
  // Drop old columns
  await knex.schema.table('users', (table) => {
    table.dropColumn('first_name');
    table.dropColumn('last_name');
  });
};

exports.down = async (knex) => {
  // Add old columns
  await knex.schema.table('users', (table) => {
    table.string('first_name');
    table.string('last_name');
  });
  
  // Migrate data back
  const users = await knex('users').select('id', 'full_name');
  for (const user of users) {
    const [firstName, ...lastNameParts] = user.full_name.split(' ');
    await knex('users')
      .where('id', user.id)
      .update({
        first_name: firstName,
        last_name: lastNameParts.join(' ')
      });
  }
  
  // Drop new column
  await knex.schema.table('users', (table) => {
    table.dropColumn('full_name');
  });
};

Foreign Keys

exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.foreign('user_id')
      .references('id')
      .inTable('users')
      .onDelete('CASCADE')
      .onUpdate('CASCADE');
  });
};

exports.down = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.dropForeign('user_id');
  });
};

Renaming Tables/Columns

exports.up = async (knex) => {
  await knex.schema.renameTable('posts', 'articles');
  
  await knex.schema.table('articles', (table) => {
    table.renameColumn('content', 'body');
  });
};

exports.down = async (knex) => {
  await knex.schema.table('articles', (table) => {
    table.renameColumn('body', 'content');
  });
  
  await knex.schema.renameTable('articles', 'posts');
};

Migration Tools

Knex.js:

# Create migration
npx knex migrate:make create_users_table

# Run migrations
npx knex migrate:latest

# Rollback last batch
npx knex migrate:rollback

# Rollback all
npx knex migrate:rollback --all

# Check status
npx knex migrate:status

TypeORM:

# Generate migration
npm run typeorm migration:generate -- -n CreateUsersTable

# Run migrations
npm run typeorm migration:run

# Revert last migration
npm run typeorm migration:revert

Prisma:

# Create migration
npx prisma migrate dev --name create_users_table

# Apply migrations
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

Sequelize:

# Create migration
npx sequelize-cli migration:generate --name create-users-table

# Run migrations
npx sequelize-cli db:migrate

# Undo last migration
npx sequelize-cli db:migrate:undo

Best Practices

1. Always include rollback:

// Every migration must have down()
exports.down = async (knex) => {
  // Reverse the changes
};

2. Make migrations idempotent:

exports.up = async (knex) => {
  const exists = await knex.schema.hasTable('users');
  if (!exists) {
    await knex.schema.createTable('users', (table) => {
      // ...
    });
  }
};

3. Test migrations:

# Run migration
npm run migrate

# Test application
npm test

# Rollback
npm run migrate:rollback

# Run again
npm run migrate

4. Backup before production:

# PostgreSQL
pg_dump dbname > backup.sql

# MySQL
mysqldump dbname > backup.sql

# Then run migration
npm run migrate

5. Run migrations incrementally:

# Don't run all at once in production
# Run one migration at a time
npx knex migrate:up 001_create_users_table.js
# Verify
# Then next migration
npx knex migrate:up 002_create_posts_table.js

Common Patterns

Add column with default:

exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.boolean('is_active').defaultTo(true);
  });
};

Add enum column:

exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.enum('status', ['draft', 'published', 'archived'])
      .defaultTo('draft');
  });
};

Add timestamp columns:

exports.up = async (knex) => {
  await knex.schema.table('posts', (table) => {
    table.timestamps(true, true); // created_at, updated_at
  });
};

Add JSON column:

exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.json('metadata');
  });
};

Handling Large Tables

Add index without locking (PostgreSQL):

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Add column with default (PostgreSQL 11+):

-- Fast: doesn't rewrite table
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

Batch data migration:

exports.up = async (knex) => {
  const batchSize = 1000;
  let offset = 0;
  
  while (true) {
    const users = await knex('users')
      .select('id', 'email')
      .limit(batchSize)
      .offset(offset);
    
    if (users.length === 0) break;
    
    for (const user of users) {
      await knex('users')
        .where('id', user.id)
        .update({ email_lower: user.email.toLowerCase() });
    }
    
    offset += batchSize;
  }
};

Migration Checklist

Before creating:

• Understand the change needed
• Plan rollback strategy
• Consider data migration
• Check for dependencies

In migration:

• Include up and down functions
• Add appropriate indexes
• Set constraints
• Handle existing data

Before running:

• Test locally
• Test rollback
• Backup database
• Plan maintenance window

After running:

• Verify changes
• Test application
• Monitor performance
• Document changes

Troubleshooting

Migration fails:

• Check error message
• Verify database connection
• Check for syntax errors
• Ensure dependencies exist

Can't rollback:

• Check down() function
• Verify rollback logic
• May need manual intervention
• Restore from backup if needed

Performance issues:

• Add indexes after data load
• Use CONCURRENTLY for indexes
• Batch large data migrations
• Run during low traffic