clickhouse-migrations

Version control for your ClickHouse database schema.

Run migrations from the command line or embed them in your application. Track what's applied, verify integrity, deploy consistently across environments. Built for production use with clustering, TLS, and structured logging.

Forked from VVVi/clickhouse-migrations.

What's New in 2.0.0

Version 2.0.0 brings significant improvements and breaking changes:

• Package Rename: Now published as @volkagames/clickhouse-migrations (scoped package)
• Modern Build System: Full ESM/CJS dual-package support with Rollup
• Updated Output: Build artifacts now in dist/ directory (was lib/)
• Active Maintenance: Fork with ongoing development and governance
• Enhanced Tooling: Husky git hooks, VSCode workspace config, Biome formatting

Migrating from 1.x

If you're upgrading from version 1.x or the original package:

# Uninstall old package
npm uninstall clickhouse-migrations

# Install new scoped package
npm install @volkagames/clickhouse-migrations

Update your imports:

// Old (1.x)
import { runMigration } from 'clickhouse-migrations';

// New (2.0.0)
import { runMigration } from '@volkagames/clickhouse-migrations';

All other APIs remain backward compatible. See the CHANGELOG for complete details.

Features

• Production-Ready Defaults - Configured for clustered ClickHouse with replicated engines out of the box
• Sequential Migration Management - Apply migrations in order with version tracking
• Checksum Verification - Detect modified migrations to prevent inconsistencies
• Security First - Automatic password sanitization in error messages
• Structured Logging - JSON output with severity levels for production monitoring
• TLS/HTTPS Support - Secure connections with custom certificates
• Clustered ClickHouse - Built-in support for ON CLUSTER and replicated tables
• Standalone Mode - Easy configuration for local development and testing
• Flexible Configuration - CLI options or environment variables
• SQL Comment Support - Comprehensive comment parsing (PostgreSQL/ClickHouse compatible)
• Zero Dependencies - Minimal footprint with only @clickhouse/client and commander

Table of Contents

• What's New in 2.0.0
• Installation
• Quick Start
• Migration File Format
• Usage Examples
• CLI Reference
• Cluster vs Standalone Mode
• Programmatic Usage
• Development
• Best Practices
• Philosophy: Forward-Only Migrations
• Security
• Troubleshooting
• Contributing

Installation

npm install @volkagames/clickhouse-migrations

Or install globally:

npm install -g @volkagames/clickhouse-migrations

Quick Start

1. Create a migrations directory

mkdir -p ./migrations

2. Create your first migration

Create ./migrations/1_init.sql:

-- Initial schema setup
CREATE TABLE IF NOT EXISTS users (
  id UInt64,
  email String,
  created_at DateTime DEFAULT now()
)
ENGINE = MergeTree()
ORDER BY (id);

CREATE TABLE IF NOT EXISTS events (
  user_id UInt64,
  event_type String,
  timestamp DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, timestamp);

3. Run migrations

# Minimal example (uses ClickHouse default authentication)
clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations

# Or with explicit credentials
clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --user=default \
  --password='' \
  --db=myapp \
  --migrations-home=./migrations

Migration File Format

Naming Convention

Migration files must follow this pattern: {version}_{description}.sql

• {version} - Sequential integer (e.g., 1, 2, 3, 10, 100)
• _ - Underscore separator (required)
• {description} - Any descriptive text (alphanumeric, hyphens, underscores)

Valid examples:

• 1_init.sql
• 2_add_users_table.sql
• 10_create_materialized_views.sql
• 001_initial_schema.sql (leading zeros are OK)

Invalid examples:

• init.sql (missing version)
• v1_init.sql (version must be numeric)
• 1-init.sql (wrong separator, use underscore)

SQL Syntax

Multiple queries can be included in a single migration file. Each query must be terminated with a semicolon (;).

-- Multiple queries example
CREATE TABLE table1 (...);
CREATE TABLE table2 (...);
INSERT INTO table1 VALUES (...);

ClickHouse Settings

You can include ClickHouse settings at the query level:

SET allow_experimental_json_type = 1;
SET allow_experimental_object_type = 1;

CREATE TABLE events (
  id UInt64,
  data JSON
) ENGINE = MergeTree() ORDER BY id;

Supported Comment Styles

The parser supports comprehensive SQL comment syntax:

Single-line comments:

-- Standard SQL comment
# Shell-style comment (must be at line start)
#! Shebang comment

SELECT * FROM users;  -- Inline comment after code

Block comments:

/* Single-line block comment */

/*
 * Multi-line block comment
 * Can span multiple lines
 */

SELECT /* inline block */ * FROM users;

String literal protection:

-- Comments inside strings are preserved
SELECT '-- this is NOT a comment' AS text;
SELECT '/* also NOT a comment */' AS text;
SELECT 'it''s ok' AS escaped_quote;  -- Doubled quotes work

Usage Examples

Basic Local Development

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --user=default \
  --password='' \
  --db=analytics \
  --migrations-home=./db/migrations

Check Migration Status

View which migrations have been applied and which are pending:

clickhouse-migrations status \
  --host=http://localhost:8123 \
  --user=default \
  --password='' \
  --db=analytics \
  --migrations-home=./db/migrations

Example output:

clickhouse-migrations : Migration Status: 3 applied, 2 pending

