DEV Community: Luke

The Analytics Language Layer: Why Real-Time Data Needs Typed APIs, Not Just Faster Databases

Luke — Sat, 07 Mar 2026 20:54:48 +0000

We’ve made our databases real-time. We haven’t made our analytics interfaces real-time-safe. The missing abstraction between your analytics DB and your consumers is a typed, programmable analytics language layer.

ClickHouse can ingest a billion rows per second and return aggregations across terabytes in milliseconds. The storage problem is solved. The execution problem is solved. But the interface problem, how consumers actually talk to the engine, remains stuck in the era of hand-crafted SQL strings, copy-pasted metric definitions, and dashboards that nobody trusts.

This gap didn’t matter much when the consumer was a human analyst writing a query in a notebook. It matters enormously now that the consumer is increasingly a service, a background job, an embedded dashboard, or an AI agent. The weakest link in the modern analytics stack isn’t the database. It’s the language we use to talk to it.

When Your User Is a Model, SQL Becomes a Liability

The push toward AI-driven analytics has exposed a fundamental fragility in text-to-SQL approaches.

Spider 2.0, released in late 2024 with enterprise-level complexity (3,000+ columns, multiple SQL dialects), showed even the best models solving only 17% of queries. The BIRD-Interact benchmark, which simulates real interactive analytics sessions, reports a best-case success rate of 16%. Uber built an internal text-to-SQL system and found only 50% overlap with ground truth on their own evaluation set.

The failure modes are what make this genuinely dangerous. An analysis of 50,000+ production LLM-generated queries found that most broken queries execute successfully and return data, they’re semantically wrong but syntactically valid. The model hallucinates columns that don’t exist, picks wrong join paths, applies incorrect aggregation logic, or silently drops required filters. You get a clean DataFrame back. The numbers just happen to be wrong.

The evidence for a different approach is already in. Snowflake’s internal tests show query accuracy jumping from 40% to 85% when LLMs are routed through a semantic layer instead of raw SQL. DataBrain reports accuracy going from roughly 55% to over 90% with semantic context. dbt Labs reported at Coalesce 2025 that their semantic layer achieved 83% accuracy on natural language analytics questions, with several categories at 100%. The pattern is clear: constrain what the model can express, and accuracy improves dramatically.

The fix isn’t better documentation or more careful code review. It’s making the interface itself safe by default. Type-safe query builders. Pre-declared metrics and datasets. Schema-aware tooling that catches errors at compile time, not at query time. The system should enforce correctness structurally, not rely on the discipline of the person or model writing the query.

Defining the Analytics Language Layer

What’s needed is an abstraction that doesn’t exist cleanly in the current tooling landscape. Call it an analytics language layer: a typed, programmable, stable API for a company’s metrics and queries that everything else plugs into.

It’s not an ORM — those map objects to rows and optimise for CRUD. It’s not a BI tool — those own the visualisation and assume human consumers. It’s not a raw query builder — those give you flexibility without constraints. The analytics language layer sits between the database and all its consumers, providing a contract that is:

Type-safe and schema-aware. Column references, filter expressions, and aggregation logic are validated at compile time. If the schema changes, your build breaks before your dashboards do.

Versioned and evolvable. Metrics and query definitions are first-class code constructs with version history, review workflows, and the ability to deprecate gracefully. You can evolve your analytics API the same way you’d evolve a public REST API.

Multi-protocol. The same definitions are consumable by backend services, React components, CLI tools, AI agent toolchains, traditional BI. The metric definition is written once; the consumption pattern varies.

This is the layer that platform teams at scale end up building internally, whether they call it a semantic layer, a metrics catalog, a query translation engine, or something else entirely. The pattern is universal because the problem is universal.

Seven Predictions for the Next Three Years

The analytics language layer isn’t a speculative concept. The convergence is already visible. Here’s where this heads:

