# Database Migration Practice: Master Schema Evolution

## The Migration Challenge

Most tutorials teach you to build databases from scratch. But real production work is different:

**Tutorial**: Create a perfect schema
**Reality**: Evolve an existing schema while preserving data

This is one of the **hardest** and **most critical** backend skills. And it's almost impossible to practice without a real evolving system.

## Why Migrations Are Hard

### You Can't Start Over

In production, you have:
- Live data users depend on
- Running services that expect certain schemas
- No downtime tolerance
- No "just drop and recreate" option

### Requirements Change Constantly

Real systems evolve:
- New features need new fields
- Relationships change
- Performance requires denormalization
- Business logic evolves

### You Must Preserve Everything

Migrations must:
- Transform existing data correctly
- Maintain referential integrity
- Preserve all historical data
- Work across all environments

### Backward Compatibility Matters

Often you need:
- Old API version still working
- Gradual rollout of changes
- Rollback capability
- Zero downtime deployments

## Most Platforms Don't Teach This

**LeetCode/HackerRank**: No persistent data, no migrations

**Tutorials**: Usually start fresh every time

**Bootcamps**: Teach schema creation, not evolution

**Side Projects**: Rarely reach the complexity where migrations matter

## YoloCorp: Migration Bootcamp

Every YoloCorp project **forces schema evolution**.

### Episode 1: Initial Schema
You design your database:
```sql
CREATE TABLE users (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);
```

### Episode 5: Requirements Change
New feature requires user roles:
```sql
-- Can't just add a column - need to:
-- 1. Add column with default value
-- 2. Migrate existing data based on business rules
-- 3. Remove default after backfill
-- 4. Maybe add constraints

ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';
-- Update existing data based on episode-specific logic
UPDATE users SET role = 'admin' WHERE id IN (...);
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;
```

### Episode 8: Relationship Changes
Users now have organizations:
```sql
-- Must preserve user data while adding relationships
CREATE TABLE organizations (...);
CREATE TABLE user_organizations (...);
-- Migrate existing users into default organizations
-- Update application code to handle new structure
```

## Real Migration Scenarios

### Adding Required Fields
**Challenge**: Can't add NOT NULL column to table with data
**Solution**: Multi-step migration with defaults and backfills

### Changing Relationships
**Challenge**: Data model refactor (1-to-1 becomes 1-to-many)
**Solution**: Create new tables, migrate data, update references, deprecate old

### Denormalization for Performance
**Challenge**: Queries too slow, need to duplicate data
**Solution**: Add computed columns, backfill, maintain consistency

### Removing Features
**Challenge**: Column/table no longer needed but can't just drop
**Solution**: Deprecation strategy, data archival, careful removal

### Splitting Tables
**Challenge**: Single table grew too complex, needs normalization
**Solution**: Extract to new table, migrate data, update foreign keys

### Data Type Changes
**Challenge**: TEXT field needs to become ENUM or INT
**Solution**: Validate existing data, transform, handle invalid cases

## Migration Patterns You'll Learn

### Forward Migration
```sql
-- Up: Apply changes
ALTER TABLE products ADD COLUMN stock_count INTEGER DEFAULT 0;
```

### Rollback Migration
```sql
-- Down: Undo changes safely
ALTER TABLE products DROP COLUMN stock_count;
```

### Data Transformation
```sql
-- Migrate data format
UPDATE users
SET phone = format_phone(old_phone)
WHERE old_phone IS NOT NULL;
```

### Multi-Step Migrations
```sql
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN verified BOOLEAN;

-- Step 2: Backfill data (run application logic)
-- (Application sets values based on business rules)

-- Step 3: Make non-nullable
ALTER TABLE users ALTER COLUMN verified SET NOT NULL;
```

### Zero-Downtime Patterns
```sql
-- Old and new columns coexist
ALTER TABLE orders ADD COLUMN status_v2 TEXT;
-- Application writes to both, reads from new
-- After deploy, remove old column
```

## Skills You'll Develop

### Schema Design for Evolution
- Designing schemas that can change
- Anticipating future needs
- Planning for flexibility
- Avoiding migration traps

### Migration Strategy
- Breaking changes into safe steps
- Ordering operations correctly
- Handling failures gracefully
- Testing migrations thoroughly

### Data Integrity
- Maintaining referential integrity
- Preserving data relationships
- Validating data transformations
- Handling edge cases

### Production Safety
- Zero-downtime techniques
- Rollback strategies
- Backup and restore
- Risk mitigation

### Database-Specific Features
- Postgres migrations vs MySQL
- Transaction handling
- Index management during migrations
- Lock prevention

## YoloCorp Forces Best Practices

### Must Preserve Data
Tests verify data continuity across episodes. Drop the table? Tests fail.

### Must Handle Edge Cases
Your migration code must handle:
- NULL values
- Duplicates
- Invalid data
- Missing references

### Must Plan Ahead
Episode 10 requirements might make episode 3 decisions painful. Learn to design for change.

### Must Debug Production Issues
Migration failed? Container won't start? Tests fail mysteriously? Debug it.

## Real Interview Questions

YoloCorp prepares you for questions like:

**"How do you add a NOT NULL column to a table with millions of rows?"**
You'll have done it. You know the multi-step pattern.

**"How do you migrate from one data model to another with zero downtime?"**
You've lived through refactoring episodes. You know the strategies.

**"What do you do if a migration fails halfway through?"**
You've debugged failed migrations. You understand transactions and rollback.

**"How do you handle schema versioning?"**
You've evolved schemas across episodes. You understand version management.

## Practice Scenarios

YoloCorp projects might force you to:

**Add Computed Fields**
> Episode 6 requires instant access to data that requires expensive joins. Add denormalized columns and keep them synchronized.

**Change Primary Keys**
> Episode 7 reveals UUID isn't the right choice. Migrate to different ID strategy without losing data.

**Split Monolithic Tables**
> Episode 9's complexity means your Users table is doing too much. Extract concerns without breaking existing code.

**Add Constraints**
> Episode 4 requires unique constraints on data that isn't currently unique. Clean data, then add constraint.

## Beyond YoloCorp

These skills apply everywhere:

**Postgres, MySQL, MongoDB** - Migration concepts are universal

**ORMs** - Learn to use Prisma, TypeORM, Django migrations, ActiveRecord

**Production Databases** - Know how to safely evolve real systems

**Cloud Platforms** - AWS RDS, Google Cloud SQL require same skills

## Start Practicing

You can't learn migrations from theory. You need a system that evolves.

[Download the CLI](/downloads) and start a project.

By episode 10, you'll have migrated schemas multiple times. You'll understand the pain points, the patterns, and the solutions.

**Perfect for**: Developers wanting to level up database skills, engineers preparing for senior roles, anyone who needs to understand production database evolution.