-- NEVER do this on production (breaks existing queries):
ALTER TABLE users RENAME COLUMN username TO user_name;

-- DO this instead:
-- 1. Add new column
ALTER TABLE users ADD COLUMN user_name VARCHAR(100);

-- 2. Create trigger to sync (keep both in sync during transition)
CREATE OR REPLACE FUNCTION sync_username()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.username IS DISTINCT FROM OLD.username THEN
    NEW.user_name = NEW.username;
  END IF;
  IF NEW.user_name IS DISTINCT FROM OLD.user_name THEN
    NEW.username = NEW.user_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_username_trigger
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_username();

-- 3. Backfill
UPDATE users SET user_name = username;

-- 4. Deploy code using new column name
-- 5. Remove trigger and old column
DROP TRIGGER sync_username_trigger ON users;
ALTER TABLE users DROP COLUMN username;

Database Migration Strategies — Zero-Downtime Migrations

Database Migration Strategies — Zero-Downtime Migrations

Database Migration Strategies — Zero-Downtime Migrations