JSPM – @jantokic/chtype@0.12.0

Package Exports

@jantokic/chtype
@jantokic/chtype/client
@jantokic/chtype/codegen
@jantokic/chtype/migrate
@jantokic/chtype/query

Readme

Type-safe ClickHouse toolkit for TypeScript.
Schema codegen · Query builder · Full autocomplete · Zero overhead

Install

npm install @jantokic/chtype
# or
bun add @jantokic/chtype

Why chtype?

ClickHouse has no mature ORM for TypeScript. The official @clickhouse/client is excellent but gives you raw SQL strings — typos in column names only fail at runtime, result types are manually maintained, and schema drift silently breaks your code.

chtype fixes this with four subpath imports:

Import	What it does
`chtype/codegen`	Introspects your ClickHouse DB and generates TypeScript types
`chtype/query`	Type-safe query builder with ClickHouse-specific functions
`chtype/client`	Thin wrapper over `@clickhouse/client` that connects query builder to execution
`chtype/migrate`	Schema snapshots, diffing, and migration SQL generation

Quick Start

1. Generate types from your database

npx chtype generate \
  --host http://localhost:8123 \
  --database my_db \
  --output ./src/generated/schema.ts

This produces a file like:

// @generated by chtype — do not edit manually

/**
 * Table: `users`
 * Engine: ReplacingMergeTree(updated_at)
 * ORDER BY: (user_id)
 */
export type UsersRow = {
  user_id: string;
  name: string;
  email: string;
  status: "active" | "inactive" | "banned";  // Enum → union literals
  score: number | null;
  tags: string[];
  metadata: Record<string, unknown>;          // JSON columns
  updated_at: string;
};

/** Insert type for `users` — DEFAULT columns are optional. */
export type UsersInsert = {
  user_id: string;
  name: string;
  email: string;
  status: "active" | "inactive" | "banned";
  score?: number | null;
  tags?: string[];
  metadata?: Record<string, unknown>;
  updated_at?: string;                        // has DEFAULT → optional
};

export type Database = {
  users: {
    row: UsersRow;
    insert: UsersInsert;
    engine: "ReplacingMergeTree";
    versionColumn: "updated_at";
  };
  daily_stats: {
    row: DailyStatsRow;
    engine: "MaterializedView";               // MVs included
    source: "events";
  };
};

2. Query with full type safety

import { createQueryBuilder } from 'chtype/query';
import { createClient } from 'chtype/client';
import type { Database } from './generated/schema';

const qb = createQueryBuilder<Database>();
const ch = createClient<Database>({
  url: 'http://localhost:8123',
  database: 'my_db',
});

// Column names autocomplete — typos caught at compile time
const query = qb
  .selectFrom('users')
  .select(['user_id', 'name', 'score'])
  .where('score', '>', qb.param('minScore', 'Float64'))
  .orderBy('score', 'DESC')
  .limit(20)
  .compile();

const users = await ch.execute(query);

3. ClickHouse-specific features

// argMax — first-class citizen for ReplacingMergeTree tables
const latest = qb
  .selectFrom('users')
  .select([
    'user_id',
    qb.fn.argMax('name', 'updated_at').as('name'),
    qb.fn.argMax('score', 'updated_at').as('score'),
  ])
  .groupBy('user_id')
  .compile();

// FINAL modifier
qb.selectFrom('users')
  .select(['user_id', 'name'])
  .final()
  .compile();

// SETTINGS clause
qb.selectFrom('users')
  .select(['user_id'])
  .settings({ max_execution_time: 30 })
  .compile();

// PREWHERE — ClickHouse disk-level filter optimization
qb.selectFrom('events')
  .select(['event_type', fn.count()])
  .prewhere('date', '>=', qb.param('since', 'Date'))
  .groupBy('event_type')
  .compile();

// GLOBAL IN — for distributed tables
qb.selectFrom('events')
  .where('user_id', 'GLOBAL IN', qb.subquery(activeUsers))
  .compile();

// groupByTimeInterval — analytics convenience
qb.selectFrom('events')
  .select(['event_type', fn.count()])
  .groupByTimeInterval('timestamp', 'hour')
  .compile();

// AggregatingMergeTree — -State/-Merge combinators
qb.selectFrom('metrics_agg')
  .select(['user_id', fn.sumMerge('amount_sum').as('total')])
  .groupBy(['user_id'])
  .compile();

4. All values are parameterized

The query builder only accepts Param or Expression values in WHERE clauses — raw strings are not allowed. This makes SQL injection impossible by design.

// This is the only way to pass values — always safe
qb.selectFrom('users')
  .where('name', '=', qb.param('name', 'String'))

// Raw strings in WHERE are a type error — won't compile

5. Schema diff

Compare your live database against the last generated types:

npx chtype diff --config chtype.config.ts

Returns exit code 1 if changes are detected — useful for CI pipelines.

Config File

Instead of CLI flags, use a chtype.config.ts file:

import { defineConfig } from 'chtype/codegen';

export default defineConfig({
  connection: {
    host: 'http://localhost:8123',
    database: 'my_db',
    username: 'default',
    password: process.env.CLICKHOUSE_PASSWORD,
  },
  output: './src/generated/schema.ts',
  bigints: true,
  include: ['users', 'events', 'market_*'],
  exclude: [],
});

Then run:

npx chtype generate --config chtype.config.ts
# or watch for schema changes
npx chtype generate --config chtype.config.ts --watch

How It Compares

Feature	chtype	Raw @clickhouse/client	Kysely + CH dialect	hypequery
Column autocomplete	Yes	No	Yes (limited)	Yes
Schema codegen	Yes	No	No	Yes
Schema diff CLI	Yes	No	No	No
Materialized view codegen	Yes	No	No	No
Enum → union literals	Yes	No	No	No
JSON type support	Yes	No	No	No
Row vs Insert types	Yes	No	No	No
argMax / FINAL / PREWHERE	Yes	Manual SQL	No	No
SETTINGS / SAMPLE	Yes	Manual SQL	No	Partial
GLOBAL IN	Yes	Manual SQL	No	Yes
AggregateFunction typing	Yes	No	No	No
-State/-Merge combinators	Yes	No	No	No
Engine metadata in types	Yes	No	No	No
Zero runtime overhead	Yes	N/A	Yes	No (framework)
SQL injection prevention	By design	Manual	By design	Partial

Requirements

Node.js >= 20 (or Bun)
TypeScript >= 5.0
ClickHouse server (any recent version)

Contributing

See CONTRIBUTING.md for setup instructions and guidelines.

License

MIT