@housekit/orm 🏠⚡️

The high-performance, type-safe ClickHouse ORM for Node.js and Bun.

⚠️ Public Beta: This package is currently in public beta. Feedback is highly appreciated as we polish API for v1.0.

💡 Interactive Docs: Use RepoGrep to search and query entire codebase and documentation for free (Updated instantly).

💡 Ask ZRead: Need deep insights? Ask ZRead for AI-powered understanding of codebase (Updated weekly).

💡 Ask Devin AI: Have questions about integrating HouseKit? Ask Wiki for AI-powered assistance (Updated weekly).

HouseKit ORM is a modern database toolkit designed specifically for ClickHouse. It bridges gap between ergonomic developer experiences and extreme performance requirements of high-volume OLAP workloads.

🚀 Key Features

• 🛡️ First-Class TypeScript: Full type inference for every query. Schema definition acts as the single source of truth.
• 🏎️ High-Performance Inserts: Optimized streaming with JSONCompact format and sync insert mode.
• 🏗️ ClickHouse Native Engines: Fluent DSL for MergeTree, ReplacingMergeTree, SummingMergeTree, Distributed, Buffer, and more.
• 🔍 Advanced Analytics: Specialized support for ASOF JOIN, ARRAY JOIN, PREWHERE, and complex Window Functions.
• 🤝 Smart Relational API: Query relations using groupArray internally, preventing row duplication.
• 📦 Background Batching: Built-in buffering to collect small inserts into high-performance batches automatically.

📦 Installation

bun add @housekit/orm @clickhouse/client

⚡️ Quick Start

1. Define your Table

// schema.ts
import { defineTable, t, Engine, relations } from '@housekit/orm';

export const users = defineTable('users', {
  id: t.uuid('id').autoGenerate({ version: 7 }).primaryKey(),
  email: t.string('email'),
  role: t.enum('role', ['admin', 'user']),
  ...t.timestamps(),
}, {
  engine: Engine.MergeTree(),
  orderBy: 'id'
});

export const posts = defineTable('posts', {
  id: t.uuid('id').autoGenerate({ version: 7 }).primaryKey(),
  userId: t.uuid('user_id'),
  title: t.string('title'),
  createdAt: t.timestamp('created_at').default('now()'),
}, {
  engine: Engine.MergeTree(),
  orderBy: 'createdAt'
});

relations(users, ({ many }) => ({
  posts: many(posts, { fields: [users.id], references: [posts.userId] })
}));

export type User = typeof users.$type;
export type NewUser = typeof users.$insert;

UUID Generation Options

HouseKit supports two approaches for UUID generation:

| Approach | Method | When to Use | |----------|--------|-------------| | Client-side | .autoGenerate({ version: 7 }) | When using .returning() or .returningOne() | | Server-side | .default('generateUUIDv7()') | When you don't need the ID back immediately |

// Client-side generation (recommended for most cases)
// UUID is generated in JS before insert, works with returning()
id: t.uuid('id').autoGenerate({ version: 7 }).primaryKey()

// Server-side generation
// UUID is generated by ClickHouse, cannot use returning()
id: t.uuid('id').primaryKey().default('generateUUIDv7()')

Note: Don't combine both - it's redundant. Choose one based on whether you need .returning() support.

Custom IDs: You can always provide your own ID when inserting - autoGenerate only kicks in when the field is undefined:

// Auto-generated UUID
await db.insert(users).values({ email: '[email protected]' });

// Custom ID provided by user
await db.insert(users).values({ id: 'my-custom-uuid', email: '[email protected]' });

2. Connect and Query

import { housekit } from '@housekit/orm';
import * as schema from './schema';

const db = housekit({ url: 'http://localhost:8123' }, { schema });

// Standard insert (no data returned)
await db.insert(schema.users).values({ email: '[email protected]', role: 'admin' });

// JSON insert with returning data
const [user] = await db
  .insert(schema.users)
  .values({ email: '[email protected]', role: 'admin' })
  .returning();

🎯 The housekit() Client

The housekit() function creates a fully-featured ClickHouse client with query builders for all operations.

Client Methods

