Schema Migrations using Alembic

SQLModel integrates with Alembic to handle schema migrations. Alembic is a lightweight database migration tool for usage with SQLAlchemy. Since SQLModel is built on top of SQLAlchemy, it's easy to use Alembic with SQLModel.

Installation

To use Alembic with SQLModel, first install it:

$ pip install alembic
---> 100%
Successfully installed alembic

Then, initialize Alembic in your project directory:

alembic init migrations

This will create a directory named migrations and a configuration file named alembic.ini.

Info

migrations is the directory where Alembic will store the migration scripts. You can choose any other name for this directory, but migrations is a common convention.

Integration

By making class Table(SQLModel, table=true), you can add tables' information to SQLModel(SQLAlchemy) Metadata.

Info

Metadata is a container object that keeps together many different features of a database. You can access Working with Database Metadata for more information.

Import SQLModel on ./migrations/script.py.mako and add the following code:

"""${message}

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

"""
from typing import Sequence, Union

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

# revision identifiers, used by Alembic.
revision: str = ${repr(up_revision)}
down_revision: Union[str, None] = ${repr(down_revision)}

# More code here later 👇

👀 Full file preview

"""${message}

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

"""
from typing import Sequence, Union

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

# revision identifiers, used by Alembic.
revision: str = ${repr(up_revision)}
down_revision: Union[str, None] = ${repr(down_revision)}
branch_labels: Union[str, Sequence[str], None] = ${repr(branch_labels)}
depends_on: Union[str, Sequence[str], None] = ${repr(depends_on)}


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


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

Next, load your models and set the target metadata on ./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

from app.models import *

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

(...)

Lastly, set the database connection string in ./alembic.ini.

# around line 63
sqlalchemy.url = driver://user:pass@localhost/dbname

Revise and Upgrade

After setting up Alembic, you can create a new revision:

alembic revision --autogenerate -m "create table"

This will create a new revision file in ./migrations/versions/.

To apply the new revision and update the database schema, run:

alembic upgrade head

Tip

Remember to run alembic upgrade head to update the remote database's schema.