Db migrator

Database Migrator

Professional database schema migration and version control for production systems.

Core Workflow

1. Create Migration

# Generate migration file
npm run migrate:make create_users_table
# or
npx prisma migrate dev --name add_user_email
# or
python -m alembic revision -m "add_user_email"

2. Write Migration

// Knex.js example
exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('email').notNullable().unique();
    table.string('password_hash').notNullable();
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
    
    // Indexes
    table.index('email');
  });
};

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

3. Run Migration

# Apply pending migrations
npm run migrate:up

# Rollback last migration
npm run migrate:down

# Rollback all
npm run migrate:reset

# Refresh (rollback + re-run)
npm run migrate:refresh

Migration Best Practices

Always Include Down Migration

// ✅ Good - Reversible
exports.up = (knex) => knex.schema.alterTable('users', t => {
  t.string('phone').nullable();
});

exports.down = (knex) => knex.schema.alterTable('users', t => {
  t.dropColumn('phone');
});

// ❌ Bad - Irreversible
exports.up = (knex) => knex.schema.alterTable('users', t => {
  t.string('phone').nullable();
});
exports.down = () => {}; // Empty rollback!

Atomic Migrations

// ✅ Good - Single responsibility
// Migration 1: Add column
exports.up = (knex) => knex.schema.alterTable('users', t => {
  t.string('phone');
});

// Migration 2: Populate data
exports.up = (knex) => knex.raw(`
  UPDATE users SET phone = '000-000-0000' WHERE phone IS NULL
`);

// ❌ Bad - Mixed concerns
exports.up = async (knex) => {
  await knex.schema.alterTable('users', t => t.string('phone'));
  await knex.raw('UPDATE users SET phone = ...');
  await knex.schema.createTable('audit_log', ...);
};

Safe Column Operations

// Add column with default (avoid locking)
exports.up = (knex) => knex.raw(`
  ALTER TABLE users 
  ADD COLUMN phone VARCHAR(20) DEFAULT ''
`);

// Remove column safely
exports.down = (knex) => knex.raw(`
  ALTER TABLE users 
  DROP COLUMN phone
`);

// Rename column (data preserved)
exports.up = (knex) => knex.schema.alterTable('users', t => {
  t.renameColumn('phone', 'phone_number');
});

Framework-Specific Patterns

Prisma (Node.js)

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  posts     Post[]
  createdAt DateTime @default(now())
}

// Run migration
npx prisma migrate dev --name init
npx prisma migrate deploy // Production

Alembic (Python)

# migrations/versions/abc123_add_email.py
def upgrade():
    op.add_column('users', sa.Column('email', sa.String(255)))

def downgrade():
    op.drop_column('users', 'email')

# Commands
alembic upgrade head
alembic downgrade -1

Flyway (Java/General)

-- V1__create_users_table.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- V2__add_phone_column.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Data Migrations

// Transform data safely
exports.up = async (knex) => {
  const users = await knex('users').whereNull('full_name');
  
  for (const user of users) {
    await knex('users')
      .where({ id: user.id })
      .update({
        full_name: `${user.first_name} ${user.last_name}`
      });
  }
};

Production Safety

// Add timeout for large tables
exports.up = (knex) => knex.raw(`
  SET statement_timeout = '300s';
  ALTER TABLE large_table ADD COLUMN new_field TEXT;
`);

// Create index concurrently (PostgreSQL)
exports.up = (knex) => knex.raw(`
  CREATE INDEX CONCURRENTLY idx_users_email 
  ON users(email)
`);

Scripts

• scripts/create_migration.sh - Generate migration file with timestamp
• scripts/check_migrations.js - Compare local vs remote migration status
• scripts/seed_data.js - Load seed data from JSON/CSV

References

• references/postgres_types.md - PostgreSQL data types reference
• references/mysql_types.md - MySQL data types reference
• references/migration_templates/ - Framework-specific templates