ClickHouse Local Dev Loop

Overview

Run ClickHouse in Docker for local development with fast schema iteration, seed

data, and integration testing using vitest. This skill scaffolds a

Docker-Compose-based dev loop: an auto-migrating init script, a seed generator,

a reusable client singleton, and a truncate-between-tests integration harness —

so schema changes and query work stay a docker compose up away.

Prerequisites

• Docker or Docker Compose installed and running (docker info succeeds).
• Node.js 18+ with the @clickhouse/client package for the seed/test scripts.
• A local project directory you can Write files into. No cloud account or

network access is required — everything runs on localhost.

Authentication

This is a local dev setup with no external ClickHouse Cloud service. The

container's credentials are declared inline in docker-compose.yml

(CLICKHOUSEUSER: default, CLICKHOUSEPASSWORD: dev_password) and consumed by

the client via env vars (CLICKHOUSEUSER, CLICKHOUSEPASSWORD,

CLICKHOUSEHOST, CLICKHOUSEDATABASE). Keep real per-developer overrides in a

git-ignored .env.local; commit only a .env.example. Never reuse

dev_password outside local development.

Instructions

Follow the seven-step build. The lean skeleton is below; the

full walkthrough carries the complete file

contents for every step.

1. Docker Compose setup — Write a docker-compose.yml exposing 8123

(HTTP) and 9000 (native TCP), mounting ./init-db for auto-migration and a

named volume for persistence:

   services:
     clickhouse:
       image: clickhouse/clickhouse-server:latest
       ports: ["8123:8123", "9000:9000"]
       volumes:
         - clickhouse-data:/var/lib/clickhouse
         - ./init-db:/docker-entrypoint-initdb.d
       environment:
         CLICKHOUSE_PASSWORD: dev_password
         CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
   volumes:
     clickhouse-data:

Then docker compose up -d and verify with

curl http://localhost:8123/ping → Ok.

1. Init script — Write init-db/001-schema.sql with your

CREATE DATABASE / CREATE TABLE ... ENGINE = MergeTree() DDL. ClickHouse

auto-runs it on the container's first start.

1. Seed data — Write scripts/seed.ts that batch-inserts synthetic rows via

client.insert({ format: 'JSONEachRow' }).

1. Project structure — lay out init-db/, scripts/, src/, tests/, and

the .env.local / .env.example pair.

1. Client singleton — Write src/db.ts exposing a memoized getClient()

that reads connection settings from env vars with localhost fallbacks.

1. Integration testing — Write a vitest tests/setup.ts that TRUNCATEs

tables in beforeEach and closes the client in afterAll, then write

per-feature test files.

1. Package scripts — Edit package.json to add db:up, db:down,

db:reset, db:seed, db:shell, and test scripts.

Read the full walkthrough for the exact,

copy-pasteable contents of each file.

Output

Running this skill produces a working local ClickHouse dev loop:

• A running clickhouse-server container reachable at http://localhost:8123

(HTTP) and localhost:9000 (native), answering curl .../ping with Ok.

• An auto-created app database and schema from init-db/001-schema.sql.
• Seedable data via npm run db:seed (default: 1000 synthetic events).
• A green vitest integration suite that inserts and queries against the live

container, resetting table state between tests.

• One-command lifecycle scripts (db:up, db:down, db:reset, db:seed,

db:shell, test) wired into package.json.

Error Handling

Examples

Bring the stack up, seed it, and confirm the rows landed:

docker compose up -d
curl http://localhost:8123/ping                 # → "Ok.\n"
npm run db:seed                                   # seeds 1000 events
curl 'http://localhost:8123/?query=SELECT+count()+FROM+app.events'   # → 1000

Open an interactive SQL shell:

docker exec -it <container> clickhouse-client --password dev_password

See more examples — server-state inspection, HTTP

queries, environment reset, and a passing integration-test run.

Resources

• Full implementation walkthrough — all seven

build steps with complete file contents.

• Examples — cold-start, shell, HTTP query, reset, and

test-run recipes.

• ClickHouse Docker Image
• clickhouse-client CLI
• Vitest Documentation

Next Steps

Once the local loop is green, see the clickhouse-sdk-patterns skill for

production-ready client patterns (connection pooling, retries, typed query

helpers, and async inserts) that build on the getClient() singleton above.