Database/Schema migrations

Database Schema Migrations and Compatibility

Understanding Schema Migrations

Schema migrations are controlled changes to your database structure over time. Unlike schema-on-read databases that can handle mixed data formats, relational databases enforce a single schema at any point in time. This creates unique challenges when evolving your application.

As the text notes, in large applications, code changes cannot happen instantaneously. You might have:

• Multiple application servers running different versions during deployment

• Mobile apps that users haven't updated yet

• Background workers processing old jobs

• Long-running transactions that started before the migration

This is where backward and forward compatibility become critical.

Backward vs Forward Compatibility

Backward Compatibility: New code can work with old schema

• Your updated application code can handle the database structure from before the migration

Forward Compatibility: Old code can work with new schema

• Your existing application code continues working after the schema changes

Common Migration Patterns

1. Adding a Nullable Column (Safe ✓)

This is the safest migration because it's both backward and forward compatible.

-- Migration: Add optional email column
ALTER TABLE users 
ADD COLUMN email VARCHAR(255) NULL;

# Old code (backward compatible) - still works
def get_user(user_id):
    cursor.execute("SELECT id, name FROM users WHERE id = %s", (user_id,))
    return cursor.fetchone()

# New code (forward compatible) - handles missing emails
def get_user_with_email(user_id):
    cursor.execute("SELECT id, name, email FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()
    return {
        'id': user[0],
        'name': user[1],
        'email': user[2] if user[2] else '[e-mail]'
    }

2. Adding a NOT NULL Column (Requires Multi-Step)

This requires a careful, multi-phase approach:

-- Phase 1: Add nullable column with default
ALTER TABLE users 
ADD COLUMN status VARCHAR(20) NULL DEFAULT 'active';

-- Phase 2: Backfill existing data
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Phase 3: After deployment, make it NOT NULL
ALTER TABLE users 
ALTER COLUMN status SET NOT NULL;

// Phase 1 code: Handle both NULL and non-NULL
func GetUser(db *sql.DB, userID int) (*User, error) {
    var user User
    var status sql.NullString
    
    err := db.QueryRow(
        "SELECT id, name, status FROM users WHERE id = ?", 
        userID,
    ).Scan(&user.ID, &user.Name, &status)
    
    if status.Valid {
        user.Status = status.String
    } else {
        user.Status = "active" // Default for old rows
    }
    
    return &user, err
}

// Phase 3 code: After constraint is added
func GetUserV2(db *sql.DB, userID int) (*User, error) {
    var user User
    err := db.QueryRow(
        "SELECT id, name, status FROM users WHERE id = ?", 
        userID,
    ).Scan(&user.ID, &user.Name, &user.Status)
    
    return &user, err
}

3. Renaming a Column (Expand-Contract Pattern)

The safest approach uses three phases:

-- Phase 1: EXPAND - Add new column
ALTER TABLE products 
ADD COLUMN description TEXT NULL;

-- Copy data from old column
UPDATE products SET description = product_desc;

-- Phase 2: Application code reads/writes both columns

-- Phase 3: CONTRACT - Remove old column
ALTER TABLE products 
DROP COLUMN product_desc;

# Phase 2: Transitional code writing to both columns
def update_product(product_id, new_description):
    cursor.execute("""
        UPDATE products 
        SET product_desc = %s, description = %s 
        WHERE id = %s
    """, (new_description, new_description, product_id))
    
# Phase 2: Reading with fallback
def get_product(product_id):
    cursor.execute("""
        SELECT id, name, 
               COALESCE(description, product_desc) as desc
        FROM products 
        WHERE id = %s
    """, (product_id,))
    return cursor.fetchone()

4. Removing a Column (Two-Step Process)

-- Step 1: Stop using the column in code first!
-- Deploy code that doesn't read/write the column

-- Step 2: Then drop it
ALTER TABLE users 
DROP COLUMN middle_name;

// Step 1: Update code to ignore the column
type User struct {
    ID        int
    FirstName string
    LastName  string
    // middle_name removed from struct but still in DB
}

func CreateUser(db *sql.DB, user *User) error {
    // Only insert fields we care about
    _, err := db.Exec(
        "INSERT INTO users (first_name, last_name) VALUES (?, ?)",
        user.FirstName, user.LastName,
    )
    return err
}

// Step 2: After this code is deployed everywhere, 
// run the DROP COLUMN migration

5. Changing Column Type (Complex Migration)

-- Example: Change user_id from INT to BIGINT

-- Phase 1: Add new column
ALTER TABLE orders 
ADD COLUMN user_id_new BIGINT NULL;

-- Phase 2: Backfill data
UPDATE orders SET user_id_new = user_id;

-- Phase 3: Create index on new column
CREATE INDEX idx_orders_user_id_new ON orders(user_id_new);

-- Phase 4: Swap columns (requires downtime or careful orchestration)
ALTER TABLE orders DROP COLUMN user_id;
ALTER TABLE orders RENAME COLUMN user_id_new TO user_id;
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;

# Phase 2-3: Application reads from both columns
def get_user_orders(user_id):
    # Use COALESCE to handle transition period
    cursor.execute("""
        SELECT id, product_id, 
               COALESCE(user_id_new, user_id) as user_id,
               created_at
        FROM orders 
        WHERE COALESCE(user_id_new, user_id) = %s
    """, (user_id,))
    return cursor.fetchall()

Migration Tools and Best Practices

Using Migration Libraries

# Example with Alembic (Python)
"""Add email column

Revision ID: 001
"""

def upgrade():
    op.add_column('users',
        sa.Column('email', sa.String(255), nullable=True)
    )

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

// Example with golang-migrate
// 001_add_email.up.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255) NULL;

// 001_add_email.down.sql
ALTER TABLE users DROP COLUMN email;

Key Principles

1. Never break backward compatibility immediately - Old code must continue working

2. Use multi-phase migrations for risky changes (add → migrate → remove)

3. Always have a rollback plan - migrations should be reversible

4. Test migrations on production-like data - edge cases matter

5. Make schema changes additive first - expand, then contract

6. Deploy code before schema for removals - deploy after schema for additions

7. Monitor during migrations - watch for errors in production

The Expand-Contract Pattern

This is the gold standard for zero-downtime migrations:

1. Expand: Add new schema elements (columns, tables) without removing old ones

2. Migrate: Update application code to use both old and new

3. Contract: Remove old schema elements after all code is updated

This ensures that at every step, both old and new application versions can coexist, which is exactly what you need when "code changes often cannot happen instantaneously" in large applications.

---

Python tools: Alembic, Django migrations, Yoyo-migrations, Atlas

Golang tools: Goose