Every serious ClickHouse deployment will have a dedicated analytics language layer sitting between the database and its consumers. The alternative is linear growth in platform engineering headcount as consumer count increases.
AI agents will talk to semantic layers, not databases. The MCP server approach of exposing raw SQL to agents will mature into structured tool interfaces where agents invoke named, typed queries rather than generating SQL strings. The accuracy data demands it.
BI tools will consume typed endpoints. Rather than authoring raw SQL or maintaining their own query logic, BI tools will connect to analytics language layers the same way frontend applications consume REST or GraphQL APIs. The Open Semantic Interchange initiative (dbt, Snowflake, Salesforce, ThoughtSpot) is an early signal of this convergence.
Metric definitions will be code, not configuration. YAML-based metric definitions will give way to programmatic definitions in the same language as the application (TypeScript, Python), enabling IDE support, testing, and the same CI/CD workflows used for application code.
Schema drift will become a build failure, not a production incident. Type-safe analytics layers will catch breaking schema changes at compile time. The “we renamed a column and three dashboards broke” class of incident will go the way of runtime type errors in typed languages — still possible, but structurally discouraged.
The semantic layer market will consolidate around API-first architectures. Gartner’s concept of “composable analytics” — modular, API-first business components — will define the winning pattern. Tools that can’t serve their semantics via APIs will lose ground to those that can.
Real-time and batch semantic layers will merge. The artificial divide between dbt (batch) and Cube/hypequery (real-time) will collapse as the analytics language layer becomes the single interface regardless of freshness. The layer’s job is to provide safe access; the underlying engine handles the latency profile.

The Challenge

If you’re running ClickHouse in production and your consumers are still writing raw SQL strings to query it, you’ve solved the hard problem (making the database fast) and left the easy problem unsolved (making it safe to talk to).

The analytics language layer is the missing piece. Not because the database needs help, but because every consumer that touches it does. The organisations that adopt this abstraction early will ship fewer data incidents, onboard new teams faster, and safely expose real-time analytics to more products and agents than their competitors.

We’ve spent a decade making databases real-time. It’s time to make the interfaces real-time-safe.

We’re solving this problem at hypequery, you can check out the project on GitHub.

Seven Companies, One Pattern: Why Every Scaled ClickHouse Deployment Looks the Same

Luke — Thu, 01 Jan 2026 17:36:40 +0000

Uber, Cloudflare, Instacart, GitLab, Lyft, Microsoft, and Contentsquare share more than ClickHouse adoption. Each independently built the same abstraction stack—query translation engines, semantic layers, and self service interfaces that let thousands of people query without reading a line of raw ClickHouse SQL. Different industries, different workloads, identical pattern.

This convergence is not stylistic. It is the direct consequence of optimising ClickHouse for speed. Every schema level optimisation that squeezes out milliseconds also adds cognitive overhead that analysts should not carry. Platform teams end up absorbing that complexity into infrastructure because it is the only way to scale.

The Architecture Everyone Arrives At

Every scaled deployment eventually looks like this:

+-------------------------------------------------------------+
|  SELF SERVICE LAYER                                         |
|  Superset, Grafana, Kibana, internal UIs                    |
+------------------------------+------------------------------+
                               |
+------------------------------v------------------------------+
|  SEMANTIC LAYER                                             |
|  GraphQL schemas, YAML configs, DSLs, metrics catalogs      |
+------------------------------+------------------------------+
                               |
+------------------------------v------------------------------+
|  QUERY TRANSLATION LAYER                                    |
|  QueryBridge, ABR router, AST optimisers, HogQL compilers   |
+------------------------------+------------------------------+
                               |
+------------------------------v------------------------------+
|  CLICKHOUSE                                                 |
|  Optimised schemas, materialised views, array based storage |
+-------------------------------------------------------------+

The tooling changes; Uber leans on Elasticsearch compatible query shapes, Cloudflare wraps everything in GraphQL, Lyft writes TOML while Instacart prefers YAML—but the roles never do.

The Root Cause: Performance Forces Complexity

The explanation is not that ClickHouse SQL is "hard." The explanation is that performant ClickHouse schemas are deliberately complex, so teams build veils between the raw schema and the consumers who only need governed, high level access.

