@aragornhq/ch-migration

> ⚔️ Production-grade CLI for managing ClickHouse schema migrations with raw SQL, rollback, integrity tracking, strict mode, and GitHub automation.

---

🚀 Features

- ✅ Native ClickHouse support using @clickhouse/client
- ✅ Fully typed CLI (TypeScript)
- ✅ Supports migration:create, migration:up, migration:down, dump
- ✅ Optional dry run to validate migrations without applying them
- ✅ Rollback support using -- ROLLBACK BELOW -- separator
- ✅ SHA-256 hash tracking for applied migrations
- ✅ Supports multiple SQL statements per migration section with ordered rollback
- ✅ Optional config via ch-migration.json
- ✅ ${CH_CLUSTER} placeholder replaced with the CH_CLUSTER environment variable
- ✅ Uses ReplicatedReplacingMergeTree for migration tracking when CH_CLUSTER is set
- ✅ Validates CREATE TABLE migrations use ON CLUSTER with a Replicated engine when CH_CLUSTER is set

---

📦 Installation

``bash
npm install --save-dev @aragornhq/ch-migration
`

🔧 Setup

1. Set the ClickHouse connection using environment variables (prefixed with CH_):

`bash
CH_HOST=localhost
CH_PORT=8123
CH_DB=default
CH_USER=default
CH_PASSWORD=


set to "true" when using HTTPS


CH_USE_TLS=false


optional: set cluster name for ${CH_CLUSTER}


CH_CLUSTER=
`

2. Specify where your migration files live via a ch-migration.json file:

`json
{
"path": "db/migrations"
}
`

Create the folder if it does not already exist.

🛠️ Usage

Run the CLI with npx or via an npm script. The executable name is ch-migrate:

`bash
npx ch-migrate [options]
`

$3

- migration:create --path= – create a timestamped migration file. The --path option is optional when the path is defined in ch-migration.json.
- migration:up --path= [--dry-run] – apply all pending migrations. Use --dry-run to preview without applying.
- migration:down --file= --path= – roll back a single migration.
- dump --out= – export CREATE statements for all tables in the current database. Each statement includes IF NOT EXISTS and no DROP statements so rerunning is safe.

Each file should contain your SQL up statements followed by -- ROLLBACK BELOW -- and the down statements. Statements are executed in order; rollbacks run in reverse order.

`sql
-- 20250101_create_table.sql
CREATE TABLE example (id UInt8) ENGINE = MergeTree;
INSERT INTO example VALUES (1);

-- ROLLBACK BELOW --
DELETE FROM example WHERE id = 1;
DROP TABLE example;
`

Applied migrations are recorded in a migrations` table together with a SHA‑256 hash. If a hash changes, the run fails to prevent drift.