ClickHouse dialect

$ npm install ts-prorm-orm @clickhouse/client

$ pnpm add ts-prorm-orm @clickhouse/client

$ yarn add ts-prorm-orm @clickhouse/client

$ bun add ts-prorm-orm @clickhouse/client

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'clickhouse',
  host: 'localhost',
  database: 'default',
  username: 'default',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    protocol: 'http', // 'https' on 8443
  },
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'clickhouse',
  url: 'clickhouse://default:secret@localhost:8123/default',
  dialectOptions: {
    protocol: 'http', // 'https' on 8443
  },
});

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, allowNull: false },
  email: { type: DataTypes.STRING(255), allowNull: false },
  name: { type: DataTypes.STRING(100) },
}, { timestamps: false });

await db.sync(); // CREATE TABLE — see Generated SQL below

import { Op } from 'ts-prorm-orm';

await User.create({ id: 1, email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await User.create({ id: 1, email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

const latest = db.getDialectInstance()!.buildSelectQuery({
  tableName: 'users',
  distinctOn: ['name'],
  order: [['name', 'ASC'], ['id', 'DESC']],
});
await db.query(latest.sql, { replacements: latest.values });

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'clickhouse',
  host: 'localhost',
  port: 8123,          // ClickHouse's default HTTP port
  database: 'default',
  username: 'default',
  password: '',
  dialectOptions: {
    protocol: 'http',    // 'http' | 'https' (use 8443 for https)
  },
});

await prorm.authenticate(); // pings the server up front, like the pooled dialects

import { DataTypes } from 'ts-prorm-orm';

const AnalyticsEvent = prorm.define('Event', {
  user_id:    { type: DataTypes.BIGINT, primaryKey: true },
  event_type: DataTypes.STRING,
  created_at: { type: DataTypes.DATE, primaryKey: true },
});

// -> CREATE TABLE `Event` (...) ENGINE = MergeTree() ORDER BY (`user_id`, `created_at`)
await prorm.sync();

const [row] = (await prorm.query('SELECT count() AS c FROM `Event`')).rows;
row.c;          // "10423"  <-- a string, not 10423
Number(row.c);  // 10423    <-- coerce explicitly before doing math

// ReplacingMergeTree: later inserts with the same ORDER BY key win on merge,
// keeping the row with the greatest `version`.
await prorm.getQueryInterface().createTable('users', {
  id:         { type: DataTypes.BIGINT, primaryKey: true },
  email:      { type: DataTypes.STRING, allowNull: false },
  updated_at: { type: DataTypes.DATE,   allowNull: false },
}, {
  mergeTreeEngine: { type: 'ReplacingMergeTree', version: 'updated_at' },
} as any);

await AnalyticsEvent.bulkCreate([
  { user_id: 1n, event_type: 'view',  created_at: new Date() },
  { user_id: 1n, event_type: 'click', created_at: new Date() },
  // ... hundreds of thousands of rows stream efficiently
]);

// WRONG — may return the old row, the new row, or BOTH:
dialect.buildSelectQuery({ tableName: 'users' });
// SELECT * FROM `users`

// CORRECT — force ReplacingMergeTree dedup at read time:
dialect.buildSelectQuery({ tableName: 'users', final: true } as any);
// SELECT * FROM `users` FINAL

const rows = await User.findAll({
  attributes: [
    dialect.uniqCombined('user_id', 'unique_users'), // approx distinct count
    dialect.quantile(0.99, 'latency_ms', 'p99'),      // approx p99
  ],
  group: ['day'],
} as any);