✓ [1] 1_init.sql - applied at 2025-01-20 10:30:45
✓ [2] 2_add_users_table.sql - applied at 2025-01-20 10:30:46
✓ [3] 3_add_indexes.sql - applied at 2025-01-20 10:30:47
○ [4] 4_add_events_table.sql - pending
○ [5] 5_add_materialized_views.sql - pending

Production with Environment Variables

Create a .env file:

CH_MIGRATIONS_HOST=https://clickhouse.prod.example.com:8443
CH_MIGRATIONS_USER=migration_user
CH_MIGRATIONS_PASSWORD=secure_password_here
CH_MIGRATIONS_DB=production_db
CH_MIGRATIONS_HOME=/app/migrations
CH_MIGRATIONS_TIMEOUT=60000

Then run:

clickhouse-migrations migrate

Using DSN (Data Source Name)

You can use a single DSN string to specify connection parameters:

clickhouse-migrations migrate \
  --dsn="clickhouse://user:password@localhost:8123/mydb" \
  --migrations-home=./migrations

DSN format:

clickhouse://[user[:password]@]host[:port][/database][?setting1=value1&setting2=value2]

Query parameters in the DSN are passed as ClickHouse settings (equivalent to SET statements). This is useful for applying global settings to all migrations.

You can also use http:// or https:// schemes directly:

clickhouse-migrations migrate \
  --dsn="https://user:[email protected]:8443/production" \
  --migrations-home=./migrations

Environment variable:

CH_MIGRATIONS_DSN=clickhouse://user:password@localhost:8123/mydb
CH_MIGRATIONS_HOME=/app/migrations

IMPORTANT: DSN and individual parameters cannot be mixed:

You must use either DSN or individual connection parameters, but not both. Mixing them will result in an error:

# This will throw an error:
clickhouse-migrations migrate \
  --dsn="clickhouse://user:password@localhost:8123/dev_db" \
  --db=production_db \
  --migrations-home=./migrations
# Error: Configuration conflict: provide either --dsn OR separate parameters (--host, --user, --password, --db), but not both

Choose one approach:

• Use DSN alone for all connection parameters
• Use individual flags (--host, --user, --password, --db) alone
• Do not combine them

Using ClickHouse settings in DSN:

You can pass ClickHouse settings via query parameters in the DSN:

# Enable experimental features for all migrations
clickhouse-migrations migrate \
  --dsn="clickhouse://user:password@localhost:8123/mydb?allow_experimental_json_type=1&allow_experimental_object_type=1" \
  --migrations-home=./migrations

# Increase memory limit for large data migrations
clickhouse-migrations migrate \
  --dsn="clickhouse://user:password@localhost:8123/mydb?max_memory_usage=10000000000" \
  --migrations-home=./migrations

Priority of settings:

• Settings in individual migration files (via SET statements) override DSN settings
• DSN settings apply to all migrations globally
• This allows you to set defaults via DSN and override them per-migration when needed

Clustered ClickHouse Setup

For replicated environments:

clickhouse-migrations migrate \
  --host=http://clickhouse-node1:8123 \
  --user=admin \
  --password='cluster_password' \
  --db=distributed_db \
  --migrations-home=./migrations \
  --db-engine="ON CLUSTER my_cluster ENGINE=Replicated('/clickhouse/databases/{database}', '{shard}', '{replica}')" \
  --table-engine="ReplicatedMergeTree('/clickhouse/tables/{database}/{table}', '{replica}')"

Example clustered migration file 3_distributed_table.sql:

-- Create replicated table across cluster
CREATE TABLE IF NOT EXISTS events ON CLUSTER my_cluster (
  event_id UInt64,
  user_id UInt64,
  event_type String,
  timestamp DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/events', '{replica}')
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, timestamp);

-- Create distributed table
CREATE TABLE IF NOT EXISTS events_distributed ON CLUSTER my_cluster AS events
ENGINE = Distributed(my_cluster, currentDatabase(), events, rand());

TLS/HTTPS Connections

For secure connections with custom certificates:

clickhouse-migrations migrate \
  --host=https://secure-clickhouse.example.com:8443 \
  --user=secure_user \
  --password='secure_password' \
  --db=secure_db \
  --migrations-home=./migrations \
  --ca-cert=./certs/ca.pem \
  --cert=./certs/client.crt \
  --key=./certs/client.key \
  --timeout=60000

Allow Divergent Migrations (Development Only)

Warning: Only use in development environments!

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --user=default \
  --password='' \
  --db=dev_db \
  --migrations-home=./migrations \
  --abort-divergent=false

This allows you to modify already-applied migrations during development.

Disable Auto Database Creation

For users without CREATE DATABASE permissions:

clickhouse-migrations migrate \
  --host=http://clickhouse.example.com:8123 \
  --user=limited_user \
  --password='user_password' \
  --db=existing_db \
  --migrations-home=./migrations \
  --create-database=false

CLI Reference

Commands

migrate

Apply pending migrations.

clickhouse-migrations migrate [options]

status

Show the current migration status (which migrations are applied, which are pending).

clickhouse-migrations status [options]

Connection Options

Connection parameters can be specified via DSN or individual options:

