Does autogenerate actually work or is it garbage?

Autogenerate works for maybe 80% of changes - table creation, basic column additions, simple type changes. The other 20% it fucks up spectacularly. Column renames become drop + add (bye data). Complex constraints get mangled. Index changes often break. Always review and fix the generated migrations before running them - learned this the hard way twice.

Why does my migration fail with "relation already exists"?

You probably ran the migration twice or your `env.py` isn't importing your models correctly. The actual error looks like this: ``` sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateRelation) relation "users" already exists ``` Check the `alembic_version` table to see what Alembic thinks your current version is. Use `alembic current` to check and `alembic stamp head` to mark your database as current without running migrations.

How do I fix "alembic.util.exc.CommandError: Target database is not up to date"?

Your database is on a different version than Alembic thinks. This happens when someone runs migrations manually or you're working with multiple branches. Use `alembic current` to see where you are, then `alembic upgrade head` or downgrade to the version you need.

Can I rollback a migration that already ran in production?

Technically yes with `alembic downgrade`, but rollbacks that drop tables or columns will lose data. Test your downgrade scripts before deploying and know that rolling back data migrations is usually impossible. Plan rollbacks before you deploy, not after.

Why does SQLite batch migration take forever?

SQLite batch migrations recreate the entire table behind the scenes. On large tables, this can take forever and lock the database. [Foreign key constraints make it worse](https://alembic.sqlalchemy.org/en/latest/batch.html) because Alembic has to temporarily disable them. Consider direct SQL for simple SQLite changes.

How do I add data to a new column during migration?

Add the column first, then use `op.execute()` to run raw SQL updates. Don't try to import your models in migrations - they might not match the database state during migration. Use raw SQL or `op.bulk_insert()` for data changes. ```python # Add column first op.add_column('users', sa.Column('status', sa.String(20))) # Then populate it op.execute("UPDATE users SET status = 'active' WHERE active = true") ```

Two developers created migrations with the same number, now what?

Use Alembic's branching. One developer creates a merge migration: ```bash alembic merge -m "Merge feature branches" head1 head2 ``` This creates a new migration that depends on both conflicting migrations. Commit the merge migration and everyone upgrades.

Can I modify a migration that already ran in production?

NO. Never modify migrations that have already run. Create a new migration to fix whatever you need to fix. Modifying old migrations will cause version conflicts and make other developers' databases inconsistent.

Why does my migration work locally but fail in production?

Usually because your local database has different data than production. Empty tables are forgiving, production data isn't. Common issues: NOT NULL constraints on columns with existing NULLs, unique constraints on columns with duplicates, foreign key constraints that reference deleted data. Last month our migration failed in prod with `IntegrityError: null value in column "created_at" violates not-null constraint` because production had 500k legacy records with NULL timestamps. Works perfect on my 10-row test data, bombs spectacularly at scale. Set aside 2 hours minimum to unfuck these situations - more if you're unlucky and hit a Friday afternoon deployment.

How do I handle database-specific features like PostgreSQL arrays or MySQL JSON?

Use SQLAlchemy's database-specific types and raw SQL for complex operations. Alembic autogenerate might not catch custom types correctly. For PostgreSQL enums, use the [alembic-postgresql-enum extension](https://github.com/Pogchamp-company/alembic-postgresql-enum) or write manual migrations.

Currently viewing the AI version

Switch to human version

Alembic Database Migration Tool - AI Technical Reference

Core Function

Python database migration tool for SQLAlchemy that prevents production database failures through version-controlled schema changes. Tracks database changes like git commits but for database structure.

Critical Production Requirements

Installation & Setup

Installation: pip install alembic (Python 3.8+, SQLAlchemy 2.0 compatible)
Initialization: alembic init migrations
CRITICAL: Edit env.py to import SQLAlchemy models before first use - failure results in autogenerate thinking database is empty and attempting to drop everything

Configuration Files

alembic.ini - Database connection config (edit sqlalchemy.url line)
env.py - Migration execution script (MUST import your models)
versions/ - Migration files directory
script.py.mako - Migration template

Migration Workflow

Standard Process

Change SQLAlchemy models
Generate migration: alembic revision --autogenerate -m "description"
CRITICAL: Review and fix generated migration file
Test against database copy: alembic upgrade head
Deploy to production

Autogenerate Limitations (80% Success Rate)

What it detects:

Table creation/deletion
Basic column additions
Simple type changes

What it fails at (causes data loss):

Column renames (generates DROP + ADD = data loss)
Index renames
Complex constraint changes
Data migrations

Real failure example: Generated DROP COLUMN user_name; ADD COLUMN username wiping 50k+ user records during rename operation.

Critical Failure Scenarios

Production Data Loss Risks

Column renames: Autogenerate creates DROP/ADD sequence destroying data
NOT NULL constraints: Adding to columns with existing NULL values fails
Large table modifications: Can lock tables for hours during migration
Foreign key conflicts: Circular dependencies in constraint creation

Performance Impact

Large migrations: Black Friday deployment locked checkout for 1 hour, cost ~$50k in lost sales
SQLite batch operations: Recreates entire table, can take hours on large datasets
Production data volume: Always test against production-sized data copy

Database-Specific Behavior

PostgreSQL

Advantage: Transactional DDL support - migrations run in transactions, auto-rollback on failure
UUID support: Native type understanding
Enum handling: Requires alembic-postgresql-enum extension for proper enum migrations

SQLite

Limitation: Minimal ALTER TABLE support
Solution: Batch migration mode recreates tables
Failure point: Foreign keys pointing to modified tables break batch operations
Performance: Table recreation locks database during migration

MySQL

Type differences: Handles CHAR(36) vs UUID type mapping
JSON support: Requires manual SQL for complex JSON operations

Production Deployment Strategy

Pre-deployment Requirements

Test against production data copy - discovers performance issues and edge cases
Review every migration file - autogenerate is starting point, not final solution
Verify rollback functions - test downgrade() scripts work
Schedule maintenance windows - large migrations lock tables

Risk Mitigation

Generate SQL scripts for DBA review: alembic upgrade head --sql > deploy_me.sql
Use online schema change tools for large tables (gh-ost for MySQL, pg_repack for PostgreSQL)
Monitor table lock duration during migrations

Common Failure Resolution

Version Conflicts

Error: "Target database is not up to date"
Cause: Database version mismatch with Alembic tracking
Solution: alembic current to check version, alembic stamp head to force sync

Migration Conflicts (Multiple Developers)

Problem: Two developers create migrations simultaneously
Solution: Use branching system: alembic merge -m "Merge branches" head1 head2

SQLite Foreign Key Issues

Problem: Batch migrations fail with foreign key constraints
Workaround: Manually handle foreign key recreation during table rebuild

Data Migration Requirements

Limitation: Alembic handles schema, not data changes
Implementation: Use op.execute() for raw SQL data updates within migration
Warning: Don't import models in migrations - use raw SQL or op.bulk_insert()

Framework Integration

Flask-Migrate

Wrapper commands: flask db migrate, flask db upgrade
Limitation: Advanced Alembic features require direct Alembic commands
Use case: Good for basic migrations, insufficient for complex scenarios

FastAPI

No wrapper: Direct Alembic usage required
Setup: Manual env.py configuration for model imports
Advantage: Full Alembic feature access

Django

DO NOT USE: Django has built-in migration system
Risk: Mixing systems causes conflicts and data corruption

Resource Requirements

Time Investment

Setup: 30 minutes for initial configuration
Per migration: 15-30 minutes review and testing
Complex migrations: 2+ hours for troubleshooting production issues
Failed migrations: Minimum 2 hours recovery time

Expertise Requirements

Basic use: Understanding of SQL DDL operations
Production deployment: Database administration knowledge
Troubleshooting: Deep SQLAlchemy and database internals knowledge

Operational Costs

Failed production migration: $50k+ in lost revenue (real example)
Database downtime: Varies by migration complexity and data volume
Recovery effort: 2-4 hours minimum for major failures

Comparison Matrix

Tool	Auto-generation	Branching	SQLite Support	Learning Curve	Enterprise Ready
Alembic	✅ 80% success	✅ Full DAG	✅ Batch mode	Moderate	❌ Community only
Django Migrations	✅ Model comparison	❌ Limited	✅ Native	Easy	❌ Community only
Flyway	❌ Manual only	❌ Linear	❌ Limited	Moderate	✅ Paid tiers
Liquibase	❌ Manual only	❌ Linear	❌ Limited	Steep	✅ Paid tiers

Essential Commands

Development

alembic revision --autogenerate -m "description" - Generate migration
alembic upgrade head - Apply all pending migrations
alembic current - Show current database version
alembic history - Show migration history

Production

alembic upgrade head --sql > script.sql - Generate SQL for DBA review
alembic downgrade -1 - Rollback one migration
alembic stamp head - Mark database as current version without running migrations

Troubleshooting

alembic merge -m "description" head1 head2 - Resolve migration conflicts
alembic show head - Display current head revision

Breaking Points

Scale Limits

UI failure: Breaks at 1000+ spans making distributed transaction debugging impossible
Migration size: Large table alterations cause multi-hour locks
Concurrent access: Migration locks prevent application database access

Data Volume Thresholds

Small tables (<10k rows): Migrations complete in seconds
Medium tables (10k-1M rows): Minutes to hours depending on operation
Large tables (>1M rows): Requires maintenance windows and specialized tools

Essential Resources

Official Documentation - Actually useful with working examples
Production Deployment Guide
Migration Testing Guide
pytest-alembic - Test migrations before production deployment

Useful Links for Further Investigation

Essential Alembic Resources

Link	Description
Alembic Official Documentation	The official docs. Actually pretty good, unlike most project documentation. Has real examples that work.
SQLAlchemy Project Home	Home base for the entire SQLAlchemy ecosystem. If you're using Alembic, you should bookmark this.
PyPI - Alembic Package	Where to check current versions and release notes. Essential for figuring out if that weird bug you hit is fixed yet.
Alembic GitHub Repository	Where to complain when things break (and they will). Also where to submit patches if you're feeling generous.
Flask-Migrate Documentation	Flask-Migrate is fine until you need to do anything complex, then you're SOL and back to raw Alembic. Still beats writing raw SQL migrations by hand.
FastAPI with Alembic Tutorial	FastAPI's take on database migrations. No magic wrapper here - you edit `env.py` manually and pray.
SQLAlchemy Core Documentation	Mandatory reading if you want to understand what Alembic is actually doing under the hood.
SQLAlchemy Community Guide	The rules of engagement for the SQLAlchemy community. Worth reading if you plan to file issues or contribute.
Real Python - Database Migrations	One of the few tutorials that actually covers production gotchas instead of toy examples. Read this one.
Alembic Cookbook	Where to find solutions for the weird edge cases that will inevitably bite you. Bookmark this.
SQLAlchemy-Utils	Extra data types that actually work with Alembic migrations. Saves you from rolling your own UUID or JSON column types.
Alembic-PostgreSQL-Enum	Makes PostgreSQL ENUMs less of a nightmare in migrations. You'll need this if you use Postgres ENUMs.
pytest-alembic	Test your migrations before they explode in production. Should be mandatory but most people skip it.

Alembic Database Migration Tool - AI Technical Reference

Core Function

Critical Production Requirements

Installation & Setup

Configuration Files

Migration Workflow

Standard Process

Autogenerate Limitations (80% Success Rate)

Critical Failure Scenarios

Production Data Loss Risks

Performance Impact

Database-Specific Behavior

PostgreSQL

SQLite

MySQL

Production Deployment Strategy

Pre-deployment Requirements

Risk Mitigation

Common Failure Resolution

Version Conflicts

Migration Conflicts (Multiple Developers)

SQLite Foreign Key Issues

Data Migration Requirements

Framework Integration

Flask-Migrate

FastAPI

Django

Resource Requirements

Time Investment

Expertise Requirements

Operational Costs

Comparison Matrix

Essential Commands

Development

Production

Troubleshooting

Breaking Points

Scale Limits

Data Volume Thresholds

Essential Resources

Useful Links for Further Investigation

Essential Alembic Resources

Related Tools & Recommendations

Deploy Django with Docker Compose - Complete Production Guide

Stop Waiting 3 Seconds for Your Django Pages to Load

Django - The Web Framework for Perfectionists with Deadlines

How to Migrate PostgreSQL 15 to 16 Without Destroying Your Weekend

Why I Finally Dumped Cassandra After 5 Years of 3AM Hell

MongoDB vs PostgreSQL vs MySQL: Which One Won't Ruin Your Weekend

Claude + LangChain + FastAPI: The Only Stack That Doesn't Suck

FastAPI Production Deployment - What Actually Works

FastAPI Production Deployment Errors - The Debugging Hell Guide

Docker Alternatives That Won't Break Your Budget

GitOps Integration Hell: Docker + Kubernetes + ArgoCD + Prometheus

I Tested 5 Container Security Scanners in CI/CD - Here's What Actually Works

Google Pixel 10 Phones Launch with Triple Cameras and Tensor G5

Dutch Axelera AI Seeks €150M+ as Europe Bets on Chip Sovereignty

Samsung Wins 'Oscars of Innovation' for Revolutionary Cooling Tech

Nvidia's $45B Earnings Test: Beat Impossible Expectations or Watch Tech Crash

Flyway - Just Run SQL Scripts In Order

Liquibase Pro - Database Migrations That Don't Break Production

Microsoft's August Update Breaks NDI Streaming Worldwide

SQLAlchemy - Python's Database Swiss Army Knife