Clickhouse multi env setup

ClickHouse Multi-Environment Setup

Overview

Configure separate ClickHouse instances for development, staging, and production with proper secrets management, environment detection, and infrastructure-as-code. A single typed config module resolves the right instance from NODE_ENV, so application code never branches on environment and destructive operations are blocked outside dev/staging.

The full code for every step lives in references/implementation.md; worked end-to-end flows are in references/examples.md.

Prerequisites

• ClickHouse Cloud account or self-hosted instances per environment
• Secret management solution (Vault, AWS Secrets Manager, GCP Secret Manager)
• CI/CD pipeline with environment variables

Instructions

Step 1: Choose an environment strategy

Provision one instance per tier, isolated by data sensitivity:

Environment	Instance	Purpose	Data
Development	Docker local	Fast iteration	Synthetic seed data
Staging	ClickHouse Cloud (Dev tier)	Pre-prod validation	Sampled prod copy
Production	ClickHouse Cloud (Prod tier)	Live traffic	Real data

Step 2: Build a typed config module

Create src/config/clickhouse.ts with one ClickHouseEnvConfig per environment, keyed by NODE_ENV. Fail fast in non-dev environments — require a password and enforce HTTPS at startup:

export function getConfig(): ClickHouseEnvConfig {
  const env = process.env.NODE_ENV ?? 'development';
  const config = configs[env];
  if (!config) throw new Error(`Unknown environment: ${env}`);
  if (env !== 'development') {
    if (!config.password) throw new Error(`CLICKHOUSE_PASSWORD not set for ${env}`);
    if (!config.url.startsWith('https://')) {
      throw new Error(`ClickHouse ${env} must use HTTPS`);
    }
  }
  return config;
}

Full per-environment config table (pool sizes, timeouts, compression): references/implementation.md.

Step 3: Add a client factory

A lazily-initialized singleton in src/clickhouse/client.ts builds the pool once per process from the resolved config. Full code: references/implementation.md.

Step 4: Wire secrets per environment

Dev uses a git-ignored .env.local; every other tier pulls from a secret manager (AWS Secrets Manager, GCP Secret Manager, or Vault) — never commit credentials. CLI recipes for all three: references/implementation.md.

Step 5: Apply schema deterministically

Run sorted .sql files through a migration runner (scripts/apply-schema.ts). Fail hard in production, log-and-continue in dev/staging: references/implementation.md.

Step 6: Add environment guards

requireNonProduction blocks TRUNCATE/reset in prod and validateDatabaseName catches cross-environment queries. Full guards: references/implementation.md.

Step 7: Integrate with CI/CD

Auto-deploy staging; gate production behind needs: deploy-staging plus a manual-approval environment. Full workflow: references/examples.md.

Output

Completing this workflow yields:

• src/config/clickhouse.ts — typed, per-environment ClickHouse config with startup validation (password present, HTTPS enforced outside dev).
• src/clickhouse/client.ts — a singleton client factory keyed off NODE_ENV.
• scripts/apply-schema.ts — a deterministic, sorted migration runner.
• Environment guards preventing destructive operations against production.
• Secrets stored in a secret manager per environment (nothing committed).
• A .github/workflows/deploy.yml pipeline that migrates schema and deploys staging automatically, production behind manual approval.

Application code then calls getClient() with zero environment branching.

Error Handling

Issue	Cause	Solution
Wrong database in prod	Env var not set	Validate config on startup
TLS error in staging	Using http:// for Cloud	Force https:// in non-dev
Schema drift	Applied manually	Use migration runner in CI
Secret not found	Missing env var	Check secret manager + CI config

Examples

Query from application code with no environment logic — getConfig() resolves the right instance from NODE_ENV:

import { getClient } from './clickhouse/client';

const rows = await getClient().query({
  query: 'SELECT count() AS c FROM events WHERE date = today()',
  format: 'JSONEachRow',
});
console.log(await rows.json());

More worked flows — the environment-strategy layout, the full CI/CD deploy pipeline, and end-to-end query usage — are in references/examples.md.

Resources

• full implementation walkthrough
• worked examples
• 12-Factor App Config
• ClickHouse Cloud Console
• AWS Secrets Manager
• GCP Secret Manager

Next Steps

For monitoring and observability, see the clickhouse-observability skill, which covers query performance dashboards, slow-query logging, and alerting across the same dev/staging/production tiers you configured here.