How to run a database migration when using SQLModel (such as Alembic)? · Issue #85 · fastapi/sqlmodel · GitHub

First Check

I added a very descriptive title to this issue.
I used the GitHub search to find a similar issue and didn't find it.
I searched the SQLModel documentation, with the integrated search.
I already searched in Google "How to X in SQLModel" and didn't find any information.
I already read and followed all the tutorial in the docs and didn't find an answer.
I already checked if it is not related to SQLModel but to Pydantic.
I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

I commit to help with one of those options 👆

Example Code

I created a simple app (based on the documentation using FastAPI and SQLModel)
This can be run with either Docker or a python virtual environment, to speed things up.

https://github.com/joemudryk/learning-sqlmodel

Description

I am trying to:

Create a database from the existing model
Update the model (or uncomment this line )
Run a database migration such as Alembic
Continue to use the application with the updated model and database

Any help would be greatly appreciated, and maybe the instructions can be used to help update the official documentation :)

Operating System

Linux

Operating System Details

Linux Mint 20.1

SQLModel Version

0.0.4

Python Version

3.9.6

Additional Context

I was able to use Alembic for the orm library ormar okay. The instructions for that can be found here:
https://collerek.github.io/ormar/models/migrations/

Maybe that will give some insight for the approach I took, or you will find a solution that I didn't.

Thank you in advanced.

@joemudryk I put together a sample project highlighting how to get SQLModel, FastAPI, and Alembic to work together - https://github.com/testdrivenio/fastapi-sqlmodel-alembic.

@joemudryk I faced similar problem and tried to write down the solution - https://stackoverflow.com/questions/68932099/how-to-get-alembic-to-recognise-sqlmodel-database-model . Hope it helps

Thanks @mjhea0 and @artemkalugin01 for the assistance.
For others that might come across this, this is a good async example.
Though this was how I approached it.

Install and setup

Install and initiallise alembic

pip install alembic
alembic init migrations

Note: Make sure that there are __init__.py files in the root project directory and in the models directory (if modules aren't in your project root directory)

Update the autogenerated alembic files

Changes to alembic.ini

Update the alembic.ini to point to your database driver and location, so the whole file should look like this:

[alembic]
# path to migration scripts
script_location = migrations

prepend_sys_path = .                            # New

# database driver and location
sqlalchemy.url = sqlite:///local-database.db    # Updated

# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

Changes to migrations/script.py.mako

Update the script.py.mako file to include "import sqlmode", so the whole file should look like this:

"""${message}

Revision ID: ${up_revision}
Revises: ${down_revision | comma,n}
Create Date: ${create_date}

"""
from alembic import op
import sqlalchemy as sa
import sqlmodel                                 # New
${imports if imports else ""}

# revision identifiers, used by Alembic.
revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}
branch_labels = ${repr(branch_labels)}
depends_on = ${repr(depends_on)}


def upgrade():
    ${upgrades if upgrades else "pass"}


def downgrade():
    ${downgrades if downgrades else "pass"}

Changes to migrations/env.py

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

from sqlmodel import SQLModel                   # New

config = context.config
fileConfig(config.config_file_name)

# Model / Schema imports
from models.hero import Hero                    # New (be sure to import all models you need migrated)

target_metadata = SQLModel.metadata             # Updated


def run_migrations_offline():
    # etc...

Note: You will need to import models that you want alembic to watch and migrate for.
(If you know a way to automatically detect and add these imports, please let me know. Maybe something like a recursive
import that gets each class in each file in a "models" directory.)

Run migration

Initial migration

Create your first autogenerated migration.
Note: Running alembic as python -m alembic resolves an issue with PYTHONPATH and not having some imports working

python -m alembic revision --autogenerate -m "Initial Migration"

Upgrade the database with the newly created migration script in migrations/versions/

python -m alembic upgrade head

Update a model and the database

Update the model

Example of updating a SQLModel

