Table of contents
- TL;DR
- The Goal
- Setting Up the Environment
- Installing Alembic and SQLModel
- Creating Models Using SQLModel
- SQL Constraint Naming Convention
- Initialize Alembic Migrations Directory
- Add Database URL to alembic.ini
- Set target metadata
- Configure env.py
- Adding SQLModel Import to script.py.mako
- Create the Initial Migration
- Migrate our Database
- What's next?
TL;DR
Install Alembic and SQLModel.
Create models with
SQLModel.SQLModel
base class.Add naming conventions to
SQLModel.metadata
usingSQLModel.metadata.naming_convention
.Initialize Alembic with
alembic init migrations
.Add your database URL to the
alembic.ini
configuration file.In
migrations/env.py
, settarget_metadata
toSQLModel.metadata
. This should be done after the Model classes have been imported.Add the
render_as_batch=True
parameter tocontext.configure(...)
in the functionrun_migrations_online
of themigrations/env.py
file.Add the
user_module_prefix="sqlmodel.sql.sqltypes."
parameter tocontext.configure(...)
in the functionrun_migrations_online
of themigrations/env.py
file.Add
import sqlmodel.sql.sqltypes
inmigrations/script.py.mako
.Now make changes in your data models and run
alembic revision --autogenerate -m "some change message"
.Upgrade the database using
alembic upgrade head
.
The Goal
We want to create a basic Pet manager app.
Setting Up the Environment
This step is optional.
We use Poetry for package management.
Our project environment looks like this:
sqlmodel-alembic-async/
├── sqlmodel_alembic_async/
│ ├── __init__.py
│ └── models.py
├── poetry.lock
└── pyproject.toml
At the time of writing, the dependencies are:
# pyproject.toml
[tool.poetry.dependencies]
python = "^3.8"
sqlmodel = "^0.0.8"
alembic = "^1.9.2"
Installing Alembic and SQLModel
Install alembic
and SQLModel
with your preferred package manager. I prefer Poetry.
poetry add alembic SQLModel
Make sure you activate the Poetry shell using
poetry shell
before executing any Alembic command.
Creating Models Using SQLModel
We create two entities: User and Pet.
# sqlmodel_alembic_async/models.py
from typing import Optional
from sqlmodel import Field, SQLModel
metadata = SQLModel.metadata
class User(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
class Pet(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
SQL Constraint Naming Convention
This step is optional but recommended.
Consider utilising a naming convention when adding SQL constraints to help prevent migration errors (especially with SQLite).
# sqlmodel_alembic_async/models.py
# ...
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",
}
metadata = SQLModel.metadata
metadata.naming_convention = NAMING_CONVENTION
# ...
Alembic's documentation explains the importance of naming conventions.
Initialize Alembic Migrations Directory
alembic init migrations
# Creating directory ./sqlmodel-alembic-async/migrations ... done
# Creating directory ./sqlmodel-alembic-async/migrations/versions ... done
# Generating ./sqlmodel-alembic-async/migrations/README ... done
# File ./sqlmodel-alembic-async/alembic.ini already exists, skipping
# Generating ./sqlmodel-alembic-async/migrations/env.py ... done
# Generating ./sqlmodel-alembic-async/migrations/script.py.mako ... done
# Please edit configuration/connection/logging settings in './sqlmodel-alembic-async/alembic.ini' before proceeding.
This generates alembic.ini
and migrations
directory. After executing the command, our project directory looks like this:
sqlmodel-alembic/
├── migrations/
│ ├── versions/
│ ├── env.py
│ ├── README
│ └── script.py.mako
├── sqlmodel_alembic/
│ ├── __init__.py
│ └── models.py
├── alembic.ini
├── poetry.lock
└── pyproject.toml
Add Database URL to alembic.ini
# alembic.ini
[alembic]
# ... some other configuration above 👆
sqlalchemy.url = sqlite:///./data.db
# ... some other configuration below 👇
A better way to do this is by getting the database URL from the environment. However, that is out of the scope of this tutorial.
Set target metadata
# migrations/env.py
# ...
import sqlmodel_alembic.models
target_metadata = sqlmodel_alembic.models.metadata
# ...
Note that we use the metadata
variable from the sqlmodel_alembic.models
module itself. Why? We want metadata to store information about our models during runtime.
Configure env.py
Batch Rendering Mode
Miguel Grinberg's blog outlines the importance of batch rendering and why it is NOT a magic bullet.
With SQLite, the
ALTER TABLE
command only supports adding or renaming columns. Any other change to columns or constraints is going to be rejected with an error.
We instruct Alembic to perform batch operations by configuring run_migrations_online()
like this:
# migrations/env.py
# ...
def run_migrations_online() -> None:
# ...
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
+ render_as_batch=True,
)
# ...
You can also include render_as_batch=True
in run_migrations_offline
, however, this has not been tested.
What is the difference between offline and online migration?
Taken from Alembic's documentation:
Generation of migrations as SQL scripts, instead of running them against the database - is also referred to as offline mode.
SQLModel Custom Datatypes
SQLModel uses its custom datatypes and we need to instruct Alembic to include them in migration scripts.
For that, we add the use_module_prefix
argument to context.configure()
in run_migrations_online()
.
# migrations/env.py
# ...
def run_migrations_online() -> None:
# ...
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
render_as_batch=True,
+ user_module_prefix="sqlmodel.sql.sqltypes.",
)
# ...
You can also include user_module_prefix="sqlmodel.sql.sqltypes."
in run_migrations_offline
, however, this has not been tested.
For more information, refer to Alembic's documentation on custom datatypes.
Adding SQLModel Import to script.py.mako
Finally, we include the actual SQLModel datatypes import
in migrations/script.py.mako
.
# migrations/script.py.mako
# ...
from alembic import op
import sqlalchemy as sa
+import sqlmodel.sql.sqltypes
${imports if imports else ""}
# ...
For more information, refer to Alembic's documentation on custom datatypes.
Create the Initial Migration
alembic revision --autogenerate -m "initial"
# INFO [alembic.runtime.migration] Context impl SQLiteImpl.
# INFO [alembic.runtime.migration] Will assume non-transactional DDL.
# INFO [alembic.autogenerate.compare] Detected added table 'pet'
# INFO [alembic.autogenerate.compare] Detected added table 'user'
# Generating ./sqlmodel-alembic-async/migrations/versions/<somehash>_initial.py ... done
The generated migration script should look approximately like this:
# migrations/versions/<somehash>_initial.py
"""initial
Revision ID: <somehash>
Revises:
Create Date: 2023-01-29 19:39:24.556269
"""
from alembic import op
import sqlalchemy as sa
import sqlmodel.sql.sqltypes
# revision identifiers, used by Alembic.
revision = '<somehash>'
down_revision = None
branch_labels = None
depends_on = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('pet',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sqlmodel.sql.sqltypes.AutoString(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sqlmodel.sql.sqltypes.AutoString(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('user')
op.drop_table('pet')
# ### end Alembic commands ###
Migrate our 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 -> <somehash>, initial
And done! You are now harnessing the power of Alembic and SQLModel to manage your database.
What's next?
Next, we are going to use SQLModel with Asyncio.