SQL Yodeler

Schema changes for ClickHouse and Postgres, and the ops they imply. Declare your schema in TypeScript, and SQL Yodeler works out the change: it plans and applies it against the live database, or writes it as a versioned migration, behind an approval bound to the plan. Built on chant's sql lexicon.

// src/schema.ts
import { database, table } from "@intentius/chant-lexicon-sql/clickhouse";

export const db = database`CREATE DATABASE events ENGINE = Atomic`;

export const events = table`
  CREATE TABLE ${db}.events (
    id    UInt64,
    kind  LowCardinality(String),
    at    DateTime
  )
  ENGINE = MergeTree
  ORDER BY (kind, at)`;

Each export is one object in the database's own DDL; ${db} is a reference, so the build orders the objects and checks them against a pinned server's catalog. Declaring the schema has the whole form, both dialects, and why it is plain SQL.

Some schema changes imply work beyond one statement, and yodel does that work as part of the migration:

• ClickHouse rebuild. Some changes, such as a new sort key, engine or partitioning, can't be made with ALTER. yodel creates a new table, copies the rows into it, checks that they match, and swaps it in.
• Postgres safe rename. Renaming a column or table in one step breaks the app that is still using the old name. yodel adds the new name, keeps both in sync, moves readers over, and drops the old name later. This is called expand and contract.
• Backfill. A new column often needs values for the rows already there. You write the fill once, and yodel runs it in batches, resuming where it stopped if it is interrupted.

Rebuilds, renames and backfills run once, inside the migration. No recurring jobs. Not an ORM; it works next to one (Schema from an ORM). Data migrations has the details.

SQL Yodeler is built on chant, a declarative infrastructure-as-code toolkit in TypeScript. The yodel CLI covers every step, and runs chant's CLI underneath: yodel create makes a project from a template, yodel emulator up starts a local database, and yodel approve records an approval. Before a project exists, run it as npx @intentius/sql-yodeler; inside one, as npx yodel.

Getting started

Your first migration goes from an empty directory to two applied migrations on a local ClickHouse, with every command and its output. In short:

npx @intentius/sql-yodeler@latest create my-schema --clickhouse --database events
cd my-schema && npm install && git init -b main && git add -A && git commit -m "chore: a new yodel project"
npx yodel emulator up                          # ClickHouse and Postgres in Docker
curl -s http://127.0.0.1:8123 --data-binary "CREATE USER IF NOT EXISTS reader IDENTIFIED WITH no_password SETTINGS readonly = 1"
curl -s http://127.0.0.1:8123 --data-binary "GRANT SELECT, SHOW ON *.* TO reader"
export DEV_CLICKHOUSE_READER_USER=reader DEV_CLICKHOUSE_READER_PASSWORD= DEV_CLICKHOUSE_WRITER_USER=default DEV_CLICKHOUSE_WRITER_PASSWORD=
npx yodel new init && npx yodel lint
git add -A && git commit -m "feat: the first migration"
YODEL_CREDENTIALS=writer:dev npx yodel apply dev   # stops at the approval
npx yodel approve dev   # shows the plan; type dev to approve
YODEL_CREDENTIALS=writer:dev npx yodel apply dev

yodel refuses an environment whose reader and writer are one user, so dev reads with a read-only user, reader, and applies with the emulator's default user, picked by YODEL_CREDENTIALS=writer:dev. The template's just up makes the reader and prints these variables for a .env file, and just apply sets the writer.

For Postgres, use --postgres --schema app.

Each template gives chant.config.ts with a profile per environment (dev, prod), yodel.config.ts, a migrate Op per environment (yodel apply behind a gate bound to the plan digest), a WatchOp per environment for drift on a schedule, and pipelines for GitHub Actions, GitLab CI and Forgejo Actions: yodel lint and the plan comment on pull requests with read-only credentials, yodel apply on a push to main with the writer, and the drift watch on its cron with the reader. yodel ci (npm run ci) renders the pipelines from the installed package, so upgrading yodel and rendering again brings its pipeline fixes, and the project's own jobs, declared in a module yodel.config.ts ci.jobs names, are kept. yodel create --help lists its options; the template's README covers the credentials and each forge's setup, and so does Setting up each forge.