| Option | Environment Variable | Required | Default | Description | Example | | ------------------- | ------------------------ | -------- | ---------------- | ------------------------------------------------------------------------------------------------------------------------- | ------------------------------------- | | --dsn | CH_MIGRATIONS_DSN | No* | - | Connection DSN (cannot be combined with individual connection parameters) | clickhouse://user:pass@host:8123/db | | --host | CH_MIGRATIONS_HOST | Yes* | - | ClickHouse server URL | http://localhost:8123 | | --user | CH_MIGRATIONS_USER | No | (none) | Username (uses ClickHouse defaults) | default | | --password | CH_MIGRATIONS_PASSWORD | No | (none) | Password (uses ClickHouse defaults) | mypassword | | --db | CH_MIGRATIONS_DB | No | (server default) | Database name (server uses default if not specified, see HTTP interface) | analytics | | --migrations-home | CH_MIGRATIONS_HOME | Yes | - | Migrations directory | ./migrations |

Notes:

• Connection Flexibility: You must provide either --dsn alone or separate parameters (--host, --user, --password, --db) alone. You cannot mix both - doing so will result in a configuration error.
• If --user and --password are not provided, ClickHouse will use its default authentication mechanism (typically the default user with no password for local connections).
• If --db is not specified, the ClickHouse server will automatically use the default database.

Additional Options

For migrate command

| Option | Environment Variable | Default | Description | | ------------------------ | ------------------------------- | ----------------------- | ---------------------------------- | | --db-engine | CH_MIGRATIONS_DB_ENGINE | ON CLUSTER... Replicated | Database engine clause (see Cluster Mode) | | --table-engine | CH_MIGRATIONS_TABLE_ENGINE | ReplicatedMergeTree | Migration table engine | | --migration-table-name | CH_MIGRATIONS_TABLE_NAME | _migrations | Name for migrations tracking table | | --timeout | CH_MIGRATIONS_TIMEOUT | 30000 | Request timeout (ms) | | --ca-cert | CH_MIGRATIONS_CA_CERT | - | CA certificate path | | --cert | CH_MIGRATIONS_CERT | - | Client certificate path | | --key | CH_MIGRATIONS_KEY | - | Client key path | | --abort-divergent | CH_MIGRATIONS_ABORT_DIVERGENT | true | Abort on checksum mismatch | | --create-database | CH_MIGRATIONS_CREATE_DATABASE | true | Auto-create database | | --log-format | CH_MIGRATIONS_LOG_FORMAT | json | Log output format | | --log-level | CH_MIGRATIONS_LOG_LEVEL | info | Minimum log level | | --log-prefix | CH_MIGRATIONS_LOG_PREFIX | clickhouse-migrations | Log component/prefix name |

For status command

| Option | Environment Variable | Default | Description | | ------------------------ | ---------------------------- | ----------------------- | ---------------------------------- | | --table-engine | CH_MIGRATIONS_TABLE_ENGINE | ReplicatedMergeTree | Migration table engine | | --migration-table-name | CH_MIGRATIONS_TABLE_NAME | _migrations | Name for migrations tracking table | | --timeout | CH_MIGRATIONS_TIMEOUT | 30000 | Request timeout (ms) | | --ca-cert | CH_MIGRATIONS_CA_CERT | - | CA certificate path | | --cert | CH_MIGRATIONS_CERT | - | Client certificate path | | --key | CH_MIGRATIONS_KEY | - | Client key path | | --log-format | CH_MIGRATIONS_LOG_FORMAT | json | Log output format | | --log-level | CH_MIGRATIONS_LOG_LEVEL | info | Minimum log level | | --log-prefix | CH_MIGRATIONS_LOG_PREFIX | clickhouse-migrations | Log component/prefix name |

Logging Options

The tool supports both human-readable console output and structured JSON logging for production environments.

Log Format

Control the output format with --log-format:

JSON format (default) - Structured logs with severity field for production:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations

Console format - Human-readable colored output for development:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations \
  --log-format=pretty

JSON output example:

{"severity":"INFO","message":"The migration(s) 1_init.sql was successfully applied!","timestamp":"2025-01-23T12:34:56.789Z","component":"clickhouse-migrations"}
{"severity":"ERROR","message":"Connection failed","timestamp":"2025-01-23T12:34:57.123Z","component":"clickhouse-migrations","details":"Timeout after 5000ms"}

The JSON format includes:

• severity - Log level (DEFAULT, DEBUG, INFO, NOTICE, WARNING, ERROR, CRITICAL, ALERT, EMERGENCY)
• message - Log message
• timestamp - ISO 8601 timestamp
• component - Component name (default: "clickhouse-migrations")
• details - Optional error details (for errors)

Log Level

Filter logs by minimum severity with --log-level:

| Level | Description | Shows | | ------- | -------------------------------------------- | ----------------------------- | | debug | All log messages including debug information | debug, info, warnings, errors | | info | Informational messages and above (default) | info, warnings, errors | | warn | Warnings and errors only | warnings, errors | | error | Errors only | errors only |

Examples:

Show only errors:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations \
  --log-level=error

Debug mode with JSON output:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations \
  --log-format=json \
  --log-level=debug

Quiet mode (errors only) for CI/CD:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations \
  --log-level=error

Using environment variables:

CH_MIGRATIONS_LOG_FORMAT=json
CH_MIGRATIONS_LOG_LEVEL=info

Log Prefix (Component Name)

Customize the component name in logs with --log-prefix:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations \
  --log-format=json \
  --log-prefix=my-app