Uber learned only 5% of indexed fields were queried. Their fix—array based key value storage with runtime type resolution—removed schema conflicts but made direct SQL unusable for most users.
Contentsquare's AST optimiser propagates partition keys, merges subqueries, and simplifies algebra before emitting SQL. Ten fold speedups materialised, but only after they asked users to describe queries as ASTs rather than SQL strings.
Instacart orders fraud detection tables by (shopper_id, created_at_ts) so partitions prune hundreds of millions of rows. Asking every analyst to reason about sort key permutations does not scale.

Every win on the query planner side adds new rules about materialised columns, adaptive indexing, or array pairs. The abstraction layer hides those rules so performance gains are preserved without retraining an entire company.

What Each Company Built

Company	Translation Layer	Semantic Layer	Key Outcome
Uber	QueryBridge (ES → SQL)	Schema metadata service	10,000+ Kibana dashboards migrated unchanged
Cloudflare	ABR router	GraphQL analytics API	Consistent response times at any data volume
Instacart	Yoda feature system	YAML feature definitions	Engineers shifted from 80% → 20% time on heuristics
Microsoft	Titan query service	Visual query builder	2,500 non engineers run 100K queries per day
Contentsquare	AST optimiser	Query representation layer	10× speedup on the slowest 5% of queries
GitLab	ClickHouse::Client	Arel based QueryBuilder	Sub second queries across 100M row datasets
Lyft	Dynamic transpiler	TOML configurations	8× cost reduction versus their previous Druid stack

Seven different teams built the identical stack because there is no alternative architecture that survives petabyte scale self service.

Economics Drive the Convergence

Abstraction layers are an economic decision, not just architectural taste. Without them, each new analyst adds a stream of Jira tickets for query triage and optimisation, so platform headcount grows linearly with the number of people asking questions. With them, platform headcount grows with data volume and infrastructure complexity instead.

Market data echoes this: self service analytics is compounding at ~15% CAGR from $4.8B in 2024 to a projected $17.5B by 2033. Organisations fund these layers because they change cost curves.

Microsoft's Titan team proves the point. A single platform group serves 2,500+ monthly active users executing 100,000 queries daily. Without the abstraction layer, supporting that workload would demand an army of embedded data engineers.

Reported ROI numbers are similarly blunt:

40–60% fewer inbound IT data requests
30% faster decision making cycles
50%+ faster analysis completion
30%+ template reuse as platform effects accumulate

These are step changes in organisational throughput, not marginal “quality of life” upgrades.

Organisational Transformation

Abstractions collapse the feedback loop between “I spotted a pattern” and “I validated it.”

Before abstraction: Analyst notices a signal → files a ticket → engineer writes and hardens a query → someone reviews → results show up days later.

After abstraction: Analyst encodes logic in a config or UI → platform translates → results land the same day.

Instacart quantified it: before the Yoda platform, engineers spent 80% of their time writing and maintaining fraud heuristics. After YAML based feature definitions on top of ClickHouse, that dropped to 20%. Engineers now work on model quality, not query babysitting.

Uber's QueryBridge migration preserved 10,000+ Kibana dashboards with zero user retraining. Lyft's interfaces let “any person… even an exec that's able to write SQL” touch real time datasets through Trino without ever seeing ClickHouse.

The platform team absorbs complexity once; the organisation scales without proportional engineering headcount.

Why These Abstractions Become Moats

Once embedded, these layers create natural lock in—not through dark patterns, but through workflow familiarity. Uber's Kibana dashboards represent years of institutional memory. PostHog's HogQL becomes communal language. GitLab's Arel based builders teach analysts GitLab specific conventions. Instacart's YAML definitions capture historical fraud heuristics.

Retraining humans, migrating saved queries, and rebuilding dashboards is far harder than swapping databases. The abstraction layer becomes an asset that justifies further investment.

AI Is Joining the Stack, Not Sitting on Top

The newest twist is treating AI as native infrastructure inside this stack.

