SQL

The sql lexicon declares database schema in TypeScript. It has two dialects, each a subpath of the one package: ClickHouse at @intentius/chant-lexicon-sql/clickhouse and Postgres at @intentius/chant-lexicon-sql/postgres. A declaration is the database’s own CREATE statement in a tagged template, and an interpolated object or column stays a reference. Install it with npm install --save-dev @intentius/chant @intentius/chant-lexicon-sql; each dialect’s Getting Started page runs the example the lexicon ships.

import { database, table, view } from "@intentius/chant-lexicon-sql/clickhouse";

export const analytics = database`CREATE DATABASE analytics ENGINE = Atomic`;

export const events = table`
  CREATE TABLE ${analytics}.events (
    user_id  UUID,
    kind     LowCardinality(String),
    ts       DateTime
  )
  ENGINE = MergeTree
  ORDER BY (user_id, kind, ts)`;

import { schema, table, index } from "@intentius/chant-lexicon-sql/postgres";

export const app = schema`CREATE SCHEMA app`;

export const users = table`
  CREATE TABLE ${app}.users (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
  )`;

export const usersEmail = index`
  CREATE INDEX CONCURRENTLY users_email_lower_idx ON ${users} (lower(${users.columns.email}))`;

Spec-true per dialect

Each dialect is true to its own database. For SQL the spec is the database’s own DDL, so a ClickHouse declaration is a ClickHouse CREATE statement and a Postgres declaration a Postgres one. The names a declaration can use (engines, codecs and settings for ClickHouse; types, access methods, storage parameters and extensions for Postgres) are generated from a pinned server’s own catalog: ClickHouse’s system.* tables, and Postgres’s pg_catalog at one pinned release of each supported major. There is no neutral schema model that the dialects are translated from.

The reason is that the databases differ exactly where a schema tool does its work. ClickHouse has no foreign keys and no transactional DDL, and it cannot change a table’s sort key, partition key or engine in place: the rows have to be copied into a new table. Postgres makes most changes in place and inside a transaction, and what a change costs there is the lock it takes: adding a column with a constant default is a catalog change, a type change that rewrites the table blocks every reader until it finishes, and an index built without CONCURRENTLY blocks every writer. A neutral model would have to hide those differences, so a plan could not say what a change will cost, or carry every dialect’s features and check each one against the target anyway.

So each dialect has its own types, parser, lint rules and post-synth checks, change classifier, applier, migration Op, composites and skills. What the two share is the way of working:

	ClickHouse	Postgres
Declaring	`database`, `table`, `view`	`schema`, `table`, `index`, `view`, `sequence`, `type`, `domain`, `extension`
Types from	one pinned `clickhouse-server` release	one pinned `postgres` release per major, 14 to 18
Rule ids	SQLCH001-003, SQLCH101-120, SQLCH200-250	SQLPG001-004, SQLPG101-118, SQLPG200-270
Change classes	metadata only, background rewrite, rebuild	metadata only, validates under a weaker lock, needs `CONCURRENTLY`, `ACCESS EXCLUSIVE` rewrite or scan, expand and contract
Applier (`ApplyOp` target)	`clickhouseApply` (`clickhouse`)	`postgresApply` (`postgres`)
What a plan refuses runs as	`ClickHouseRebuildOp`	`PostgresMigrationOp`
Ownership marker	a trailer on the object’s comment	a trailer on the object’s comment

References, dependency order and column lineage work the same way in both, as do chant sql diff and chant sql plan (they read the build’s dialect), import with chant import --from <env>, the -- previously: rename hint, and the comment trailer that marks what chant owns.

One build holds one dialect. A project that declares both ClickHouse and Postgres objects fails the build naming one of each; a Postgres database and a ClickHouse database are two projects, which a workspace can hold as two members. sql.dialect in chant.config.ts says which dialect a project is when nothing declared says so, as for an import into an empty project.

Schema changes and `chant migrate`

chant migrate translates a file from one lexicon’s format into another’s, such as a GitHub Actions workflow into GitLab CI. It does not run schema migrations, and the sql lexicon registers nothing for it. A schema change is a change to the declarations: chant sql diff or chant sql plan classifies it, the dialect’s applier makes the changes it can make in place, and a change it cannot make runs as a gated migration Op: ClickHouseRebuildOp (see Rebuilding a Table) or PostgresMigrationOp (see Migrating a Column).

At a Glance

Metric	Count
Resources	26
Property types	0
Services	2
Intrinsic functions	0
Pseudo-parameters	0
Lint rules	61

Lexicon version: 0.122.0
Namespace: SQL

All Rules — 61 rules
Serialization — output format details

SQL

Spec-true per dialect

Pages

Schema changes and chant migrate

At a Glance

Schema changes and `chant migrate`