Schema introspection

This flow is currently experimental and lives in the separate @oorestisime/quarry-cli package, not the published quarry runtime package.

Use it when you already have ClickHouse tables and want a first-pass DB type instead of writing everything by hand.

What it does

The introspection command:

• reads object metadata from system.tables
• reads column metadata from system.columns
• reads dictionary metadata from system.dictionaries and system.dictionary_attributes
• generates plain TypeScript Tables, Views, Dictionaries, and DB types
• writes an autogenerated header that marks the file as generated
• reports how many objects were generated and how many were skipped

Run it

Use CLI 0.10.0 with core quarry 0.10.0. Generated-column metadata requires core 0.10.0 or newer.

npx @oorestisime/quarry-cli@0.10.0 introspect \
  --url http://localhost:8123 \
  --database analytics \
  --out db.ts

You can also provide connection settings through environment variables:

export CLICKHOUSE_URL=http://localhost:8123
export CLICKHOUSE_DATABASE=analytics
export CLICKHOUSE_USER=default
export CLICKHOUSE_PASSWORD=secret

npx @oorestisime/quarry-cli@0.10.0 introspect --out db.ts

For repeatable generation, put the introspection settings in a JSON config file and pass it explicitly:

npx @oorestisime/quarry-cli@0.10.0 introspect --config quarry.introspect.json

{
  "url": "http://localhost:8123",
  "database": "analytics",
  "out": "src/db.ts",
  "includePattern": "^public_",
  "excludePattern": "^public_tmp_"
}

CLI args override config values, config values override environment variables, and environment variables fall back to the existing defaults. Prefer environment variables for secrets such as CLICKHOUSE_PASSWORD instead of checking them into the config file.

The config file is not discovered automatically. Pass --config explicitly so generation does not change based on the current working directory.

The generated file starts with an autogenerated header and should be treated as generated code.

It looks like this:

import type {
  ClickHouseDate,
  ClickHouseDateTime64,
  ClickHouseUInt64,
  TypedDictionary,
  TypedTable,
  TypedView,
} from "quarry";

export interface Tables {
  users: TypedTable<Users>;
}

export interface Views {
  daily_users: TypedView<DailyUsers>;
}

export interface Dictionaries {
  partner_rates: TypedDictionary<PartnerRates>;
}

export interface Users {
  id: number;
  created_at: ClickHouseDateTime64;
}

export interface DailyUsers {
  signup_date: ClickHouseDate;
  total_users: ClickHouseUInt64;
}

export interface PartnerRates {
  id: number;
  rate_cents: number;
  currency: string;
}

export interface DB extends Tables, Views, Dictionaries {}

Useful options

Use --tablesOnly when you want to bootstrap table metadata first and deal with views later.

npx @oorestisime/quarry-cli@0.10.0 introspect --database analytics --tablesOnly --out db.ts

Use include and exclude filters to limit the generated surface area.

npx @oorestisime/quarry-cli@0.10.0 introspect \
  --database analytics \
  --includePattern '^public_' \
  --excludePattern '^public_tmp_' \
  --out db.ts

The same options can be stored in quarry.introspect.json:

{
  "database": "analytics",
  "tablesOnly": false,
  "includePattern": "^public_",
  "excludePattern": "^public_tmp_",
  "out": "db.ts"
}

Type overrides

ClickHouse metadata cannot always describe the application type you want in TypeScript. Use config imports and typeOverrides to replace specific column types while keeping the rest of the row interface generated.

{
  "database": "analytics",
  "out": "src/db.ts",
  "imports": {
    "./db-overrides": ["UserPayload", "EventPayload"],
    "./external": [{ "name": "Payload", "as": "PartnerPayload" }]
  },
  "typeOverrides": {
    "users": {
      "payload": "UserPayload"
    },
    "events": {
      "payload": "EventPayload | null"
    },
    "partner_rates": {
      "metadata": "PartnerPayload"
    }
  }
}

This emits type-only imports and uses the configured type expression for only the specified columns:

import type { EventPayload, UserPayload } from "./db-overrides";
import type { Payload as PartnerPayload } from "./external";

export interface Users {
  id: number;
  payload: UserPayload;
}

typeOverrides keys are exact ClickHouse object names from metadata, usually snake_case table names. They are not generated TypeScript interface names. The second-level keys are exact ClickHouse column names.

The import module paths are emitted verbatim in the generated file, so write them relative to the generated out file. Nullable overrides are explicit: use "UserPayload | null" when the generated column should be nullable.

What gets skipped

The command skips unsupported objects instead of failing the whole run whenever it can still produce a trusted type file.

Today that usually means:

• materialized views are excluded up front
• objects whose column metadata is unavailable are skipped
• dictionary interfaces only include attribute columns — primary keys and range columns are intentionally omitted for all dictionary layouts because they are not retrievable via dictGet

After generation

The generated file is a starting point, not a lockstep mirror of your database.

Review the output for:

• naming you want to keep or normalize
• view names you may want to separate in your own code
• runtime types that should stay honest to ClickHouse JSONEachRow behavior
• skipped objects reported by the CLI

Once you are happy with the generated file, use it like any handwritten DB type:

import { createClickHouseDB } from "quarry";
import type { DB } from "./db";

const db = createClickHouseDB<DB>();

Why plain TS

Quarry is a query builder first. The CLI now emits plain TypeScript because that keeps the generated output close to what Quarry actually needs:

• typed source names
• typed table vs view distinction
• typed selected rows
• honest runtime types under JSONEachRow
• a small set of type-only ClickHouse aliases where read and write types differ

It avoids asking users to maintain a second schema DSL when the main job is still query building.

Generated columns and schema drift

Introspection reads system.columns.default_kind. DEFAULT columns use Generated<T>: they are required when selected and optional on insert. MATERIALIZED and ALIAS columns use GeneratedAlways<T> and cannot be passed to values() or columns(). These wrappers preserve separate read, insert, and predicate types for dates and wide integers, including configured type overrides.

Check generated types into your application and regenerate them after schema changes. The analytics API example includes a db:generate command; CI regenerates its schema and checks for a diff. TypeScript can only report schema drift after the TypeScript schema is updated.

For large databases, generate the complete schema once and derive service-specific aliases with Pick<DB, "events" | "users">. This preserves the introspected column types while reducing the schema each query module needs to check. See TypeScript performance.