ClickORM - Type-Safe ORM for ClickHouse

A production-grade, type-safe ORM for ClickHouse with full TypeScript support, designed to rival Sequelize in robustness, TypeORM in type safety, and Drizzle in developer experience.

🚀 Features

• Type Safety First: Full end-to-end type inference with zero any types
• Zero Raw SQL: Users never write SQL strings directly (with escape hatches when needed)
• Fluent API: Method chaining with immutable query builders
• ClickHouse Optimized: Batch inserts, streaming, and ClickHouse-specific features
• Developer Experience: Intuitive API with excellent autocomplete
• Extensible: Plugin architecture for custom data types and operations

📦 Installation

npm install @clickorm/core @clickhouse/client

🎯 Quick Start

import { createClickORMClient, DataType } from '@clickorm/core';

// Create client
const orm = createClickORMClient({
  host: 'http://localhost:8123',
  database: 'default',
  username: 'default',
  password: '',
});

// Define schema
const UserSchema = {
  id: { type: DataType.UInt32, primaryKey: true },
  name: { type: DataType.String, nullable: false },
  email: { type: DataType.String, unique: true },
  age: { type: DataType.UInt8, nullable: true },
  country: { type: DataType.String, default: 'US' },
  createdAt: { type: DataType.DateTime, default: () => new Date() },
  metadata: { type: DataType.JSON, nullable: true },
} as const;

// Create model
const User = orm.define('users', UserSchema);

// TypeScript infers the full type:
// type UserType = {
//   id: number;
//   name: string;
//   email: string;
//   age: number | null;
//   country: string;
//   createdAt: Date;
//   metadata: Record<string, any> | null;
// };

📚 Core Concepts

Schema Definition

ClickORM provides multiple ways to define your schema:

// 1. Object-based schema (recommended)
const UserSchema = {
  id: { type: DataType.UInt32, primaryKey: true },
  name: { type: DataType.String },
  email: { type: DataType.String, unique: true },
} as const;

// 2. Schema builder (fluent API)
import { createSchema } from '@clickorm/core';

const User = createSchema()
  .uint('id', { primaryKey: true })
  .string('name')
  .string('email', { unique: true })
  .build('users');

Supported Data Types

ClickORM supports all major ClickHouse data types:

Numeric Types:

• UInt8, UInt16, UInt32, UInt64
• Int8, Int16, Int32, Int64
• Float32, Float64, Decimal

String Types:

• String, FixedString, UUID

Date/Time Types:

• Date, Date32, DateTime, DateTime64

Boolean:

• Boolean (mapped to UInt8)

Complex Types:

• Array, Tuple, Map, Nested, JSON
• Nullable, LowCardinality

Network Types:

• IPv4, IPv6

Enum Types:

• Enum8, Enum16

🔍 Project Structure

clickorm/
├── src/
│   ├── core/
│   │   ├── client.ts          # ✅ Connection pool management
│   │   ├── model.ts           # ⏳ Base Model class (in progress)
│   │   ├── schema.ts          # ✅ Schema definition & validation
│   │   ├── types.ts           # ✅ Type utilities & inference
│   │   └── errors.ts          # ✅ Custom error classes
│   ├── query/
│   │   ├── builder.ts         # ⏳ Type-safe query builder
│   │   ├── where.ts           # ✅ WHERE clause builder
│   │   ├── select.ts          # ⏳ SELECT operations
│   │   ├── insert.ts          # ⏳ INSERT operations
│   │   ├── update.ts          # ⏳ UPDATE operations
│   │   └── delete.ts          # ⏳ DELETE operations
│   ├── utils/
│   │   ├── sql-builder.ts     # ✅ SQL string construction
│   │   ├── type-mapper.ts     # ✅ TS ↔ ClickHouse mapping
│   │   ├── validator.ts       # ✅ Runtime validation
│   │   └── logger.ts          # ✅ Structured logging
│   └── index.ts               # ⏳ Public API exports
├── tests/                     # ⏳ Test suite
├── examples/                  # ⏳ Usage examples
└── docs/                      # ⏳ Documentation

Legend: ✅ Completed | ⏳ In Progress | ⬜ Pending

