Typed Columns

tsc sees a tagged template’s text as a plain string, so on its own it cannot tell that ${orders.columns.emial} names no column. chant sql types builds the project and writes === the columns of every table, view and dictionary the build declares as TypeScript declarations. A tag that names its object as a type argument then returns typed columns.

The example lexicons/sql/examples/postgres-typed-columns shows the whole setup: typed declarations, a committed sql-types.d.ts, RowOf, and its own tsconfig so the augmentation stays out of other programs. A test runs plain tsc over it and plants a misspelled column to see it fail.

Name the declaration

Give the table, view or ClickHouse dictionary tag the object’s qualified name as its type argument:

src/schema.ts

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

export const shop = schema`CREATE SCHEMA shop`;

export const customers = table<"shop.customers">`
  CREATE TABLE ${shop}.customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL
  )`;

export const orders = table<"shop.orders">`
  CREATE TABLE ${shop}.orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES ${customers} (${customers.columns.id}),
    refunded_at timestamptz
  )`;

The name is schema.name in Postgres and database.name in ClickHouse, unquoted, with Postgres’s folding applied (CREATE TABLE Shop.Orders is shop.orders). An object the DDL does not qualify is its name alone. The type argument changes nothing at build time: the build output is the same with it or without it.

chant import and chant import <file>.sql write the type argument on every table, view and dictionary they declare.

Generate the declarations

chant sql types

The command builds the project the way chant build does and writes sql-types.d.ts in the project’s sourceDir, or in the project root when sourceDir is unset. Any tsconfig that includes the schema includes the file too. --output <path> writes it somewhere else. chant build never writes it.

The file starts with chant’s generated-file marker, so source discovery skips it, and it is a module that augments the dialect’s SqlSchema interface:

// Generated by chant — do not edit directly. `chant sql types` writes it from the build's entities.
import type { Column, KeyColumn } from "@intentius/chant-lexicon-sql/postgres";

declare module "@intentius/chant-lexicon-sql/postgres" {
  interface SqlSchema {
    /** src/schema.ts: orders */
    "shop.orders": {
      columns: {
        id: Column<"bigint", { ts: string; nullable: false; cmp: "N" }>;
        customer_id: Column<"bigint", { ts: string; nullable: false; cmp: "N" }>;
        refunded_at: Column<"timestamp with time zone", { ts: Date; nullable: true; cmp: "D" }>;
      };
      /** `${}` 3 is the column customer_id references. */
      slots: [unknown, unknown, KeyColumn<"N">];
    };
  }
}

Each column carries its type as the catalog spells it and its value in application code (ts). nullable says whether it can be NULL, and cmp is how it compares in a key. slots types the template’s interpolations by position, up to the last one that is checked.

Commit the file, and run the command again after a schema change. The checks below are only as current as the file. If order_ref changes to bigint and nobody regenerates, tsc keeps checking the old type, and a ${} inserted before a checked one shifts the positions until the next run.

The command refuses to write sql-types.d.ts when a sql-types.ts sits beside it, since tsc would read the .ts file and drop the declarations.

Keep the file current

Four checks catch a file that no longer matches the schema.

• chant sql types --check renders the file in memory and compares it with the one on disk. It writes nothing, and exits 1 naming the file when it differs or does not exist. Put it in CI. --output <path> checks that path instead.
• SQL102 is the same comparison as a post-synth check, so chant build and chant lint fail on a stale file. It is silent when the project has no sql-types.d.ts in its source directory, so a project that has not adopted typed columns sees nothing.
• SQLPG005 and SQLCH004 are source lint rules. A type argument must be the qualified name its template declares (an error), and it should be listed in the nearest sql-types.d.ts above the file (a warning, until chant sql types runs). Without them, a mistyped argument falls back to unchecked columns without a word.
• In a workspace, list the file as a generated entry of the member, and chant workspace check --generated fails when it differs:

{
  "name": "shop",
  "dir": "services/shop",
  "kind": "chant",
  "generated": [
    {
      "path": "src/sql-types.d.ts",
      "generator": "chant sql types --output src/sql-types.d.ts",
      "sources": ["src"]
    }
  ]
}

The workspace check points --output at a temporary file and compares it with path, so the tree is not written. A plain chant workspace check runs no generator, since a generator runs member code. See Generated files.

What tsc checks

A column that does not exist is a compile error, with a suggestion:

error TS2551: Property 'emial' does not exist on type '{ id: Column<"bigint", ...>; ... }'. Did you mean 'email'?

A Postgres foreign key whose referenced column is interpolated is checked against the referencing column. Types compare by their catalog category, as Postgres compares them: integer to bigint passes and text to bigint does not. A domain compares as its base type and an enum as itself. The error lands on the ${} that names the column:

order_ref text NOT NULL REFERENCES ${orders} (${orders.columns.id})
// error TS2345: Argument of type 'Column<"bigint", ...>' is not assignable to parameter of type 'KeyColumn<"S">'.