| Method | Description | |--------|-------------| | db.select() | Creates a SELECT query builder | | db.insert(table) | Inserts data into a table | | db.insertMany(table, data, opts) | Bulk inserts with configuration | | db.update(table) | Updates rows in a table | | db.delete(table) | Deletes rows from a table | | db.raw(sql, params) | Executes raw SQL queries | | db.command({query, query_params}) | Executes ClickHouse commands | | db.close() | Closes the connection |

Client Properties

| Property | Description | |----------|-------------| | db.rawClient | Raw @clickhouse/client instance (direct access) | | db.query ⭐ | Relational API - only available if { schema } is passed | | db.schema | Your defined table schema |

⭐ The Relational API (db.query)

Only available when you pass a schema:

const db = housekit({ url: 'http://localhost:8123' }, { 
  schema: { users, events } 
});

Then you can query using ORM-style methods:

// Find by ID
db.query.users.findById('uuid-here');

// Find many with conditions
db.query.users.findMany({ where: { role: 'admin' } });

// Find first with columns
db.query.users.findFirst({ columns: { id: true, email: true } });

// Find with relations (automatic JOIN)
db.query.users.findMany({ 
  with: { posts: true } 
});

Complete Example

const db = housekit({ url: 'http://localhost:8123' }, { schema });

// 1. Insert using builder
await db.insert(schema.users).values({ email: '[email protected]', role: 'admin' });

// 2. Regular SELECT
const result = await db.select().from(schema.users).where(eq(schema.users.role, 'admin'));

// 3. Relational query (automatic JOIN)
const user = await db.query.users.findById('uuid-here', {
  with: { posts: true }
});

// 4. Raw SQL
const data = await db.raw('SELECT * FROM users LIMIT 10');

// 5. Close connection
await db.close();

🔍 Relational Query API

findMany / findFirst

const users = await db.query.users.findMany({
  where: { role: 'admin', active: true },
  columns: { id: true, email: true },
  orderBy: (cols, { desc }) => desc(cols.createdAt),
  limit: 10,
  with: {
    posts: { limit: 5 }
  }
});

findById

// Simple lookup
const user = await db.query.users.findById('uuid-here');

// With relations
const user = await db.query.users.findById('uuid-here', {
  with: { posts: true }
});

where syntax

// Object syntax (simplest)
where: { email: '[email protected]' }
where: { role: 'admin', active: true }  // AND implícito

// Direct expression
where: eq(users.role, 'admin')

// Callback for complex filters
where: (cols, { and, gt, inArray }) => and(
  gt(cols.age, 18),
  inArray(cols.role, ['admin', 'moderator'])
)

Available operators: eq, ne, gt, gte, lt, lte, inArray, notInArray, between, notBetween, has, hasAll, hasAny, and, or, not, isNull, isNotNull

columns selection

Select specific columns:

const users = await db.query.users.findMany({
  columns: { id: true, email: true }
});
// Returns: [{ id: '...', email: '...' }]

orderBy

// Callback (recommended)
orderBy: (cols, { desc }) => desc(cols.createdAt)

// Multiple columns
orderBy: (cols, { desc, asc }) => [desc(cols.createdAt), asc(cols.name)]

// Direct value
orderBy: desc(users.createdAt)

// Array
orderBy: [desc(users.createdAt), asc(users.name)]

🚀 High-Performance Inserts

Default Insert (Sync Mode)

HouseKit uses synchronous inserts by default for maximum speed:

// Standard insert - uses sync mode automatically
await db.insert(events).values([
  { type: 'click', userId: '...' },
  { type: 'view', userId: '...' },
]);

Async Insert (Server-Side Batching)

Use .asyncInsert() when you want ClickHouse to batch writes internally:

await db.insert(events).values(data).asyncInsert();

JSON Insert with Returning

Use .returningOne() for single inserts or .returning() for multiple:

// Single insert
const user = await db
  .insert(users)
  .values({ email: '[email protected]', role: 'admin' })
  .returningOne();

console.log(user.id); // Generated UUID

// Multiple inserts
const [user1, user2] = await db
  .insert(users)
  .values([{ email: '[email protected]' }, { email: '[email protected]' }])
  .returning();

Background Batching

Collect small writes into efficient batches:

const builder = db.insert(events).batch({ 
  maxRows: 10000, 
  flushIntervalMs: 5000 
});

// Fire-and-forget
await builder.append(event1);
await builder.append(event2);

🧠 Advanced Schema

Complex Engines

