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 viaColumn(..., 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')))
.