Clickhouse database schema migrations

migrate

migrate is simple schema migration tool written in golang. No external dependencies are required (like interpreter, jre), only one platform-specific executable.

migrate supports several databases, including ClickHouse (support was introduced by @kshvakov).

Original project by @mattes is not supported anymore, active fork is here: golang-migrate/migrate

To store information about migrations state migrate creates one additional table in target database, by default that table is called schema_migrations.

Install:

download the migrate executable for your platform and put it to the folder listed in your %PATH.

# on Linux:
wget https://github.com/golang-migrate/migrate/releases/download/v3.2.0/migrate.linux-amd64.tar.gz
tar -xzf migrate.linux-amd64.tar.gz 
mkdir -p ~/bin
mv migrate.linux-amd64 ~/bin/migrate
rm migrate.linux-amd64.tar.gz 

Sample usage:

mkdir migrations
echo 'create table test(id UInt8) Engine = Memory;' > migrations/000001_my_database_init.up.sql
echo 'DROP TABLE test;' > migrations/000001_my_database_init.down.sql

# you can also auto-create file with new migrations with automatic numbering like that:
# > migrate create -dir migrations -seq -digits 6 -ext sql my_database_init
# edit migrations/000001_my_database_init.up.sql & migrations/000001_my_database_init.down.sql

➜ migrate -database 'clickhouse://localhost:9000' -path ./migrations up
1/u my_database_init (6.502974ms)

➜ migrate -database 'clickhouse://localhost:9000' -path ./migrations down
1/d my_database_init (2.164394ms)

# clears the database (use carefully - will not ask any confirmations)
➜ migrate -database 'clickhouse://localhost:9000' -path ./migrations drop

Connection string format

clickhouse://host:port?username=user&password=qwerty&database=clicks

URL Query	Description
x-migrations-table	Name of the migrations table
database	The name of the database to connect to
username	The user to sign in as
password	The user's password
host	The host to connect to.
port	The port to bind to.

Replicated / Distributed / Cluster environments.

By default migrate create table schema_migrations with the following structure

CREATE TABLE schema_migrations (
  version UInt32,
  dirty UInt8,
  sequence UInt64
) ENGINE = TinyLog

That allow to store version of schema locally.

If you need to use migrate in some multiserver environment (replicated / cluster) you should create schema_migrations manually with the same structure and with appropriate Engine (Replicated / Distributed), otherwise other servers will not know the version of the DB schema. As an alternative you can force the current version number on other server manually, like that:

migrate -database 'clickhouse://localhost:9000' -path ./migrations force 123456 # force version 123456

Other

• https://github.com/smi2/phpMigrationsClickhouse

  and also their article (Russian): https://habrahabr.ru/company/smi2/blog/317682/

• Flybase - there is pull request https://github.com/flyway/flyway/pull/1773 and issue https://github.com/flyway/flyway/issues/1772. You can try to use that fork: https://github.com/peturrun/flyway/tree/clickhouse

• more?