Getting up and running with sqlmodel and alembic took more than 10 minutes, and required editing boilerplate, so I wrote these notes to future me.
I did this in december 2022, with library versions:
alembic==1.9.1
asyncpg==0.27.0
SQLAlchemy==1.4.41
sqlmodel==0.0.8
# I used asyncpg, which means database_url has to look like:
postgresql+asyncpg:///...
# and you have to:
pipenv install asyncpg
# use async mode when generating alembic boilerplate
alembic init -t async alembic
json, foreign keys, unique-together
from sqlmodel import SQLModel, Field, UniqueConstraint, Column
from sqlalchemy.dialects.postgresql import JSONB
class Whatever(SQLModel, table=True):
# dicts aren't automatically json
# you have to:
json_col: Optional[dict] = Field(default=None, sa_column=Column(JSONB))
# foreign keys don't detect tables
# i.e. this doesn't work:
# other: OtherTable
# do this instead:
other_id: uuid.UUID = Field(foreign_key='other.id')
__table_args__ = (
# unique index
UniqueConstraint('column1', 'column2'),
)
I don't think you can reference an environment var in alembic.ini
. Instead, in env.py, replace the target_metadata = None
line with:
config.set_main_option('sqlalchemy.url', os.environ['SQLALCHEMY_URL'])
import backend.models # replace with your models location
target_metadata = backend.models.SQLModel.metadata
I wrote a hook to import sqlmodel
in the migration files. Guessing there's a simpler way to do this?
In alembic.ini:
[post_write_hooks]
hooks = import_sqlmodel
# ugh yes type is actually name https://github.com/sqlalchemy/alembic/blob/3b09a89d/alembic/script/write_hooks.py#L43
import_sqlmodel.type = import_sqlmodel
In env.py:
from alembic.script import write_hooks
@write_hooks.register('import_sqlmodel')
def hook_import_sqlmodel(filename, options):
"without this, alembic generates migrations which NameError when they use sqlmodel"
# ugh this is super brittle and annoying
subprocess.run(f"sed -i 's/import sqlalchemy as sa/&, sqlmodel/' {filename}", shell=True)