FastAPI Database Migrations with Alembic and SQLModel

Why create_all Isn’t Enough

CRUD with SQLModel created its table with SQLModel.metadata.create_all(engine). That works once. Add a field to the model later, and create_all does nothing, because the table already exists, while the code now expects a column that isn’t there. Migrations are versioned scripts that change the schema step by step and record which step the database is at. Alembic, from the SQLAlchemy authors, is the standard tool for this, and SQLModel tables work with it because they’re SQLAlchemy tables underneath.

Install it with pip install alembic. Outputs come from Alembic 1.20.0, SQLModel 0.0.48 and SQLAlchemy 2.1.4 on Python 3.12, against SQLite 3.49.1. Run every command from your project folder; the paths in the output are from the author’s machine.

Set Up Alembic

Start with one model in models.py:

from sqlmodel import Field, SQLModel


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    age: int | None = None

$ alembic init migrations
Creating directory C:\tmp\fapi-b2\l13\migrations ...  done
Creating directory C:\tmp\fapi-b2\l13\migrations\versions ...  done
Generating C:\tmp\fapi-b2\l13\alembic.ini ...  done
Generating C:\tmp\fapi-b2\l13\migrations\env.py ...  done
Generating C:\tmp\fapi-b2\l13\migrations\README ...  done
Generating C:\tmp\fapi-b2\l13\migrations\script.py.mako ...  done
Please edit configuration/connection/logging settings in C:\tmp\fapi-b2\l13\alembic.ini before proceeding.

alembic.ini holds settings, migrations/env.py is the Python that runs every migration, script.py.mako is the template for new migration files, and versions/ will hold the migrations themselves. Two edits connect Alembic to your app. In alembic.ini, set the database URL:

sqlalchemy.url = sqlite:///heroes.db

In migrations/env.py, replace the target_metadata = None line and the comments above it with:

from sqlmodel import SQLModel

import models  # noqa: F401  registers the tables on SQLModel.metadata

target_metadata = SQLModel.metadata

target_metadata is what autogenerate compares the database against. The import models line is easy to forget, and it matters: a table class only registers itself on SQLModel.metadata when its module is imported. Without the import, Alembic sees no tables and generates an empty migration.

The First Migration, and the First Pitfall

revision --autogenerate compares the models with the database and writes a migration for the difference:

$ alembic revision --autogenerate -m "create hero table"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.schemas
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.tables
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.types
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.constraints
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.defaults
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.comments
INFO  [alembic.autogenerate.compare.tables] Detected added table 'hero'
INFO  [alembic.autogenerate.compare.constraints] Detected added index 'ix_hero_name' on '('name',)'
Generating C:\tmp\fapi-b2\l13\migrations\versions\5148a06dda3a_create_hero_table.py ...  done

It found the table and its index and wrote this file, migrations/versions/5148a06dda3a_create_hero_table.py:

