@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

``bash
bun add @housekit/orm @clickhouse/client
`

---

⚡️ Quick Start

$3

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

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

`typescript
// Auto-generated UUID
await db.insert(users).values({ email: 'a@b.com' });

// Custom ID provided by user
await db.insert(users).values({ id: 'my-custom-uuid', email: 'a@b.com' });
`

$3

`typescript
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: 'a@b.com', role: 'admin' });

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

---

🎯 The housekit() Client

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

$3

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

$3

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

$3

Only available when you pass a schema:

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

Then you can query using ORM-style methods:

`typescript
// 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 }
});
`

$3

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

// 1. Insert using builder
await db.insert(schema.users).values({ email: 'a@b.com', 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

$3

`typescript
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 }
}
});
`

$3

`typescript
// 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 }
});
`

$3

`typescript
// Object syntax (simplest)
where: { email: 'a@b.com' }
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

$3

Select specific columns:

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

$3

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

$3

HouseKit uses synchronous inserts by default for maximum speed:

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

$3

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

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

$3

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

`typescript
// Single insert
const user = await db
.insert(users)
.values({ email: 'a@b.com', role: 'admin' })
.returningOne();

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

// Multiple inserts
const [user1, user2] = await db
.insert(users)
.values([{ email: 'a@b.com' }, { email: 'b@c.com' }])
.returning();
`

$3

Collect small writes into efficient batches:

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

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

---

🧠 Advanced Schema

$3

`typescript
// 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'
});
`

$3

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

$3

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

$3

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

$3

- 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

$3

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

$3

`typescript
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:
`bash
bun run benchmark # in app directory
`

---

⚡ Performance Optimizations

HouseKit includes several optimizations for maximum throughput in production environments.

$3

Reuse HTTP connections across requests:

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

$3

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

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

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

---

🛠 Observability

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