--- name: clickhouse-multi-env-setup description: | Configure ClickHouse across dev, staging, and production with environment-specific settings, secrets management, and infrastructure-as-code patterns. Use when setting up per-environment ClickHouse instances, managing connection configs, or deploying to multiple environments. Trigger: "clickhouse environments", "clickhouse dev staging prod", "clickhouse multi-env", "clickhouse environment config", "clickhouse staging setup". allowed-tools: Read, Write, Edit, Bash(aws:*), Bash(gcloud:*), Bash(vault:*) version: 1.0.0 license: MIT author: Jeremy Longshore tags: [saas, database, analytics, clickhouse, olap] compatible-with: claude-code --- # ClickHouse Multi-Environment Setup ## Overview Configure separate ClickHouse instances for development, staging, and production with proper secrets management, environment detection, and infrastructure-as-code. ## 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: Environment Strategy | 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: Configuration Module ```typescript // src/config/clickhouse.ts interface ClickHouseEnvConfig { url: string; username: string; password: string; database: string; maxConnections: number; requestTimeout: number; compression: boolean; } const configs: Record = { development: { url: 'http://localhost:8123', username: 'default', password: process.env.CLICKHOUSE_PASSWORD ?? 'dev_password', database: 'app_dev', maxConnections: 5, requestTimeout: 60_000, // Longer for debugging compression: false, // Easier to debug raw }, staging: { url: process.env.CLICKHOUSE_HOST ?? 'https://staging.clickhouse.cloud:8443', username: process.env.CLICKHOUSE_USER ?? 'app_staging', password: process.env.CLICKHOUSE_PASSWORD!, database: 'app_staging', maxConnections: 10, requestTimeout: 30_000, compression: true, }, production: { url: process.env.CLICKHOUSE_HOST!, username: process.env.CLICKHOUSE_USER!, password: process.env.CLICKHOUSE_PASSWORD!, database: 'app_prod', maxConnections: 20, requestTimeout: 30_000, compression: true, }, }; export function getConfig(): ClickHouseEnvConfig { const env = process.env.NODE_ENV ?? 'development'; const config = configs[env]; if (!config) throw new Error(`Unknown environment: ${env}`); // Validate required fields in non-dev environments 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; } ``` ### Step 3: Client Factory ```typescript // src/clickhouse/client.ts import { createClient, ClickHouseClient } from '@clickhouse/client'; import { getConfig } from '../config/clickhouse'; let client: ClickHouseClient | null = null; export function getClient(): ClickHouseClient { if (!client) { const config = getConfig(); client = createClient({ url: config.url, username: config.username, password: config.password, database: config.database, max_open_connections: config.maxConnections, request_timeout: config.requestTimeout, compression: { request: config.compression, response: config.compression, }, }); } return client; } ``` ### Step 4: Secrets Management ```bash # --- Development --- # .env.local (git-ignored) CLICKHOUSE_PASSWORD=dev_password # --- Staging (GitHub Actions) --- # Set via: gh secret set CLICKHOUSE_PASSWORD_STAGING # Access in workflow: # env: # CLICKHOUSE_PASSWORD: ${{ secrets.CLICKHOUSE_PASSWORD_STAGING }} # --- Production (AWS Secrets Manager) --- aws secretsmanager create-secret \ --name clickhouse/production \ --secret-string '{"host":"https://prod.clickhouse.cloud:8443","password":"..."}' # Fetch at runtime: aws secretsmanager get-secret-value \ --secret-id clickhouse/production \ --query SecretString --output text # --- Production (GCP Secret Manager) --- echo -n "https://prod.clickhouse.cloud:8443" | \ gcloud secrets create ch-prod-host --data-file=- gcloud secrets versions access latest --secret=ch-prod-host # --- Production (HashiCorp Vault) --- vault kv put secret/clickhouse/prod \ host="https://prod.clickhouse.cloud:8443" \ password="..." vault kv get -field=password secret/clickhouse/prod ``` ### Step 5: Schema Management Across Environments ```typescript // scripts/apply-schema.ts import { getClient } from '../src/clickhouse/client'; import { readFileSync, readdirSync } from 'fs'; import { join } from 'path'; async function applySchema() { const client = getClient(); const env = process.env.NODE_ENV ?? 'development'; const schemaDir = join(__dirname, '../src/clickhouse/schemas'); const files = readdirSync(schemaDir).filter((f) => f.endsWith('.sql')).sort(); console.log(`Applying ${files.length} schema files to ${env}...`); for (const file of files) { const sql = readFileSync(join(schemaDir, file), 'utf-8'); try { await client.command({ query: sql }); console.log(` [OK] ${file}`); } catch (err) { console.error(` [FAIL] ${file}: ${(err as Error).message}`); if (env === 'production') throw err; // Fail hard in prod } } } applySchema(); ``` ### Step 6: Environment Guards ```typescript // Prevent dangerous operations in production function requireNonProduction(operation: string): void { if (process.env.NODE_ENV === 'production') { throw new Error(`${operation} is blocked in production`); } } // TRUNCATE only in dev/staging async function resetTestData() { requireNonProduction('resetTestData'); const client = getClient(); await client.command({ query: 'TRUNCATE TABLE events' }); } // Prevent accidental cross-environment queries function validateDatabaseName(database: string): void { const env = process.env.NODE_ENV ?? 'development'; const expected = `app_${env === 'development' ? 'dev' : env}`; if (database !== expected) { throw new Error(`Database mismatch: expected ${expected}, got ${database}`); } } ``` ### Step 7: CI/CD Integration ```yaml # .github/workflows/deploy.yml jobs: deploy-staging: runs-on: ubuntu-latest environment: staging env: NODE_ENV: staging CLICKHOUSE_HOST: ${{ secrets.CLICKHOUSE_HOST_STAGING }} CLICKHOUSE_USER: ${{ secrets.CLICKHOUSE_USER_STAGING }} CLICKHOUSE_PASSWORD: ${{ secrets.CLICKHOUSE_PASSWORD_STAGING }} steps: - uses: actions/checkout@v4 - uses: actions/setup-node@v4 with: { node-version: "20" } - run: npm ci - run: npm run schema:apply # Apply schema changes - run: npm run test:integration # Run against staging CH - run: npm run deploy:staging deploy-production: needs: deploy-staging runs-on: ubuntu-latest environment: production # Requires manual approval env: NODE_ENV: production CLICKHOUSE_HOST: ${{ secrets.CLICKHOUSE_HOST_PROD }} CLICKHOUSE_USER: ${{ secrets.CLICKHOUSE_USER_PROD }} CLICKHOUSE_PASSWORD: ${{ secrets.CLICKHOUSE_PASSWORD_PROD }} steps: - uses: actions/checkout@v4 - uses: actions/setup-node@v4 with: { node-version: "20" } - run: npm ci - run: npm run schema:apply - run: npm run deploy:production ``` ## 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 | ## Resources - [12-Factor App Config](https://12factor.net/config) - [ClickHouse Cloud Console](https://clickhouse.cloud/) - [AWS Secrets Manager](https://aws.amazon.com/secrets-manager/) - [GCP Secret Manager](https://cloud.google.com/secret-manager) ## Next Steps For monitoring and observability, see `clickhouse-observability`. --- Source attribution: Jeremy Longshore Original project: https://github.com/jeremylongshore/claude-code-plugins-plus-skills License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice MIT License Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Mega AI Hub resource metadata Creator / publisher: Jeremy Longshore Original project: https://github.com/jeremylongshore/claude-code-plugins-plus-skills Attribution basis: Original repository namespace preserved by package mirror Fields of work: Data & Analytics, Development & Engineering Roles: Analytics Engineer, Backend Developer, Data Engineer, Database Administrator, DevOps / SRE Specialties: Database, DevOps Tools: AWS, GCP, Clickhouse Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 934930cb7fe0908d1f691592425fb2d4209102bf56d7dcb49db38eaa56a8ea89 Metadata reviewed: 2026-10-27 This metadata supersedes earlier Mega AI Hub catalog attribution. Original instruction and supporting files are preserved.