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 CH_MIGRATIONS_HOME).

In the directory, create migration files, which should be named like this: 1_some_text.sql, 2_other_text.sql, 10_more_test.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.

Migration files contain ClickHouse SQL statements separated by semicolons (;). Semicolons and comment markers inside single-quoted strings, quoted identifiers ("...", `...`), and dollar-quoted strings ($tag$...$tag$ or $$...$$) are preserved. Whitespace inside quoted text is also preserved. In SQL, comments (--, //, # , #!, and nested /* ... */) are removed, and whitespace outside quoted text is collapsed. Inline formatted data is preserved separately as described below.

See SQL execution and settings below for details on settings, supported migration content, and error handling.

If the database provided in the --db option (or in CH_MIGRATIONS_DB) 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 CH_MIGRATIONS_SKIP_DB_CREATION 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 CH_MIGRATIONS_CA_CERT, CH_MIGRATIONS_CERT, and CH_MIGRATIONS_KEY 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
      --db-engine=<value>       ON CLUSTER and/or ENGINE for DB
                                  (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.
      CH_MIGRATIONS_HOST        Clickhouse hostname (--host)
      CH_MIGRATIONS_USER        Username (--user)
      CH_MIGRATIONS_PASSWORD    Password (--password)
      CH_MIGRATIONS_DB          Database name (--db)
      CH_MIGRATIONS_HOME        Migrations' directory (--migrations-home)

      CH_MIGRATIONS_DB_ENGINE   (optional) DB engine (--db-engine)
      CH_MIGRATIONS_TABLE_ENGINE  (optional) Migrations table engine (--table-engine)
      CH_MIGRATIONS_TIMEOUT     (optional) Client request timeout
                                  (--timeout)
      CH_MIGRATIONS_CA_CERT     (optional) CA certificate file path
      CH_MIGRATIONS_CERT        (optional) Client certificate file path
      CH_MIGRATIONS_KEY         (optional) Client key file path
      CH_MIGRATIONS_SKIP_DB_CREATION
                                (optional) Skip database creation,
                                  set to 'true' to enable
                                  (--skip-db-creation)
      CH_MIGRATIONS_SUBSTITUTE_ENV
                                (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

    settings provided as options with TLS certificates
      clickhouse-migrations migrate --host=https://localhost:8443
      --user=default --password='' --db=analytics
      --migrations-home=/app/clickhouse/migrations
      --ca-cert=/app/certs/ca.pem --cert=/app/certs/client.crt --key=/app/certs/client.key

    skip database creation (assumes database already exists)
      clickhouse-migrations migrate --host=http://localhost:8123
      --user=default --password='' --db=analytics
      --migrations-home=/app/clickhouse/migrations --skip-db-creation

Migration file example: (e.g., located at /app/clickhouse/migrations/1_init.sql)

-- an example of migration file 1_init.sql

SET allow_experimental_json_type = 1;

CREATE TABLE IF NOT EXISTS events (
  timestamp DateTime('UTC'),
  session_id UInt64,
  event JSON
)
ENGINE=AggregatingMergeTree
PARTITION BY toYYYYMM(timestamp)
SAMPLE BY session_id
ORDER BY (session_id)
SETTINGS index_granularity = 8192;

Environment variable substitution (optional)

Some migrations need a value that differs per environment, for example: the host of a Postgres instance behind a DICTIONARY, a cluster name, a bucket, and so on.

Enable CH_MIGRATIONS_SUBSTITUTE_ENV=true and ${VAR} placeholders in migration files are replaced with the matching environment variables when the migration is applied. It's off by default, so existing migrations behave exactly as before.

Rules:

• ${NAME} is replaced with the value of the NAME environment variable.
• If NAME is unset, the migration fails - the placeholder is never left behind in the SQL.
• To keep a literal ${NAME} (for example in a comment or a string), escape it as $${NAME}.
• A malformed (${PG-HOST}, ${}) or unterminated (${PG_HOST) placeholder also fails, so nothing is ever silently passed through.

Substitution is textual (like envsubst): it doesn't parse SQL and doesn't escape values, so anything placed inside quotes must be quoting-safe.

A typical use is an idempotent dictionary pointing at a per-environment source:

CREATE OR REPLACE DICTIONARY my_db.dict_offers
(
    `id` UUID,
    `name` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(POSTGRESQL(HOST '${PG_HOST}' PORT ${PG_PORT} USER '${PG_USER}' PASSWORD '${PG_PASSWORD}' DB '${PG_DB}' TABLE 'offers'))
LIFETIME(MIN 0 MAX 300)
LAYOUT(COMPLEX_KEY_HASHED());

Please note: When substitution is enabled, the checksum is taken from the raw file, before substitution. This keeps a migration stable across environments and keeps secrets out of _migrations. Two consequences follow: an already-applied migration is not re-run when a variable changes (ship a new migration instead), and the substituted SQL still reaches ClickHouse, so it may appear in system.query_log or server logs.

SQL execution and settings

Queries execute in file order. Assignment-style SET name = value statements are collected before executing the file: the last assignment to each setting applies to every query in that file, including queries written before the SET. This preserves the package's existing behavior. Settings do not carry over to the next migration file.

Settings are sent with each HTTP request, overriding defaults supplied in the host URL. This works without shared session state when a load balancer routes requests to different servers, and also supports HTTP parameters such as wait_end_of_query.

Use assignment-style SET commands for settings and query parameters. Quoted string values, multiline assignments and compound parameter values are supported; the available setting names and values depend on your ClickHouse version:

SET max_threads = 2,
    log_comment = 'migration; keep this text';
SET wait_end_of_query = 1;
SET session_timezone = 'UTC';
SET param_d = {'10': [11, 12], '13': [14, 15]};
SELECT {d:Map(String, Array(UInt8))};

For a setting specific to one query, use that query's SETTINGS clause where ClickHouse supports it. Commands requiring shared session state, such as SET ROLE followed by another query, are not supported. Use SET session_timezone = 'UTC' to configure the timezone for the file.

Inline INSERT ... FORMAT data is accepted, including INSERT ... SELECT ... FROM input(...) FORMAT .... FORMAT Values uses the same quote handling as INSERT ... VALUES. For other formats, everything after the format name is preserved verbatim up to the next semicolon or the end of the file: tabs, newlines, quotes and comment markers remain data.

Raw formats retain the legacy semicolon delimiter, even inside quoted data. Payloads containing semicolons should use INSERT ... VALUES or be loaded separately. ClickHouse validates the data format; this package does not parse CSV, JSON or other raw formats.

Unterminated SQL quotes/comments and malformed setting assignments cause an error identifying the migration file before any query in that file executes. ClickHouse validates SQL syntax, setting names, setting values and inline data when each query runs. Database setup and earlier files may already have run, and server-side errors can still leave a file partially applied. Where appropriate, use idempotent statements such as CREATE TABLE IF NOT EXISTS ....