How to Create Database Migrations

Table Of Contents

• Quick Fix: Basic Migration with Knex
• Node.js Built-in Solution: Migration Management

Quick Fix: Basic Migration with Knex

// Install: npm install knex pg
// Initialize: npx knex init

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

// migrations/20240101_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('email').unique().notNullable();
    table.string('name');
    table.timestamps(true, true);
  });
};

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

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

Node.js Built-in Solution: Migration Management

// knexfile.js configuration
module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      database: 'myapp_dev',
      user: 'postgres',
      password: 'password'
    },
    migrations: {
      directory: './db/migrations',
      tableName: 'knex_migrations'
    },
    seeds: {
      directory: './db/seeds'
    }
  },
  production: {
    client: 'postgresql',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './db/migrations'
    }
  }
};

// Complex migration example
exports.up = async function(knex) {
  // Create multiple tables with relations
  await knex.schema.createTable('organizations', table => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.string('name').notNullable();
    table.jsonb('settings').defaultTo('{}');
    table.timestamps(true, true);
    table.index('name');
  });
  
  await knex.schema.createTable('users', table => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.string('email', 255).unique().notNullable();
    table.string('password_hash').notNullable();
    table.uuid('organization_id').references('id').inTable('organizations').onDelete('CASCADE');
    table.enum('role', ['admin', 'user', 'guest']).defaultTo('user');
    table.boolean('active').defaultTo(true);
    table.timestamp('last_login');
    table.timestamps(true, true);
    
    // Composite index
    table.index(['organization_id', 'email']);
  });
  
  // Add check constraint
  await knex.raw('ALTER TABLE users ADD CONSTRAINT email_format CHECK (email ~* \'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}$\')');
};

exports.down = async function(knex) {
  await knex.schema.dropTable('users');
  await knex.schema.dropTable('organizations');
};

// Data migration example
exports.up = async function(knex) {
  // Add new column
  await knex.schema.alterTable('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.alterTable('users', table => {
    table.dropColumn('first_name');
    table.dropColumn('last_name');
  });
};

// Manual migration runner
class MigrationRunner {
  constructor(db) {
    this.db = db;
  }
  
  async createMigrationsTable() {
    await this.db.query(`
      CREATE TABLE IF NOT EXISTS migrations (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) UNIQUE NOT NULL,
        executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }
  
  async runMigration(name, upFunction) {
    const executed = await this.db.query(
      'SELECT * FROM migrations WHERE name = $1',
      [name]
    );
    
    if (executed.rows.length === 0) {
      console.log(`Running migration: ${name}`);
      await upFunction(this.db);
      await this.db.query(
        'INSERT INTO migrations (name) VALUES ($1)',
        [name]
      );
      console.log(`Migration ${name} completed`);
    }
  }
  
  async rollback(name, downFunction) {
    await downFunction(this.db);
    await this.db.query(
      'DELETE FROM migrations WHERE name = $1',
      [name]
    );
    console.log(`Rolled back: ${name}`);
  }
}

// Programmatic migration execution
const knex = require('knex');
const config = require('./knexfile');

async function runMigrations() {
  const db = knex(config[process.env.NODE_ENV || 'development']);
  
  try {
    const pending = await db.migrate.list();
    console.log('Pending migrations:', pending[1]);
    
    const completed = await db.migrate.latest();
    console.log('Completed migrations:', completed[1]);
    
    // Check migration status
    const status = await db.migrate.status();
    console.log('Migration status:', status);
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  } finally {
    await db.destroy();
  }
}

// MySQL migrations with mysql2
async function mysqlMigration(connection) {
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_email (email)
    )
  `);
}

Database migrations solve "schema versioning", "team synchronization", and "deployment consistency" issues. Always include both up and down migrations. Test rollbacks before production. Alternative tools: node-migrate, umzug for Sequelize, prisma migrate, db-migrate.