Database Migration Patterns

-- Migration: Add column
-- Up
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20);

-- Down (rollback)
ALTER TABLE users 
DROP COLUMN phone;

-- Migration: Add NOT NULL column safely
-- Up
ALTER TABLE users 
ADD COLUMN status VARCHAR(20) DEFAULT 'active';

UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users 
MODIFY COLUMN status VARCHAR(20) NOT NULL;

-- Down
ALTER TABLE users 
DROP COLUMN status;

-- Migration: Rename column
-- Up (PostgreSQL)
ALTER TABLE users 
RENAME COLUMN old_name TO new_name;

-- Down
ALTER TABLE users 
RENAME COLUMN new_name TO old_name;

-- Migration: Change column type
-- Up
ALTER TABLE products 
ALTER COLUMN price TYPE DECIMAL(12, 2);

-- Down
ALTER TABLE products 
ALTER COLUMN price TYPE DECIMAL(10, 2);

-- Migration: Add foreign key
-- Up
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user 
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Down
ALTER TABLE orders 
DROP CONSTRAINT fk_orders_user;

-- Migration: Create index
-- Up
CREATE INDEX idx_users_email ON users(email);

-- Down
DROP INDEX idx_users_email;

-- Migration: Create new table
-- Up
CREATE TABLE audit_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  action VARCHAR(50) NOT NULL,
  details JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Down
DROP TABLE audit_logs;

-- Complex migration: Split column
-- Up
ALTER TABLE users ADD COLUMN first_name VARCHAR(50);
ALTER TABLE users ADD COLUMN last_name VARCHAR(50);

UPDATE users 
SET 
  first_name = SUBSTRING_INDEX(name, ' ', 1),
  last_name = SUBSTRING_INDEX(name, ' ', -1);

-- Don't drop old column yet (deploy app first)

-- Down
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

-- Data migration: Copy to new table
-- Up
CREATE TABLE users_new (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(320) NOT NULL UNIQUE,
  -- new schema...
);

INSERT INTO users_new (id, email)
SELECT id, email FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

-- Down
-- Restore from backup

// Knex.js migration example
exports.up = function(knex) {
  return knex.schema
    .createTable('users', table => {
      table.increments('id').primary();
      table.string('email', 255).notNullable().unique();
      table.string('username', 50).notNullable();
      table.timestamp('created_at').defaultTo(knex.fn.now());
    })
    .createTable('posts', table => {
      table.increments('id').primary();
      table.integer('user_id').unsigned().notNullable();
      table.string('title', 255).notNullable();
      table.text('content');
      table.timestamp('created_at').defaultTo(knex.fn.now());
      
      table.foreign('user_id')
        .references('users.id')
        .onDelete('CASCADE');
    });
};

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

// Prisma migration
// prisma/migrations/20240101_add_user_role/migration.sql
-- CreateEnum
CREATE TYPE "UserRole" AS ENUM ('USER', 'ADMIN');

-- AlterTable
ALTER TABLE "User" ADD COLUMN "role" "UserRole" NOT NULL DEFAULT 'USER';