Migrations

Since version 0.1.10 clickhouse-sqlalchemy has alembic support. This support allows autogenerate migrations from source code with some limitations. This is the main advantage comparing to other migration tools when you need to write plain migrations by yourself.

Note

It is necessary to notice that ClickHouse doesn’t have transactions. Therefore migration will not rolled back after some command with an error. And schema will remain in partially migrated state.

Autogenerate will detect:

  • Table and materialized view additions, removals.

  • Column additions, removals.

  • Column and table comment additions, removals.

Example project with migrations https://github.com/xzkostyan/clickhouse-sqlalchemy-alembic-example.

Requirements

Minimal versions:

  • ClickHouse server 21.11.11.1

  • clickhouse-sqlalchemy 0.1.10

  • alembic 1.5.x

You can always write you migrations with pure alembic’s op.execute if autogenerate is not possible for your schema objects or your are using clickhouse-sqlalchemy<0.1.10.

Limitations

Common limitations:

  • Engines are not added into op.create_table.

  • Nullable(T) columns generation via Column(..., nullable=True) is not supported.

Currently ATTACH MATERIALIZED VIEW with modified SELECT statement doesn’t work for Atomic engine.

Migration adjusting

You can and should adjust migrations after autogeneration.

Following parameters can be specified for:

  • op.detach_mat_view: if_exists, on_cluster, permanently.

  • op.attach_mat_view: if_not_exists, on_cluster.

  • op.create_mat_view: if_not_exists, on_cluster, populate.

See ClickHouse’s Materialized View documentation.

For op.add_column you can add:

  • AFTER name_after: op.add_column(..., sa.Column(..., clickhouse_after=sa.text('my_column'))).