JSON output with custom prefix:

{"severity":"INFO","message":"Migration applied","timestamp":"2025-01-23T12:34:56.789Z","component":"my-app"}

This is useful for:

• Multi-tenant deployments - Identify which service is running migrations
• Log aggregation - Filter logs by component in centralized logging systems
• Monitoring - Track migrations per application in dashboards

Using environment variable:

CH_MIGRATIONS_LOG_PREFIX=my-application

Exit Codes

• 0 - Success
• 1 - Error occurred (check error message)

Cluster vs Standalone Mode

Production-First Philosophy

This tool follows a production-first design philosophy. We believe that software should be written and configured for production environments from day one, not retrofitted later.

Why production defaults?

• Real environments come first — Production is where your code runs 99% of its lifetime. Local development is just a stepping stone.
• No surprises in production — What works in development works in production. No "it worked on my machine" issues.
• Cluster-ready from the start — ClickHouse shines in clustered deployments. Start with the right architecture.
• Structured logging by default — JSON logs integrate with monitoring systems. Human-readable output is opt-in for development.

Local development should adapt to production requirements, not the other way around.

Default Mode: Cluster (Production)

Out of the box, the tool uses:

• Database engine: ON CLUSTER '{cluster}' ENGINE = Replicated('/clickhouse/{installation}/{cluster}/databases/{database}', '{shard}', '{replica}')
• Table engine: ReplicatedMergeTree
• Log format: json (structured logging for production monitoring)

These defaults are optimized for:

• Multi-node ClickHouse clusters
• High availability setups
• Production environments with monitoring and log aggregation

Standalone Mode (Development/Testing)

For local development, testing, or single-node installations, override the defaults with standalone engines:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --db=myapp \
  --migrations-home=./migrations \
  --db-engine="ENGINE=Atomic" \
  --table-engine="MergeTree" \
  --log-format=pretty

Or via environment variables:

CH_MIGRATIONS_HOST=http://localhost:8123
CH_MIGRATIONS_DB=myapp
CH_MIGRATIONS_HOME=./migrations
CH_MIGRATIONS_DB_ENGINE=ENGINE=Atomic
CH_MIGRATIONS_TABLE_ENGINE=MergeTree
CH_MIGRATIONS_LOG_FORMAT=pretty

When to Use Each Mode

| Mode | Use Case | Engines | |------|----------|---------| | Cluster (default) | Production, staging, multi-node clusters | ReplicatedMergeTree, ON CLUSTER Replicated | | Standalone | Local development, unit tests, CI/CD, single-node | MergeTree, ENGINE=Atomic |

Migration File Compatibility

Your migration SQL files work in both modes. The engine settings only affect:

1. How the database is created (--db-engine)
2. How the _migrations tracking table is created (--table-engine)

Your actual table definitions in migration files should specify their own engines as needed for your use case.

Programmatic Usage

You can use clickhouse-migrations as a library in your Node.js application:

import { runMigration, createLogger } from '@volkagames/clickhouse-migrations';

async function applyMigrations() {
  const logger = createLogger();

  try {
    // Minimal configuration (uses ClickHouse defaults for authentication)
    await runMigration({
      host: 'http://localhost:8123',
      migrationsHome: './migrations',
      logger,
    });
    console.log('Migrations applied successfully!');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  }
}

// With explicit credentials and standalone mode (for local development)
async function applyMigrationsStandalone() {
  // Create logger with pretty output for development
  const logger = createLogger({
    format: 'pretty',   // Human-readable output (default is 'json')
    level: 'info',      // 'trace' | 'debug' | 'info' | 'warn' | 'error' | 'fatal'
    name: 'my-app',     // custom prefix
  });

  try {
    await runMigration({
      host: 'http://localhost:8123',
      username: 'default',          // Optional: uses ClickHouse server defaults
      password: 'mypassword',        // Optional: uses ClickHouse server defaults
      dbName: 'myapp',               // Optional: server uses 'default' database if not provided
      migrationsHome: './migrations',
      logger,
      // Standalone mode engines (for local development/testing)
      dbEngine: 'ENGINE=Atomic',
      tableEngine: 'MergeTree',
      // Optional parameters
      timeout: '30000',
      migrationTableName: '_migrations',  // Optional: custom migration table name
      abortDivergent: true,
      createDatabase: true,
    });
    console.log('Migrations applied successfully!');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  }
}

// Using DSN
async function applyMigrationsWithDSN() {
  const logger = createLogger();

  try {
    await runMigration({
      dsn: 'clickhouse://user:password@localhost:8123/myapp',
      migrationsHome: './migrations',
      logger,
    });
    console.log('Migrations applied successfully!');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  }
}

applyMigrations();

// IMPORTANT: Do NOT mix DSN with individual connection parameters
// This will throw an error:
async function invalidConfiguration() {
  const logger = createLogger();

  try {
    await runMigration({
      dsn: 'clickhouse://user:password@localhost:8123/myapp',
      host: 'http://localhost:8123', // ERROR: Cannot use both DSN and separate parameters
      migrationsHome: './migrations',
      logger,
    });
  } catch (error) {
    console.error('Configuration error:', error);
    // Error: Configuration conflict: provide either --dsn OR separate parameters (--host, --user, --password, --db), but not both
  }
}

TypeScript Types