🏗️ Architecture

Type System

ClickORM's type system provides full type inference:

// Schema definition with const assertion
const schema = {
  id: { type: DataType.UInt32 },
  name: { type: DataType.String, nullable: false },
  age: { type: DataType.UInt8, nullable: true },
} as const;

// TypeScript automatically infers:
type Inferred = {
  id: number;
  name: string;
  age: number | null;
};

Query Builder

Type-safe query building with method chaining:

const users = await User.where({ country: 'IN' })
  .where({ age: { gte: 18 } })
  .select(['name', 'email'])
  .orderBy('age', 'DESC')
  .limit(10)
  .execute();
// Type: Array<{ name: string; email: string }>

WHERE Clause Operators

// Comparison operators
{ age: { gt: 18 } }           // age > 18
{ age: { gte: 18, lt: 65 } }  // age >= 18 AND age < 65

// Array operators
{ country: { in: ['US', 'IN', 'UK'] } }
{ status: { notIn: ['deleted', 'banned'] } }

// String operators
{ name: { like: '%John%' } }
{ email: { ilike: '%@gmail.com' } }

// NULL checks
{ deletedAt: { isNull: true } }
{ avatar: { notNull: true } }

// Complex conditions (ClickORM style)
{
  and: [
    { age: { gte: 18 } },
    { or: [
      { country: 'US' },
      { country: 'IN' }
    ]}
  ]
}

// MongoDB-style operators (also supported)
{
  $and: [
    { age: { gte: 18 } },
    { $or: [
      { country: 'US' },
      { country: 'IN' }
    ]}
  ]
}

MongoDB Compatibility: ClickORM supports MongoDB-style operators ($and, $or, $not) for seamless migration from MongoDB to ClickHouse. See MongoDB Compatibility Guide for details.

🛠️ Development

Setup

# Install dependencies
npm install

# Run type checking
npm run typecheck

# Run linter
npm run lint

# Run tests
npm test

# Build
npm run build

Testing

# Unit tests
npm run test:unit

# Integration tests (requires ClickHouse)
npm run test:integration

# Watch mode
npm run test:watch

📖 Documentation

Core Components

1. Client (src/core/client.ts)

Manages database connections with pooling, retry logic, and health monitoring.

2. Schema (src/core/schema.ts)

Defines table schemas with validation and DDL generation.

3. Types (src/core/types.ts)

Comprehensive type system for type-safe operations throughout the library.

4. SQL Builder (src/utils/sql-builder.ts)

Secure SQL query construction with automatic parameterization.

5. Type Mapper (src/utils/type-mapper.ts)

Bidirectional type conversion between TypeScript and ClickHouse.

6. Validator (src/utils/validator.ts)

Runtime data validation against schema definitions.

7. WHERE Builder (src/query/where.ts)

Type-safe WHERE clause construction with operator support.

🔐 Security

ClickORM is designed with security in mind:

• SQL Injection Prevention: All queries use parameterized statements
• Identifier Validation: Column and table names are validated against injection
• Input Sanitization: User inputs are automatically sanitized
• Type Safety: Runtime validation ensures data integrity

🚧 Current Status

Phase 1: Core ORM (80% Complete)

• ✅ Connection management
• ✅ Schema definition and validation
• ✅ Type system and inference
• ✅ SQL builder utilities
• ✅ WHERE clause builder
• ⏳ Model class implementation
• ⏳ Query builder (SELECT, INSERT, UPDATE, DELETE)

Phase 2: Relations & Associations (Planned)

• Relation definitions
• Eager loading
• Lazy loading
• Join operations

Phase 3: Analytics & Aggregations (Planned)

• Aggregation functions
• GROUP BY operations
• Time-series analysis
• Window functions

Phase 4: Migrations (Planned)

• Migration generator
• Schema diffing
• Migration runner

📝 License

MIT

👥 Contributing

Contributions are welcome! Please read our contributing guidelines before submitting PRs.

🙏 Acknowledgments

• Built with @clickhouse/client
• Inspired by Sequelize, TypeORM, and Drizzle
• TypeScript compiler team for amazing type inference

Note: This project is currently in active development. The API may change before v1.0.5.