Database Migrator

You are an expert at handling database schema changes safely.

When To Use

•Adding/removing/modifying database columns
•Creating new tables
•Data transformations needed
•User says "Add this field", "Change the schema"

Inputs

•Current schema
•Desired change
•Data preservation requirements

Outputs

•Migration file(s)
•Rollback strategy
•Updated schema documentation

Workflow

1. Analyze Change

•Is this additive (safe) or destructive (risky)?
•Any data to preserve or transform?
•Downtime required?

2. Generate Migration

bash

# Alembic (Python)
alembic revision --autogenerate -m "add user preferences"

# Prisma
npx prisma migrate dev --name add_preferences

# Raw SQL
# Create timestamped file: migrations/20241206_add_preferences.sql

3. Review Migration

•Check generated SQL
•Verify rollback works
•Consider indexes

4. Test Locally

bash

# Apply
alembic upgrade head

# Rollback
alembic downgrade -1

5. Document

•Update ERD if exists
•Note in LLM-OVERVIEW.md

Safe Migration Patterns

Change	Safe?	Notes
Add nullable column	✅	No data change needed
Add column with default	✅	Backfills automatically
Remove unused column	⚠️	Verify no code references
Rename column	❌	Requires code coordination
Change column type	❌	May lose data
Add index	✅	May lock table briefly
Add foreign key	⚠️	Existing data must be valid

Migration Templates

SQLite

sql

-- migrations/001_add_preferences.sql

-- Up
ALTER TABLE users ADD COLUMN preferences TEXT;

-- Down
-- SQLite doesn't support DROP COLUMN easily
-- Create new table without column, copy data, rename

PostgreSQL

sql

-- migrations/001_add_preferences.sql

-- Up
ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}';

-- Down
ALTER TABLE users DROP COLUMN preferences;

Alembic (Python)

python

"""add preferences column

Revision ID: abc123
"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('users',
        sa.Column('preferences', sa.JSON(), nullable=True)
    )

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

Zero-Downtime Patterns

Adding Column

•Add column (nullable)
•Deploy code that writes to new column
•Backfill existing data
•Add NOT NULL constraint (if needed)

Renaming Column

•Add new column
•Deploy code that writes to both
•Backfill old → new
•Deploy code that reads from new
•Drop old column

Rollback Strategy

Always have a plan:

yaml

migration: add_preferences
rollback:
  command: "alembic downgrade -1"
  data_loss: false
  downtime: "~30 seconds"
  tested: true

NoSQL Migrations

MongoDB Schema Evolution

Unlike SQL, MongoDB is schema-less but you still need to manage document structure changes.

Adding a Field

javascript

// No migration needed for new fields - just start writing them
// Optional: backfill existing documents
db.users.updateMany(
  { preferences: { $exists: false } },
  { $set: { preferences: {} } }
);

Renaming a Field

javascript

// 1. Add new field, copy data
db.users.updateMany(
  {},
  [{ $set: { newName: "$oldName" } }]
);

// 2. Update code to use newName

// 3. Remove old field
db.users.updateMany(
  {},
  { $unset: { oldName: "" } }
);

Changing Field Type

javascript

// String to Number
db.users.updateMany(
  {},
  [{ $set: { age: { $toInt: "$age" } } }]
);

// Nested object to flat
db.users.updateMany(
  {},
  [{
    $set: {
      city: "$address.city",
      country: "$address.country"
    },
    $unset: "address"
  }]
);

MongoDB Migration Script Template

javascript

// migrations/001_add_preferences.js
module.exports = {
  async up(db) {
    await db.collection('users').updateMany(
      { preferences: { $exists: false } },
      { $set: { preferences: { theme: 'light', notifications: true } } }
    );
  },

  async down(db) {
    await db.collection('users').updateMany(
      {},
      { $unset: { preferences: "" } }
    );
  }
};

Redis Data Evolution

Redis doesn't have migrations per se, but key naming and data structure changes need planning.

Key Naming Convention

code

# Good: namespace:entity:id:attribute
user:123:profile
user:123:sessions
cache:products:list

# Bad: no structure
user_profile_123

Changing Key Structure

python

# Migrate from old to new key format
import redis
r = redis.Redis()

# 1. Copy data to new keys
for key in r.scan_iter("user_*"):
    user_id = key.decode().split("_")[1]
    new_key = f"user:{user_id}:profile"
    r.copy(key, new_key)

# 2. Update code to use new keys
# 3. Delete old keys (after verification)
for key in r.scan_iter("user_*"):
    r.delete(key)

When SQL vs NoSQL

Factor	Use SQL	Use NoSQL
Schema	Fixed, well-known	Flexible, evolving
Relationships	Many, complex	Few, embedded
Transactions	Critical	Nice to have
Scale	Vertical first	Horizontal first
Query patterns	Complex joins	Key-value, document
Data size	<100GB	Any size

Default to SQL (SQLite → PostgreSQL) unless you have specific NoSQL needs.

Anti-Patterns

•Migrations without rollback plan
•Destructive changes without backup
•Long-running migrations during peak hours
•Not testing migrations locally first
•Combining multiple changes in one migration
•MongoDB: Not validating document structure
•Redis: Not using key namespaces

Keywords

migration, schema change, add column, database, alter table, alembic, prisma, mongodb, redis, nosql