class HeroBase(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None
    power: Optional[str] = None                # New


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

Note: Be sure that the new class variable is either Optional or has a default value. This is so alembic can back
populate entries in the database if needed.

Autogenerate a new migration

python -m alembic revision --autogenerate -m "Add power to Hero model"

Apply the migration

python -m alembic upgrade head

All done

And Bob's your Aunty. A fairly simplistic way of using SQLModel with alembic :)

@joemudryk I tried exactly what you wrote... With the hero model class too

I got an error ModuleNotFoundError: No module named 'sqlmodel' even when i have it installed on both my general python and that of my virtual enviroment

Directory Structure

.
├── alembic.ini
├── app
│   ├── __init__.py
│   ├── main.py
│   └── __pycache__
│       ├── __init__.cpython-38.pyc
│       └── main.cpython-38.pyc
├── migrations
│   ├── env.py
│   ├── __pycache__
│   │   └── env.cpython-38.pyc
│   ├── README
│   ├── script.py.mako
│   └── versions

Part of my env.py

from sqlmodel import SQLModel

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

from app.main import Hero

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = SQLModel.metadata

Part of script.py.mako

from alembic import op
import sqlalchemy as sa
import sqlmodel
${imports if imports else ""}

Part of alembic.ini

# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8

sqlalchemy.url = postgresql://sheyzi:password@localhost:5432/sqlmodel

Pip Freeze Result

╭─░▒▓ ~/code/sqlmodel-tutorial ▓▒░─────────────────────-─░▒▓ ✔  sqlmodel-tutorial    at 12:50:12 pm ▓▒░─╮
╰─ pip freeze                                                                                                                                                                                ─╯
alembic==1.7.1
black==21.9b0
click==8.0.1
greenlet==1.1.1
Mako==1.1.5
MarkupSafe==2.0.1
mypy-extensions==0.4.3
pathspec==0.9.0
platformdirs==2.3.0
psycopg2==2.9.1
pydantic==1.8.2
regex==2021.8.28
SQLAlchemy==1.4.23
sqlalchemy2-stubs==0.0.2a15
sqlmodel==0.0.4
tomli==1.2.1
typing-extensions==3.10.0.2

I fixed it! i was running

alembic revision --autogenerate -m "Initialize Alembic"
but that didn't work

Anyways i ran python -m alembic revision --autogenerate -m "Initialize Alembic" and it worked

Just a note to anyone reading this - if you're converting from SA models to SQLModel and trying to avoid doing it all at once, you will probably have to merge target_metadata in env.py

from sqlalchemy import MetaData
from sqlalchemy.ext.declarative import declarative_base

...

target_metadata_old_sa = Base.metadata
target_metadata_sqlmodel = SQLModel.metadata

# from https://stackoverflow.com/questions/29954815/merge-multiple-declarative-bases-in-sqlalchemy 
def merge_metadata(*original_metadata) -> MetaData:
    merged = MetaData()

    for original_metadatum in original_metadata:
        for table in original_metadatum.tables.values():
            table.to_metadata(merged)
    
    return merged

target_metadata = merge_metadata(target_metadata_old_sa, target_metadata_sqlmodel)

After I added this, I was able to autogenerate migrations. They're weird migrations because this isn't merged yet #11 but they work as a jumping-off point.

added alembic

add alembic config

Note: You will need to import models that you want alembic to watch and migrate for.
(If you know a way to automatically detect and add these imports, please let me know. Maybe something like a recursive
import that gets each class in each file in a "models" directory.)

@joemudryk

I hope I'm not too late, but there is a trick for that mentioned by @tiangolo in the docs. It isn't fully automated, but at least you don't have to mess with the env.py file anymore.
You can do it by importing all your models in the same file where you create your engine, and import SQLModel in that same file.

# db.py
from sqlmodel import SQLModel, create_engine
from . import models


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url)

Now all you have to do is import SQLModel in env.py from this db.py file, instead of importing it from the library. Doing so, all your model will be already registred.

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

from core.db import SQLModel                   # New

config = context.config
fileConfig(config.config_file_name)

target_metadata = SQLModel.metadata 


def run_migrations_offline():
    # etc...

Thanks for the help here everyone! 👏 🙇

Thanks for reporting back and closing the issue @joemudryk 👍

For those using SQLite, here are the other things I had to do to make this work:

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

from sqlmodel import SQLModel

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata

# Model / Schema imports
from objects import SQLModel
# New (be sure to import all models you need migrated)

###### Dan Added this section
from sqlalchemy import MetaData      

#fixes issue with manipulating contraints in sqlite
SQLModel.metadata.naming_convention = MetaData(naming_convention={
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}).naming_convention

#FIXME change to TRUE if you have problems with alter tables in sqlite
render_as_batch_default = False
###### end

target_metadata = SQLModel.metadata             # Updated

# target_metadata = None

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
        render_as_batch=render_as_batch_default.   #Dan added this
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata, render_as_batch=render_as_batch_default  #Dan added the last parameter
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

@joemudryk I put together a sample project highlighting how to get SQLModel, FastAPI, and Alembic to work together - https://github.com/testdrivenio/fastapi-sqlmodel-alembic.

Hi, guys!
Sorry if this issue is closed.
As @joemudryk said, for me this is the way.
This answer helped me a lot and I found an good tutorial about it.

It's been two years, but there are still no official docs on applying migrations with SQL Model. Will this ever be done?
As well as other topics in the promised "Advanced User Guide" section of the docs.

