Lesson 5.5 · Databases

Migrations with Alembic

create_all cannot change a table - Alembic can. Set it up for SQLModel, generate migrations from your models, add a column without losing data, and go back with downgrade. Tested with the traps that stop beginners: the missing import sqlmodel, a NOT NULL column on a table that has rows, and a rename that autogenerate turns into "drop the column".

Intermediate40 min

What you will be able to do

Explain what a migration is and why create_all is not enough
Set up Alembic for SQLModel and SQLite
Generate, read and apply migrations
Add a NOT NULL column to a table with data
Go back with downgrade, and read history and current
Recognise when autogenerate gets it wrong

The idea, in plain English

Your models change: today a to-do gets a due date, next month a priority. The database must change with them - without losing the rows already in it. Lesson 5.2 showed that create_all cannot do this: it only creates missing tables.

A migration is a small Python file that changes the database one step: "add column due_date". Alembic keeps these files in order, remembers in the database which step it is at (the alembic_version table), and can go forward (upgrade) and back (downgrade). It can also write a first draft of each file by comparing your models with the database - autogenerate.

We set Alembic up for SQLModel, grow the to-do table in three steps while three rows stay in it, and hit three real problems on the way. Alembic 1.20.0, SQLModel 0.0.48, SQLite.

Worked example: The to-do table grows: first created by Alembic, then a due_date column, then a priority column - with three to-dos kept safe the whole time.

workflowA chain of migrationsstep 1 / 3

<base>

empty database

bae12f164417

create todo table

✓

528bdec234ad

add due_date

5d24cb989557

add priority (head)

React Flow

1 - Create the table

The first upgrade failed: import sqlmodel was missing in the generated file. After fixing the template, it ran.

first try

NameError: name 'sqlmodel' is not defined

tables

alembic_version, todo

Each migration points to the one before it. Real revisions from our project.

Words you will see in this lesson

A few words about migrations.

Small dictionary

MigrationOne step that changes the database structure, as a Python file.

RevisionThe id of a migration, like 528bdec234ad.

HeadThe newest migration.

Upgrade / downgradeRun migrations forward / back.

AutogenerateAlembic compares the models with the database and drafts a migration.

server_defaultA default value stored IN the database, used for rows it fills itself.

Batch modeAlembic copies a SQLite table to change it, because SQLite cannot change most columns in place.

An everyday example: renovating a house

You do not knock the house down to add a room - you renovate while people live in it. The people are your rows.
Each job is written in the building log: "step 3: add a window to the kitchen". Migrations are that log.
The log says which step the house is at - the alembic_version table.
A good plan also says how to undo each step - downgrade.
The architect’s drawing (your models) is not the house. Autogenerate compares them and proposes the next job - you check the plan before work starts.

Step 1 - set up Alembic

alembic init migrations makes a folder and a config file. Then three changes:

alembic.ini: sqlalchemy.url = sqlite:///todos.db - which database.
migrations/env.py: import SQLModel and your models, and set target_metadata = SQLModel.metadata - so autogenerate knows your tables.
env.py: render_as_batch=True in context.configure - for SQLite, so column changes work by copying the table.
Once you use Alembic, stop calling create_all in the app: migrations own the tables now.

models.py (the start)

from sqlmodel import Field, SQLModel