ClickHouse 25.7 shipped text to SQL generation directly inside the CLI, while ClickHouse Cloud bakes ClickHouse.ai into the workflow with awareness of live schemas and dashboards.
Shopify wired 30+ MCP (Model Context Protocol) servers into LibreChat so employees issue natural language questions that resolve into governed queries.
Lightdash combines dbt defined metrics with AI agents that generate chart ready outputs from plain English while respecting semantic layer rules.

The canonical four tier stack now looks like: natural language → semantic layer → query translation → ClickHouse. 2024 was the experiment phase; 2025 onward is standardisation.

The Insight: Architecture Follows Economics

Seven companies. Billions of events per second. Hundreds of petabytes. Hundreds of thousands of daily queries. Every one of them discovered that ClickHouse performance only democratises insight when the complexity is quarantined behind abstractions.

The pattern holds because:

Raw ClickHouse SQL grows unwieldy when schemas are tuned for petabyte performance.
Platform teams can internalise that complexity once and unlock sublinear scaling.
Business users expect intuitive interfaces, not database literacy.
Organisational stickiness forms around tools, saved dashboards, and shared semantics.
AI assisted workflows need governed semantic layers as context.

The lesson is not purely technical. It is organisational: platform teams absorb complexity into infrastructure so analytical capability scales with data volume rather than engineering headcount. That is why seven companies built the same thing, why the market compounds at 15% annually, and why AI is being wired into the stack itself.

Want to learn more about building production-ready ClickHouse applications with hypequery? Check out our guide on getting started with ClickHouse and TypeScript.

Sources: Engineering blogs from Uber, Cloudflare, Instacart, GitLab, Lyft, Microsoft, Contentsquare, and PostHog; ClickHouse case studies; market data from Mordor Intelligence and Grand View Research.

Type-Safe Schema Management & Evolution in ClickHouse: Keeping Analytics in Sync

Luke — Thu, 17 Jul 2025 05:15:43 +0000

Modern analytics applications rely on ClickHouse for its speed and scalability, but as team structures and business needs evolve, so too must your schema. Uncontrolled schema changes known as schema drift - can silently break applications or degrade performance, especially when teams manage both application and analytics codebases in strongly typed languages like TypeScript.

In this comprehensive guide, we’ll look at tools such as hypequery and explore best practices to solve the schema evolution challenge by providing automated type generation, compile-time safety, and streamlined workflows that keep your analytics applications resilient as your data grows.

The Schema Evolution Challenge

What is Schema Drift?

Schema drift occurs when the actual database schema deviates from what your application expects. This happens frequently in analytics environments where:

New business requirements demand additional data fields
Data sources evolve and require schema adjustments
Multiple teams modify the same database independently
Hot fixes are applied directly to production without proper documentation

The Pain Points of Traditional Approaches

Most TypeScript applications connecting to ClickHouse suffer from these common problems:

Manual Type Definitions: Developers manually create and maintain TypeScript interfaces that mirror their database schema. This is tedious and error-prone.

// Manual types - prone to drift
interface UserAnalytics {
  user_id: string;
  session_count: number;
  last_seen: Date;
  // Did someone add a new column? You'll find out at runtime...
}

Runtime Errors: Schema changes often go unnoticed until queries fail in production, causing downtime and data inconsistencies.

Raw SQL Strings: Without type safety, SQL queries are written as strings, making them vulnerable to typos and SQL injection attacks

// Raw SQL - no type safety
const query = `
  SELECT user_id, sesion_count, last_seen  -- Typo in 'session_count'
  FROM user_analytics 
  WHERE non_existent_column = ?  -- This will fail at runtime
`;

Slow Development Cycles: Developers must manually check and update types whenever the schema changes, slowing down feature development.

Principles of Effective Schema Design

Model for Your Query Patterns

Start by understanding your most frequent analytics queries. Design tables, columns, and data types to support them.

Denormalise for read efficiency: wide tables and pre-aggregated metrics are preferred over excessive joins134.

Use appropriate table engines:

