Database migration management for this project. Covers migration tools, commands, rollback procedures, and seeder patterns.
HIGH-RISK: Database migrations can cause data loss. Always backup data before running migrations in production. Test thoroughly in development first.
This project uses Alembic for database migrations (recommended for SQLAlchemy).
pip install alembic sqlalchemy
alembic init alembic
This creates:
project/
├── alembic/
│ ├── env.py # Migration environment
│ ├── script.py.mako # Template for migrations
│ └── versions/ # Migration files
├── app/
│ └── models.py # SQLAlchemy models
├── alembic.ini # Alembic config
└── requirements.txt
# Auto-generate migration from model changes
alembic revision --autogenerate -m "add_users_table"
# Create empty migration (manual changes)
alembic revision -m "create_feedback_table"
# Apply all migrations
alembic upgrade head
# Apply to specific revision
alembic upgrade +1
alembic upgrade abc123
# Rollback last migration
alembic downgrade -1
# Rollback to specific revision
alembic downgrade abc123
# Rollback all migrations
alembic downgrade base
# Show current revision
alembic current
# Show migration history
alembic history --verbose
# Show pending migrations
alembic check
NEVER run downgrade in production without explicit approval.
# 1. Backup production database
pg_dump production_db > backup_$(date +%Y%m%d).sql
# 2. Verify current migration
alembic current
# 3. Rollback ONE migration at a time
alembic downgrade -1
# 4. Verify application still works
curl https://your-api/health
# 5. If issues, roll forward again
alembic upgrade +1
# Rollback all to clean state (destroys all tables!)
alembic downgrade base
# Or manually restore from backup
psql production_db < backup_20240101.sql
Format: <timestamp>_<description>.py
alembic/versions/
├── 20240101_001_initial_create_users.py
├── 20240115_002_add_email_unique_constraint.py
├── 20240201_003_create_feedback_table.py
└── 20240210_004_add_resume_embeddings.py
# alembic/versions/20240101_001_initial_create_users.py
"""add_users_table
Revision ID: abc123
Revises:
Create Date: 2024-01-01 12:00:00.000000
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
# revision identifiers