Why doesn't my SQLAlchemy model change update the database?

SQLAlchemy models don't auto-migrate the database. You need Alembic to detect model changes and generate migration scripts. Run 'alembic revision --autogenerate' then 'alembic upgrade head' to apply changes.

Alembic Migrations

Core Concept

Your database doesn't update itself. Change the Python model without a migration, and your app crashes at runtime.

You add a new 'phone' column to your User model and restart the app. The column doesn't exist in the database. Your app crashes because you changed the Python model but forgot the database doesn't update itself.

terminal

# You added this to your model:
class User(Base):
    phone: Mapped[str | None]  # New column!

# You restart the app and hit the endpoint:
GET /users/1

sqlalchemy.exc.OperationalError:
(sqlite3.OperationalError) no such column: users.phone

# The model says "phone exists"
# The database says "phone? never heard of it"
# SQLAlchemy models DON'T auto-migrate the database.

Question

Ever added a field to your model, restarted, and gotten a "no such column" error? That's the fundamental disconnect: your Python model describes what the schema should look like. The database is what it actually looks like. Alembic bridges that gap by generating migration scripts that transform the database to match your models.

The migration workflow

Every time you change a model, you run this same cycle. Alembic compares what your models say vs. what the database actually has, and generates the SQL to fix the difference.

Edit Model

Add/change columns

Autogenerate

alembic revision --autogenerate

Review Script

Check the generated migration

Apply

alembic upgrade head

Core Concept

What you just learned

Models describe the desired schema. The database has the actual schema. They can drift apart.

Alembic detects the drift and generates migration scripts to reconcile them.

You always review before applying — Alembic isn't perfect, especially with renames.

Setting up Alembic

You only do this once per project. It creates the folder structure that holds your migration scripts. Think of it like git init but for your database schema.

terminal

# Install
pip install alembic

# Initialize in your project root
alembic init alembic

# This creates:
# alembic/
#   env.py          <- connects Alembic to your models
#   script.py.mako  <- template for migration files
#   versions/       <- migration scripts live here
# alembic.ini       <- database URL configuration

alembic.ini

# alembic.ini — set your database URL
sqlalchemy.url = sqlite:///./app.db

# For PostgreSQL:
# sqlalchemy.url = postgresql://user:pass@localhost/dbname

The critical step: configuring env.py

This is where most people get stuck. You MUST import your Base and all your models so Alembic can see them. If you skip this, autogenerate will produce empty migrations because it doesn't know your tables exist.

alembic/env.py

# alembic/env.py

# Import your Base so Alembic can see all registered models
from app.database import Base
from app.models import User, Post, Tag  # Import ALL models!

# This is the line that matters — it tells Alembic your desired schema
target_metadata = Base.metadata

# Everything else in env.py can stay as the default.
# Alembic compares target_metadata against the actual database
# to figure out what changed.

Watch out

The most common gotcha: you create a new model file but forget to import it in env.py. Alembic only sees models that are imported into the same Python process. If the import is missing, the model is invisible, and autogenerate skips it entirely. You run the migration and wonder why the table wasn't created.

Configuration

What you just learned

env.py connects Alembic to your models via Base.metadata

Every model must be imported in env.py, or Alembic can't see it

target_metadata is the 'desired state' that Alembic compares against the database

Generating & applying migrations

Alembic compares your models against the current database schema and generates a migration script with the differences. Always review the generated file — Alembic is smart but not perfect.

terminal

# Generate a migration from model changes
alembic revision --autogenerate -m "add is_active column to users"

# This creates a file like: alembic/versions/a3f2b1c9_add_is_active.py

alembic/versions/a3f2b1c9_add_is_active.py

# Generated migration file
"""add is_active column to users"""

revision = "a3f2b1c9"
down_revision = "8b4e2d1f"

from alembic import op
import sqlalchemy as sa

def upgrade():
    # This runs when you apply the migration
    op.add_column("users", sa.Column(
        "is_active", sa.Boolean(),
        server_default=sa.text("true"),
        nullable=False,
    ))

def downgrade():
    # This runs when you rollback
    op.drop_column("users", "is_active")

terminal

# Apply pending migrations
alembic upgrade head

# Rollback one migration
alembic downgrade -1

# Check current revision
alembic current

# Show migration history
alembic history

Generating Migrations

What you just learned

alembic revision --autogenerate compares models to the DB and generates a script

Always review autogenerated migrations — Alembic can miss renames and data moves

upgrade() goes forward, downgrade() rolls back. Always write both.

alembic upgrade head applies all pending migrations in order

See It: Migration Workflow

Watch the complete migration workflow — from editing a model to seeing the schema update in the database.

Go Deeper: Tricky migrations

Alembic handles simple column adds automatically. But adding a NOT NULL column to an existing table? Renaming columns? Moving data? You need to handle these yourself.

alembic/versions/migration.py

# 1. New NOT NULL column on existing table?
# You MUST provide server_default, or existing rows violate the constraint
op.add_column("users", sa.Column(
    "role", sa.String(20),
    server_default="user",   # <- database fills existing rows with this
    nullable=False,
))

# 2. Data migration — when you need to move data, not just schema
def upgrade():
    # Add column first
    op.add_column("users", sa.Column("full_name", sa.String(200)))

    # Then populate it from existing data
    op.execute("UPDATE users SET full_name = name")

    # Then drop the old column
    op.drop_column("users", "name")

# 3. Never use Base.metadata.create_all() in production
# It doesn't track changes, can't rollback, and skips migrations
# It's fine for tests. That's it.

Adding NOT NULL without a default

You add a required 'role' column to Users with 500 existing rows. The migration fails because existing rows have no value for 'role'.

Broken code

alembic/versions/migration.py

def upgrade():
    op.add_column("users", sa.Column(
        "role", sa.String(20),
        nullable=False,  # Required! But existing rows have no value...
    ))

Think about it...

You run alembic upgrade head but the migration fails halfway through. Is your database in the old state, the new state, or something in between?

Hint: Different databases handle DDL (CREATE, ALTER, DROP) transactions differently.

Key Points

Autogenerate

Alembic detects model changes and generates migration scripts automatically

Version Chain

Migrations form a linked chain with upgrade() and downgrade() functions

Never create_all

Use create_all only for tests — production needs Alembic migrations

Review First

Always review generated migrations — Alembic can miss renames and data moves