MergeTree for most cases.
ReplicatedMergeTree for HA clusters.
ReplacingMergeTree, CollapsingMergeTree, AggregatingMergeTree for special use cases

Partitioning and Ordering

Partition tables for efficient batch deletes e.g. by month or day.

Choose an order key to support fast range queries e.g. (user_id, timestamp)

Aim for partitions of 100MB–1GB for best balance between metadata and performance

Use LowCardinality(String) for enumerations, Decimal for currency, and DateTime64 where needed

Pillars of Schema Evolution Best Practices

Schema Change Automation: The Foundations

Change-based migration tools (e.g., Houseplant, Goose) use explicit, ordered scripts (e.g., SQL, YAML) to apply and track every schema modification. Each migration script represents a single change, and migrations are executed in sequence.

State-based tools (e.g., Atlas, Bytebase) start with a “desired state” schema definition. They automatically detect any difference between the application’s expectation and the current database, then plan and apply the necessary migration scripts, minimising drift and enforcing policy checks

Version Control Integration: All migrations and schema definitions should be committed to source control for traceability and collaboration.

Automating Schema Changes & Type Generation

For strongly-typed application layers (TypeScript, Go, Java, etc.), automated type generation tools ensure that application code and database schema remain in lockstep.

hypequery is a notable tool for TypeScript and ClickHouse users: it introspects your live database schema and emits up-to-date, type-safe interfaces that application code can trust at compile time.

When schema changes are applied (via any migration tool), hypequery regenerates TypeScript types based on your current ClickHouse schema.
If drift is detected (such as a missing column or type change), the next type-check or build will fail, catching mismatches before they reach production.

Additive-First (Expand–Migrate–Contract) Pattern

Favour ADD COLUMN with defaults over destructive DROP or renames.
When renaming, add the new column, copy/backfill data, switch reads, then safely drop the old column after a grace period (“expand–migrate–contract”).

Testing and Observability

Assert that queries work with current schema via integration tests before deployment.

Monitor outstanding mutations:

sql
SELECT database, table, parts_to_do FROM system.mutations WHERE is_done = 0;

Observe system tables (system.mutations, system.replication_queue, system.query_log) for DDL lag and slow queries.

Governance and Change Review

Require schema migration scripts (or generated diffs) to be code-reviewed alongside application changes.

Assign change owners and maintain a schema changelog documenting the rationale and business impact.

Zero-Downtime Deployment Techniques

Use ADD COLUMN for online DDL.

Schedule mutative operations (DROP, MODIFY) during low-traffic windows; coordinate on clusters to avoid replication lag.

For critical fields like partition or order keys, create a new table, backfill, then swap aliases.

Example Modern CI/CD Pipeline

Apply Migrations: Run schema migration tool (Houseplant, Goose, Atlas, Bytebase) to update database schema, typically in a controlled staging environment.

Regenerate Types: Use hypequery (or a similar tool in your language) to generate or refresh application type definitions from live database schema.

Schema Drift Check: Automate a git diff or similar check — if the generated type models have changed, require a review or halt the pipeline until resolved.

Compile / Type-Check: Run type checking on application code; build fails on any schema mismatch.

Test & Deploy: Proceed only when all checks pass.

Conclusion

Discipline in schema management is foundational for rapidly scaling, reliable analytics on ClickHouse. While tooling (migration frameworks, type generators, managed platforms) eases much of the operational load, the enduring best practices remain: automate every aspect of schema change, design for additive evolution, continuously test, and rigorously monitor. This approach enables teams to move swiftly, minimise drift, and deliver resilient analytical applications, regardless of team size or infrastructure maturity.

Whether you favour change-based scripts, state-driven declarative management, or full-featured platforms, the key is to match your workflow to team needs — and standardise it. Treating schema as code, embedding drift detection in your pipelines, and leveraging ClickHouse’s system observability will future-proof your analytics infrastructure as business and data evolve.

Want to learn more about building production-ready ClickHouse applications with hypequery? Check out our guide on getting started with ClickHouse and TypeScript.

