Database Migrator | Skills Pool

Database Migrator | Skills Pool

-- Safe way (add with default)
ALTER TABLE users ADD COLUMN bio TEXT DEFAULT '';

-- Unsafe way (add without default on large table)
ALTER TABLE users ADD COLUMN bio TEXT;

-- Multi-step approach
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN email_new VARCHAR(255);

-- Step 2: Backfill data
UPDATE users SET email_new = email;

-- Step 3: Update application to use new column

-- Step 4: Remove old column
ALTER TABLE users DROP COLUMN email;

-- Create index CONCURRENTLY (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- For MySQL, use ONLINE DDL
ALTER TABLE users ADD INDEX idx_email (email), LOCK=NONE, ALGORITHM=INPLACE;