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.

For clickhouse it creates one additional table in target database to store last migration. By default that table is called schema_migrations.

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 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 

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.

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?