onboarding notes from sqlmodel / alembic

README.md

sqlmodel / alembic onboarding notes

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.

versions

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

misc

# 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

table definition tricks I had to hunt for

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'),
  )

sqlalchemy.url

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

migration hook

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)