4 min lecture • Guide 373 of 877
How to Manage Database Migrations in Development Projects?
How to manage database migrations in development projects?
Manage database migrations by creating tasks for each migration with clear sequencing labels, documenting rollback procedures in task descriptions, and coordinating deployment timing with Team Standup. Use labels for migration status (migration:pending, migration:applied), review migrations in code review column, and keep migration runbooks in NoteVault.
Migration task template
## Migration: Add user_preferences table
Purpose: Store user notification preferences
Migration file: 20250127_add_user_preferences.sql
Changes:
- CREATE TABLE user_preferences
- Add foreign key to users table
- Create indexes for user_id
Dependencies:
- Requires: 20250120_update_users_table (applied)
- Blocks: User preferences feature
Rollback:
DROP TABLE IF EXISTS user_preferences;
Testing:
- [ ] Run in local environment
- [ ] Apply to staging
- [ ] Verify app works with new schema
- [ ] Test rollback procedure
Deployment:
- Window: Tuesday 2am-4am UTC
- Estimated time: 5 minutes
- Downtime: None (additive change)
Migration labels
| Label | Purpose |
|---|---|
| migration:pending | Not yet applied anywhere |
| migration:staging | Applied to staging |
| migration:production | Applied to production |
| migration:breaking | Requires app code change |
| migration:additive | Safe to apply anytime |
| migration:data-migration | Moves/transforms data |
Migration board columns
| Column | Purpose |
|---|---|
| Backlog | Planned migrations |
| Development | Writing migration |
| Review | Code review + DBA review |
| Staging | Applied to staging |
| Approved | Ready for production |
| Production | Applied and verified |
Migration coordination:
- Create migration task - Use template above
- Add dependencies - What must apply first
- Write migration - Development work
- Code review - Standard PR process
- DBA review - For complex migrations
- Apply to staging - Test in staging env
- Verify staging - App works correctly
- Schedule production - Deployment window
- Apply to production - Execute migration
- Verify production - Confirm success
- Mark complete - Move to Done
NoteVault migration runbook
# Database Migration Runbook
## Pre-Deployment Checklist
- [ ] Migration reviewed and approved
- [ ] Rollback script tested
- [ ] Staging verification complete
- [ ] Dependent teams notified
- [ ] Backup verified
## Deployment Steps
1. Enable maintenance mode (if needed)
2. Take database backup
3. Run migration script
4. Verify migration applied
5. Run smoke tests
6. Disable maintenance mode
## Rollback Procedure
1. Enable maintenance mode
2. Run rollback script
3. Verify rollback complete
4. Disable maintenance mode
5. Notify team of rollback
## Emergency Contacts
- DBA: @dba-oncall
- Platform: @platform-oncall
Common migration patterns
| Pattern | When to Use |
|---|---|
| Additive | Adding tables, columns |
| Rename | Blue-green with alias |
| Data migration | Backfill historical data |
| Index | Performance improvements |
| Constraint | Adding foreign keys |
| Breaking | Coordinate with app deploy |