.. _clickhouse-migrations:

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.