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

Feature	Vercel	Fly.io	Cloud Run
Model	Serverless functions	Containers	Containers
Persistent connections	No (cold starts)	Yes	Yes (min-instances)
Max timeout	30s (Pro)	Unlimited	60min
Best for	API endpoints	Long-running apps	Event-driven
max_open_connections	1-3	10-20	5-10

Error Handling

Issue	Cause	Solution
ECONNRESET on Vercel	Function timeout	Reduce query scope, increase timeout
TLS handshake failed	Wrong port	Use port 8443 for Cloud
Secret not found	Env var not set	Check platform secret config
Cold start latency	New connection per invocation	Use min-instances=1 on Cloud Run

Resources

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

Next Steps

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