npm package: @jantokic/chtype
Description: Type-safe ClickHouse toolkit for TypeScript — schema codegen, query builder, and enhanced client
Author: Jan Tokic
Latest version: 0.13.1

<p align="center">
  <a href="https://chtype.jantokic.com">
    <img src=".github/banner.svg" alt="chtype" width="100%" />
  </a>
</p>

<p align="center">
  <a href="https://www.npmjs.com/package/@jantokic/chtype"><img src="https://img.shields.io/npm/v/@jantokic/chtype?style=flat-square&color=FADB14&labelColor=0a0a0a" alt="npm version" /></a>
  <a href="https://www.npmjs.com/package/@jantokic/chtype"><img src="https://img.shields.io/npm/dm/@jantokic/chtype?style=flat-square&color=FADB14&labelColor=0a0a0a" alt="npm downloads" /></a>
  <a href="https://github.com/jantokic/chtype/blob/main/LICENSE"><img src="https://img.shields.io/github/license/jantokic/chtype?style=flat-square&color=FADB14&labelColor=0a0a0a" alt="license" /></a>
  <img src="https://img.shields.io/badge/node-%3E%3D20-FADB14?style=flat-square&labelColor=0a0a0a" alt="node >= 20" />
  <img src="https://img.shields.io/badge/TypeScript-%3E%3D5.0-FADB14?style=flat-square&labelColor=0a0a0a" alt="TypeScript >= 5.0" />
  <img src="https://img.shields.io/badge/bun-supported-FADB14?style=flat-square&labelColor=0a0a0a" alt="bun supported" />
</p>

<p align="center">
  <b>Type-safe ClickHouse toolkit for TypeScript.</b><br/>
  Schema codegen &middot; Query builder &middot; Full autocomplete &middot; Zero overhead
</p>

<p align="center">
  <a href="https://chtype.jantokic.com">Website</a> &middot;
  <a href="https://www.npmjs.com/package/@jantokic/chtype">npm</a> &middot;
  <a href="https://github.com/jantokic/chtype/issues">Issues</a> &middot;
  <a href="https://github.com/jantokic/chtype/blob/main/CONTRIBUTING.md">Contributing</a>
</p>

---

## Install

```bash
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

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

This produces a file like:

```typescript
// @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

```typescript
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

```typescript
// 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.

```typescript
// 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:

```bash
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:

```typescript
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:

```bash
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](CONTRIBUTING.md) for setup instructions and guidelines.

## License

MIT