ClickHouse CI Integration

Overview

Run integration tests against a real ClickHouse server in GitHub Actions using Docker service containers. No mocks needed for schema and query validation.

Prerequisites

• GitHub repository with Actions enabled
• @clickhouse/client in project dependencies
• Test suite (vitest or jest)

Instructions

Step 1: GitHub Actions Workflow with ClickHouse Service

# .github/workflows/clickhouse-tests.yml
name: ClickHouse Integration Tests

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest

    services:
      clickhouse:
        image: clickhouse/clickhouse-server:latest
        ports:
          - 8123:8123
          - 9000:9000
        options: >-
          --health-cmd "wget --no-verbose --tries=1 --spider http://localhost:8123/ping || exit 1"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    env:
      CLICKHOUSE_HOST: http://localhost:8123
      CLICKHOUSE_USER: default
      CLICKHOUSE_PASSWORD: ""

    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "20"
          cache: "npm"

      - run: npm ci

      # Apply schema before tests
      - name: Apply schema
        run: |
          curl -s 'http://localhost:8123/' -d 'CREATE DATABASE IF NOT EXISTS test_db'
          for f in init-db/*.sql; do
            echo "Applying $f..."
            curl -s 'http://localhost:8123/?database=test_db' --data-binary @"$f"
          done

      - name: Run unit tests
        run: npm test -- --coverage

      - name: Run integration tests
        run: npm run test:integration

Step 2: Integration Test Setup

// tests/setup-integration.ts
import { createClient, ClickHouseClient } from '@clickhouse/client';
import { beforeAll, afterAll, beforeEach } from 'vitest';

let client: ClickHouseClient;

beforeAll(async () => {
  client = createClient({
    url: process.env.CLICKHOUSE_HOST ?? 'http://localhost:8123',
    database: 'test_db',
  });

  // Verify connection
  const { success } = await client.ping();
  if (!success) throw new Error('ClickHouse not reachable');
});

beforeEach(async () => {
  // Clean test data between tests
  await client.command({ query: 'TRUNCATE TABLE IF EXISTS test_db.events' });
});

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

export { client };

Step 3: Write Real Integration Tests

// tests/events.integration.test.ts
import { describe, it, expect } from 'vitest';
import { client } from './setup-integration';

describe('Events table', () => {
  it('creates and queries events', async () => {
    // Insert test data
    await client.insert({
      table: 'events',
      values: [
        { event_type: 'page_view', user_id: 1, properties: '{"url":"/home"}' },
        { event_type: 'click', user_id: 1, properties: '{"btn":"cta"}' },
        { event_type: 'page_view', user_id: 2, properties: '{"url":"/pricing"}' },
      ],
      format: 'JSONEachRow',
    });

    // Query and validate
    const rs = await client.query({
      query: `
        SELECT event_type, count() AS cnt, uniqExact(user_id) AS users
        FROM events GROUP BY event_type ORDER BY cnt DESC
      `,
      format: 'JSONEachRow',
    });
    const rows = await rs.json<{ event_type: string; cnt: string; users: string }>();

    expect(rows).toHaveLength(2);
    expect(rows[0]).toMatchObject({ event_type: 'page_view', cnt: '2', users: '2' });
    expect(rows[1]).toMatchObject({ event_type: 'click', cnt: '1', users: '1' });
  });

  it('validates parameterized queries prevent injection', async () => {
    await client.insert({
      table: 'events',
      values: [{ event_type: 'test', user_id: 42, properties: '{}' }],
      format: 'JSONEachRow',
    });

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

  it('handles empty results gracefully', async () => {
    const rs = await client.query({
      query: 'SELECT * FROM events WHERE user_id = 999999',
      format: 'JSONEachRow',
    });
    const rows = await rs.json();
    expect(rows).toEqual([]);
  });
});

Step 4: Schema Validation in CI

// tests/schema.integration.test.ts
import { describe, it, expect } from 'vitest';
import { client } from './setup-integration';

describe('Schema validation', () => {
  it('events table has expected columns', async () => {
    const rs = await client.query({
      query: "SELECT name, type FROM system.columns WHERE database='test_db' AND table='events'",
      format: 'JSONEachRow',
    });
    const columns = await rs.json<{ name: string; type: string }>();
    const colMap = new Map(columns.map((c) => [c.name, c.type]));

    expect(colMap.get('event_type')).toBe("LowCardinality(String)");
    expect(colMap.get('user_id')).toBe('UInt64');
    expect(colMap.get('created_at')).toMatch(/DateTime/);
  });

  it('events table uses MergeTree engine', async () => {
    const rs = await client.query({
      query: "SELECT engine FROM system.tables WHERE database='test_db' AND name='events'",
      format: 'JSONEachRow',
    });
    const [row] = await rs.json<{ engine: string }>();
    expect(row.engine).toBe('MergeTree');
  });
});

Step 5: Package Scripts

{
  "scripts": {
    "test": "vitest run",
    "test:integration": "vitest run --config vitest.integration.config.ts",
    "test:ci": "vitest run --coverage --reporter=junit --outputFile=test-results.xml"
  }
}

CI Matrix for Multiple ClickHouse Versions

strategy:
  matrix:
    clickhouse-version: ["24.3", "24.8", "latest"]

services:
  clickhouse:
    image: clickhouse/clickhouse-server:${{ matrix.clickhouse-version }}
    ports:
      - 8123:8123

Error Handling

Issue	Cause	Solution
Service not healthy	Slow container start	Increase health-retries
Schema not found	Init scripts not run	Run schema step before tests
Flaky test order	Shared state	Use beforeEach with TRUNCATE
Port conflict	Another process	Use random port mapping

Resources

• GitHub Actions Service Containers
• ClickHouse Docker Image
• Vitest Documentation

Next Steps

For deployment patterns, see clickhouse-deploy-integration.