[hypequery](https://hypequery.com/) › [Guides](https://hypequery.com/guides) › ClickHouse with Docker Compose: a pinned, persistent local server with a million-row sample dataset
Index for agents: https://hypequery.com/llms.txt

# ClickHouse with Docker Compose: a pinned, persistent local server with a million-row sample dataset (https://hypequery.com/guides/clickhouse-docker-compose)

Run ClickHouse 25.8 in Docker Compose with a healthcheck, a password, volumes and a 1M-row sample dataset, then connect from Node, Python and hypequery.

Tested on ClickHouse 25.8. Every SQL block below is a file CI runs against the seed dataset: https://github.com/hypequery/hypequery/tree/main/guides/clickhouse-docker-compose


The short version: pin the image tag, set `CLICKHOUSE_PASSWORD` (without it the `default` user cannot connect over the network), mount a volume at `/var/lib/clickhouse`, expose 8123 for HTTP clients and 9000 for `clickhouse-client`, and add a healthcheck on `/ping` so `docker compose up -d --wait` returns only when the server answers. The file below does all of that.

Everything here was run on `clickhouse/clickhouse-server:25.8` (25.8.33.6) with Docker Compose v2, and the SQL steps run in CI against the same seed dataset on every change.

## The compose file

```yaml
# docker-compose.yml
services:
  clickhouse:
    image: clickhouse/clickhouse-server:25.8
    ports:
      - "8123:8123"   # HTTP: @clickhouse/client, clickhouse-connect, hypequery, curl
      - "9000:9000"   # native TCP: clickhouse-client
    environment:
      CLICKHOUSE_USER: default
      CLICKHOUSE_PASSWORD: hypequery
      CLICKHOUSE_DB: hypequery_examples
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: "1"
      TZ: UTC
    volumes:
      - clickhouse-data:/var/lib/clickhouse
      - clickhouse-logs:/var/log/clickhouse-server
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    healthcheck:
      test: ["CMD", "wget", "--no-verbose", "--tries=1", "--spider", "http://localhost:8123/ping"]
      interval: 2s
      timeout: 5s
      retries: 30
      start_period: 5s

volumes:
  clickhouse-data:
  clickhouse-logs:
```

What each part is for:

| Setting | Why |
| --- | --- |
| `image: ...:25.8` | A fixed LTS tag. `latest` moves under you, and a new major can change defaults and SQL behaviour. |
| `8123` and `9000` | 8123 is the HTTP interface every JavaScript and most Python clients use. 9000 is the native protocol for `clickhouse-client` and native drivers. Expose only what you use. |
| `CLICKHOUSE_PASSWORD` | Without `CLICKHOUSE_USER` or `CLICKHOUSE_PASSWORD`, the entrypoint disables network access for `default`; every client outside the container gets an authentication error. |
| `CLICKHOUSE_DB` | Creates the database on first start. It does not make it the default for clients: pass `database` in every connection. |
| `CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT` | Lets `default` run `CREATE USER` and `GRANT`, so you can make a read-only user for your app in SQL. |
| `volumes` | Keeps data and logs across `docker compose down` and image upgrades. Without them, removing the container removes the data. |
| `ulimits.nofile` | ClickHouse opens many files. Docker Desktop already defaults high (1,048,576 in our test); some Linux hosts do not. |
| `healthcheck` | `/ping` answers `Ok.` without credentials once the server accepts queries; `--wait` blocks on it. |

Start it and wait for healthy:

```bash
docker compose up -d --wait
curl http://localhost:8123/ping   # Ok.
```

On our machine `up -d --wait` returned in about 7 seconds with the image already pulled.

## Check the server

```sql
-- Is the container up, which version is it, and who am I connected as?
-- `docker compose up -d --wait` only returns once the healthcheck passes, so
-- this is the first query a script can safely run.
SELECT
    version()         AS version,
    uptime()          AS uptime_seconds,
    timezone()        AS server_timezone,
    currentUser()     AS user,
    currentDatabase() AS database;

-- CLICKHOUSE_DB creates this database on the first start of an empty volume.
SELECT name, engine
FROM system.databases
WHERE name NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
ORDER BY name;
```

Run SQL however you like: `curl --user default:hypequery 'http://localhost:8123/' --data-binary 'SELECT version()'`, or the bundled client inside the container, which picks up the password from the container's environment:

```bash
docker compose exec clickhouse clickhouse-client --database hypequery_examples
```

## Load the 1M-row sample dataset

hypequery's examples share one deterministic dataset: 200 tenants, 6,368 users, 1,000,000 events over twelve months (2025-11-01 to 2026-10-31) and 141 subscription periods. Its compose file is the one above without volumes, and its loader generates every row from a fixed seed and verifies checksums:

```bash
git clone https://github.com/hypequery/hypequery.git && cd hypequery
pnpm install
docker compose -f examples/_seed/docker-compose.yml up -d --wait
pnpm seed
```

`pnpm seed` drops and recreates `hypequery_examples`, so point it only at a server where that is fine. It reads `CLICKHOUSE_URL`, `CLICKHOUSE_USERNAME`, `CLICKHOUSE_PASSWORD` and `CLICKHOUSE_DATABASE`, so it also loads into the compose file above. A full load took about 10 seconds on a laptop, and the million events take about 18 MiB on disk (36 MiB uncompressed).

```sql
-- After `pnpm seed`: four tables, one million events, and how much disk they
-- take inside the clickhouse-data volume.
SELECT
    table,
    sum(rows)                                     AS rows,
    count()                                       AS active_parts,
    formatReadableSize(sum(data_compressed_bytes))   AS on_disk,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.parts
WHERE database = currentDatabase()
  AND active
  AND table IN ('tenants', 'users', 'events', 'subscriptions')
GROUP BY table
ORDER BY rows DESC;

-- The same order-independent checksum `pnpm seed` compares with checksums.json.
SELECT count() AS rows, sum(cityHash64(*)) AS checksum
FROM events;

-- A first real query: events and active users per month.
SELECT
    toStartOfMonth(occurred_at) AS month,
    count()                     AS events,
    uniqExact(user_id)          AS active_users
FROM events
GROUP BY month
ORDER BY month;
```

## Create your own table

The sample data is just tables in a database; add your own next to it. `ORDER BY` is the most important choice: it is the primary index, so put the columns you filter on first.

```sql
-- Your own table next to the sample data. Dropped first so the file can be
-- re-run; the last file drops it again.
DROP TABLE IF EXISTS clickhouse_docker_compose_page_views;

CREATE TABLE clickhouse_docker_compose_page_views
(
    viewed_at  DateTime('UTC'),
    tenant_id  UInt32,
    path       LowCardinality(String),
    latency_ms UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(viewed_at)
ORDER BY (tenant_id, viewed_at);

-- Insert rows by hand...
INSERT INTO clickhouse_docker_compose_page_views VALUES
    ('2026-11-01 09:00:00', 1, '/dashboard', 120),
    ('2026-11-01 09:00:05', 1, '/reports', 340);

-- ...or from a query: October's page views from the sample events.
INSERT INTO clickhouse_docker_compose_page_views
SELECT occurred_at, tenant_id, path, latency_ms
FROM events
WHERE event_type = 'page_view'
  AND occurred_at >= '2026-10-01 00:00:00' AND occurred_at < '2026-11-01 00:00:00';

SELECT
    path,
    count()                    AS views,
    quantile(0.95)(latency_ms) AS p95_latency_ms
FROM clickhouse_docker_compose_page_views
GROUP BY path
ORDER BY views DESC
LIMIT 5;
```

## Give your app its own user

Use `default` for administration and give the application a user that can only read what it needs.

```sql
-- Do not ship the default user to your app. With
-- CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 the default user can create users
-- and grants in SQL; this one can only read the sample database.
CREATE USER IF NOT EXISTS clickhouse_docker_compose_app
    IDENTIFIED WITH sha256_password BY 'change-me-app-password'
    DEFAULT DATABASE hypequery_examples
    SETTINGS readonly = 1;

GRANT SELECT ON hypequery_examples.* TO clickhouse_docker_compose_app;

SHOW GRANTS FOR clickhouse_docker_compose_app;

SELECT name, auth_type, default_database
FROM system.users
WHERE name = 'clickhouse_docker_compose_app';

-- Clean up so the file can run again from scratch.
DROP USER IF EXISTS clickhouse_docker_compose_app;
```

Connected as that user, `SELECT count() FROM events` works and `CREATE TABLE` fails with `ACCESS_DENIED` (code 497). In production, keep the password out of the compose file: `env_file`, Docker secrets, or your platform's secret store.

## Storage, memory and logs

```sql
-- Where the data lives inside the container: /var/lib/clickhouse/, which the
-- compose file mounts from the clickhouse-data volume.
SELECT
    name,
    path,
    formatReadableSize(free_space)  AS free,
    formatReadableSize(total_space) AS total
FROM system.disks;

-- Memory ClickHouse will allow itself; it follows the container's limit when
-- one is set (deploy.resources.limits.memory or `docker run --memory`).
SELECT formatReadableSize(toUInt64(value)) AS max_server_memory_usage
FROM system.server_settings
WHERE name = 'max_server_memory_usage';

-- The slowest recent queries. query_log is flushed every few seconds, so an
-- empty result right after startup is normal.
SELECT
    event_time,
    query_duration_ms,
    read_rows,
    formatReadableSize(memory_usage) AS memory,
    substring(query, 1, 80)          AS query
FROM system.query_log
WHERE type = 'QueryFinish' AND event_date >= today() - 1
ORDER BY query_duration_ms DESC
LIMIT 5;

-- Clean up the table from 03-your-first-table.sql.
DROP TABLE IF EXISTS clickhouse_docker_compose_page_views;
```

ClickHouse sizes itself from the container: with a 2 GB memory limit (`deploy.resources.limits.memory: 2g` in Compose, or `docker run --memory 2g`), `max_server_memory_usage` came out at 1.80 GiB, 90% of the limit. Without a limit it uses the Docker VM's memory.

## Connect from Node, Python and hypequery

All three use the HTTP port, 8123.

Node with the official client:

```typescript
import { createClient } from '@clickhouse/client';

const client = createClient({
  url: 'http://localhost:8123',
  username: 'default',
  password: 'hypequery',
  database: 'hypequery_examples',
});
const rs = await client.query({
  query: 'SELECT event_type, count() AS events FROM events GROUP BY event_type ORDER BY events DESC LIMIT 3',
  format: 'JSONEachRow',
});
console.log(await rs.json()); // [{ event_type: 'page_view', events: 450311 }, ...]
```

Python with `clickhouse-connect`:

```python
import clickhouse_connect

client = clickhouse_connect.get_client(
    host="localhost", port=8123, username="default", password="hypequery", database="hypequery_examples"
)
print(client.query("SELECT event_type, count() AS events FROM events GROUP BY event_type ORDER BY events DESC LIMIT 3").result_rows)
# [('page_view', 450311), ('api_call', 250126), ('query_run', 149953)]
```

TypeScript with hypequery, typed from the live schema. `npx hypequery generate` reads the same `CLICKHOUSE_*` variables and writes `analytics/schema.ts`:

```bash
export CLICKHOUSE_URL=http://localhost:8123 CLICKHOUSE_USERNAME=default CLICKHOUSE_PASSWORD=hypequery CLICKHOUSE_DATABASE=hypequery_examples
npx hypequery generate
```

```typescript
import { createQueryBuilder } from '@hypequery/clickhouse';
import type { IntrospectedSchema } from './analytics/schema.js';

const db = createQueryBuilder<IntrospectedSchema>({
  url: process.env.CLICKHOUSE_URL!,
  username: process.env.CLICKHOUSE_USERNAME!,
  password: process.env.CLICKHOUSE_PASSWORD!,
  database: process.env.CLICKHOUSE_DATABASE!,
});

const rows = await db
  .table('events')
  .select(['event_type'])
  .count('event_id', 'events')
  .groupBy('event_type')
  .orderBy('events', 'DESC')
  .limit(3)
  .execute();
```

The hypequery examples and recipes default to exactly these connection settings, so once the sample dataset is loaded they run against this container with no extra configuration.

## Common errors

| Symptom | Cause and fix |
| --- | --- |
| `Code: 194 ... Authentication failed: password is incorrect, or there is no user with such name` over HTTP (status 401) | Wrong password, or no password set in compose so network access for `default` is disabled (the container log says `disabling network access for user 'default'`). Set `CLICKHOUSE_PASSWORD` and pass it from the client. Over the native port the same failure is code 516, `AUTHENTICATION_FAILED`. |
| `Port 9000 is for clickhouse-client program. You must use port 8123 for HTTP.` | An HTTP client (Node, `clickhouse-connect`, curl) pointed at 9000. Use 8123. |
| `connect ECONNREFUSED 127.0.0.1:8123` | The container is not running or not ready yet. Use `docker compose up -d --wait`, or wait on `/ping` in scripts. |
| `Bind for 0.0.0.0:8123 failed: port is already allocated` | Another ClickHouse (or anything else) holds the port. Stop it, or map a different host port: `"18123:8123"`, and use that in the client URL. |
| A new `CLICKHOUSE_DB`, user or `/docker-entrypoint-initdb.d` script has no effect | Initialisation only runs on an empty data directory. The log says `Database directory appears to contain a database; Skipping initialization`. Create it in SQL, or `docker compose down -v` to start from scratch (this deletes the data). |
| The data is gone after `docker compose down` | No volume was mounted at `/var/lib/clickhouse`. `down` keeps named volumes; `down -v` deletes them. |
| `Code: 81 ... Database hypequery_examples does not exist (UNKNOWN_DATABASE)` | The seed has not been loaded, or the client connects to a different server. `pnpm seed` creates it. |
| The container exits with code 126 and `/docker-entrypoint-initdb.d/<file>.sh: Permission denied` | Init `.sh` scripts must be executable: `chmod +x`. `.sql` files need no permission change. |

## FAQ

### Which ClickHouse Docker image should I use?

`clickhouse/clickhouse-server` with an explicit version tag, such as `25.8`. An LTS tag gets bug fixes without behaviour changes for about a year.

### How do I set the ClickHouse password in Docker?

Set `CLICKHOUSE_PASSWORD` (and optionally `CLICKHOUSE_USER`) in the service's `environment`. It is applied on the first start of an empty data volume.

### Do I need port 9000?

Only for `clickhouse-client` from the host or native-protocol drivers. Node's `@clickhouse/client`, `clickhouse-connect` and hypequery use HTTP on 8123. `docker compose exec clickhouse clickhouse-client` works without exposing 9000.

### How do I keep data between restarts?

Mount a named volume at `/var/lib/clickhouse`. `docker compose down` and `up` keep it; `down -v` removes it.

### How do I run SQL files on startup?

Mount a folder of `.sql` or executable `.sh` files at `/docker-entrypoint-initdb.d`. They run in name order, once, on the first start with an empty data directory.

## Related

- [The examples seed dataset](https://github.com/hypequery/hypequery/tree/main/examples/_seed): the compose file, schema and loader this guide uses.
- [p95 latency recipe](https://hypequery.com/recipes/p95-latency): a first typed query over the sample events.
- [Next.js dashboard example](https://hypequery.com/examples/nextjs-dashboard) and [FastAPI analytics API example](https://hypequery.com/examples/fastapi-analytics-api): full apps against the same container.
- [Refreshable materialized views](https://hypequery.com/guides/refreshable-materialized-views): the next guide to run on this setup.

## Related

- [Next.js dashboard on ClickHouse with cross-filtering](https://hypequery.com/examples/nextjs-dashboard): A Next.js 16 dashboard over ClickHouse: one typed dataset, a serve endpoint, React hooks, and click-to-filter across every panel.
- [A FastAPI analytics API on ClickHouse with tenant-scoped datasets](https://hypequery.com/examples/fastapi-analytics-api): Serve governed ClickHouse analytics from FastAPI: datasets defined once in Python, query endpoints mounted beside your routes, tenant fixed by the token.
- [p95 latency per event type in ClickHouse with a typed dataset](https://hypequery.com/recipes/p95-latency): Define p50, p95 and p99 latency once as dataset measures and get the quantile() SQL, the result on a million events, and the exactness trade-off.
- [All guides](https://hypequery.com/guides)
- [/clickhouse-nodejs](https://hypequery.com/clickhouse-nodejs)
- [/clickhouse-python](https://hypequery.com/clickhouse-python)
- [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript)
- [/clickhouse-js](https://hypequery.com/clickhouse-js)