import type { MigrationRunConfig } from '@volkagames/clickhouse-migrations';
import { createLogger } from '@volkagames/clickhouse-migrations';

// Production configuration (uses cluster defaults)
const configProduction: MigrationRunConfig = {
  // Required
  host: 'https://clickhouse.prod.example.com:8443',
  migrationsHome: './migrations',
  logger: createLogger(),  // JSON format by default

  // Optional connection
  username: 'migration_user',
  password: 'secure_password',
  dbName: 'production_db',

  // Uses cluster defaults: ReplicatedMergeTree, ON CLUSTER Replicated
  // No need to specify dbEngine or tableEngine

  // Optional settings
  timeout: '60000',
  migrationTableName: '_migrations',
  abortDivergent: true,
  createDatabase: true,

  // TLS for production
  caCert: './certs/ca.pem',
  cert: './certs/client.crt',
  key: './certs/client.key',
};

// Standalone configuration (for local development/testing)
const configStandalone: MigrationRunConfig = {
  // Required
  host: 'http://localhost:8123',
  migrationsHome: './migrations',
  logger: createLogger({ format: 'pretty' }),  // Human-readable for development

  // Optional connection
  username: 'default',
  password: '',
  dbName: 'dev_db',

  // Standalone mode engines (override cluster defaults)
  dbEngine: 'ENGINE=Atomic',
  tableEngine: 'MergeTree',

  // Optional settings
  timeout: '30000',
  abortDivergent: true,
  createDatabase: true,
};

// Configuration using DSN
const configDSN: MigrationRunConfig = {
  // Required
  dsn: 'clickhouse://user:pass@localhost:8123/db',
  migrationsHome: './migrations',
  logger: createLogger(),

  // Standalone mode (optional - uses cluster defaults if not specified)
  dbEngine: 'ENGINE=Atomic',
  tableEngine: 'MergeTree',

  // Optional settings
  timeout: '30000',
  abortDivergent: true,
  createDatabase: true,
};

// IMPORTANT: Do NOT mix DSN with individual connection parameters
// This configuration is INVALID and will throw an error:
const invalidConfig: MigrationRunConfig = {
  dsn: 'clickhouse://user:pass@localhost:8123/db',
  host: 'http://localhost:8123', // ERROR: Cannot use both DSN and separate parameters
  migrationsHome: './migrations',
  logger: createLogger(),
};

Development

This project uses modern tooling and follows industry best practices for TypeScript development.

Why pnpm?

This project uses pnpm as the package manager. We chose pnpm because it's widely adopted, has fewer bugs than alternatives, and avoids exotic behavior - it just works reliably.

Prerequisites

• Node.js >= 20
• pnpm >= 10 (Install pnpm)

Setup

Clone the repository and install dependencies:

git clone https://github.com/volkagames/clickhouse-migrations.git
cd clickhouse-migrations
pnpm install

Project Structure

clickhouse-migrations/
├── src/              # TypeScript source files
│   ├── cli.ts        # Command-line interface
│   ├── cli-setup.ts  # CLI setup and configuration
│   ├── migrate.ts    # Migration logic
│   ├── sql-parse.ts  # SQL parser
│   ├── logger.ts     # Logging utilities
│   └── dsn-parser.ts # DSN parsing utilities
├── tests/            # Test files
│   ├── *.unit.test.ts       # Unit tests
│   ├── *.integration.test.ts # Integration tests
│   └── *.e2e.test.ts        # End-to-end tests
├── dist/             # Compiled JavaScript output (gitignored)
│   ├── migrate.js    # ESM library bundle
│   ├── migrate.cjs   # CommonJS library bundle
│   └── cli.js        # CLI executable
├── migrations/       # Example migrations
├── rollup.config.js  # Rollup bundler configuration
└── biome.json        # Biome configuration

Executing DDL: command vs exec

All migration and schema statements (CREATE DATABASE, the _migrations tracking table, and the DDL inside migration files) are sent through the ClickHouse client's command() method, not exec().

The two differ in how they treat the server's response stream:

| | command() | exec() | | --- | --- | --- | | Intended for | DDL / statements with no useful output | queries whose response stream you consume | | Response stream | destroyed immediately by the client | returned to the caller, must be consumed | | If the stream is ignored | nothing to do — already drained | stays open until GC/socket timeout |

Because migrations are DDL with no output we need, exec() left an unconsumed response stream on every statement. For small statements the socket usually drained before reuse, but larger responses (e.g. a CREATE MATERIALIZED VIEW ... POPULATE bound to a populated table) kept the stream open long enough to trigger a socket hang up warning and a potential ECONNRESET on the next request. command() drains the stream as soon as the response arrives, which is exactly what DDL needs. See #15.

Why exec() gains us nothing here

exec() is only worth its extra complexity when you need one of two things, and this tool needs neither:

