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
(CLICKHOUSE_USER: default, CLICKHOUSE_PASSWORD: dev_password) and consumed by
the client via env vars (CLICKHOUSE_USER, CLICKHOUSE_PASSWORD,
CLICKHOUSE_HOST, CLICKHOUSE_DATABASE). 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.
-
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.
-
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.
-
Seed data — Write scripts/seed.ts that batch-inserts synthetic rows via
client.insert({ format: 'JSONEachRow' }).
-
Project structure — lay out init-db/, scripts/, src/, tests/, and
the .env.local / .env.example pair.
-
Client singleton — Write src/db.ts exposing a memoized getClient()
that reads connection settings from env vars with localhost fallbacks.
-
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.
-
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
| 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 |
Examples
Bring the stack up, seed it, and confirm the rows landed:
docker compose up -d
curl http://localhost:8123/ping
npm run db:seed
curl 'http://localhost:8123/?query=SELECT+count()+FROM+app.events'
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
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.
