Database Migration Management

Database Migration Management

Table of Contents

• Overview
• When to Use
• Quick Start
• Reference Guides
• Best Practices

Overview

Implement robust database migration systems with version control, rollback capabilities, and data transformation strategies. Includes migration frameworks and production deployment patterns.

When to Use

• Schema versioning and evolution
• Data transformations and cleanup
• Adding/removing tables and columns
• Index creation and optimization
• Migration testing and validation
• Rollback planning and execution
• Multi-environment deployments

Quick Start

Minimal working example:

-- Create migrations tracking table
CREATE TABLE schema_migrations (
  version BIGINT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  duration_ms INTEGER,
  checksum VARCHAR(64)
);

-- Create migration log table
CREATE TABLE migration_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  version BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  error_message TEXT,
  rolled_back_at TIMESTAMP,
  executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Function to record migration
CREATE OR REPLACE FUNCTION record_migration(
  p_version BIGINT,
  p_name VARCHAR,
  p_duration_ms INTEGER
) RETURNS void AS $$
// ... (see reference guides for full implementation)

Reference Guides

Detailed implementations in the references/ directory:

Best Practices

✅ DO

• Follow established patterns and conventions
• Write clean, maintainable code
• Add appropriate documentation
• Test thoroughly before deploying

❌ DON'T

• Skip testing or validation
• Ignore error handling
• Hard-code configuration values

Adding Columns

Adding Columns

PostgreSQL - Safe Column Addition:

-- Migration: 20240115_001_add_phone_to_users.sql

-- Add column with default (non-blocking)
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) DEFAULT '';

-- Add constraint after population
ALTER TABLE users
ADD CONSTRAINT phone_format
CHECK (phone = '' OR phone ~ '^\+?[0-9\-\(\)]{10,}$');

-- Create index
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);

-- Rollback:
-- DROP INDEX CONCURRENTLY idx_users_phone;
-- ALTER TABLE users DROP COLUMN phone;

MySQL - Column Addition:

-- Migration: 20240115_001_add_phone_to_users.sql

-- Add column with ALTER
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) DEFAULT '',
ADD INDEX idx_phone (phone);

-- Rollback:
-- ALTER TABLE users DROP COLUMN phone;

Creating Indexes Non-blocking

Creating Indexes Non-blocking

PostgreSQL - Concurrent Index Creation:

-- Migration: 20240115_003_add_performance_indexes.sql

-- Create indexes without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders(user_id, created_at DESC);

CREATE INDEX CONCURRENTLY idx_products_category_active
ON products(category_id)
WHERE active = true;

-- Verify index creation
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE indexname LIKE 'idx_%';

-- Rollback:
-- DROP INDEX CONCURRENTLY idx_orders_user_created;
-- DROP INDEX CONCURRENTLY idx_products_category_active;

MySQL - Online Index Creation:

-- Migration: 20240115_003_add_performance_indexes.sql

-- Create indexes with ALGORITHM=INPLACE and LOCK=NONE
ALTER TABLE orders
ADD INDEX idx_user_created (user_id, created_at),
ALGORITHM=INPLACE, LOCK=NONE;

-- Monitor progress
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE INFO LIKE 'ALTER TABLE%';

Data Transformations

Data Transformations

PostgreSQL - Data Cleanup Migration:

-- Migration: 20240115_004_normalize_email_addresses.sql

-- Normalize existing email addresses
UPDATE users
SET email = LOWER(TRIM(email))
WHERE email != LOWER(TRIM(email));

-- Remove duplicates by keeping latest
DELETE FROM users
WHERE id NOT IN (
  SELECT DISTINCT ON (LOWER(email)) id
  FROM users
  ORDER BY LOWER(email), created_at DESC
);

-- Rollback: Restore from backup (no safe rollback for data changes)

MySQL - Bulk Data Update:

-- Migration: 20240115_004_update_product_categories.sql

-- Update multiple rows with JOIN
UPDATE products p
JOIN category_mapping cm ON p.old_category = cm.old_name
SET p.category_id = cm.new_category_id
WHERE p.old_category IS NOT NULL;

-- Verify update
SELECT COUNT(*) as updated_count
FROM products
WHERE category_id IS NOT NULL;

Renaming Columns

Renaming Columns

PostgreSQL - Column Rename:

-- Migration: 20240115_002_rename_user_name_columns.sql

-- Rename columns
ALTER TABLE users RENAME COLUMN user_name TO full_name;
ALTER TABLE users RENAME COLUMN user_email TO email_address;

-- Update indexes
REINDEX TABLE users;

-- Rollback:
-- ALTER TABLE users RENAME COLUMN email_address TO user_email;
-- ALTER TABLE users RENAME COLUMN full_name TO user_name;

Table Structure Changes

Table Structure Changes

PostgreSQL - Alter Table Migration:

-- Migration: 20240115_005_modify_order_columns.sql

-- Add new column
ALTER TABLE orders
ADD COLUMN status_updated_at TIMESTAMP;

-- Add constraint
ALTER TABLE orders
ADD CONSTRAINT valid_status
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- Set default for existing records
UPDATE orders
SET status_updated_at = updated_at
WHERE status_updated_at IS NULL;

-- Make column NOT NULL
ALTER TABLE orders
ALTER COLUMN status_updated_at SET NOT NULL;

-- Rollback:
-- ALTER TABLE orders DROP COLUMN status_updated_at;
-- ALTER TABLE orders DROP CONSTRAINT valid_status;