Database Migration Helper

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