Building Dashboards on ClickHouse with hypequery and Next.js

Luke — Sat, 12 Jul 2025 19:52:24 +0000

Modern analytics applications demand both real-time insights and developer productivity. If you’re building dashboards on ClickHouse with TypeScript, you’ve likely run into the pain of raw SQL strings, manual type definitions, and the challenges of interactive, scalable data apps. In this guide, we’ll show you how to solve these problems using hypequery — a TypeScript-first, type-safe query builder for ClickHouse — alongside Next.js, the leading React framework.

You can check hypequery out on github.

Why ClickHouse + TypeScript for Dashboards?

ClickHouse is an industry-leading analytical database, purpose-built for high-performance, real-time analytics. TypeScript, meanwhile, brings type safety and modern tooling to JavaScript development. But connecting the two has historically meant sacrificing type safety, maintainability, or both.

hypequery bridges this gap by providing:
A fully type-safe query builder tailored for ClickHouse
Autocomplete and type validation for every part of your query
Features designed for dashboard development: cross-filtering, streaming, pagination, and more

The Challenge

Real-Time Dashboards Without the Pain
Traditional approaches to dashboard development with ClickHouse and TypeScript are fraught with issues:

Raw SQL strings: Prone to runtime errors and SQL injection
Manual type definitions: Tedious to maintain and easily out of sync
Complex query management: Difficult to build interactive, cross-filtered dashboards
Handling large result sets: Risk of memory issues and slow performance

The Solution: hypequery

hypequery is a TypeScript SDK and query builder for ClickHouse, designed specifically for real-time, type-safe analytics dashboards. Its core features include:

Complete Type Safety: Every query is type-checked, from table and column names to filter values and result types. Get autocomplete and instant feedback in your IDE
Flexible Join System: Fluent API for all join types, with support for custom relationships and smart aliasing
SQL Expressions & Functions: Use ClickHouse-specific functions and raw SQL expressions while maintaining type safety
Streaming Support: Efficiently process large result sets with streaming, ideal for real-time dashboards and monitoring
Advanced Filtering: Build complex, reusable filters and apply cross-filtering logic across multiple queries
Comprehensive Logging: Track queries, parameters, execution times, and row counts for observability = Schema Generation: CLI tool to generate TypeScript types directly from your ClickHouse schema, keeping your types in sync

Getting Started: Setting Up hypequery with Next.js

1. Install Dependencies

npm install @hypequery/clickhouse @clickhouse/client
npm install next react react-dom

2. Generate TypeScript Types from Your ClickHouse Schema

Use the CLI to introspect your schema and generate types:

npx hypequery-generate --host your-clickhouse-host --database your-database

This creates a generated-schema.ts file you’ll import in your app.

3. Initialise the Query Builder and build a quer

import { createQueryBuilder } from '@hypequery/clickhouse';
import type { IntrospectedSchema } from './generated-schema';

const db = createQueryBuilder<IntrospectedSchema>({
  host: 'your-clickhouse-host',
  username: 'default',
  password: '',
  database: 'default'
});

// Example: Fetch top 10 recent trips with fares over $50
const results = await db
  .table('trips')
  .select(['pickup_datetime', 'dropoff_datetime', 'total_amount'])
  .where('total_amount', '>', 50)
  .orderBy('pickup_datetime', 'DESC')
  .limit(10)
  .execute();

Column names are type-checked, typos or missing columns are caught at compile time.
Query results are fully typed, no more any or manual casting

4. Cross-Filtering for Interactive Dashboards

Interactive dashboards often need to synchronise filters across multiple queries. hypequery’s CrossFilter makes this easy:

import { CrossFilter } from '@hypequery/clickhouse';

const filter = new CrossFilter()
  .add({ column: 'pickup_datetime', operator: 'gte', value: '2024-01-01' })
  .add({ column: 'total_amount', operator: 'gt', value: 20 });

const trips = await db.table('trips').applyCrossFilters(filter).execute();
const drivers = await db.table('drivers').applyCrossFilters(filter).execute();