Documentation

The documentation is at intentius.io/sql-yodeler, built from docs/: installing (npm install @intentius/sql-yodeler), starting a project from a template or with yodel init --from, the declarative and versioned workflows, migrations, steps, lint, approval, drift and topology.

examples/clickhouse and examples/postgres are finished example projects, each with a transcript of a run against chant's emulator.

Versioned migrations

yodel apply <env> takes the versioned path when the project has a migrations/ directory holding at least one migration (yodel new writes them); otherwise it is the declarative path, unchanged. yodel status <env> lists applied, pending, out-of-order and part-way migrations and checksum mismatches (--json; exit 0 up to date, 3 pending, 4 mismatch or out of order, 2 an Op step, 1 error).

yodel init --from <env> adopts an existing database: it reads it with chant's import (chant import --from), writes the declarations to src/, checks they plan clean, writes the baseline migration (the first one, creating everything) and records it applied in the history without running it (a succeeded migration row whose note starts baseline:). It needs a chant.config.ts with sql.profiles.<env> already, and for ClickHouse the profile's databases; yodel init --help has the rest. On Postgres it does the same, recording the baseline in <history schema>.history. yodel init --baseline <env> then records the same baseline in another environment that already holds the schema (staging, prod): it compares that environment's live schema with the schema the baseline records, refuses naming each difference, and otherwise records the baseline applied behind the environment's gate.

The history is an append-only table, <db>.history, one row per migration and per statement event, never updated or deleted.

yodel.config.ts

An optional yodel.config.ts next to chant.config.ts sets what chant's config does not, per environment (by its sql.profiles name):

// yodel.config.ts
import { defineConfig } from "@intentius/sql-yodeler";

export default defineConfig({
  environments: {
    dev: { topology: "single" },
    staging: { topology: "replicated" },
    prod: { topology: "cluster:main", history: { database: "yodeler" }, lockTtl: 1800 },
  },
});

• topology: single (a single node), cluster:<name> (ON CLUSTER <name>, Replicated*MergeTree with a Keeper path), replicated or replicated:<cluster> (a Replicated database: no ON CLUSTER, Replicated*MergeTree with no arguments), or cloud (no ON CLUSTER, plain MergeTree, which Cloud makes SharedMergeTree). The object form, { kind: "cluster", cluster: "main", replicaPath, replicaName }, sets the Keeper path and replica name of the tables the migrations create. Order: yodel.config.ts, then sql.profiles.<env>.topology in chant.config.ts, then YODEL_TOPOLOGY, then single.
• history.database: the database for the history and the lock. Then YODEL_HISTORY_DATABASE, then yodeler.
• lockTtl: the apply lock's TTL in seconds. Then YODEL_LOCK_TTL, then 900.

One migrations directory serves every topology. yodel new writes each statement for a single node and records nothing about where it runs; yodel apply renders each statement for the environment's topology as it sends it (chant's renderStatement). The history records a statement by the hash of the statement as written, so a migration that failed part way resumes the same way on any topology, and the statement row's note says the topology it was sent for (topology=cluster:main). The plan digest covers the topology, so an approval given for one topology does not apply on another. The history table's own engine follows the topology too: MergeTree on a single node and on Cloud, ReplicatedMergeTree ON CLUSTER on a cluster (one copy for the whole cluster), ReplicatedMergeTree inside a Replicated database. yodel status and yodel plan print the topology and where it came from.

The declarative path (no migrations/) plans and applies through chant, which reads sql.profiles.<env>.topology only, so a topology other than single set in yodel.config.ts alone is refused there; set it in chant.config.ts as well. A rebuild step (ClickHouseRebuildOp) runs through chant too, but yodel hands its activities the environment's topology, so it renders for the same topology as the statements around it.

