clickhouse-migrations

ClickHouse Migrations CLI

Install

npm install clickhouse-migrations

Usage

Create a directory, where migrations will be stored. It will be used as the value for the --migrations-home option (or for environment variable CHMIGRATIONSHOME).

In the directory, create migration files, which should be named like this: 1sometext.sql, 2othertext.sql, 10moretest.sql. What's important here is that the migration version number should come first, followed by an underscore (), and then any text can follow. The version number should increase for every next migration. Please note that once a migration file has been applied to the database, it cannot be modified or removed.

For migrations' content should be used correct SQL ClickHouse queries. Multiple queries can be used in a single migration file, and each query should be terminated with a semicolon (;). The queries could be idempotent - for example: CREATE TABLE IF NOT EXISTS table ...; Clickhouse settings, that can be included at the query level, can be added like SET allowexperimentalobjecttype = 1;. For adding comments should be used --, # , #!.

If the database provided in the --db option (or in CHMIGRATIONSDB) doesn't exist, it will be created automatically. To disable this behavior (for example, when the user has no privileges to create databases), use the --skip-db-creation option (or set CHMIGRATIONSSKIPDBCREATION to 'true'); in that case the database must already exist.

For TLS/HTTPS connections, you can provide a custom CA certificate and optional client certificate/key via the --ca-cert, --cert, and --key options (or the CHMIGRATIONSCACERT, CHMIGRATIONSCERT, and CHMIGRATIONSKEY environment variables).

  Usage
    $ clickhouse-migrations migrate <options>

  Required options
      --host=<name>             Clickhouse hostname
                                  (ex. https://clickhouse:8123)
      --user=<name>             Username
      --password=<password>     Password
      --db=<name>               Database name
      --migrations-home=<dir>   Migrations' directory

  Optional options

(default: 'ENGINE=Atomic') --table-engine=<value Engine for the migrations table (default: 'MergeTree') --timeout=<value Client request timeout (milliseconds, default: 30000) --ca-cert=<path CA certificate file path --cert=<path Client certificate file path --key=<path Client key file path --skip-db-creation Skip database creation

Environment variables Instead of options can be used environment variables. CHMIGRATIONSHOST Clickhouse hostname (--host) CHMIGRATIONSUSER Username (--user) CHMIGRATIONSPASSWORD Password (--password) CHMIGRATIONSDB Database name (--db) CHMIGRATIONSHOME Migrations' directory (--migrations-home)

CHMIGRATIONSDBENGINE (optional) DB engine (--db-engine) CHMIGRATIONSTABLEENGINE (optional) Migrations table engine (--table-engine) CHMIGRATIONSTIMEOUT (optional) Client request timeout (--timeout) CHMIGRATIONSCACERT (optional) CA certificate file path CHMIGRATIONSCERT (optional) Client certificate file path CHMIGRATIONSKEY (optional) Client key file path CHMIGRATIONSSKIPDBCREATION (optional) Skip database creation, set to 'true' to enable (--skip-db-creation) CHMIGRATIONSSUBSTITUTEENV (optional) Substitute ${VAR} in migration files from the environment at apply time, set to 'true' to enable

CLI executions examples settings are passed as command-line options clickhouse-migrations migrate --host=http://localhost:8123 --user=default --password='' --db=analytics --migrations-home=/app/clickhouse/migrations

settings provided as options, including timeout and db-engine clickhouse-migrations migrate --host=http://localhost:8123 --user=default --password='' --db=analytics --migrations-home=/app/clickhouse/migrations --timeout=60000 --db-engine="ON CLUSTER default ENGINE=Replicated('{replica}')" --table-engine="ReplicatedMergeTree('/clickhouse/tables/{database}/migrations', '{replica}')"

settings provided as environment variables clickhouse-migrations migrate

settings provided partially through options and environment variables clickhouse-migrations migrate --timeout=60000

clickhouse-migrations

Installation

README.md