SQLAlchemy and Alembic

Most engineers learn it as a one-line definition. SQLAlchemy is the ORM. Alembic is the migrations tool. That answer is technically true and almost entirely useless, because it does not explain why both exist or why teams that understand only one of them eventually find themselves debugging a production database at two in the morning.

The honest distinction is closer to this. SQLAlchemy defines what your application thinks the database should look like. Alembic manages how the actual database evolves to match that thinking, over time, across environments, without losing data. One is a description of intent. The other is a record of change. Conflating them is the most common reason a small schema modification turns into a deployment incident.

The Genesis of Problem

Consider a familiar scenario. You launch a SaaS product with a users table that holds three columns: an id, a name, and an email. Everything works. Then the product team asks for phone-based login. Then user roles. Then account status. Then audit timestamps. Each request, taken on its own, is a small change. Taken together, over six months, they are a schema that no longer resembles the one you started with.

Now imagine that production has real users, that dev and staging and prod have all drifted apart, that three engineers have been making local changes on their own machines, and that an older version of the app is still running on a server somewhere. Every manual ALTER TABLE you ran along the way is undocumented. There is no version history. There is no rollback. There is only the database as it currently is, which is not the database anyone can describe with confidence.

This is the problem Alembic was built to solve. And it is the problem that SQLAlchemy, despite being the more famous of the two, cannot touch.

What SQLAlchemy actually does

SQLAlchemy is a Python representation of your database. You write tables as classes, columns as attributes, constraints as arguments. A minimal model looks like this:

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

That class is not the table. It is a declaration of what the table should look like, expressed in Python so that the rest of your application can reason about it. SQLAlchemy then does the work of translating Python operations into SQL, mapping rows back into objects, and generally pretending that the database is just another collection of Python objects you happen to be holding.

Without it, you write raw SQL and shuttle tuples around. With it, you write session.query(User).filter(User.id == user_id).first() and let the library handle the rest. The abstraction is real and it is useful.

But notice what the abstraction does not do. When you add a phone column to the User class and save the file, nothing happens to the production database. The Python model has evolved. The actual table has not. The gap between the two is silent, and that silence is where bugs hide.

Alembic as version control for schema

The right mental model for Alembic is git, but for the shape of your database rather than the contents of your code. Each schema change becomes a versioned migration. Each migration is a small file with two functions: one that moves the database forward, and one that walks it back. The state of the database is no longer an oral tradition. It is a sequence of commits.
The workflow in practice is straightforward. After modifying a SQLAlchemy model, you ask Alembic to compare the model to the current state of the database and generate a migration:

alembic revision --autogenerate -m "add phone column"

Alembic produces a file that looks something like this:

def upgrade():
    op.add_column(
        'users',
        sa.Column('phone', sa.String(), nullable=True)
    )

def downgrade():
    op.drop_column('users', 'phone')

You then run alembic upgrade head, and the real database catches up to the model. Internally, Alembic keeps a small alembic_version table that records which migrations have been applied, which is how it knows where each environment currently stands. Every environment, from a junior engineer’s laptop to production, evolves through the same ordered sequence of changes. Drift, in theory, becomes impossible.

The thing tutorials do not tell you

The --autogenerate flag is helpful, and it is also the single most dangerous part of the toolchain when used without thought. It can detect that a column has been added. It cannot reliably detect that a column has been renamed rather than dropped and recreated. It does not understand intent. It will happily generate a migration that drops a column containing six months of customer data because, from its narrow perspective, the column simply disappeared from the model.

Senior engineers read every generated migration before committing it. That is not paranoia. It is the recognition that the tool is doing pattern matching, not engineering, and that the difference matters most precisely when the change is non-trivial.

Consider a real case. You decide to split a name column into first_name and last_name. Autogenerate cannot infer what to do with the existing data in name. Left alone, it may drop the column outright. The migration you actually need looks like this:

def upgrade():
    op.add_column('users', sa.Column('first_name', sa.String()))
    op.add_column('users', sa.Column('last_name', sa.String()))

    op.execute("""
        UPDATE users
        SET first_name = split_part(name, ' ', 1),
            last_name = split_part(name, ' ', 2)
    """)

    op.drop_column('users', 'name')

The new columns are created first. The data is copied across with a hand-written SQL statement. Only then is the old column dropped. That ordering is not a stylistic choice. It is the difference between a successful deploy and a Monday morning spent restoring from backup.

What the distinction is really about

It helps to hold the two tools in mind as answers to two different questions. SQLAlchemy answers what should the schema be right now. Alembic answers how do we get from the schema we had yesterday to the schema we want today, safely, on every machine, with the ability to undo the move if it goes wrong. The first is a description of a destination. The second is the route.

Most juniors confuse the two because the tutorials treat schema design as the interesting problem and migrations as a chore that the framework handles for you. The truth is closer to the reverse. Designing a schema in SQLAlchemy is a couple of hours of work. Evolving that schema across years of product changes, with real users and real data and a team that keeps growing, is one of the genuinely hard operational problems in software. The framework does not make it easy. It only makes it possible.

Treat SQLAlchemy as the language in which you describe what you want. Treat Alembic as the discipline by which you actually get there. Read every migration before you run it. Write the dangerous ones by hand. Keep them in version control alongside the code that depends on them. Do these things, and the database stops being the part of the system you are afraid of touching.