clickhouse-sql-builder

ClickHouse Query Builder

Quick Start

bun i clickhouse-sql-builder

Notes

• This is an early-stage concept—integrate with caution.
• It's already powering the analytics behind the short URL feature on u301.com.
• Released as an open-source internal utility by the U301 URL Shortener team.

Problems with Using the ClickHouse JS SDK

• In the JavaScript ecosystem, ORM support for ClickHouse is minimal, and trying to force it into an ORM doesn’t make much sense, especially given how rigid schemas and complex queries are in ClickHouse.
• The native ClickHouse JS SDK is hard to use. you often end up manually concatenating SQL strings.
• This becomes especially painful when dealing with optional filters, where query readability quickly degrades.

To address this, I created a minimal ClickHouse Query Builder with a few guiding principles:

• It should focus purely on SQL string composition—no custom syntax or third-party dependencies. You can pass in raw SQL strings as needed.
• It should add lightweight support for parameter binding in the ClickHouse JS SDK to help prevent SQL injection.
• It should be designed to feel intuitive.
• It should leverage TypeScript's type system to simplify building SQL queries.

Example

import { createClient } from '@clickhouse/client'
import { ClickHouseSqlBuilder } from 'clickhouse-query-builder'
const builder = ClickHouseSqlBuilder
    .select([
        'count(*) AS click',
        'toDate(access_date, :timezone) date'
    ])
    .from('analyze_short_url_visit')
    .where('workspace_id=:workspaceId')
    .whereBetween('access_date', ':startDatetime', ':endDatetime')
    .groupBy('date')
    .orderBy('date', 'ASC')
    .withFill({
        from: 'toDate(:startDatetime, :timezone)',
        to: 'toDate(:endDatetime, :timezone) + INTERVAL 1 DAY',
        step: `INTERVAL 1 DAY`
    })

// Add optional conditions
const shortLink = 'u301.co/try'
if(shortLink) {
    builder.and(shortLink)
}

// Bind Params
builder.addParam('String', {
    workspaceId: '1',
    timezone: 'Europe/London',
    shortLink
})
builder.addParam('DateTime64', {
    startDatetime: new Date('2025-06-17 00:00:00'),
    endDatetime: new Date('2025-07-17 23:59:59')
})

const client = createClient({
    url: 'http://localhost:8123/default',
    compression: {
        response: true,
        request: true
    },
})

const result = await client.query({
    query: builder.toSql(),
    query_params: builder.queryParams(),
    format: 'JSONEachRow',
})

The resulting SQL query looks like this:

SELECT count(*) AS click, toDate(access_date, :timezone) date 
	FROM analyze_short_url_visit 
	WHERE workspace_id=:workspaceId AND access_date BETWEEN :startDatetime AND :endDatetime 
	GROUP BY date 
	ORDER BY date ASC 
	WITH FILL FROM toDate(:startDatetime, :timezone) TO toDate(:endDatetime, :timezone) + INTERVAL 1 DAY STEP INTERVAL 1 DAY

License

The MIT License