"""create hero table

Revision ID: 5148a06dda3a
Revises: 
Create Date: 2026-10-13 12:56:26.982596

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '5148a06dda3a'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('hero',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sqlmodel.sql.sqltypes.AutoString(), nullable=False),
    sa.Column('age', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_hero_name'), 'hero', ['name'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_hero_name'), table_name='hero')
    op.drop_table('hero')
    # ### end Alembic commands ###

Each migration has an upgrade() that applies the change and a downgrade() that reverses it. down_revision links it to the previous migration, None here because it’s the first. Now apply it, then ask where the database is:

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 5148a06dda3a, create hero table
...
  File "C:\tmp\fapi-b2\l13\migrations\versions\5148a06dda3a_create_hero_table.py", line 26, in upgrade
    sa.Column('name', sqlmodel.sql.sqltypes.AutoString(), nullable=False),
                      ^^^^^^^^
NameError: name 'sqlmodel' is not defined
$ alembic current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

SQLModel stores str fields with its own column type, AutoString, and autogenerate wrote it out by its full name, sqlmodel.sql.sqltypes.AutoString(). The template imports only op and sa, so line 26 refers to a module that was never imported. This happens to every SQLModel project on its first migration. alembic current printed no revision: the migration failed before it was recorded, so the database is still at the start and nothing needs cleaning up.

Fix the template once, so every future migration gets the import. In migrations/script.py.mako, add one line under import sqlalchemy as sa:

from alembic import op
import sqlalchemy as sa
import sqlmodel

The broken migration was generated from the old template, so delete it and generate it again. From here on, the six setting up autogenerate plugin lines that every autogenerate prints are left out:

$ alembic revision --autogenerate -m "create hero table"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare.tables] Detected added table 'hero'
INFO  [alembic.autogenerate.compare.constraints] Detected added index 'ix_hero_name' on '('name',)'
Generating C:\tmp\fapi-b2\l13\migrations\versions\1a1b4ab02539_create_hero_table.py ...  done
$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 1a1b4ab02539, create hero table
$ alembic current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
1a1b4ab02539 (head)

Every revision gets a new random id, so this one is 1a1b4ab02539. (head) means the database is at the newest migration. Alembic records the id in a table of its own, alembic_version.

Look at the Real Schema

To see what the migrations did, a short script prints the table’s CREATE statement and its rows. Save as schema.py:

import sqlite3

db = sqlite3.connect("heroes.db")
for (sql,) in db.execute("SELECT sql FROM sqlite_master WHERE name = 'hero'"):
    print(sql)
for row in db.execute("SELECT * FROM hero"):
    print(row)

Add two heroes, so there’s data to lose. Save as seed.py:

from sqlmodel import Session, create_engine

from models import Hero

engine = create_engine("sqlite:///heroes.db")
with Session(engine) as session:
    session.add(Hero(name="Deadpond", age=30))
    session.add(Hero(name="Rusty-Man", age=48))
    session.commit()
print("seeded")

$ python seed.py
seeded
$ python schema.py
CREATE TABLE hero (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	age INTEGER, 
	PRIMARY KEY (id)
)
(1, 'Deadpond', 30)
(2, 'Rusty-Man', 48)

AutoString became a plain VARCHAR in SQLite. The SQL DDL lesson covers what these statements mean.

Add a Column

Add an email field to the model:

from sqlmodel import Field, SQLModel


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    age: int | None = None
    email: str | None = None

$ alembic revision --autogenerate -m "add hero email"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare.tables] Detected added column 'hero.email'
Generating C:\tmp\fapi-b2\l13\migrations\versions\88321afba5ec_add_hero_email.py ...  done
$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 1a1b4ab02539 -> 88321afba5ec, add hero email
$ python schema.py
CREATE TABLE hero (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	age INTEGER, email VARCHAR, 
	PRIMARY KEY (id)
)
(1, 'Deadpond', 30, None)
(2, 'Rusty-Man', 48, None)

The generated upgrade() is a single op.add_column('hero', sa.Column('email', sqlmodel.sql.sqltypes.AutoString(), nullable=True)), and downgrade() is the matching op.drop_column. The existing rows kept their data and got NULL for the new column. SQLite stores the table’s CREATE text and edits it in place, which is why email VARCHAR sits on the same line as age.

The Rename That Deletes Data

Now rename age to years in the model (years: int | None = None) and autogenerate again:

$ alembic revision --autogenerate -m "rename age to years"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare.tables] Detected added column 'hero.years'
INFO  [alembic.autogenerate.compare.tables] Detected removed column 'hero.age'
Generating C:\tmp\fapi-b2\l13\migrations\versions\a24135f18ddc_rename_age_to_years.py ...  done

Autogenerate can’t see a rename. It sees age gone and years new, and writes exactly that:

def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('hero', sa.Column('years', sa.Integer(), nullable=True))
    op.drop_column('hero', 'age')
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('hero', sa.Column('age', sa.INTEGER(), nullable=True))
    op.drop_column('hero', 'years')
    # ### end Alembic commands ###

Run it without reading it, then try to undo it:

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 88321afba5ec -> a24135f18ddc, rename age to years
$ python schema.py
CREATE TABLE hero (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	email VARCHAR, years INTEGER, 
	PRIMARY KEY (id)
)
(1, 'Deadpond', None, None)
(2, 'Rusty-Man', None, None)
$ alembic downgrade -1
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade a24135f18ddc -> 88321afba5ec, rename age to years
$ python schema.py
CREATE TABLE hero (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	email VARCHAR, age INTEGER, 
	PRIMARY KEY (id)
)
(1, 'Deadpond', None, None)
(2, 'Rusty-Man', None, None)
$ alembic current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
88321afba5ec

The ages 30 and 48 are gone. The upgrade added an empty years and dropped age along with its data. alembic downgrade -1 stepped back one revision and did put an age column back, but an empty one: a downgrade can only restore structure, not data that was dropped. alembic current confirms the database is one step back, at 88321afba5ec, with no (head). The header comment “please adjust!” is meant literally. Read every generated migration before you run it.

Fix It by Hand

In real life the ages would come back from a backup. Here a script puts them back. Save as restore_ages.py:

import sqlite3

db = sqlite3.connect("heroes.db")
db.executemany("UPDATE hero SET age = ? WHERE id = ?", [(30, 1), (48, 2)])
db.commit()
print("ages restored")

Then replace both functions in a24135f18ddc_rename_age_to_years.py with a real rename:

def upgrade() -> None:
    """Upgrade schema."""
    op.alter_column('hero', 'age', new_column_name='years')


def downgrade() -> None:
    """Downgrade schema."""
    op.alter_column('hero', 'years', new_column_name='age')

$ python restore_ages.py
ages restored
$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 88321afba5ec -> a24135f18ddc, rename age to years
$ python schema.py
CREATE TABLE hero (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	email VARCHAR, years INTEGER, 
	PRIMARY KEY (id)
)
(1, 'Deadpond', None, 30)
(2, 'Rusty-Man', None, 48)

The values moved with the column. Finally, check that the models and the database agree, list the migrations, and prove the rename now goes both ways:

$ alembic check
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
No new upgrade operations detected.
$ alembic history
88321afba5ec -> a24135f18ddc (head), rename age to years
1a1b4ab02539 -> 88321afba5ec, add hero email
<base> -> 1a1b4ab02539, create hero table
$ alembic downgrade -1
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade a24135f18ddc -> 88321afba5ec, rename age to years
$ python schema.py
CREATE TABLE hero (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	email VARCHAR, age INTEGER, 
	PRIMARY KEY (id)
)
(1, 'Deadpond', None, 30)
(2, 'Rusty-Man', None, 48)
$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 88321afba5ec -> a24135f18ddc, rename age to years
$ alembic current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
a24135f18ddc (head)

alembic check runs autogenerate without writing a file and reports whether anything is left to migrate. It’s useful in CI to catch a model change with no migration. history lists the chain from <base>. This time the downgrade kept the data, because renaming is reversible and dropping isn’t. The workflow to keep: change the model, autogenerate, read and fix the migration, upgrade, and commit the migration file with the code that needs it. In production the app no longer calls create_all; you run alembic upgrade head before starting it.

Practice

Start each task from the finished project at revision a24135f18ddc, with the two heroes in the database.

1. Add a required team: str field to Hero and migrate the database that already holds two heroes. What goes wrong, and how do you fix the migration?

$ alembic upgrade head
...
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL
[SQL: ALTER TABLE hero ADD COLUMN team VARCHAR NOT NULL]
(Background on this error at: https://sqlalche.me/e/21/e3q8)

    op.add_column('hero', sa.Column('team', sqlmodel.sql.sqltypes.AutoString(), nullable=False,
                                    server_default='Unassigned'))

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade a24135f18ddc -> 0322cd3c3ab6, add hero team
$ python schema.py
CREATE TABLE hero (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	email VARCHAR, years INTEGER, team VARCHAR DEFAULT 'Unassigned' NOT NULL, 
	PRIMARY KEY (id)
)
(1, 'Deadpond', None, 30, 'Unassigned')
(2, 'Rusty-Man', None, 48, 'Unassigned')

2. Make email unique with email: str | None = Field(default=None, unique=True), then autogenerate and upgrade. Fix both problems you hit.

$ alembic revision --autogenerate -m "unique hero email"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare.constraints] Detected added unique constraint None on '('email',)'
C:\tmp\fapi-b2\venv\Lib\site-packages\alembic\autogenerate\render.py:484: UserWarning: Autogenerate rendered a drop_constraint() directive for an unnamed constraint on table 'hero'; the migration will fail unless a constraint name is added to the directive.  Consider using a naming convention so that constraint names are known ahead of time.
  util.warn(
Generating C:\tmp\fapi-b2\l13p2\migrations\versions\73b980ec796b_unique_hero_email.py ...  done
$ alembic upgrade head
...
NotImplementedError: No support for ALTER of constraints in SQLite dialect. Please refer to the batch mode feature which allows for SQLite migrations using a copy-and-move strategy.

        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            render_as_batch=True,
        )

def upgrade() -> None:
    """Upgrade schema."""
    with op.batch_alter_table('hero', schema=None) as batch_op:
        batch_op.create_unique_constraint('uq_hero_email', ['email'])


def downgrade() -> None:
    """Downgrade schema."""
    with op.batch_alter_table('hero', schema=None) as batch_op:
        batch_op.drop_constraint('uq_hero_email', type_='unique')

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade a24135f18ddc -> 6fd48f29d7c3, unique hero email
$ python schema.py
CREATE TABLE "hero" (
	id INTEGER NOT NULL, 
	name VARCHAR NOT NULL, 
	email VARCHAR, 
	years INTEGER, 
	PRIMARY KEY (id), 
	CONSTRAINT uq_hero_email UNIQUE (email)
)
(1, 'Deadpond', None, 30)
(2, 'Rusty-Man', None, 48)
$ alembic downgrade -1
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 6fd48f29d7c3 -> a24135f18ddc, unique hero email

3. Before running migrations against a database you care about, look at the SQL they will execute without touching the database.

$ alembic upgrade 1a1b4ab02539:head --sql
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 1a1b4ab02539 -> 88321afba5ec, add hero email
-- Running upgrade 1a1b4ab02539 -> 88321afba5ec

ALTER TABLE hero ADD COLUMN email VARCHAR;

UPDATE alembic_version SET version_num='88321afba5ec' WHERE alembic_version.version_num = '1a1b4ab02539';

INFO  [alembic.runtime.migration] Running upgrade 88321afba5ec -> a24135f18ddc, rename age to years
-- Running upgrade 88321afba5ec -> a24135f18ddc

ALTER TABLE hero RENAME COLUMN age TO years;

UPDATE alembic_version SET version_num='a24135f18ddc' WHERE alembic_version.version_num = '88321afba5ec';