Non-Atomic Schema Migrations

-- Another dangerous example: Renaming columns without backward compatibility

-- Migration:
ALTER TABLE users RENAME COLUMN username TO user_name;

-- Application code assumes new column name immediately:
const getUser = async (userId) => {
  // Fails if any app instance hasn't updated yet
  return db.query('SELECT id, user_name, email FROM users WHERE id = $1', [userId]);
};

-- Safe: Multi-phase migration with backward compatibility

-- Phase 1: Add new structure (backward compatible)
ALTER TABLE orders ADD COLUMN customer_id INTEGER;
-- Create trigger to sync old and new columns
CREATE OR REPLACE FUNCTION sync_customer_data() RETURNS TRIGGER AS $
BEGIN
  IF NEW.customer_id IS NOT NULL AND NEW.customer_name IS NULL THEN
    SELECT name INTO NEW.customer_name FROM customers WHERE id = NEW.customer_id;
  ELSIF NEW.customer_name IS NOT NULL AND NEW.customer_id IS NULL THEN
    SELECT id INTO NEW.customer_id FROM customers WHERE name = NEW.customer_name;
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER orders_customer_sync 
BEFORE INSERT OR UPDATE ON orders 
FOR EACH ROW EXECUTE FUNCTION sync_customer_data();

-- Phase 2: Deploy code that handles both schemas
const createOrder = async (customerIdOrName, items) => {
  // Works with both old and new schema
  const customer = typeof customerIdOrName === 'number' 
    ? { id: customerIdOrName }
    : await getCustomerByName(customerIdOrName);
    
  const columns = db.schemaVersion >= 2 
    ? 'customer_id, items, total'
    : 'customer_name, items, total';
    
  const values = db.schemaVersion >= 2
    ? [customer.id, items, calculateTotal(items)]
    : [customer.name, items, calculateTotal(items)];
    
  return db.query(`INSERT INTO orders (${columns}) VALUES ($1, $2, $3)`, values);
};

-- Phase 3: Backfill data
UPDATE orders o 
SET customer_id = c.id 
FROM customers c 
WHERE o.customer_name = c.name 
AND o.customer_id IS NULL;

-- Phase 4: Add constraints (after all instances updated)
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Phase 5: Remove old column (after verification)
DROP TRIGGER orders_customer_sync ON orders;
DROP FUNCTION sync_customer_data();
ALTER TABLE orders DROP COLUMN customer_name;