Overview
Safe database migration practices. Covers migration tools (Prisma, Knex, Flyway), forward-only migrations, rollback strategies, data backfill, and zero-downtime deployments.
Capabilities
- Write safe schema migrations (add column, rename, drop)
- Design rollback strategies for destructive changes
- Plan zero-downtime migrations for production
- Backfill data without locking tables
- Test migrations against production data snapshots
When to Use
Trigger phrases:
"database migration"
"Safe database migrations — schema changes, data migrations, rollback strategies,"
Adding or modifying database schema
Need zero-downtime deployment with schema changes
Data migration between tables or formats
Production migration needs rollback plan
When NOT to Use
- Task is about deployment, not development (use deploy skills)
- Task is about code review, not writing (use review skills)
- You need to understand existing code first (use research skills)
- Task is about testing only (use test skills)
- Requirements are unclear (clarify first)
- Task is trivially simple (single line fix)
Pseudo Code
The database-migration workflow follows a standard pipeline pattern.
Core flow:
# database-migration primary flow
input = prepare(raw_data)
result = process(input, config={changes, data, database, deploys, downtime})
validate(result)
deliver(result)
Error handling:
on error:
log(error_details)
retry_with_backoff(max=3)
if still_failing: alert_and_escalate()
Safe Column Addition
-- Step 1: Add column (nullable, no lock)
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;
-- Step 2: Backfill (in batches)
UPDATE users SET phone = legacy_phone WHERE phone IS NULL LIMIT 1000;
-- Step 3: Add NOT NULL constraint (after backfill complete)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
Prisma Migration
# Create migration
npx prisma migrate dev --name add_phone_column
# Deploy to production
npx prisma migrate deploy
Common Patterns
- Nullable first: Add columns as NULL, backfill, then add NOT NULL
- Batch backfill: Update in batches of 1000 to avoid locking
- Shadow tables: Create new table, migrate data, swap names
- Test with prod snapshot: Always test migrations against prod data copy
How to Use
- Understand the requirement and existing codebase patterns
- Design the solution with error handling and testability in mind
- Implement incrementally with tests for each change
- Verify against expected outcomes (manual and automated)
- Document usage, edge cases, and integration points
- Review with team before merging to shared branches
Red Flags
- Skipping tests to ship faster: Untested code breaks in production when you least expect it
- No error handling in production code: Unhandled errors crash services and lose user data
- Hardcoded configuration values: Hardcoded values prevent environment switching and leak secrets
- Ignoring security implications: Missing input validation, auth bypasses, and injection vulnerabilities
- Over-engineering simple solutions: Premature abstraction adds complexity without proportional benefit
Verification
Process
- Analyze the task requirements
- Apply domain expertise
- Verify output quality
Anti-Rationalization Table
| Rationalization |
Reality |
| "Tests slow me down" |
Bugs slow you down 10x more. Tests are speed, not overhead. |
| "I will refactor later" |
Technical debt compounds. Refactor as you go. |
| "It works on my machine" |
If it is not in CI, it does not work. Ship proof, not claims. |
1---2name: database-migration3description: Use when safe database migrations — schema changes, data migrations,4 rollback strategies, and zero-downtime deploys. Use when working with database migration.5domain: development6author: oyi777license: Apache-2.08subdomain: software-development9tags:10- coding11- database12- migration13- software-engineering14- testing15version: 1.0.016category: development17metadata:18 source: https://skillmd.com/skills/oyi77/database-migration19---20212223## Overview2425Safe database migration practices. Covers migration tools (Prisma, Knex, Flyway), forward-only migrations, rollback strategies, data backfill, and zero-downtime deployments.2627## Capabilities2829- Write safe schema migrations (add column, rename, drop)30- Design rollback strategies for destructive changes31- Plan zero-downtime migrations for production32- Backfill data without locking tables33- Test migrations against production data snapshots3435## When to Use36**Trigger phrases:**37- "database migration"38- "Safe database migrations — schema changes, data migrations, rollback strategies,"394041- Adding or modifying database schema42- Need zero-downtime deployment with schema changes43- Data migration between tables or formats44- Production migration needs rollback plan4546## When NOT to Use4748- Task is about deployment, not development (use deploy skills)49- Task is about code review, not writing (use review skills)50- You need to understand existing code first (use research skills)51- Task is about testing only (use test skills)52- Requirements are unclear (clarify first)53- Task is trivially simple (single line fix)545556## Pseudo Code5758The database-migration workflow follows a standard pipeline pattern.5960Core flow:61```62# database-migration primary flow63input = prepare(raw_data)64result = process(input, config={changes, data, database, deploys, downtime})65validate(result)66deliver(result)67```6869Error handling:70```71on error:72 log(error_details)73 retry_with_backoff(max=3)74 if still_failing: alert_and_escalate()75```767778### Safe Column Addition79```sql80-- Step 1: Add column (nullable, no lock)81ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;8283-- Step 2: Backfill (in batches)84UPDATE users SET phone = legacy_phone WHERE phone IS NULL LIMIT 1000;8586-- Step 3: Add NOT NULL constraint (after backfill complete)87ALTER TABLE users ALTER COLUMN phone SET NOT NULL;88```8990### Prisma Migration91```bash92# Create migration93npx prisma migrate dev --name add_phone_column9495# Deploy to production96npx prisma migrate deploy97```9899## Common Patterns100101- **Nullable first**: Add columns as NULL, backfill, then add NOT NULL102- **Batch backfill**: Update in batches of 1000 to avoid locking103- **Shadow tables**: Create new table, migrate data, swap names104- **Test with prod snapshot**: Always test migrations against prod data copy105106## How to Use1071081. Understand the requirement and existing codebase patterns1092. Design the solution with error handling and testability in mind1103. Implement incrementally with tests for each change1114. Verify against expected outcomes (manual and automated)1125. Document usage, edge cases, and integration points1136. Review with team before merging to shared branches114115## Red Flags116117- **Skipping tests to ship faster**: Untested code breaks in production when you least expect it118- **No error handling in production code**: Unhandled errors crash services and lose user data119- **Hardcoded configuration values**: Hardcoded values prevent environment switching and leak secrets120- **Ignoring security implications**: Missing input validation, auth bypasses, and injection vulnerabilities121- **Over-engineering simple solutions**: Premature abstraction adds complexity without proportional benefit122123## Verification124125- [ ] Skill output matches expected behavior126127## Process1281291. Analyze the task requirements1302. Apply domain expertise1313. Verify output quality132133## Anti-Rationalization Table134135| Rationalization | Reality |136|---|---|137| "Tests slow me down" | Bugs slow you down 10x more. Tests are speed, not overhead. |138| "I will refactor later" | Technical debt compounds. Refactor as you go. |139| "It works on my machine" | If it is not in CI, it does not work. Ship proof, not claims. |