5. Integrate with Next.js API Routes

In your Next.js app, create an API route that fetches data using hypequery:

// pages/api/trips.ts
import type { NextApiRequest, NextApiResponse } from 'next';
import { db } from '../../lib/db'; // your hypequery instance

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  const data = await db.table('trips')
    .select(['pickup_datetime', 'total_amount'])
    .orderBy('pickup_datetime', 'DESC')
    .limit(10)
    .execute();
  res.status(200).json(data);
}

Type safety is preserved from the database to your frontend.

8. Display Data in Your Next.js Dashboard

Use React components to fetch and display the data:

// components/TripsTable.tsx
import useSWR from 'swr';

const fetcher = (url: string) => fetch(url).then(res => res.json());
export default function TripsTable() {
  const { data, error } = useSWR('/api/trips', fetcher);
  if (error) return <div>Error loading data</div>;
  if (!data) return <div>Loading...</div>;
  return (
    <table>
      <thead>
        <tr>
          <th>Pickup</th>
          <th>Dropoff</th>
          <th>Total Amount</th>
        </tr>
      </thead>
      <tbody>
        {data.map((trip: any) => (
          <tr key={trip.pickup_datetime}>
            <td>{trip.pickup_datetime}</td>
            <td>{trip.dropoff_datetime}</td>
            <td>{trip.total_amount}</td>
          </tr>
        ))}
      </tbody>
    </table>
  );
}

Why Use hypequery for Real-Time Dashboards?

Type safety at every step: No more runtime surprises or mismatched columns
Developer productivity: Autocomplete, instant error checking, and less boilerplate
Built for analytics: Features like cross-filtering, streaming, and pagination are designed for dashboard use cases
Works everywhere: Node.js and browser support for flexible deployments

Conclusion

If you’re building real-time dashboards on ClickHouse with TypeScript and Next.js, hypequery provides the type safety, developer experience, and analytics features you need to ship faster and with confidence.

Ready to build?
Install hypequery and start building your type-safe analytics dashboard today:

Explore the hypequery documentation for more examples and advanced features.

Getting Started with ClickHouse in TypeScript using hypequery.

Luke — Fri, 11 Jul 2025 18:56:12 +0000

ClickHouse has become the go-to choice for high-performance analytics, powering everything from real-time dashboards to complex data warehouses. As TypeScript continues to dominate the JavaScript ecosystem, combining these two technologies creates a powerful foundation for modern data applications. In this guide, we'll get you from zero to running your first ClickHouse query in TypeScript in under 10 minutes.

For type-safe ClickHouse queries, check out hypequery, the TypeScript SDK for Clickhouse

Why ClickHouse + TypeScript?

Before diving into the implementation, let's understand why this combination is so compelling:

ClickHouse's Strengths:

Blazing Fast Analytics: Designed for OLAP workloads, ClickHouse can process billions of rows in seconds
Columnar Storage: Optimised for analytical queries with incredible compression ratios
SQL Compatibility: Familiar SQL syntax with powerful analytical functions
Horizontal Scaling: Easily handles petabyte-scale datasets

TypeScript's Benefits:

Type Safety: Catch errors at compile time rather than runtime
Developer Experience: Superior IDE support with autocomplete and refactoring
Maintainability: Self-documenting code with clear interfaces
Modern Tooling: Excellent ecosystem for testing, building, and deploying

Together, they provide a robust foundation for building analytics applications that are both performant and maintainable.

Quick Setup with @clickhouse/client

Let's start with the basics. First, install the official ClickHouse client:

npm install @clickhouse/client

Here's a minimal setup to connect to ClickHouse:


import { createClient } from '@clickhouse/client'

const client = createClient({
  host: 'http://localhost:8123',
  username: 'default',
  password: '',
  database: 'default'
})

// Test the connection
async function testConnection() {
  try {
    const result = await client.query({
      query: 'SELECT version()',
      format: 'JSON'
    })
    console.log('Connected to ClickHouse:', await result.json())
  } catch (error) {
    console.error('Connection failed:', error)
  }
}