Although I can get Alembic to create migrations it does not name foreign key constraints even though the naming convention dict is set. This means performing a downgrade will fail. It would be good if someone has a working example.

Wondering the same @ruslaniv...

I am still actively building projects using FastAPI, SQLModel, and Alembic (with postgres and sqlite). @tiangolo If you like I can contribute to the advanced docs on SQLModel with a guide on how to setup and do migrations with Alembic?

(If you know a way to automatically detect and add these imports, please let me know. Maybe something like a recursive
import that gets each class in each file in a "models" directory.)

I came up with a solution that works for my use case. I am using the project structure defined here (https://github.com/zhanymkanov/fastapi-best-practices). TLDR - app logic is in package src with daughter packages that handle logic. The following block of code crawls these packages and looks for all modules name models and imports them. Migrations seems to be running successfully (tested on sqlite)

import src
import pkgutil
import importlib

for _, name, _ in pkgutil.walk_packages(src.__path__, f"{src.__name__}."):
    if name.endswith("models"):
        module = importlib.import_module(name)

The above code only works if you have a suitable project structure. But I'm sure it'd be trivial to adapt it.
If anyone sees any potential flaws with this approach or has some advice, I'd be more than grateful.

Although I can get Alembic to create migrations it does not name foreign key constraints even though the naming convention dict is set. This means performing a downgrade will fail. It would be good if someone has a working example.

+1, I tried setting naming_convension as shown below but not work:

target_metadata = SQLModel.metadata
target_metadata.naming_convention = {
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
    "ix": "ix_%(table_name)s_%(column_0_name)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
}

I found this video on YouTube explaining the whole process very useful and in line with the most upvoted solution here.

FastAPI & Alembic - Database Migrations in FastAPI apps

Although I can get Alembic to create migrations it does not name foreign key constraints even though the naming convention dict is set. This means performing a downgrade will fail. It would be good if someone has a working example.

+1, I tried setting naming_convension as shown below but not work:
target_metadata = SQLModel.metadata
target_metadata.naming_convention = {
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
    "ix": "ix_%(table_name)s_%(column_0_name)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
}

Apparently you have to customize metadata prior to importing your models, i.e., you'd have to move your model imports after the quoted code block:

target_metadata = SQLModel.metadata
target_metadata.naming_convention = {...}
import package.with.models
# or...
from package.with.models import *

Something to keep that in mind before letting the alembic do the --autogenerate for you is:
Alembic will view the status of the database (pointed to by sqlalchemy.url in your alembic.ini file), and compares that against the table metadata. And it will generate the "obvious" migrations based on that comparison.

So make sure the current state of the target database is appropriate.
For example, before initializing the first migration with:
alembic revision --autogenerate -m "Initial Migration"
You probably want to make sure that, the database is actually empty! Otherwise, unexpected things might show in the migration file.

How to run a database migration when using SQLModel (such as Alembic)? · Issue #85 · fastapi/sqlmodel · GitHub

Repository navigation

How to run a database migration when using SQLModel (such as Alembic)? #85

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Install and setup

Install and initiallise alembic

Update the autogenerated alembic files

Changes to alembic.ini

Changes to migrations/script.py.mako

Changes to migrations/env.py

Run migration

Initial migration

Update a model and the database

Update the model

Autogenerate a new migration

Apply the migration

All done

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

How to run a database migration when using SQLModel (such as Alembic)? · Issue #85 · fastapi/sqlmodel · GitHub

Repository navigation

How to run a database migration when using SQLModel (such as Alembic)? #85

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

mjhea0 commented on Sep 8, 2021

artemkalugin01 commented on Sep 9, 2021

joemudryk commented on Sep 10, 2021

Install and setup

Install and initiallise alembic

Update the autogenerated alembic files

Changes to alembic.ini

Changes to migrations/script.py.mako

Changes to migrations/env.py

Run migration

Initial migration

Update a model and the database

Update the model

Autogenerate a new migration

Apply the migration

All done

sheyzi commented on Sep 14, 2021

sheyzi commented on Sep 14, 2021

thebleucheese commented on Dec 12, 2021

abreumatheus commented on Sep 12, 2022

tiangolo commented on Nov 20, 2022

dncastilho commented on Jan 12, 2023

luiz-floresdelima commented on Jul 8, 2023

ruslaniv commented on Apr 5, 2024

robinelvin commented on Apr 6, 2024

0x054 commented on Apr 7, 2024

joemudryk commented on Apr 9, 2024

20-buck-spin commented on May 23, 2024

moon548834 commented on Jul 9, 2024

eulersson commented on Aug 22, 2025

KSmanis commented on Sep 22, 2025

ParsaJR commented on Feb 11, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions