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