One apply runs at a time: the lock is a row in a KeeperMap table, <db>.lock, which needs Keeper and keeper_map_path_prefix on the server. A single node without them gets a lock file on the runner's machine, and the run says cross-runner locking needs Keeper. A lock left by a dead runner expires; to clear it sooner, once you know nothing is applying: ALTER TABLE <db>.lock DELETE WHERE name = 'apply' (or remove the lock file the refusal names).

The apply runs as a chant Op behind a gate bound to the plan digest (the pending migrations' checksums, the history and the live schema). yodel apply <env> prints the declaration to add when there is none:

// ops/migrate-prod.op.ts
import { Op, phase, gate, shell, stepOutput } from "@intentius/chant/op";

const plan = shell("yodel apply prod --digest", { id: "plan" });
const digest = stepOutput(plan, "stdout");

export const migrateProd = Op({
  name: "migrate-prod",
  overview: "Apply the pending migrations to prod, behind an approval bound to their plan digest",
  phases: [
    phase("Plan", [plan]),
    phase("Approve", [gate("approve-migrate-prod", { plan: digest })]),
    phase("Apply", [shell("yodel apply prod --execute", { env: { YODEL_APPROVED_PLAN: digest }, timeout: "2h" })]),
  ],
});

yodel apply prod stops at the gate and prints the command that approves its plan, npx yodel approve prod --plan <digest>: run it at a terminal, then run yodel apply prod again. yodel apply prod --json also gives chant's own approve command, chantApprove, for a script that records approvals itself. yodel apply prod --plan prints the pending migrations and the digest without applying.

yodel lint

yodel lint checks the migrations directory offline, with no server, so it runs in CI on every pull request. Every rule is free. Exit 0 when there is no error (warnings and silenced findings do not fail), 3 when there is at least one, 1 when lint cannot run; --json prints the findings.

• Integrity, always errors: fork (two migrations follow the same parent; yodel rebase), directory (a bad name or file, a missing parent, a cycle, another dialect), checksum (a migration's files changed since it was written), unreadable-object (an object in a recorded schema, or a step, the sql lexicon cannot read or classify; never skipped).
• From the classifier's classes: destructive (a drop), pg-rewrite (a rewrite or full scan under ACCESS EXCLUSIVE), pg-lock (writes blocked for a whole index build or scan: no CONCURRENTLY, a constraint validated in place), pg-signature (a function or procedure dropped and created with another signature), pg-access (a privilege revoked, a policy dropped, row-level security turned off), pg-rls-not-forced (a warning: row-level security enabled but not forced), ch-mutation (a mutation rewriting parts), ch-rebuild (a ClickHouseRebuildOp step), ch-access (a privilege or role revoked).
• refused-step (a step yodel apply will not run), pg-routine-in-use (a function dropped while a declared trigger still executes it), migration-sql (migration.sql does not hold a statement migration.json records), silence (a bad or unused yodel:allow line).

A line above a statement in its migration.sql silences one of the classifier rules for that statement:

-- yodel:allow destructive the column was never read; checked in prod 2026-10-08
ALTER TABLE `db`.`events` DROP COLUMN `legacy`;

The reason is required. yodel apply records each silence (step, rule, reason) in every history row the migration writes, in the silences column. The line changes the migration's checksum, so record the new one with yodel lint --update-checksum <id> while it is applied nowhere.

yodel lint --env <env> also reads the environment's history: an applied migration whose files changed since it ran is a checksum error, a pending migration's edit a warning. yodel.config.ts sets rule levels: lint: { rules: { "ch-mutation": "warning", "pg-lock": "off" } }; the integrity rules cannot be lowered. yodel lint --rules lists the rules and their levels.

yodel lint --replay <env> also replays every migration, in order, into <env>'s server (a throwaway emulator, npx yodel emulator up) and compares each with its recorded schema; the first that differs fails the run (rule replay, exit 3), named. The databases or schemas the history names must not exist there yet: otherwise it exits 4 and runs nothing, unless --reset, which drops them first (never point it at a server whose data matters). --keep leaves the replayed database in place to look at.

Contributing

CONTRIBUTING.md covers building SQL Yodeler from a clone, the checks, and the scenario claims.