Skip to main content

Alembic Basics: Getting Started with Database Migrations

Alembic is a lightweight migration tool that tracks database schema changes as timestamped Python files you commit to version control. Installing Alembic and running alembic init creates a versioned migration folder, an environment configuration file, and a special metadata table in your database to track which migrations have run. Every schema change becomes a numbered revision that you can review, test, and roll back.

What Is Alembic and Why You Need It​

Alembic is a database migration framework for SQLAlchemy, though it works with any SQL database. When you add a column, rename a table, or drop an index, you write (or auto-generate) a migration file. Running alembic upgrade head applies pending migrations in sequence; running alembic downgrade -1 rolls back the last one. This decouples your application code from your database schema—you deploy code and migrations independently, and each is reversible.

Most teams without a migration tool resort to manual SQL scripts or risky ad-hoc alter statements. Alembic prevents schema drift, where your production database diverges from your development environment. It also gives you a clear audit trail of who changed what and when.

Step 1: Install Alembic​

Use pip to install Alembic and SQLAlchemy (the ORM engine Alembic depends on):

pip install alembic sqlalchemy

This installs the alembic command-line tool and the libraries it needs. For a specific version (e.g., for reproducible builds), pin it in requirements.txt:

alembic==2.0.6
sqlalchemy==2.0.23

Check the installation:

alembic --version

You should see output like alembic, version 2.0.6 (exact version depends on your pip packages).

Step 2: Initialize Your Migration Environment​

Create a new directory for your project and initialize Alembic:

mkdir my_app
cd my_app
alembic init migrations

This creates a migrations/ folder with these key files:

  • migrations/env.py — Alembic's runtime configuration; defines how to connect to your database
  • migrations/script.py.mako — Template for new migration files
  • migrations/versions/ — Folder where all migration files (numbered revisions) live
  • alembic.ini — Project-level configuration file specifying database URL, logging, and other options
  • migrations/README — Quick reference for Alembic commands

Step 3: Configure Your Database Connection​

Edit alembic.ini and set the sqlalchemy.url parameter to point to your database. For a local SQLite database:

# alembic.ini
sqlalchemy.url = sqlite:///./app.db

For PostgreSQL with a password:

sqlalchemy.url = postgresql://user:password@localhost:5432/mydb

For MySQL:

sqlalchemy.url = mysql+pymysql://user:password@localhost:3306/mydb

Alembic reads this URL on every command to know which database to talk to.

Open migrations/env.py and add your SQLAlchemy Base metadata so Alembic can auto-detect schema changes. Near the top of the file, add:

# migrations/env.py
from app.models import Base  # Import your SQLAlchemy declarative base

# ...

target_metadata = Base.metadata  # Tell Alembic where your models are

Replace app.models with the actual import path to your models. The Base is your SQLAlchemy declarative base (created with Base = declarative_base() or class Base(DeclarativeBase): pass in newer SQLAlchemy).

Step 5: Create Your First Migration​

Run the auto-generate command to create an initial migration from your models:

alembic revision --autogenerate -m "Initial schema"

This creates a new file in migrations/versions/ named something like 001_initial_schema.py. Open it and you'll see Python code that creates your tables:

# migrations/versions/001_initial_schema.py
def upgrade():
    op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(50), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )

def downgrade():
    op.drop_table('users')

The upgrade() function runs when you apply the migration; downgrade() runs when you rollback.

Step 6: Apply the Migration​

Run your first migration:

alembic upgrade head

This applies all pending migrations up to head (the latest). Alembic creates a table called alembic_version in your database to track which revisions have been applied. You can check the current version:

alembic current

Output shows the hash of the latest applied migration.

Key Directory Structure​

After initialization, your project looks like:

my_app/
  alembic.ini
  app.py
  models.py
  migrations/
    env.py
    script.py.mako
    README
    versions/
      001_initial_schema.py

Every time you run alembic revision or alembic upgrade, Alembic talks to env.py to configure the connection and then runs the appropriate scripts.

Key Takeaways​

  • Alembic manages database schema versions as repeatable, reversible Python scripts stored in Git
  • Install with pip install alembic sqlalchemy and initialize with alembic init migrations
  • Configure your database URL in alembic.ini and link your SQLAlchemy models in env.py
  • Run alembic revision --autogenerate -m "message" to create a new migration
  • Run alembic upgrade head to apply pending migrations and alembic downgrade -1 to rollback the last one
  • Alembic tracks which migrations have run in an alembic_version table in your database

Frequently Asked Questions​

What is the difference between alembic revision and alembic upgrade?​

alembic revision creates a new migration file without running it. alembic upgrade applies one or more existing migrations to your database. Think of revision as "write a change script" and upgrade as "execute it on the database."

Can I use Alembic without SQLAlchemy ORM?​

Yes. Alembic supports raw SQL migrations and non-ORM databases. In env.py, you disable auto-generation and write migrations by hand using the op object, which provides functions like op.execute() for arbitrary SQL. ORM-based projects benefit most from auto-detection, but Alembic is flexible.

How do I roll back to an earlier version?​

Use alembic downgrade <revision> to move back to a specific revision, or alembic downgrade -1 to go back one step. Downgrade runs the downgrade() function in each migration file in reverse order. Always test rollbacks in staging before production.

What if I have an existing database without migrations?​

Run alembic stamp <revision> to mark a specific revision as already applied without running it. For example, alembic stamp head marks the current schema as if all migrations had run. Then create new migrations for any future changes.

Further Reading​