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