In ClickHouse, a Nullable column interpolated as an element of a materialized view’s ORDER BY or PRIMARY KEY is a compile error (NotNullColumn).

Some things stay outside tsc, and the build and lint checks cover them instead:

• A key with no interpolated column has no slot. REFERENCES ${customers} with no column list, and a table named in text, are checked by SQLPG124 at build and lint.
• A ClickHouse table’s own ORDER BY (kind, country) names its columns in text, since a table cannot interpolate itself. SQLCH002 reports a Nullable sort key there.
• A view’s column is typed when it passes one column through (${t.columns.c} or o.${t.columns.c}, with or without an alias). A computed column (count()) is Column<"unknown", ...>.

Composites

A composite’s tables and views get their entries in SqlSchema like any other, and the members the call returns are typed from them. The composite works out each member’s qualified name from its props, the way the build does, so nothing extra is written at the call:

import { schema, SoftDeleteTable, type RowOf } from "@intentius/chant-lexicon-sql/postgres";

export const app = schema<"app">`CREATE SCHEMA app`;

export const users = SoftDeleteTable({
  name: "users",
  schema: app,
  columns: "id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL",
});

users.table.columns.emial; // error TS2551: ... Did you mean 'email'?
type User = RowOf<typeof users.table>; // the entry for app.users

In Postgres the name is <schema>.<name> plus the member’s suffix (users.live is app.users_live), folded as Postgres folds it. The schema is the schema prop: a string, or a schema whose tag was given its name as the type argument (schema<"app">). With no schema prop it is public. SQLPG005 checks that a schema’s type argument is the name its template declares. In ClickHouse the name is the name prop as written (events or analytics.events) plus the suffix.

A member keeps unchecked columns when its name cannot be read from the call: a name that is not a string literal, a schema entity declared without the type argument, or a name the generated file does not list.

Adopt it one declaration at a time

A tag with no type argument keeps unchecked columns: any name type-checks, as before. So does a name the generated file does not list yet. An unchecked column fits every slot, so a typed table can reference an untyped one’s column. A typed table is still a PostgresTable (or ClickHouseTable), so it passes wherever one is expected, a composite’s input included.

Row types for application code

RowOf takes a typed declaration or a qualified name, which also covers an object declared only in a .sql file:

import type { RowOf } from "@intentius/chant-lexicon-sql/postgres";
import type { orders } from "./schema";

type Order = RowOf<typeof orders>;
// { id: string; customer_id: string; refunded_at: Date | null }
type User = RowOf<"shop.users">; // src/users.sql

RowOf<"shop.ordrs"> is a compile error, and so is RowOf<typeof t> for a t declared without a type argument. A nullable column’s type gets | null. A Postgres view’s columns are always nullable, as Postgres’s own catalog reports them.

The type mapping

Postgres values are what node-postgres returns with its default type parsers:

Postgres type	TypeScript
smallint, integer, real, double precision, oid	number
bigint, numeric, money	string (no JavaScript number holds every value)
boolean	boolean
date, timestamp, timestamptz	Date
time, timetz	string
interval	{ years?: number; months?: number; days?: number; hours?: number; minutes?: number; seconds?: number; milliseconds?: number }
point	{ x: number; y: number }
circle	{ x: number; y: number; radius: number }
json, jsonb	unknown
bytea	Uint8Array
an enum	the union of its labels
a domain	its base type’s
T[]	the element type’s array
every other built-in type (text types, uuid, network addresses, ranges, the reg* types, xml…)	string
a type outside the project	unknown

The driver parses arrays of the built-in types it knows. An array of an enum or a domain arrives as its text form unless the application registers a parser for it.

ClickHouse values are what its JSON formats (JSONEachRow) write with the server’s default settings:

ClickHouse type	TypeScript
UInt8 to UInt32, Int8 to Int32, Float32, Float64, BFloat16, Decimal*	number
UInt64 and wider, Int64 and wider, Interval*	string (quoted by output_format_json_quote_64bit_integers)
Bool	boolean
String, FixedString, UUID, IPv4, IPv6, Date, Date32, DateTime, DateTime64, Time, Time64	string
Enum8, Enum16	the union of their labels
Nullable(T)	T | null
LowCardinality(T), SimpleAggregateFunction(f, T)	T
Array(T)	T[]
Map(K, V)	Record<string, V>
Tuple(a T, b U)	{ a: T; b: U }
Tuple(T, U)	[T, U]
Point, Ring, LineString, Polygon, MultiLineString, MultiPolygon	nested [number, number] arrays
JSON, Dynamic, Variant, AggregateFunction, Nested, Geometry, QBit	unknown

The mapping covers every type in the pinned catalog of each dialect, and a test fails when a new catalog adds a type it does not cover.

More than one project in a program

Two projects of the same dialect that declare the same qualified name both augment the one SqlSchema, and a single tsc program over both reports a duplicate property. Give each project its own tsconfig, as workspace members already have.