class Todo(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str = Field(max_length=100)
    done: bool = False

Output

$ alembic init migrations
Creating directory migrations ...  done
Creating directory migrations/versions ...  done
Generating migrations/script.py.mako ...  done
Generating migrations/env.py ...  done
Generating migrations/README ...  done
Generating alembic.ini ...  done
Please edit configuration/connection/logging settings in alembic.ini before proceeding.

The changes in alembic.ini and migrations/env.py

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

# migrations/env.py - near the top
from alembic import context
from sqlmodel import SQLModel

import models  # noqa: F401  - loads the table classes into SQLModel.metadata
...
target_metadata = SQLModel.metadata

# migrations/env.py - in run_migrations_online()
        context.configure(
            connection=connection, target_metadata=target_metadata,
            render_as_batch=True,          # SQLite cannot change columns in place
        )

Step 2 - the first migration, and a missing import

alembic revision --autogenerate -m "create todo table" wrote a file. alembic upgrade head failed:

The generated file used sqlmodel.sql.sqltypes.AutoString for title - but never imported sqlmodel.
Result: NameError: name 'sqlmodel' is not defined.
Fix once in migrations/script.py.mako (the template for every migration): add import sqlmodel next to import sqlalchemy as sa.
We deleted the broken file and the database, generated again, and upgrade worked. Alembic created todo and its own alembic_version table, which now holds the revision id.

Output - the first try

$ alembic upgrade head
  File "migrations/versions/5f2efe5d3cc5_create_todo_table.py", line 26, in upgrade
    sa.Column('title', sqlmodel.sql.sqltypes.AutoString(length=100), nullable=False),
                       ^^^^^^^^
NameError: name 'sqlmodel' is not defined

migrations/script.py.mako - the fix

from alembic import op
import sqlalchemy as sa
import sqlmodel  # SQLModel column types appear in generated migrations

migrations/versions/bae12f164417_create_todo_table.py

"""create todo table

Revision ID: bae12f164417
Revises: 
Create Date: 2026-10-12 22:45:05.169971

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
import sqlmodel  # SQLModel column types appear in generated migrations


# revision identifiers, used by Alembic.
revision: str = 'bae12f164417'
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('todo',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('title', sqlmodel.sql.sqltypes.AutoString(length=100), nullable=False),
    sa.Column('done', sa.Boolean(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('todo')
    # ### end Alembic commands ###

Output

INFO  [alembic.autogenerate.compare.tables] Detected added table 'todo'
Generating migrations/versions/bae12f164417_create_todo_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  -> bae12f164417, create todo table
$ sqlite3 todos.db ".tables" "SELECT * FROM alembic_version;"
alembic_version  todo           
bae12f164417

Step 3 - add a column, keep the data

We put three to-dos into the table, then added due_date: date | None = None to the model:

Autogenerate found it: "Detected added column 'todo.due_date'".
The upgrade() adds the column; downgrade() drops it - read both before you run them.
batch_alter_table appears because of render_as_batch=True.
After alembic upgrade head the three rows were still there, with an empty due_date (the empty last column).

models.py - one new line

    due_date: date | None = None                     # NEW - may be empty

The generated migration (upgrade and downgrade)

def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('todo', schema=None) as batch_op:
        batch_op.add_column(sa.Column('due_date', sa.Date(), nullable=True))

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('todo', schema=None) as batch_op:
        batch_op.drop_column('due_date')

    # ### end Alembic commands ###

Output

INFO  [alembic.autogenerate.compare.tables] Detected added column 'todo.due_date'
Generating migrations/versions/528bdec234ad_add_due_date.py ...  done
$ alembic upgrade head
INFO  [alembic.runtime.migration] Running upgrade bae12f164417 -> 528bdec234ad, add due_date
$ sqlite3 todos.db "SELECT * FROM todo;"
1|Buy milk|0|
2|Pay rent|1|
3|Call mom|0|

Step 4 - a NOT NULL column on old rows

Next: priority: int = 0. Autogenerate wrote nullable=False - and the upgrade failed:

"Cannot add a NOT NULL column with default value NULL". The = 0 lives only in Python; the database had no value for the three old rows.
Nothing changed: alembic_version stayed at the due_date step.
The fix is one argument we added by hand: server_default="0" - a default the database itself uses for the old rows.
Then the upgrade worked and every row got priority 0.

Output - the first try

INFO  [alembic.autogenerate.compare.tables] Detected added column 'todo.priority'
Generating migrations/versions/5d24cb989557_add_priority.py ...  done
$ alembic upgrade head
sqlite3.OperationalError: Cannot add a NOT NULL column with default value NULL
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL

The migration, fixed by hand

def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('todo', schema=None) as batch_op:
        batch_op.add_column(sa.Column('priority', sa.Integer(), nullable=False,
                                      server_default="0"))   # added by hand: a value for the old rows

    # ### end Alembic commands ###

Step 5 - current, history, downgrade

alembic current - which revision the database is at: 5d24cb989557 (head).
alembic history - the chain, newest first.
alembic downgrade -1 - one step back. The priority column was gone; the rows stayed.
alembic upgrade head - forward again.
Downgrade is for mistakes during development. In production, write a new migration that fixes things - a dropped column’s data does not come back.

Output

$ alembic upgrade head
INFO  [alembic.runtime.migration] Running upgrade 528bdec234ad -> 5d24cb989557, add priority
$ sqlite3 todos.db "SELECT * FROM todo;"
1|Buy milk|0||0
2|Pay rent|1||0
3|Call mom|0||0
$ alembic current
5d24cb989557 (head)
$ alembic history
528bdec234ad -> 5d24cb989557 (head), add priority
bae12f164417 -> 528bdec234ad, add due_date
<base> -> bae12f164417, create todo table
$ alembic downgrade -1
INFO  [alembic.runtime.migration] Running downgrade 5d24cb989557 -> 528bdec234ad, add priority
$ sqlite3 todos.db "SELECT * FROM todo;"
1|Buy milk|0|
2|Pay rent|1|
3|Call mom|0|
$ alembic upgrade head
INFO  [alembic.runtime.migration] Running upgrade 528bdec234ad -> 5d24cb989557, add priority

Autogenerate is a draft: the rename trap

We renamed title to name in the model and ran autogenerate:

Alembic cannot know it was a rename. It saw "a column name appeared" and "a column title disappeared".
Its migration: add_column("name") and drop_column("title") - running it would throw away every title.
We deleted that file and wrote the migration by hand with alter_column("title", new_column_name="name"). On a copy of the database, all three rows kept their text.
Always read a generated migration before upgrade - especially drop_column.

Output - autogenerate after the rename

INFO  [alembic.autogenerate.compare.tables] Detected added column 'todo.name'
INFO  [alembic.autogenerate.compare.tables] Detected removed column 'todo.title'
Generating migrations/versions/0a1f55c6a525_rename_title.py ...  done

def upgrade() -> None:
    with op.batch_alter_table('todo', schema=None) as batch_op:
        batch_op.add_column(sa.Column('name', sqlmodel.sql.sqltypes.AutoString(length=100), nullable=False))
        batch_op.drop_column('title')



def downgrade() -> None:

The rename, written by hand

def upgrade() -> None:
    """Upgrade schema."""
    with op.batch_alter_table("todo") as batch_op:
        batch_op.alter_column("title", new_column_name="name")   # a real rename


def downgrade() -> None:
    """Downgrade schema."""
    with op.batch_alter_table("todo") as batch_op:
        batch_op.alter_column("name", new_column_name="title")

Output

$ alembic upgrade head
INFO  [alembic.runtime.migration] Running upgrade 5d24cb989557 -> 41b591b015aa, rename title to name
$ sqlite3 todos.db "SELECT id, name FROM todo;"
1|Buy milk
2|Pay rent
3|Call mom

Alembic at a glance

Start

Once per project.

alembic init migrations

Draft a migration

Then read it.

alembic revision --autogenerate -m "add due_date"

Apply

Up to the newest.

alembic upgrade head

Go back

One step.

alembic downgrade -1

Where am I

And the chain.

alembic current
alembic history

New NOT NULL column

Value for old rows.

sa.Column("priority", sa.Integer(), nullable=False, server_default="0")

Try it yourself

The code does not change. Swap the content string and the program does something else entirely.

See the SQL

“Run alembic upgrade head --sql on a fresh database name. What SQL would Alembic run?”

Check

“Run alembic check after the last upgrade. Then change a model without a migration and run it again.”

Back to the start

“Run alembic downgrade base on a copy of todos.db. What is left?”

An index

“Add index=True to title. What does autogenerate produce?”

What usually goes wrong

Forgetting import sqlmodel in the template

NameError: name 'sqlmodel' is not defined. Add it to script.py.mako once.

A NOT NULL column without server_default

The upgrade failed on a table with rows. The Python default is not in the database.

Running autogenerate without reading it

A rename became drop_column + add_column - all titles would be lost.

Keeping create_all next to Alembic

create_all can make tables Alembic does not know about. Let migrations own the schema.

Forgetting render_as_batch for SQLite

Most column changes need batch mode on SQLite.

Practice

Write these yourself before opening anything. Getting them wrong first is most of how this sticks.

Add a category column (text, at most 30 characters, NOT NULL) with the value "general" for all existing rows. Then write a second migration that sets category to "money" for every to-do whose title contains "rent" - a data migration with op.execute. Check both upgrade and downgrade.

Show hint

server_default="general" in the first migration. In the second: op.execute("UPDATE todo SET category = 'money' WHERE title LIKE '%rent%'").

Key points

A migration changes the database one step, and keeps the data.
Alembic stores the current step in the alembic_version table.
Point env.py at SQLModel.metadata and import your models; use render_as_batch for SQLite.
Add import sqlmodel to script.py.mako.
Autogenerate drafts migrations - always read them.
New NOT NULL columns on existing rows need server_default.
Renames need alter_column(new_column_name=...) by hand.

Quick check before you move on

Why did the first upgrade fail?

The generated file used sqlmodel types without importing sqlmodel - NameError.

Which table did Alembic create next to todo?

alembic_version - it holds the current revision id.

Why did adding priority fail at first?

NOT NULL with no database default - the old rows had no value.

What did autogenerate produce for the rename?

add_column("name") and drop_column("title").

Interview questions

How do you change a production database schema safely?

Versioned migrations reviewed like code, applied by a deploy step; backwards-compatible steps (add nullable or defaulted columns, backfill, then tighten constraints); expand-and-contract for renames; backups and a tested rollback plan; never rely on create_all.

What are the limits of Alembic autogenerate?

It cannot detect renames (it sees drop + add), may miss some type, server default or constraint changes depending on settings, and knows nothing about data migrations - so every generated file must be reviewed and edited.

Quiz

1.
What is the difference between default=0 in the model and server_default="0"?
2.
Why use render_as_batch=True with SQLite?
3.
What did downgrade -1 do to the rows?

Comments

Loading comments...