• Reading the response stream. Migrations run CREATE, ALTER, INSERT ... SELECT, mutations, etc. — statements whose result we never read. exec() hands back a live stream to consume; we would just throw it away (and leaking it is what caused #15).
• Passing an input values stream. exec() can stream data into a custom INSERT via its values parameter. Migration files are plain SQL text read from .sql files — any data lives inside the statement itself (INSERT ... VALUES, INSERT ... SELECT), so there is no separate stream to send. We call command({ query, clickhouse_settings }) with no values, exactly as exec() was called before.

Since we neither read the response nor send a values stream, command() sends the identical HTTP request and simply manages the response stream for us. Data migrations (INSERT, INSERT ... SELECT, ALTER ... UPDATE) behave the same as before. If you ever need to stream a large dataset in — rather than embedding it in SQL — use the client's insert() method, not a migration statement.

This is purely an internal choice — it does not change SQL, settings, or behavior observed by CLI users.

Available Scripts

Build

Compile TypeScript to JavaScript using Rollup:

pnpm run build

Output is generated in the dist/ directory:

• dist/migrate.js - ESM library bundle (for import)
• dist/migrate.cjs - CommonJS library bundle (for require())
• dist/cli.js - CLI executable with shebang
• dist/*.d.ts - TypeScript type definitions
• dist/*.map - Source maps for debugging

Testing

This project uses Vitest for testing.

📖 See TESTING.md for detailed testing documentation.

Run all tests:

pnpm run test

Run specific test categories:

# Unit tests only
pnpm run test:unit

# Integration tests only
pnpm run test:integration

# End-to-end tests only
pnpm run test:e2e

Additional test commands:

# Watch mode - re-run tests on file changes
pnpm run test:watch

# Interactive UI mode
pnpm run test:ui

# Generate coverage report
pnpm run test:coverage

Linting and Formatting

This project uses Biome for linting and formatting (replaces ESLint + Prettier).

Check and fix code style issues:

pnpm run check

Format source code:

pnpm run format

Lint without fixing:

pnpm run lint

Git Hooks

This project uses Husky to enforce code quality via Git hooks:

pre-commit - Runs before each commit:

• Unit tests (pnpm run test:unit)
• Biome check (linting and formatting)
• TypeScript compilation check

pre-push - Runs before pushing to remote:

• Unit tests (pnpm run test:unit)
• Build verification (pnpm run build)

commit-msg - Validates commit message format:

• Enforces Conventional Commits format
• Valid types: feat, fix, docs, style, refactor, test, chore, perf, ci, build, revert
• Examples:
  • feat: add migration rollback support
  • fix(cli): handle missing config file
  • docs: update installation instructions

Hooks are automatically installed when running pnpm install via the prepare script.

Bypassing hooks (not recommended):

# Skip pre-commit hook
git commit --no-verify -m "your message"

# Skip pre-push hook
git push --no-verify

Pre-commit Requirements

The Git hooks automatically enforce:

1. Unit tests pass (pre-commit): pnpm run test:unit
2. Code is formatted and linted: pnpm run check
3. TypeScript compiles without errors: pnpm exec tsc --noEmit
4. Unit tests pass (pre-push): pnpm run test:unit
5. Build succeeds (pre-push): pnpm run build

The prepublishOnly script automatically runs tests and checks before publishing.

Code Style

The project enforces strict code style rules via Biome:

• Indentation: 2 spaces
• Line Width: 120 characters
• Quotes: Single quotes
• Semicolons: As needed (ASI - Automatic Semicolon Insertion)
• Trailing Commas: Always
• Arrow Parens: Always

See biome.json for the complete configuration.

Build System

The project uses Rollup for bundling with the following outputs:

• ESM bundle (dist/migrate.js) - Modern ES modules for import
• CommonJS bundle (dist/migrate.cjs) - Legacy format for require()
• CLI bundle (dist/cli.js) - Self-contained executable with shebang

This dual-package approach ensures compatibility with all modern JavaScript environments:

• Node.js >= 20 (both ESM and CommonJS projects)
• Modern bundlers (webpack, esbuild, vite, etc.)

See rollup.config.js for the build configuration.

TypeScript Configuration

• Target: ES2022
• Module: ES2022 (ESM)
• Module Resolution: Node
• Strict Mode: Enabled with additional strict flags
  • noImplicitAny
  • strictNullChecks
  • noUncheckedIndexedAccess
  • noImplicitReturns
  • noFallthroughCasesInSwitch
  • noUnusedLocals
  • noUnusedParameters
  • allowSyntheticDefaultImports
  • esModuleInterop

See tsconfig.json for the complete configuration.

Testing Philosophy

• Unit tests - Test individual functions and modules in isolation
• Integration tests - Test interactions between modules
• E2E tests - Test complete workflows including ClickHouse connections

Run tests frequently during development to catch regressions early.

Debugging

Run the CLI locally during development:

# Build first
pnpm run build

# Using Node
node dist/cli.js migrate --host=http://localhost:8123 --migrations-home=./migrations

# With debugging output
node dist/cli.js migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations \
  --log-level=debug

Common Development Tasks

Adding a New Feature

1. Write tests first (TDD approach recommended)
2. Implement the feature in src/
3. Run pnpm run check to ensure code quality
4. Run pnpm run test to verify tests pass
5. Update documentation if needed

Fixing a Bug

1. Write a failing test that reproduces the bug
2. Fix the bug in src/
3. Verify the test now passes
4. Run full test suite to ensure no regressions

Updating Dependencies

# Update all dependencies
pnpm update

# Update specific dependency
pnpm update @clickhouse/client

After updating dependencies, run the full test suite to ensure compatibility.

EditorConfig

The project includes .editorconfig for consistent coding style across different editors. Most modern editors support EditorConfig automatically or via plugins.

Best Practices

1. Make Migrations Idempotent

Always use IF NOT EXISTS / IF EXISTS clauses:

-- Good: Idempotent
CREATE TABLE IF NOT EXISTS users (...);
ALTER TABLE users ADD COLUMN IF NOT EXISTS email String;
DROP TABLE IF EXISTS temp_table;

-- Bad: Will fail if run twice
CREATE TABLE users (...);
ALTER TABLE users ADD COLUMN email String;

2. Never Modify Applied Migrations

Once a migration is applied to production, never modify it. Create a new migration instead:

-- migrations/5_fix_users_table.sql
-- Fixing column type from previous migration
ALTER TABLE users MODIFY COLUMN age UInt8;

3. Test Migrations Locally First

# Test on local database first
clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --db=test_db \
  --migrations-home=./migrations

# Then apply to production
clickhouse-migrations migrate \
  --host=https://prod.example.com:8443 \
  --db=production_db \
  --migrations-home=./migrations

4. Use Version Control

Commit migration files to git:

git add migrations/10_new_feature.sql
git commit -m "Add migration for new feature"

5. Backup Before Major Migrations

-- migrations/50_major_refactor.sql
-- WARNING: This migration performs major schema changes
-- Ensure database backup exists before applying

-- Create backup table
CREATE TABLE users_backup AS SELECT * FROM users;

-- Perform migration
ALTER TABLE users ...;

-- Verify migration
-- (Manual verification step)

-- Drop backup after verification
-- DROP TABLE IF EXISTS users_backup;

6. Add Comments and Documentation

-- migrations/15_add_analytics_tables.sql
-- Purpose: Add tables for user analytics tracking
-- Author: Engineering Team
-- Date: 2024-01-15
-- Related: JIRA-123

/*
 * This migration creates:
 * 1. events table - stores raw event data
 * 2. events_daily materialized view - aggregated daily stats
 * 3. events_buffer - buffer table for high-throughput writes
 */

CREATE TABLE events (...);
-- ... rest of migration

7. Use Transactions Where Possible

Note: ClickHouse has limited transaction support. Group related operations:

-- migrations/20_atomic_changes.sql
-- These operations should succeed or fail together

BEGIN TRANSACTION;  -- Note: Limited support in ClickHouse

CREATE TABLE new_table (...);
INSERT INTO new_table SELECT * FROM old_table;
RENAME TABLE old_table TO old_table_backup, new_table TO old_table;

COMMIT;

8. Monitor Migration Execution

# Run with output
clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --db=myapp \
  --migrations-home=./migrations 2>&1 | tee migration.log

9. Handle Large Data Migrations

For migrations involving large datasets:

-- migrations/25_migrate_large_table.sql
-- Split large operations into chunks

SET max_execution_time = 0;  -- Disable timeout for this migration
SET max_memory_usage = 10000000000;  -- 10GB

-- Process in batches
INSERT INTO new_table
SELECT * FROM old_table
WHERE date >= '2024-01-01' AND date < '2024-02-01';

INSERT INTO new_table
SELECT * FROM old_table
WHERE date >= '2024-02-01' AND date < '2024-03-01';
-- ... continue for other months

10. Separate Schema and Data Migrations

-- migrations/30_schema_changes.sql (fast)
CREATE TABLE new_feature (...);

-- migrations/31_data_migration.sql (potentially slow)
INSERT INTO new_feature SELECT ... FROM old_data;

Philosophy: Forward-Only Migrations

This tool does not support rollback/downgrade migrations, and never will.

Our design philosophy is inspired by Refinery and early Flyway: migrations are forward-only. To undo or rollback a migration, you must create a new migration that explicitly reverses the changes.

Why No Rollback?

1. Explicit is better than implicit - Writing a new migration forces you to think about what exactly needs to be undone and how to handle data.

2. Data loss prevention - Automatic rollbacks often involve dropping tables or columns, which can result in unintended data loss. A forward migration makes this explicit.

3. Production reality - In production environments, true rollback is rarely safe or possible:

   • Data may have been written with the new schema
   • Other systems may depend on the new schema
   • Time has passed - you can't simply "undo" data transformations

4. Version control is the rollback - Your migration history in git serves as documentation of all schema changes.

How to "Rollback"

Instead of a rollback feature, create a new forward migration:

-- migrations/42_add_user_score.sql
ALTER TABLE users ADD COLUMN score Int32 DEFAULT 0;

-- migrations/43_remove_user_score.sql  (the "rollback")
-- Removing score column added in migration 42
ALTER TABLE users DROP COLUMN IF EXISTS score;

This approach:

• Creates a clear audit trail
• Forces explicit handling of data
• Works the same in all environments
• Prevents accidents

When You Need to Undo Changes

1. Development: Use --abort-divergent=false to modify migrations locally
2. Staging/Production: Always create a new forward migration
3. Emergency: Create a new migration that reverts changes, test it, then apply

This is not a limitation - it's a design decision that leads to safer, more maintainable database evolution.

Security

Password Sanitization in Error Messages

The tool automatically sanitizes sensitive information in error messages to prevent credential leaks. All connection errors and exceptions are processed to remove:

• URL passwords: http://user:password@host → http://user:[REDACTED]@host
• Connection strings: password=secret → password=[REDACTED]
• Authorization headers: Authorization: Bearer token → Authorization: [REDACTED]
• Basic auth tokens: Basic dXNlcjpwYXNz → Basic [REDACTED]

This protection is automatic and requires no configuration. Error messages remain informative for debugging while keeping credentials secure.

Example:

Before: Failed to connect to http://admin:MySecret123@localhost:8123
After:  Failed to connect to http://admin:[REDACTED]@localhost:8123

Note: Passwords containing @ symbols will be partially masked (up to the first @). For maximum security, use URL-encoded passwords or avoid @ in credentials.

Troubleshooting

Migration Already Applied

Error: Migration file shouldn't be changed after apply

Cause: You modified a migration file that was already applied.

Solution:

1. Restore the original migration file
2. Create a new migration for the changes

Database Connection Failed

Error: Failed to connect to ClickHouse: ...

Solutions:

• Check host URL format: http://hostname:8123 or https://hostname:8443
• Verify network connectivity: curl http://clickhouse:8123/ping
• Check credentials
• Increase timeout: --timeout=60000

Permission Denied

Error: Can't create the database ...

Solution:

• Grant CREATE DATABASE permission to user
• Or use --create-database=false and pre-create database

Timeout Errors

Error: Request timeout

Solutions:

• Increase timeout: --timeout=120000 (2 minutes)
• Optimize slow queries in migration
• Check ClickHouse server load

Duplicate Migration Version

Error: Found duplicate migration version X

Solution: Rename one of the migrations with a unique version number

TLS Certificate Errors

Error: Failed to read TLS certificate files

Solutions:

• Verify certificate files exist and are readable
• Check file paths are absolute or relative to execution directory
• Ensure both --cert and --key are provided together

Missing Migration Files

Error: Migration file shouldn't be removed after apply

Cause: A previously applied migration file was deleted.

Solution: Restore the deleted migration file from version control.

Migration Table Structure

The tool automatically creates a migrations tracking table (default: _migrations) to track applied migrations. You can customize the table name using the --migration-table-name option.

Cluster mode (default):

CREATE TABLE _migrations (
  uid UUID DEFAULT generateUUIDv4(),
  version UInt32,
  checksum String,
  migration_name String,
  applied_at DateTime DEFAULT now()
)
ENGINE = ReplicatedMergeTree
ORDER BY tuple(applied_at);

Standalone mode (with --table-engine=MergeTree):

CREATE TABLE _migrations (
  uid UUID DEFAULT generateUUIDv4(),
  version UInt32,
  checksum String,
  migration_name String,
  applied_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY tuple(applied_at);

You can query this table to see migration history:

SELECT version, migration_name, applied_at
FROM _migrations
ORDER BY version;

To use a custom table name:

clickhouse-migrations migrate \
  --host=http://localhost:8123 \
  --migrations-home=./migrations \
  --migration-table-name=my_custom_migrations

Contributing

Contributions are welcome! We appreciate your help in making this project better.

Getting Started

1. Fork the repository
2. Clone your fork: git clone https://github.com/your-username/clickhouse-migrations.git
3. Install dependencies: pnpm install
4. Create a branch: git checkout -b feature/your-feature-name

Development Workflow

1. Make your changes in the src/ directory
2. Add tests for new features or bug fixes in the tests/ directory
3. Run checks to ensure code quality:

   pnpm run check      # Format and lint
   pnpm run test       # Run all tests
   pnpm run build      # Verify build succeeds

4. Commit your changes with a clear commit message:

   git add .
   git commit -m "feat: add support for feature X"

5. Push to your fork: git push origin feature/your-feature-name
6. Open a Pull Request on GitHub

Commit Message Convention

This project uses conventional commits:

• feat: - New feature
• fix: - Bug fix
• docs: - Documentation changes
• refactor: - Code refactoring
• test: - Adding or updating tests
• chore: - Maintenance tasks

Examples:

• feat: add support for materialized views in migrations
• fix: resolve checksum calculation for UTF-8 content
• docs: update DSN configuration examples

Code Quality Standards

All contributions must:

• Pass Biome linting and formatting checks (pnpm run check)
• Pass all existing tests (pnpm run test)
• Include tests for new functionality
• Follow the existing code style (enforced by Biome)
• Include appropriate error handling
• Avoid introducing security vulnerabilities
• Maintain TypeScript strict mode compatibility

What to Contribute

We welcome contributions in these areas:

• Bug fixes - Fix reported issues
• Documentation - Improve README, add examples
• Tests - Increase test coverage
• Features - Add new functionality (discuss in an issue first)
• Performance - Optimize existing code
• Security - Improve security measures

Before Submitting

• [ ] Tests pass: pnpm run test
• [ ] Code is formatted: pnpm run check
• [ ] Build succeeds: pnpm run build
• [ ] Documentation is updated (if needed)
• [ ] Commit messages follow convention
• [ ] PR description clearly explains the changes

Need Help?

• Open an issue for questions or discussion
• Check existing issues and PRs for similar topics
• Review the Development section for setup instructions

Code Review Process

1. Maintainers will review your PR
2. Address any requested changes
3. Once approved, your PR will be merged
4. Your contribution will be included in the next release

Thank you for contributing!

License

MIT

Support

• GitHub Issues: Report issues
• NPM Package: @volkagames/clickhouse-migrations

Related Projects

• ClickHouse Official Client
• ClickHouse Documentation
• Original Project (Inactive governance)