Migrations

We will use Alembic to handle database schema changes.

SQLModel is compatible with Alembic.

Initial example

We'll continue from another example that has the creation of database and tables, and other essentials features.

👀 Full file example

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


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

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

First step

Add Alembic to your project.

Example using pip.

$ pip install alembic

Installing collected packages: alembic
Successfully installed alembic-1.8.1

Clean your code

We need to clean our step that create the database and tables.

# Code above omitted 👆

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

# Code below omitted 👇

# Code above omitted 👆

def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()

# Code below omitted 👇

👀 Full file example

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


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

engine = create_engine(sqlite_url, echo=True)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

Alembic configuration

In this step we need initialize alembic.

$ alembic init migrations

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

!!! info We can also use alembic init alembic to create alembic folder instead of migrations folder.

Then go to migrations\script.py.mako to add sqlmodel module.

# Code above omitted 👆

from alembic import op
import sqlalchemy as sa
import sqlmodel

# Code below omitted 👇

!!! info In new migrations alembic will add SQLModel automatically.

👀 Full script.py.mako example

"""${message}

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

"""
from alembic import op
import sqlalchemy as sa
import sqlmodel
${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() -> None:
    ${upgrades if upgrades else "pass"}


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

Then go to migrations\env.py to finish the alembic configuration.

• Import your models (in this case Hero) and SQLModel

# Code above omitted 👆

from logging.config import fileConfig  # isort:skip

from main import Hero  # noqa: F401, isort:skip
from sqlmodel import SQLModel  # isort:skip

from sqlalchemy import engine_from_config  # isort:skip

# Code below omitted 👇

Warning

First import your models and then import SQLModel otherwise sqlmodel doesn´t recognize all models.

• Then set your database url

# Code above omitted 👆

config = context.config
config.set_main_option("sqlalchemy.url", "sqlite:///database.db")

# Code below omitted 👇

Tip

This step can be replaced setting the same `sqlalchemy.url` variable in `alembic.ini` file.

• Finally set target_metadata with your SQLModel.metada

# Code above omitted 👆

target_metadata = SQLModel.metadata

# Code below omitted 👇

👀 Full env.py example

from logging.config import fileConfig  # isort:skip

from main import Hero  # noqa: F401, isort:skip
from sqlmodel import SQLModel  # isort:skip

from sqlalchemy import engine_from_config  # isort:skip
from sqlalchemy import pool  # isort:skip

from alembic import context  # isort:skip

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
config.set_main_option("sqlalchemy.url", "sqlite:///database.db")

# 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
target_metadata = SQLModel.metadata

# 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"},
    )

    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)

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


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

Run migrations

In this step we need to generate the initial version of the database.

$ alembic revision --autogenerate -m "init_db"

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'hero'
Generating migrations\versions\34abfb7ac266_init_db.py ...  done

Now in versions folder we have a new file called 34abfb7ac266_init_db.py

Info

This file has a revision id and the message part from our revision command.

"""init_db

Revision ID: 34abfb7ac266
Revises:
Create Date:

"""

from alembic import op  # isort:skip
import sqlalchemy as sa  # isort:skip
import sqlmodel  # (1), # isort:skip


# revision identifiers, used by Alembic.
revision = "34abfb7ac266"
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:  # (2)
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "hero",  # (3)
        sa.Column("id", sa.Integer(), nullable=False),  # (4)
        sa.Column("name", sqlmodel.sql.sqltypes.AutoString(), nullable=False),  # (5)
        sa.Column(
            "secret_name", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),  # (6)
        sa.Column("age", sa.Integer(), nullable=True),  # (7)
        sa.PrimaryKeyConstraint("id"),  # (8)
    )
    # ### end Alembic commands ###


def downgrade() -> None:  # (9)
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("hero")  # (10)
    # ### end Alembic commands ###

1. SQLmodel import
2. Upgrade function to add the new schema in the database
3. Create a new table named hero
4. id field
5. name field
6. secret_name field
7. age field
8. Setting id field as primary key
9. Downgrade function to rollback our changes
10. Delete the table named hero

Success

At this moment we have all the files to create our new database model.

Initialize the database:

$ 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  -> 34abfb7ac266, init_db

Now we have two tables:

• alembic_version: with the version_num asociate with the revision id
• hero: the new table from our model

Hero table is empty.

Then run main.py script

$ python main.py

INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age) VALUES (?, ?, ?)
INFO sqlalchemy.engine.Engine [generated in 0.00035s] ('Deadpond', 'Dive Wilson', None)
INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age) VALUES (?, ?, ?)
INFO sqlalchemy.engine.Engine [cached since 0.002439s ago] ('Spider-Boy', 'Pedro Parqueador', None)
INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age) VALUES (?, ?, ?)
INFO sqlalchemy.engine.Engine [cached since 0.003134s ago] ('Rusty-Man', 'Tommy Sharp', 48)
INFO sqlalchemy.engine.Engine COMMIT
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age
FROM hero
INFO sqlalchemy.engine.Engine [generated in 0.00038s] ()
age=None id=1 name='Deadpond' secret_name='Dive Wilson'
age=None id=2 name='Spider-Boy' secret_name='Pedro Parqueador'
age=48 id=3 name='Rusty-Man' secret_name='Tommy Sharp'
INFO sqlalchemy.engine.Engine ROLLBACK

Now the hero table has new rows:

Next steps

If we edit our model changing the database schema we can run again alembic to generate a new revision.

Example: adding a new field named power

# Code above omitted 👆

    age: Optional[int] = None
    power: int = None

# Code below omitted 👇

👀 Full file example

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    power: int = None


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

engine = create_engine(sqlite_url, echo=True)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

$ alembic revision --autogenerate -m "new field power"

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'hero.power'
Generating migrations\versions\b39b8d3c77f0_new_field_power.py ...  done

The new file b39b8d3c77f0_new_field_power.py:

"""new field power

Revision ID: b39b8d3c77f0
Revises: 357d6ebcfadf
Create Date:

"""

from alembic import op  # isort:skip
import sqlalchemy as sa  # isort:skip
import sqlmodel  # noqa: F401, isort:skip


# revision identifiers, used by Alembic.
revision = "b39b8d3c77f0"  # (1)
down_revision = "357d6ebcfadf"  # (2)
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("hero", sa.Column("power", sa.Integer(), nullable=True))  # (3)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column("hero", "power")  # (4)
    # ### end Alembic commands ###

1. New revision id
2. Previous revision id, if downgrade go to this revision id
3. power new field
4. Drop column if downgrade

Note

Run `alembic upgrade head` to add the new field named power

$ 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 357d6ebcfadf -> b39b8d3c77f0, new field power

Note

After you can downgrade the database to the previous version, run `alembic downgrade -1`

$ 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 b39b8d3c77f0 -> 357d6ebcfadf, new field power

Success

Migrations complete!!! Try adding new tables and relationship.