// SummingMergeTree
export const dailyRevenue = defineTable('daily_revenue', {
  day: t.date('day'),
  revenue: t.float64('revenue'),
}, {
  engine: Engine.SummingMergeTree(['revenue']),
  orderBy: 'day'
});

// ReplacingMergeTree
export const users = defineTable('users', {
  id: t.uint64('id'),
  email: t.string('email'),
  version: t.uint64('version'),
}, {
  engine: Engine.ReplacingMergeTree('version'),
  onCluster: '{cluster}',
  orderBy: 'id'
});

Dictionaries

import { defineDictionary } from '@housekit/orm';

export const userCache = defineDictionary('user_dict', {
  id: t.uint64('id'),
  country: t.string('country')
}, {
  source: { table: users },
  layout: { type: 'hashed' },
  lifetime: 300
});

🔍 Specialized Joins

ASOF JOIN

const matched = await db.select()
  .from(trades)
  .asofJoin(quotes, sql`${trades.symbol} = ${quotes.symbol} AND ${trades.at} >= ${quotes.at}`)
  .limit(100);

GLOBAL JOIN

await db.select()
  .from(distributedTable)
  .globalJoin(rightTable, condition);

📦 Bundle Size & Performance

HouseKit is optimized for minimal bundle impact in your applications:

| Metric | Value | |--------|-------| | Tarball Size | 96KB | | Unpacked Size | 644KB | | Tree Shaking | ✅ Enabled | | Granular Exports | 17 paths for precise imports |

Optimizations

• Modular Build: 46 separate JS files vs 1 monolithic bundle
• Tree-Shakable: Consumers can eliminate unused code automatically
• Granular Exports: Import only what you need
• No Runtime Overhead: Zero runtime dependency overhead

Import Examples

// Import everything (full bundle)
import { housekit, Engine, t } from '@housekit/orm';

// Import specific modules only (recommended for tree-shaking)
import { Engine } from '@housekit/orm/engines';
import { defineTable, t } from '@housekit/orm/schema-builder';
import { ClickHouseColumn } from '@housekit/orm/column';

Note: While HouseKit includes advanced features like binary serialization, engines, and relations (96KB), the modular structure ensures your bundle only includes what you actually use.

🛠 SQL Utilities

Dynamic Queries

const conditions = [
  eq(users.active, true),
  gte(users.age, 18)
];

const query = await db.select()
  .from(users)
  .where(sql.join(conditions, sql` AND `));

📊 Benchmarks

Performance tested on local ClickHouse (Docker) with Bun runtime:

| Rows | Method | Time | Throughput | |------|--------|------|------------| | 1,000 | JSON | 19ms | 52,632 rows/sec | | 1,000 | JSON Sync | 13ms | 76,923 rows/sec | | 5,000 | JSON | 118ms | 42,373 rows/sec | | 5,000 | JSON Sync | 54ms | 92,593 rows/sec | | 10,000 | JSON | 159ms | 62,893 rows/sec | | 10,000 | JSON Sync | 161ms | 62,112 rows/sec |

Key findings:

• Sync insert is the default - fastest for most use cases
• For batches <5k rows, sync is up to 2x faster
• For larger batches (10k+), performance is similar
• Use .asyncInsert() only when you need server-side batching

Run the benchmark yourself:

bun run benchmark  # in app directory

⚡ Performance Optimizations

HouseKit includes several optimizations for maximum throughput in production environments.

Connection Pooling

Reuse HTTP connections across requests:

const db = housekit({
  url: 'http://localhost:8123',
  pool: {
    maxSockets: 200,      // Max concurrent connections
    keepAlive: true,      // Reuse connections
    timeout: 30000        // Socket timeout (ms)
  }
}, { schema });

Skip Validation

Bypass enum validation in production when you trust your data source:

// Global (all inserts)
const db = housekit({
  url: 'http://localhost:8123',
  skipValidation: true
}, { schema });

// Per-insert
await db.insert(events).values(data).skipValidation();

🛠 Observability

const db = await createClient({
  logger: {
    logQuery: (sql, params, duration, stats) => {
      console.log(`[Query] ${duration}ms | Rows: ${stats.readRows}`);
    },
    logError: (err, sql) => console.error(`[Error] ${err.message}`)
  }
});

License

MIT © Pablo Fernandez Ruiz