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

[joemudryk]

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:

1. Create a database from the existing model
2. Update the model (or uncomment this line )
3. Run a database migration such as Alembic
4. 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.

[mjhea0]

@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.

[artemkalugin01]

@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

[joemudryk]

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 :)

[sheyzi]

@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

[sheyzi]

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

[thebleucheese]

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.

[abreumatheus]

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...