A TypeScript ORM for ClickHouse with migrations support
npm install thunder-schemaA TypeScript ORM for ClickHouse databases with type-safety at its core.
Thunder Schema is a TypeScript ORM for ClickHouse that provides a simple and type-safe way to interact with ClickHouse databases. It features type-safe model definitions, a powerful query builder, migration support, connection management, and a TypeScript-first approach.
bash
npm install thunder-schema
or
yarn add thunder-schema
`Core Concepts
$3
Models are the core building blocks in Thunder Schema. They represent tables in your ClickHouse database and provide a type-safe way to interact with your data. Each model has:
- Schema: A TypeScript type defining the table structure
- Fields: Field definitions with types and options
- Table Definition: Configuration for the table including engine and indexes
$3
Fields define the properties of your models. Each field has a type (like StringField, NumberField) and options (like default values, nullable settings).
$3
The ConnectionManager handles database connections and provides a centralized way to manage connection configuration. It supports multiple connections and connection pooling.
Connection Management
The
ConnectionManager is a singleton class that manages ClickHouse database connections. It provides several key features:1. Multi-tenancy Support: You can have multiple connection instances for different databases/hosts
2. Default Connection: You can set a default connection configuration
3. Connection Pooling: It maintains connections and reuses them efficiently
4. Type Safety: It's fully typed with TypeScript
$3
You can pass connection configuration to models through the constructor:
`typescript
// Usage with specific connection
const user = new User(config)
const users = await user.objects.all()// Usage with default connection
const defaultUser = new User()
const defaultUsers = await defaultUser.objects.all()
`$3
The
ConnectionManager provides several useful methods:`typescript
// Create a new database
ConnectionManager.createDatabase('my_database')// Execute operations within a connection context
await connectionManager.with(async (client) => {
await client.query({ query: 'SELECT 1' })
})
// Close a specific connection
await connectionManager.close()
// Close all connections
await ConnectionManager.closeAll()
`$3
1. Default Connection: Set up a default connection for your application:
`typescript
// In your application initialization
ConnectionManager.setDefault({
credentials: {
url: process.env.CLICKHOUSE_URL,
username: process.env.CLICKHOUSE_USERNAME,
password: process.env.CLICKHOUSE_PASSWORD,
database: process.env.CLICKHOUSE_DATABASE
}
})
`2. Multi-tenant Applications: For multi-tenant applications, use different connection instances:
`typescript
// For tenant-specific operations
const tenantConfig: ConnectionConfig = {
credentials: {
url: tenant.url,
username: tenant.username,
password: tenant.password,
database: tenant.database
}
}const tenantUser = new User(tenantConfig)
const tenantUsers = await tenantUser.objects.all()
`3. Connection Cleanup: Always close connections when they're no longer needed:
`typescript
try {
// Use the connection
} finally {
await connectionManager.close()
}
`$3
The
ConnectionManager includes built-in error handling:`typescript
try {
await connectionManager.with(async (client) => {
// Your database operations
})
} catch (error) {
// Handle connection or query errors
console.error('Database error:', error)
}
`
Getting Started
$3
`typescript
import { Model } from 'thunder-schema'
import { FieldsOf, TableDefinition } from 'thunder-schema'
import { NumberField, StringField } from 'thunder-schema'type UserSchema = {
id: number
name: string
email: string
createdAt: number
updatedAt: number
deletedAt: number
}
class User extends Model {
static fields: FieldsOf = {
id: new NumberField({}),
name: new StringField({}),
email: new StringField({}),
createdAt: new NumberField({}),
updatedAt: new NumberField({}),
deletedAt: new NumberField({}),
}
static tableDefinition: TableDefinition = {
tableName: 'users',
engine: 'MergeTree',
orderBy: ['createdAt'],
}
}
`$3
`typescript
import { ConnectionManager, ConnectionConfig } from 'thunder-schema'const config: ConnectionConfig = {
credentials: {
url: 'http://localhost:8123',
username: 'default',
password: '',
database: 'default'
},
options: {
keepAlive: true
}
}
// Set as default connection
ConnectionManager.setDefault(config)
// Or get a specific instance
const connectionManager = ConnectionManager.getInstance(config)
`$3
Creating and saving a new record:
`typescript
// Create a new user
const user = new User().create({
id: 1,
name: 'John Doe',
email: 'john@example.com',
createdAt: Date.now(),
updatedAt: Date.now(),
deletedAt: 0
})await user.save()
`Querying Data
$3
`typescript
// Get all users
const allUsers = await User.objects.all()// Filter by field
const activeUsers = await User.objects
.filter({ isActive: true })
.all()
`$3
#### Sorting Results
`typescript
// Sort by a single field
const users = await new User().objects
.sort({ createdAt: -1 }) // -1 for descending, 1 for ascending
.all()// Sort by multiple fields
const users = await new User().objects
.sort({
isActive: -1, // Active users first
createdAt: 1 // Then by creation date ascending
})
.all()
`#### Counting Results
`typescript
// Count all users
const totalUsers = await new User().objects.count()// Count with filters
const activeUsers = await new User().objects
.filter({ isActive: true })
.count()
`#### Getting First Result
`typescript
// Get first user
const firstUser = await new User().objects.first()// Get first active user
const firstActiveUser = await new User().objects
.filter({ isActive: true })
.first()
`#### Field Selection (Projection)
`typescript
// Select specific fields
const users = await User.objects
.project(['id', 'name', 'email'])
.all()// Rename fields in the result
const users = await User.objects
.project([
'id',
{ name: 'fullName' },
{ email: 'contactEmail' }
])
.all()
`#### Using FINAL Modifier
`typescript
// Get final versions of rows
const finalUsers = await User.objects
.final()
.all()
`$3
#### Nested Queries and Complex Conditions
`typescript
import { Q } from 'thunder-schema'// Simple OR conditions
const query = new User().objects.filter(
new Q().or([
{ id: 1 },
{ id: 2 }
])
)
// Complex nested conditions
const complexQuery = new User().objects.filter(
new Q().or([
new Q().and([{ id: 1 }, { name: 'John' }]),
new Q().not(
new Q().or([{ id: 2 }, { email: 'test@test.com' }])
)
])
)
// Nested queries with Model instances (subqueries)
const user1 = new UserModel()
const user2 = new UserModel()
// Create a filtered query
const filteredQuery = user2.objects.filter({ id__gt: 5 })
// Use the filtered query in an IN condition
const inQueryWithModel = user1.objects.filter({ id__in: filteredQuery })
// Generated SQL: SELECT FROM users WHERE (id IN (SELECT FROM users WHERE (id > 5)))
`#### Excluding Records
`typescript
// Exclude specific records
const activeUsers = await User.objects
.exclude({ isActive: false })
.all()// Complex exclusion conditions
const validUsers = await User.objects
.exclude(
new Q().or([
{ email: null },
{ name: '' }
])
)
.all()
`#### Async Iteration and Streaming
The ORM supports async iteration and streaming of results.
`typescript
// Using async iteration
const userInstance = new User()
const query = userInstance.objects.filter({ isActive: true })
for await (const user of query) {
console.log(user)
}const allUsers = await userModel.objects.all()
// Combining with nested queries
const complexQuery = userModel.objects.filter(
new Q().or([
{ id: 1 },
{ id: 2 },
new Q().and([{ id: 3 }, { email: 'test@test.com' }])
])
)
for await (const user of complexQuery) {
console.log(user)
}
`
$3
You can update model instances in-place before saving them to the database:
`typescript
// Create a new user
const user = new User().create({
id: 1,
name: 'John Doe',
email: 'john@example.com',
createdAt: Date.now(),
updatedAt: Date.now(),
deletedAt: 0
})// Update values in-place
user.values.name = 'Jane Doe'
user.values.email = 'jane@example.com'
user.values.updatedAt = Date.now()
// Save the updated values
await user.save()
// You can also update multiple fields at once
Object.assign(user.values, {
name: 'Jane Smith',
email: 'jane.smith@example.com',
updatedAt: Date.now()
})
await user.save()
$3
#### Comparison Operators
`typescript
// Greater than
{ age__gt: 18 } // age > 18// Less than
{ age__lt: 65 } // age < 65
// Greater than or equal
{ age__gte: 18 } // age >= 18
// Less than or equal
{ age__lte: 65 } // age <= 65
// Not equal
{ status__ne: 'inactive' } // status != 'inactive'
`#### String Operators
`typescript
// Case-insensitive contains
{ name__icontains: 'john' } // name LIKE '%john%'
`#### Set Operators
`typescript
// In set
{ status__in: ["active", "pending"] } // status IN ('active', 'pending')// In subquery (using another model's query)
const filteredQuery = user2.objects.filter({ id__gt: 5 })
const inQueryWithModel = user1.objects.filter({ id__in: filteredQuery })
// Generated SQL: SELECT FROM users WHERE (id IN (SELECT FROM users WHERE (id > 5)))
`$3
Thunder Schema provides a powerful aggregation system that supports both basic aggregations and arithmetic operations. Here's how to use it:
#### Basic Aggregations
`typescript
// Basic aggregations with aliases
const result = await Sale.objects
.filter({ price__gt: 100 })
.aggregate({
total_revenue: new Sum("price", "total_revenue"),
avg_price: new Avg("price", "avg_price"),
total_sales: new Count("*", "total_sales"),
min_price: new Min("price", "min_price"),
max_price: new Max("price", "max_price"),
})
.all()// Result will be an array with one object containing:
// {
// total_revenue: number,
// avg_price: number,
// total_sales: number,
// min_price: number,
// max_price: number
// }
`#### Arithmetic Operations
You can perform arithmetic operations on aggregation results:
`typescript
// Addition
const result = await Sale.objects
.filter({ price__gt: 100 })
.aggregate({
total_revenue: new Sum("price", "total_revenue"),
total_quantity: new Sum("quantity", "total_quantity"),
total_with_addition: add(
new Sum("price", "total_revenue"),
new Sum("quantity", "total_quantity")
),
})
.all()// Subtraction
const result = await Sale.objects
.aggregate({
revenue_minus_quantity: subtract(
new Sum("price", "total_revenue"),
new Sum("quantity", "total_quantity")
),
})
.all()
// Multiplication
const result = await Sale.objects
.aggregate({
revenue_times_quantity: multiply(
new Sum("price", "total_revenue"),
new Sum("quantity", "total_quantity")
),
})
.all()
// Division
const result = await Sale.objects
.aggregate({
average_price_per_quantity: divide(
new Sum("price", "total_revenue"),
new Sum("quantity", "total_quantity")
),
})
.all()
`#### Complex Arithmetic Expressions
You can combine multiple arithmetic operations:
`typescript
const result = await Sale.objects
.filter({ price__gt: 100 })
.aggregate({
complex_calculation: add(
multiply(
new Sum("price", "total_revenue"),
new Sum("quantity", "total_quantity")
),
divide(
new Sum("price", "total_revenue"),
new Sum("quantity", "total_quantity")
)
),
})
.all()
`The above will generate SQL like:
`sql
SELECT ((SUM(price) * SUM(quantity)) + (SUM(price) / SUM(quantity))) as complex_calculation
FROM sales
WHERE (price > 100)
`Field Types & Options
$3
1. NumberField: For numeric values
`typescript
new NumberField({
defaultValue: 0,
nullable: false
})
`2. StringField: For text values
`typescript
new StringField({
defaultValue: '',
nullable: true,
maxLength: 255
})
`3. BooleanField: For true/false values
`typescript
new BooleanField({
defaultValue: false
})
`4. DateTimeField: For date and time values
`typescript
new DateTimeField({
defaultValue: 'now()',
timezone: 'UTC'
})
`$3
All field types support these options:
-
defaultValue: Default value for the field
- nullable: Whether the field can be null
- expression: SQL expression for computed fields
- materialized: Whether the field is materialized$3
`typescript
class Product extends Model {
static fields = {
id: new NumberField({}),
tags: new ArrayField({
elementType: new StringField({ defaultValue: '' }),
defaultValue: ['new', 'featured'],
}),
prices: new ArrayField({
elementType: new NumberField({ defaultValue: 0 }),
defaultValue: [10, 20, 30],
}),
}
}
`$3
`typescript
class UserProfile extends Model {
static fields = {
id: new NumberField({}),
preferences: new TupleField({
fields: {
name: new StringField({ defaultValue: '' }),
favoriteNumbers: new ArrayField({
elementType: new NumberField({ defaultValue: 0 }),
defaultValue: [1, 2, 3],
}),
},
}),
}
}
`$3
`typescript
type UserMaterialized = {
userName: string
}class User extends Model {
static fields: FieldsOf = {
id: new NumberField({}),
name: new StringField({}),
email: new StringField({}),
// Materialized field that concatenates name and email
userName: new StringField({
expression: "concat(name, ' ', email)"
})
}
}
`Migrations
$3
Thunder Schema provides a robust migration system to manage database schema changes. The migration system helps you version control your database schema and apply changes in a controlled manner.
$3
#### Generate Migrations
`bash
npx thunder-schema makemigrations [output-path]
`#### List Migrations
`bash
npx thunder-schema readmigrations [migrations-path]
`#### Apply Migrations
`bash
npx thunder-schema migrate [migrations-path]
`$3
The migration system supports several types of schema changes:
1. CREATE: Creating new tables
2. UPDATE: Modifying existing tables by:
- Adding new columns
- Removing columns
- Updating column definitions
3. DROP: Dropping tables
#### Migration File Examples
Here are some examples of how to use migrations in your project:
$3
`typescript
import { NumberField, StringField } from 'thunder-schema'
import { FieldsOf, TableDefinition } from 'thunder-schema'
import { Model } from 'thunder-schema'type UserSchema = {
id: number
name: string
email: string
createdAt: number
updatedAt: number
deletedAt: number
}
class User extends Model {
static fields: FieldsOf = {
id: new NumberField({}),
name: new StringField({}),
email: new StringField({}),
createdAt: new NumberField({}),
updatedAt: new NumberField({}),
deletedAt: new NumberField({}),
}
static tableDefinition: TableDefinition = {
tableName: 'users',
engine: 'MergeTree',
orderBy: ['createdAt'],
}
}
type PostSchema = {
id: string
userId: string
title: string
content: string
createdAt: number
updatedAt: number
}
class Post extends Model {
static fields: FieldsOf = {
id: new StringField({}),
userId: new StringField({}),
title: new StringField({}),
content: new StringField({}),
createdAt: new NumberField({}),
updatedAt: new NumberField({}),
}
static tableDefinition: TableDefinition = {
tableName: 'posts',
engine: 'MergeTree',
orderBy: ['createdAt'],
}
}
const models: (typeof Model)[] = [User, Post]
export default models
`$3
Here are examples of different migration scenarios and their corresponding TypeScript migration files:
#### 1. Creating a New Table
When you first define a model, the migration system will generate a migration to create the corresponding table:
`typescript
// Migration file: 1678901234567-create_users_table.ts
export const diff = [
{
changes: {
type: 'CREATE',
schema: {
tableName: 'users',
columns: [
{ name: 'id', type: 'Int32' },
{ name: 'name', type: 'String' },
{ name: 'email', type: 'String' },
{ name: 'createdAt', type: 'Int64', defaultValue: 'now()' },
{ name: 'updatedAt', type: 'Int64' },
{ name: 'deletedAt', type: 'Int64' }
],
engine: 'MergeTree',
orderBy: ['createdAt']
}
}
}
]
`#### 2. Adding New Columns
When you add new fields to your model, the migration system will generate a migration to add them to your table:
`typescript
// Migration file: 1678901245678-add_user_fields.ts
export const diff = [
{
changes: {
type: 'UPDATE',
tableName: 'users',
add: [
{ name: 'lastName', type: 'String' },
{ name: 'age', type: 'Int32' }
]
}
}
]
`#### 3. Removing Columns
When you remove fields from your model, the migration system generates a migration to remove them from your table:
`typescript
// Migration file: 1678901256789-remove_user_fields.ts
export const diff = [
{
changes: {
type: 'UPDATE',
tableName: 'users',
remove: ['deletedAt', 'isActive']
}
}
]
`#### 4. Updating Column Definitions
When you change a field's properties (like type or default value), the migration system generates a migration to update the column definition:
`typescript
// Migration file: 1678901267890-update_email_field.ts
export const diff = [
{
changes: {
type: 'UPDATE',
tableName: 'users',
update: [
{
name: 'email',
type: 'String',
expression: "lower(email)"
}
]
}
}
]
`#### 5. Creating a Table with Advanced Features
Here's an example of a more complex table creation with materialized columns, default values, and partitioning:
`typescript
// Migration file: 1678901278901-create_orders_table.ts
export const diff = [
{
changes: {
type: 'CREATE',
schema: {
tableName: 'orders',
columns: [
{ name: 'id', type: 'String' },
{ name: 'userId', type: 'String' },
{ name: 'productId', type: 'String' },
{ name: 'quantity', type: 'Int32', defaultValue: '1' },
{ name: 'price', type: 'Decimal(10, 2)' },
{
name: 'totalPrice',
type: 'Decimal(10, 2)',
expression: 'quantity * price'
},
{ name: 'orderDate', type: 'DateTime', defaultValue: 'now()' },
{ name: 'status', type: 'String', defaultValue: "'pending'" }
],
engine: 'MergeTree',
orderBy: ['orderDate'],
partitionBy: 'toYYYYMM(orderDate)',
primaryKey: ['id']
}
}
}
]
`#### 6. Dropping a Table
When you remove a model altogether, the migration system can generate a migration to drop the table:
`typescript
// Migration file: 1678901289012-drop_old_table.ts
export const diff = [
{
changes: {
type: 'DROP',
schema: {
tableName: 'old_users'
}
}
}
]
`#### 7. Complex Multi-Table Migration
You can also have migrations that affect multiple tables in one go:
`typescript
// Migration file: 1678901299123-complex_migration.ts
export const diff = [
{
// Create a new table
changes: {
type: 'CREATE',
schema: {
tableName: 'products',
columns: [
{ name: 'id', type: 'String' },
{ name: 'name', type: 'String' },
{ name: 'price', type: 'Decimal(10, 2)' },
{ name: 'createdAt', type: 'DateTime', defaultValue: 'now()' }
],
engine: 'MergeTree',
orderBy: ['id']
}
}
},
{
// Update an existing table
changes: {
type: 'UPDATE',
tableName: 'orders',
add: [
{ name: 'discountCode', type: 'String', nullable: true }
],
update: [
{
name: 'totalPrice',
type: 'Decimal(10, 2)',
expression: 'price quantity (1 - if(discountCode != \'\', 0.1, 0))'
}
]
}
}
]
`$3
1. Define your initial models
2. Generate the first migration:
`bash
npx thunder-schema makemigrations -m src/models.ts -o migrations
`
3. Apply the migration:
`bash
npx thunder-schema migrate --migrations-path migrations
`
4. When you need to make changes to your schema:
- Update your model definitions
- Generate a new migration
- Apply the new migrationThe migration system automatically tracks which migrations have been applied using a
migrations table in your database and ensures migrations are applied in the correct order.$3
The CLI tool uses the following environment variables for database connection:
-
CLICKHOUSE_URL: ClickHouse server URL (default: 'http://localhost:8123')
- CLICKHOUSE_USERNAME: ClickHouse username (default: 'default')
- CLICKHOUSE_PASSWORD: ClickHouse password (default: '')
- CLICKHOUSE_DATABASE: ClickHouse database name (default: 'default')Example:
`bash
export CLICKHOUSE_URL=http://localhost:8123
export CLICKHOUSE_USERNAME=default
export CLICKHOUSE_PASSWORD=password
export CLICKHOUSE_DATABASE=my_database
npx thunder-schema migrate
`See more about it by running:
`bash
npx thunder-schema --help
`Advanced Features
$3
`typescript
const config: ConnectionConfig = {
credentials: {
url: 'http://localhost:8123',
username: 'default',
password: '',
database: 'default'
},
options: {
keepAlive: true,
}
}
`
$3
#### Nested Arrays
`typescript
class Matrix extends Model {
static fields = {
id: new NumberField({}),
data: new ArrayField({
elementType: new ArrayField({
elementType: new NumberField({ defaultValue: 0 }),
defaultValue: [],
}),
defaultValue: [[1, 2], [3, 4]],
}),
}
}
`#### Tuple Filtering
`typescript
// Filter on nested tuple fields
const query = locationModel.objects.filter({
location: {
coordinates: {
lat__gt: 40.0,
lon__lt: -73.0
},
}
})
`$3
`typescript
// Inspect the generated SQL query
const query = User.objects
.filter({ isActive: true })
.project(['id', 'name'])
.getQuery()console.log(query) // SELECT id, name FROM users WHERE (isActive = true)
// Reset query conditions
query.reset()
`Array Fields and Nested Structures
The ORM supports array fields and nested structures like arrays within tuples. Here's how to use them:
#### Basic Array Fields
`typescript
class Product extends Model {
static fields = {
id: new NumberField({}),
tags: new ArrayField({
elementType: new StringField({ defaultValue: '' }),
defaultValue: ['new', 'featured'],
}),
prices: new ArrayField({
elementType: new NumberField({ defaultValue: 0 }),
defaultValue: [10, 20, 30],
}),
}
}
`#### Nested Arrays
You can create arrays of arrays for more complex data structures:
`typescript
class Matrix extends Model {
static fields = {
id: new NumberField({}),
data: new ArrayField({
elementType: new ArrayField({
elementType: new NumberField({ defaultValue: 0 }),
defaultValue: [],
}),
defaultValue: [[1, 2], [3, 4]],
}),
}
}
`#### Tuples with Arrays
You can combine tuples and arrays for complex nested structures:
`typescript
class UserProfile extends Model {
static fields = {
id: new NumberField({}),
preferences: new TupleField({
fields: {
name: new StringField({ defaultValue: '' }),
favoriteNumbers: new ArrayField({
elementType: new NumberField({ defaultValue: 0 }),
defaultValue: [1, 2, 3],
}),
},
}),
}
}
`The above will generate a schema with a tuple field containing a string and an array of numbers:
`sql
Tuple(name String, favoriteNumbers Array(Int32))
`#### Tuple Filtering
You can filter on tuple fields using dot notation to access nested fields. Here are some examples:
`typescript
// Filter on a simple tuple field
class AddressModel extends Model {
static fields = {
id: new NumberField({}),
address: new TupleField({
fields: {
street: new StringField({ defaultValue: '' }),
city: new StringField({ defaultValue: '' }),
zip: new NumberField({ defaultValue: 0 }),
},
}),
}
}// Filter by city
const query = addressModel.objects.filter({
'address.city': 'New York'
})
// Filter with comparison operators
const query = addressModel.objects.filter({
address: {
zip__gt: 10000,
}
})
// Filter on nested tuples
class LocationModel extends Model {
static fields = {
id: new NumberField({}),
location: new TupleField({
fields: {
coordinates: new TupleField({
fields: {
lat: new NumberField({ defaultValue: 0 }),
lon: new NumberField({ defaultValue: 0 }),
},
}),
name: new StringField({ defaultValue: '' }),
},
}),
}
}
// Filter on nested tuple fields
const locationModel = new LocationModel()
const query = locationModel.objects.filter({
location: {
coordinates: {
lat__gt: 40.0,
lon__lt: -73.0
},
}
})
// Filter with multiple conditions
const query = locationModel.objects.filter(
new Q().and([
{ location: { coordinates: { lat__gt: 40.0 } } },
{ location: { name__icontains: 'Central' } }
])
)
`The above queries will generate SQL like:
`sql
-- Simple tuple filter
SELECT * FROM address_model WHERE address.city = 'New York'-- Nested tuple filter
SELECT * FROM location_model WHERE location.coordinates.lat > 40.0 AND location.coordinates.lon < -73.0
`#### Default Values
Array fields support default values at both the array and element level:
`typescript
new ArrayField({
elementType: new StringField({ defaultValue: 'default' }), // Element default
defaultValue: ['value1', 'value2'], // Array default
})
`API Reference
$3
-
Model: Base class for all models
- NumberField: Field type for numeric values
- StringField: Field type for string values
- TableDefinition: Interface for table configuration$3
-
ConnectionManager: Manages database connections
- ConnectionConfig: Type for connection configuration
- ConnectionCredentials: Type for connection credentials$3
-
QueryBuilder: Build complex SQL queries
- Q: Class for building query conditions$3
-
MigrationService: Create and manage migrations
- MigrationRunner`: Execute migrationsContributions are welcome! Please feel free to submit a Pull Request.