Clickhouse database schema migrations

Clickhouse database schema migrations

migrate

migrate is simple schema migration tool written in golang, so only one executable needed (there are builds for major platforms). Support several databases including Clickhouse.

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

Install:

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

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
# autocreate file with new migrations: 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

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

➜ 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 all the database (use carefully - will not ask any confirmations)
➜ migrate -database 'clickhouse://localhost:9000' -path ./migrations drop

# migrate -database 'clickhouse://server1:9000?username=bob&password=secret123&database=logs' -path ./migrations 

Other

• https://github.com/smi2/phpMigrationsClickhouse
• 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?