Alembic

Todo

move the discussion here into an explanatory article to keep this guide short and simple.

Todo

add a tutorial in which we create some example revisions

Pycroft uses Alembic to manage changes to its database schema. On startup Pycroft invokes Alembic to ensure that the database schema is up-to-date. Should Alembic detect database migrations that are not yet applied to the database, it will apply them automatically.

To get familiar with Alembic it is recommended to read the official tutorial.

Looking up the last migrations

docker compose run --rm dev-app alembic history

Creating a database migration

Migrations are python modules stored under pycroft/model/alembic/versions/.

A new migration can be created by running:

docker compose run --rm dev-app alembic revision -m "add test table"

Alembic also has the really convenient feature to autogenerate migrations, by comparing the current status of the database against the table metadata of the application.

docker compose run --rm dev-app alembic revision --autogenerate -m "add complex test table"

Printing various create/drop statements

The autogeneration does not know about trigger functions, view definitons or the like. For this, you can pop up a python shell and compile the statements yourself. This way, you can just copy-and-paste them into op.execute() commands in the autogenerated schema upgrade.

import pycroft.model as m
from sqlalchemy.dialects import postgresql

print(
    m.ddl.CreateFunction(m.address.address_remove_orphans).compile(
        dialect=postgresql.dialect()
    )
)
# if the statement itself has no variable like `address_remove_orphans`,
# you can try to extract it from the `DDLManager` instance:
create_stmt, drop_stmt = [
    (c, d)
    for _, c, d in m.user.manager.objects
    if isinstance(c, m.ddl.CreateTrigger) and c.trigger.name == "TRIGGER_NAME_HERE"
]
print(create_stmt.compile(dialect=postgresql.dialect()))
print(drop_stmt.compile(dialect=postgresql.dialect()))