ClickHouse Local Dev Loop Skill

ClickHouse Local Dev Loop

Overview

Run ClickHouse in Docker for local development with fast schema iteration, seed data, and integration testing using vitest.

Prerequisites

Docker or Docker Compose installed
Node.js 18+ with @clickhouse/client

Instructions

Step 1: Docker Compose Setup

# docker-compose.yml
services:
  clickhouse:
    image: clickhouse/clickhouse-server:latest
    ports:
      - "8123:8123"   # HTTP interface
      - "9000:9000"   # Native TCP (clickhouse-client CLI)
    volumes:
      - clickhouse-data:/var/lib/clickhouse
      - ./init-db:/docker-entrypoint-initdb.d  # Auto-run SQL on first start
    environment:
      CLICKHOUSE_USER: default
      CLICKHOUSE_PASSWORD: dev_password
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1  # Enable SQL-based user management
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  clickhouse-data:

docker compose up -d
# Verify: curl http://localhost:8123/ping   → "Ok.\n"

Step 2: Init Script (Auto-Run on First Start)

-- init-db/001-schema.sql
CREATE DATABASE IF NOT EXISTS app;

CREATE TABLE IF NOT EXISTS app.events (
    event_id    UUID DEFAULT generateUUIDv4(),
    event_type  LowCardinality(String),
    user_id     UInt64,
    properties  String,     -- JSON string
    created_at  DateTime DEFAULT now()
)
ENGINE = MergeTree()
ORDER BY (event_type, created_at)
PARTITION BY toYYYYMM(created_at);

Step 3: Seed Data Script

// scripts/seed.ts
import { createClient } from '@clickhouse/client';

const client = createClient({
  url: 'http://localhost:8123',
  username: 'default',
  password: 'dev_password',
  database: 'app',
});

const events = Array.from({ length: 1000 }, (_, i) => ({
  event_type: ['page_view', 'click', 'signup', 'purchase'][i % 4],
  user_id: Math.floor(Math.random() * 100) + 1,
  properties: JSON.stringify({ index: i }),
  created_at: new Date(Date.now() - Math.random() * 86400000 * 30)
    .toISOString()
    .replace('T', ' ')
    .slice(0, 19),
}));

await client.insert({ table: 'events', values: events, format: 'JSONEachRow' });
console.log(`Seeded ${events.length} events`);
await client.close();

Step 4: Project Structure

my-clickhouse-app/
├── docker-compose.yml
├── init-db/
│   └── 001-schema.sql
├── scripts/
│   └── seed.ts
├── src/
│   ├── db.ts              # Client singleton
│   └── queries.ts          # Named query functions
├── tests/
│   ├── setup.ts            # Test lifecycle (truncate tables)
│   └── events.test.ts
├── .env.local              # Local creds (git-ignored)
├── .env.example
└── package.json

Step 5: Client Singleton

// src/db.ts
import { createClient, ClickHouseClient } from '@clickhouse/client';

let client: ClickHouseClient | null = null;

export function getClient(): ClickHouseClient {
  if (!client) {
    client = createClient({
      url: process.env.CLICKHOUSE_HOST ?? 'http://localhost:8123',
      username: process.env.CLICKHOUSE_USER ?? 'default',
      password: process.env.CLICKHOUSE_PASSWORD ?? 'dev_password',
      database: process.env.CLICKHOUSE_DATABASE ?? 'app',
    });
  }
  return client;
}

Step 6: Integration Testing with Vitest

// tests/setup.ts
import { getClient } from '../src/db';
import { beforeEach, afterAll } from 'vitest';

beforeEach(async () => {
  const client = getClient();
  // TRUNCATE is lightweight — drops parts without logging
  await client.command({ query: 'TRUNCATE TABLE IF EXISTS app.events' });
});

afterAll(async () => {
  await getClient().close();
});

// tests/events.test.ts
import { describe, it, expect } from 'vitest';
import { getClient } from '../src/db';

describe('ClickHouse events', () => {
  it('inserts and queries events', async () => {
    const client = getClient();

    await client.insert({
      table: 'events',
      values: [
        { event_type: 'test', user_id: 1, properties: '{}' },
        { event_type: 'test', user_id: 2, properties: '{}' },
      ],
      format: 'JSONEachRow',
    });

    const rs = await client.query({
      query: 'SELECT count() AS cnt FROM events',
      format: 'JSONEachRow',
    });
    const [row] = await rs.json<{ cnt: string }>();
    expect(Number(row.cnt)).toBe(2);
  });
});

Step 7: Package Scripts

{
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "db:up": "docker compose up -d",
    "db:down": "docker compose down",
    "db:reset": "docker compose down -v && docker compose up -d",
    "db:seed": "tsx scripts/seed.ts",
    "db:shell": "docker exec -it $(docker compose ps -q clickhouse) clickhouse-client --password dev_password",
    "test": "vitest",
    "test:watch": "vitest --watch"
  }
}

Useful CLI Commands

# Interactive SQL shell
docker exec -it <container> clickhouse-client --password dev_password

# Run a query from host via HTTP
curl 'http://localhost:8123/?query=SELECT+count()+FROM+app.events'

# Check running queries
curl 'http://localhost:8123/?query=SELECT+*+FROM+system.processes+FORMAT+PrettyCompact'

# Watch merges
curl 'http://localhost:8123/?query=SELECT+*+FROM+system.merges+FORMAT+PrettyCompact'

Error Handling

| Error | Cause | Solution | |-------|-------|----------| | Connection refused :8123 | Container not running | docker compose up -d | | READONLY | User lacks write perms | Set CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 | | Too many parts | Tiny frequent inserts | Batch inserts or increase parts_to_throw_insert | | Memory limit exceeded | Large query on small container | Add --memory 4g to Docker |

Resources

Next Steps

See clickhouse-sdk-patterns for production-ready client patterns.

Agent Skills: ClickHouse Local Dev Loop

Install this agent skill to your local

Skill Files