7. Alembic migrations#
Status#
Accepted
Context#
Alembic is a migration tool for SQLAlchemy. It is used to manage the Clickhouse database schema for the Aspects project. Alembic creates a migrations table in the database to keep track of the migrations that have been applied.
Alembic has support for:
Branching migrations.
Multiple databases.
Rollback migrations
Autogenerated migrations.
Multiple migration directories.
For more information about Alembic, see https://alembic.sqlalchemy.org/en/latest/
Decisions#
There should be a single initial SQL executed at initialization time. This will create the databases, users and permissions, and any other necessary setup before running migrations.
Migratiosn are separated by service (e.g. xAPI, event sink, vector, etc). Each service has its own migration directory.
Migrations are executed with RAW SQL using the
op.execute
method. This allows us to use ClickHouse-specific SQL syntax.Migrations should always define a downgrade step, even if it is a no-op.
Migrations are generated using a Sequential ID. This allows us to easily identify the order in which migrations were created and executed.
Migrations cannot be modified once they are released to production. If a migration needs to be modified, a new migration should be created with a new sequential ID.
The alembic migrations table is highly coupled with the xAPI database, for this reason the migrations table is stored in the xAPI database.
Consequences#
Users should not run migrations manually. They should be run automatically by Tutor when the project is initialized and at upgrade time.
Rejected Alternatives#
Clickhouse SQL Alchemy Models
Model allows us to automagically create tables and columns in Clickhouse. However, it is not well supported and we didn’t find any benefits over raw SQL migrations.
Django Clickhouse Models
Even when this would be created in a separated Open edX plugin, we didn’t wanted to open the possibility to couple Open edX and Clickhouse. We want to keep Clickhouse as a separated service.