testConnection()

Creating Your First Table & Query

async function createTable() {
  await client.command(`
    CREATE TABLE IF NOT EXISTS user_events (
      id UUID DEFAULT generateUUIDv4(),
      user_id UInt64,
      event_type String,
      timestamp DateTime DEFAULT now(),
      properties Map(String, String)
    ) ENGINE = MergeTree()
    ORDER BY (user_id, timestamp)
  `)
}

async function insertSampleData() {
  await client.insert({
    table: 'user_events',
    values: [
      { user_id: 1, event_type: 'page_view', properties: { page: '/home' } },
      { user_id: 1, event_type: 'click', properties: { button: 'signup' } },
      { user_id: 2, event_type: 'page_view', properties: { page: '/pricing' } }
    ],
    format: 'JSONEachRow'
  })
}

async function queryEvents() {
  const result = await client.query({
    query: `
      SELECT 
        event_type,
        count() as event_count
      FROM user_events 
      WHERE timestamp >= now() - INTERVAL 1 DAY
      GROUP BY event_type
      ORDER BY event_count DESC
    `,
    format: 'JSON'
  })

  const data = await result.json()
  console.log('Event counts:', data)
}

The Type Safety Gap

While the above code works, you'll quickly notice some significant limitations:

No Query Type Safety: The SQL strings are just strings – no autocomplete, no validation.
Unknown Result Types: TypeScript doesn't know what structure your queries return.
Manual Type Definitions: You need to manually define interfaces for every query result
Runtime Errors: Typos in column names or table names only surface at runtime

Here's a typical issue:

// This compiles but fails at runtime
const result = await client.query({
  query: 'SELECT non_existent_column FROM user_events', // Typo!
  format: 'JSON'
})

// TypeScript doesn't know what this contains
const data = await result.json() // data is 'any'

Introducing Type Safety with hypequery

This is where hypequery comes in. hypequery is a TypeScript SDK specifically designed for building type-safe dashboards and analytics applications with ClickHouse.

It solves the type safety problems we just identified while maintaining the full power of ClickHouse.

Setting Up hypequery

npm install @hypequery/clickhouse @clickhouse/client

Generate your database schema

npx hypequery-generate-types --host=http://localhost:8123 --username=default --password=password --database=my_db

This creates a generated-schema.ts file that you can import in your application:

Type-Safe Querying

Now you can write fully type-safe queries!

import { createQueryBuilder } from '@hypequery/clickhouse'
import { IntrospectedSchema } from './generated-schema'

const hq = createQueryBuilder<IntrospectedSchema>({
  host: 'http://localhost:8123',
  password: 'your-password',
  username: 'your-username',
  database: 'default'
})

// Fully type-safe query with autocomplete
const eventCounts = await hq
  .table('user_events')
  .select(['event_type'])
  .count('id', 'event_count')
  .where('timestamp', 'gte', 'now() - INTERVAL 1 DAY')
  .groupBy('event_type')
  .orderBy('event_count', 'DESC')
  .execute()

// eventCounts is fully typed - TypeScript knows the exact structure
eventCounts.forEach(row => {
  console.log(`${row.event_type}: ${row.event_count}`)
})

Features

hypequery provides several advanced features that make building analytics applications easier including:

Table joins and filtering which can be defined once and applied across your queries or at a query level
Streaming support for memory-efficient processing of large result sets
Support for ClickHouse specific functions, common table expressions and raw SQL for those hard to reach edge cases
First class developer experience with comprehensive query logging, debugging and SQL generation

Why This Matters

The combination of ClickHouse's performance and TypeScript's type safety creates a powerful development experience:

Catch Errors Early: Type checking prevents runtime errors from typos and schema mismatches
IDE Support: Full autocomplete for table names, column names, and query methods
Refactoring Confidence: Rename columns or tables and let TypeScript guide you through the changes
Self-Documenting Code: The types serve as documentation for your data structures

Checkout the repo on github: https://github.com/hypequery/hypequery