clickhouse-deploy-integration

ClickHouse Deploy Integration

Overview

Deploy applications that connect to ClickHouse Cloud from serverless and container platforms with proper connection management and secrets handling.

Prerequisites

• ClickHouse Cloud instance (or self-hosted with public endpoint)
• Platform CLI installed (vercel, fly, or gcloud)
• Application tested locally against ClickHouse

Instructions

Step 1: ClickHouse Connection Module (Platform-Agnostic)

// src/db.ts — singleton for serverless-safe connections
import { createClient, ClickHouseClient } from '@clickhouse/client';

let client: ClickHouseClient | null = null;

export function getClickHouse(): ClickHouseClient {
  if (!client) {
    client = createClient({
      url: process.env.CLICKHOUSE_HOST!,         // https://<host>:8443
      username: process.env.CLICKHOUSE_USER!,
      password: process.env.CLICKHOUSE_PASSWORD!,
      database: process.env.CLICKHOUSE_DATABASE ?? 'default',
      request_timeout: 30_000,
      max_open_connections: 5,    // Low for serverless (many cold starts)
      compression: {
        request: true,            // Saves egress bandwidth
        response: true,
      },
    });
  }
  return client;
}

Step 2: Vercel (Serverless Functions)

# Set secrets
vercel env add CLICKHOUSE_HOST production
vercel env add CLICKHOUSE_USER production
vercel env add CLICKHOUSE_PASSWORD production
vercel env add CLICKHOUSE_DATABASE production

// api/events/route.ts (Next.js App Router)
import { getClickHouse } from '@/src/db';
import { NextResponse } from 'next/server';

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const days = Number(searchParams.get('days') ?? 7);

  const client = getClickHouse();
  const rs = await client.query({
    query: `
      SELECT event_type, count() AS cnt
      FROM events
      WHERE created_at >= now() - INTERVAL {days:UInt32} DAY
      GROUP BY event_type ORDER BY cnt DESC
    `,
    query_params: { days },
    format: 'JSONEachRow',
  });

  return NextResponse.json(await rs.json());
}

Vercel gotchas:

• Serverless function timeout: 30s (Pro) / 10s (Hobby)
• Each invocation may create a new connection — set max_open_connections low
• Use Edge Runtime only with HTTP-based clients (ClickHouse client works fine)

Step 3: Fly.io (Containers)

# fly.toml
app = "my-clickhouse-app"
primary_region = "iad"

[env]
  NODE_ENV = "production"
  CLICKHOUSE_DATABASE = "analytics"

[http_service]
  internal_port = 3000
  force_https = true
  auto_stop_machines = true
  auto_start_machines = true

[[http_service.checks]]
  grace_period = "10s"
  interval = "30s"
  method = "GET"
  path = "/health"
  timeout = "5s"

# Set ClickHouse secrets
fly secrets set CLICKHOUSE_HOST="https://abc123.clickhouse.cloud:8443"
fly secrets set CLICKHOUSE_USER="app_writer"
fly secrets set CLICKHOUSE_PASSWORD="secret"

fly deploy

Step 4: Google Cloud Run

#!/bin/bash
# deploy-cloud-run.sh
PROJECT_ID="${GOOGLE_CLOUD_PROJECT}"
SERVICE="clickhouse-app"
REGION="us-central1"

# Store secrets in Secret Manager
echo -n "https://abc123.clickhouse.cloud:8443" | \
  gcloud secrets create ch-host --data-file=- --project=$PROJECT_ID
echo -n "secret-password" | \
  gcloud secrets create ch-password --data-file=- --project=$PROJECT_ID

# Build and deploy
gcloud builds submit --tag gcr.io/$PROJECT_ID/$SERVICE

gcloud run deploy $SERVICE \
  --image gcr.io/$PROJECT_ID/$SERVICE \
  --region $REGION \
  --platform managed \
  --set-secrets="CLICKHOUSE_HOST=ch-host:latest,CLICKHOUSE_PASSWORD=ch-password:latest" \
  --set-env-vars="CLICKHOUSE_USER=app_writer,CLICKHOUSE_DATABASE=analytics" \
  --min-instances=1 \
  --max-instances=10 \
  --memory=512Mi

Step 5: Health Check Endpoint

// Works on all platforms
app.get('/health', async (req, res) => {
  const start = Date.now();
  try {
    const client = getClickHouse();
    const { success } = await client.ping();
    const latencyMs = Date.now() - start;

    res.json({
      status: success ? 'healthy' : 'degraded',
      clickhouse: { connected: success, latencyMs },
      version: process.env.npm_package_version,
    });
  } catch (err) {
    res.status(503).json({
      status: 'unhealthy',
      clickhouse: { connected: false, error: (err as Error).message },
    });
  }
});

Step 6: Graceful Shutdown

// Critical: flush pending inserts on shutdown
async function gracefulShutdown() {
  console.log('Shutting down...');
  const client = getClickHouse();
  await client.close();    // Waits for pending operations to complete
  process.exit(0);
}

process.on('SIGTERM', gracefulShutdown);
process.on('SIGINT', gracefulShutdown);

Platform Comparison

Error Handling

Resources

• ClickHouse Cloud Connection
• Vercel Environment Variables
• Fly.io Secrets
• Cloud Run Secrets

Next Steps

For data ingestion patterns, see clickhouse-webhooks-events.