Alembic — Database Migration Tool for SQLAlchemy

Introduction

Alembic is the migration tool for SQLAlchemy, managing incremental, reversible changes to database schemas. It generates migration scripts by comparing SQLAlchemy models against the current database state and applies those scripts in order to bring a database to any target version. It is maintained by the SQLAlchemy project.

What Alembic Does

• Generates migration scripts automatically by diffing SQLAlchemy models against the live database
• Applies upgrade and downgrade migrations in sequence
• Tracks migration history with a version table in the database
• Supports branching and merging of migration chains for team workflows
• Executes raw SQL or SQLAlchemy operations within migration scripts

Architecture Overview

Alembic uses an env.py file that configures the database connection and migration context. Each migration is a Python script in a versions/ directory containing upgrade() and downgrade() functions. Autogenerate introspects the database and compares it with SQLAlchemy's MetaData to produce a diff. A chain of version identifiers links migrations, and an alembic_version table records the current revision.

Self-Hosting & Configuration

• Install via pip; works with any SQLAlchemy-supported database (PostgreSQL, MySQL, SQLite, Oracle, etc.)
• Initialize with alembic init which creates alembic.ini and the migration environment
• Configure the database URL in alembic.ini or in env.py
• Autogenerate compares models to the database; manual edits may be needed for data migrations
• Supports offline mode for generating SQL scripts without a live connection

Key Features

• Autogenerate migrations by detecting table, column, index, and constraint changes
• Branch and merge support for parallel development workflows
• Batch operations mode for databases that lack ALTER TABLE support (e.g., SQLite)
• Hooks for custom comparison logic and rendering of migration operations
• Stamp command to mark a database at a specific revision without running migrations

Comparison with Similar Tools

• Django Migrations — Built into Django ORM; Alembic is for SQLAlchemy-based projects
• Flyway — Java-based, uses plain SQL files; Alembic uses Python scripts with autogeneration
• Liquibase — XML/YAML changelogs; Alembic leverages SQLAlchemy's Python model introspection
• dbmate — Language-agnostic SQL migrations; Alembic's autogenerate ties directly into SQLAlchemy models

FAQ

Q: Does autogenerate detect all schema changes? A: It handles tables, columns, indexes, foreign keys, and constraints. Some changes like table or column renames require manual edits.

Q: Can I use Alembic without autogenerate? A: Yes. You can write migration scripts manually using alembic revision and fill in the upgrade/downgrade functions yourself.

Q: How do I handle data migrations? A: Migration scripts can include arbitrary Python code and SQL. Use op.execute() for raw SQL or SQLAlchemy's bulk_insert for data transformations.

Sources

• https://github.com/sqlalchemy/alembic
• https://alembic.sqlalchemy.org