Database migrations are among the most anxiety-inducing tasks in software development. Change a column type wrong and you lose data. Forget to add an index and queries slow to a crawl. Miss a null constraint and your application crashes with cryptic errors.
The stakes are high because mistakes are hard to reverse. Unlike code changes that can be reverted with a git command, database changes often can't be undone without data loss or downtime. This fear leads to migration procrastination - schema changes pile up until someone has to spend a full day carefully writing and testing migrations.
AI agents can generate migration scripts from code changes, validate them for safety, and help apply them correctly. The scary parts become automated checks. The tedious parts become generated code.
Why Database Migrations Are Difficult
Several factors make migrations challenging.
Data Preservation
Schema changes must not lose data. Dropping a column, changing a type, or renaming a table can destroy information that can never be recovered. The migration writer must think through every data transformation.
Performance Impact
Large tables take a long time to alter. Adding an index to a billion-row table can lock the database for hours. The migration must account for table sizes and access patterns.
Dependency Ordering
Tables depend on each other through foreign keys. Migrations must run in the right order, or constraints will be violated. Circular dependencies make ordering even more complex.
Rollback Complexity
Not all migrations are reversible. You can add a column, but once you've dropped one and deployed the new code, you can't easily add it back with the original data.
Multi-Environment Consistency
Migrations run differently in development (small data), staging (medium data), and production (large data). What's fast locally might be slow in production.
Code Synchronization
Schema changes must be coordinated with code changes. Deploy code before the migration, and it fails. Deploy migration before code, and old code fails.
Types of Migration Automation
Schema-to-Migration Generation
Generate migrations from model changes:
@devonair generate migration for changes between current models and database schema
@devonair create migration to add the new fields I added to the User model
The agent compares your code's data models with the current database schema and generates the required changes.
Code-Driven Migrations
Generate migrations from code analysis:
@devonair analyze PR changes and generate required database migrations
@devonair if code references new columns, create migrations to add them
Migration from Specification
Generate migrations from descriptions:
@devonair create migration to add soft delete to all user-related tables
@devonair generate migration to partition the events table by date
Migration Optimization
Improve existing migrations:
@devonair optimize this migration for a production table with 10 million rows
@devonair add batching to this data migration to avoid locking
Safe Migration Patterns
Adding Columns
Adding nullable columns is safe:
@devonair add new_column as nullable first, then backfill, then add constraint
This three-step pattern prevents failures when old code runs against new schema.
Removing Columns
Removing columns requires care:
@devonair remove column safely: mark as unused, deploy code that ignores it, then drop in later migration
Never drop columns that running code might reference.
Renaming Columns
Renames are deceptively dangerous:
@devonair rename column safely: add new column, backfill data, update code, drop old column
Direct renames break old code immediately.
Adding Indexes
Indexes can lock tables:
@devonair add index concurrently to avoid blocking queries
Use concurrent index creation in production.
Changing Column Types
Type changes can fail or lose data:
@devonair change column type safely: add new column, migrate data, swap columns
Never alter type directly on large tables.
Migration Script Generation
From ORM Models
When your models change:
@devonair generate Alembic migration from SQLAlchemy model changes
@devonair create Prisma migration for schema changes
@devonair generate TypeORM migration from entity modifications
From SQL Specifications
When you know what you want:
@devonair generate migration that adds an orders table with foreign key to users
@devonair create migration to implement many-to-many between products and categories
From Existing Database
When the database was changed manually:
@devonair generate migration to match current database state
@devonair create migration that documents existing schema for new environment setup
Data Migrations
Schema changes often require data changes.
Backfilling Data
@devonair create data migration to populate the new created_at column from logs
@devonair generate backfill script that sets default values for new required column
Data Transformation
@devonair create migration to split full_name into first_name and last_name
@devonair generate migration to denormalize user counts into the projects table
Large Table Handling
@devonair create batched data migration to update 50 million rows without locking
@devonair generate migration with progress logging for large data transformation
Migration Validation
Before running migrations, validate them.
Safety Checks
@devonair validate migration for destructive operations
@devonair check if migration could cause data loss
Performance Analysis
@devonair estimate migration runtime on production-sized table
@devonair identify potential locking issues in this migration
Rollback Verification
@devonair verify this migration can be safely rolled back
@devonair generate rollback script for this migration
Constraint Validation
@devonair verify foreign key constraints won't be violated by this migration
@devonair check if existing data satisfies new constraints
ORM-Specific Automation
Django
@devonair generate Django migration for model changes in app/models.py
@devonair squash Django migrations to reduce migration count
Rails
@devonair generate Rails migration for new fields on User model
@devonair create reversible Rails migration for column rename
Prisma
@devonair generate Prisma migration from schema.prisma changes
@devonair create baseline migration for existing database
TypeORM
@devonair generate TypeORM migration from entity changes
@devonair create migration to sync entities with existing database
SQLAlchemy/Alembic
@devonair generate Alembic migration with autogenerate
@devonair create Alembic migration for manual schema change
Zero-Downtime Migrations
Production systems need migrations that don't interrupt service.
Expand and Contract
@devonair implement expand/contract migration for renaming the users.name column
Add new structure, migrate data, remove old structure.
Online Schema Changes
@devonair generate pt-online-schema-change command for this ALTER TABLE
@devonair create gh-ost configuration for this migration
Feature Flags
@devonair implement migration with feature flag to switch between old and new schema
Gradual rollout reduces risk.
Blue-Green Databases
@devonair create migration strategy for blue-green database deployment
Keep old schema operational while new schema is prepared.
Migration Testing
Test migrations before production.
Local Testing
@devonair test migration on local database copy
@devonair verify migration runs correctly against fresh and existing databases
Production Data Testing
@devonair create anonymized production data subset for migration testing
Test with realistic data volumes and shapes.
Rollback Testing
@devonair test rollback procedure for this migration
Verify you can reverse the change.
Performance Testing
@devonair measure migration performance on production-sized dataset
Surprises in production are bad surprises.
Migration Deployment
Deployment Integration
@devonair integrate migration into deployment pipeline
@devonair run migration as part of pre-deployment hook
Health Checks
@devonair verify application health after migration
@devonair rollback migration if health check fails
Monitoring
@devonair add monitoring for migration progress and performance
Watch migrations as they run.
Migration Documentation
Document migrations for future reference.
Change Documentation
@devonair document what this migration changes and why
Future developers need context.
Runbook Creation
@devonair create runbook for executing this migration in production
Step-by-step instructions reduce errors.
Impact Analysis
@devonair document which applications are affected by this schema change
Know what might break.
Handling Migration Failures
When things go wrong.
Failure Recovery
@devonair create recovery plan for partially applied migration
@devonair generate script to detect and fix partial migration state
Rollback Execution
@devonair execute rollback procedure for failed migration
Prepared rollback plans save hours of panic.
Post-Mortem
@devonair analyze migration failure and suggest preventive measures
Learn from failures to prevent repetition.
Getting Started
Start with migration generation:
@devonair generate migration for pending model changes
Add safety validation:
@devonair validate migration for safety before applying
Implement proper testing:
@devonair test migration on staging before production
Set up automated checks:
@devonair on PR: generate migrations for model changes automatically
Database migrations don't have to be scary. When migrations generate automatically, validate thoroughly, and deploy safely, schema evolution becomes a routine part of development rather than a dreaded special event.
FAQ
Should migrations be auto-applied?
In development, yes. In production, migrations should be reviewed and applied deliberately. The generation can be automatic; the application should be controlled.
How do I handle migrations in a microservices environment?
Each service owns its database and migrations. Coordinate breaking changes through API versioning. Never share databases across services.
What about migrating production while the app is running?
Use zero-downtime patterns: expand/contract, online schema changes, and backwards-compatible changes. Never make breaking changes while old code is running.
How do I recover from a bad migration?
If you have rollback scripts, run them. If not, restore from backup. This is why migration testing and rollback planning matter.