Clickhouse Pydantic Config

ClickHouse Pydantic Config

<!-- ADR: 2025-12-09-clickhouse-pydantic-config-skill -->

Generate DBeaver database client configurations from Pydantic v2 models using mise [env] as Single Source of Truth (SSoT).

Schema documentation principle: ClickHouse table/column COMMENTs are the SSoT for what each column means and how it's computed. See quality-tools:clickhouse-architect for the full COMMENT policy.

Self-Evolving Skill: This skill improves through use. If instructions are wrong, parameters drifted, or a workaround was needed — fix this file immediately, don't defer. Only update for real, reproducible issues.

When to Use This Skill

Use this skill when:

• Setting up DBeaver connections for ClickHouse databases
• Generating database client configurations from environment variables
• Managing local vs cloud ClickHouse connection profiles
• Integrating ClickHouse with mise-based development workflows
• Automating DBeaver data-sources.json generation

Critical Design Principle: Semi-Prescriptive Adaptation

This skill is NOT a rigid template. It provides a SSoT pattern that MUST be adapted to each repository's structure and local database situation.

Why This Matters

Each repository has unique:

• Directory layouts (.dbeaver/ location may vary)
• Environment variable naming conventions
• Existing connection management patterns
• Local vs cloud database mix

The SSoT principle is the constant; the implementation details are the variables.

Quick Start

# Generate local connection config
mise run db-client-generate

# Generate cloud connection config
mise run db-client:cloud

# Preview without writing
mise run db-client:dry-run

# Launch DBeaver
mise run dbeaver

Credential Prerequisites (Cloud Mode)

<!-- ADR: 2025-12-10-clickhouse-skill-documentation-gaps -->

Before using cloud mode, obtain credentials via the skill chain:

1. Create/retrieve user: Use clickhouse-cloud-management skill to create read-only users or retrieve existing credentials from 1Password 2. Store in .env: Add to .env file (gitignored):

CLICKHOUSE_USER_READONLY=your_user
CLICKHOUSE_PASSWORD_READONLY=your_password

1. Generate config: Run mise run db-client:cloud

Skill chain: clickhouse-cloud-management → .env → clickhouse-pydantic-config

mise [env] as Single Source of Truth

All configurable values live in .mise.toml:

[env]
CLICKHOUSE_NAME = "clickhouse-local"
CLICKHOUSE_MODE = "local"  # "local" or "cloud"
CLICKHOUSE_HOST = "localhost"
CLICKHOUSE_PORT = "8123"
CLICKHOUSE_DATABASE = "default"

Scripts read from os.environ.get() with backward-compatible defaults—works with or without mise installed.

Credential Handling by Mode

Key principle: The generated data-sources.json is gitignored anyway. Pre-populating credentials trades zero security risk for maximum developer convenience.

Cloud Credentials Setup

# .env (gitignored)
CLICKHOUSE_USER_READONLY=readonly_user
CLICKHOUSE_PASSWORD_READONLY=your-secret-password

Repository Adaptation Workflow

Pre-Implementation Discovery (Phase 0)

Before writing any code, the executor MUST:

# 1. Discover existing configuration patterns
fd -t f ".mise.toml" .
fd -t f ".env*" .
fd -t d ".dbeaver" .

# 2. Test ClickHouse connectivity (local)
clickhouse-client --host localhost --port 9000 --query "SELECT 1"

# 3. Check for existing connection configs
fd -t f "data-sources.json" .
fd -t f "dataSources.xml" .

Adaptation Decision Matrix

Validation Checklist (Post-Generation)

The executor MUST verify:

• [ ] Generated JSON is valid (jq . .dbeaver/data-sources.json)
• [ ] DBeaver can import the config (launch and verify connection appears)
• [ ] mise tasks execute without error (mise run db-client-generate)
• [ ] .dbeaver/ added to .gitignore

Pydantic Model

The ClickHouseConnection model provides:

• Type-safe configuration with Pydantic v2 validation
• Computed fields for JDBC URL and connection ID
• Mode-aware defaults (cloud auto-enables SSL on port 8443)
• Environment loading via from_env() class method

See references/pydantic-model.md for complete model documentation.

DBeaver Format

DBeaver uses .dbeaver/data-sources.json with this structure:

{
  "folders": {},
  "connections": {
    "clickhouse-jdbc-{random-hex}": {
      "provider": "clickhouse",
      "driver": "com_clickhouse",
      "name": "Connection Name",
      "configuration": { ... }
    }
  }
}

Important: DBeaver does NOT support ${VAR} substitution—values must be pre-populated at generation time.

See references/dbeaver-format.md for complete format specification.

macOS Notes

1. DBeaver binary: Use /Applications/DBeaver.app/Contents/MacOS/dbeaver (NOT open -a) 2. Gitignore: Add .dbeaver/ to .gitignore

Related Skills

Python Driver Policy

For Python application code connecting to ClickHouse (not DBeaver), use clickhouse-connect (official HTTP driver). See `clickhouse-architect` for:

• Recommended code patterns
• Why NOT to use clickhouse-driver (community)
• Performance vs maintenance trade-offs

Additional Resources

---

Troubleshooting

Post-Execution Reflection

After this skill completes, check before closing:

1. Did the command succeed? — If not, fix the instruction or error table that caused the failure. 2. Did parameters or output change? — If the underlying tool's interface drifted, update Usage examples and Parameters table to match. 3. Was a workaround needed? — If you had to improvise (different flags, extra steps), update this SKILL.md so the next invocation doesn't need the same workaround.

Only update if the issue is real and reproducible — not speculative.

Skill: ClickHouse Pydantic Config

DBeaver Format Reference

<!-- ADR: 2025-12-09-clickhouse-pydantic-config-skill -->

Complete specification of the DBeaver data-sources.json format for ClickHouse connections.

File Location

DBeaver stores connection configurations in:

.dbeaver/data-sources.json

This file should be gitignored as it may contain credentials.

JSON Structure

{
  "folders": {},
  "connections": {
    "clickhouse-jdbc-{random-hex}": {
      "provider": "clickhouse",
      "driver": "com_clickhouse",
      "name": "Connection Display Name",
      "configuration": {
        "host": "localhost",
        "port": "8123",
        "database": "default",
        "url": "jdbc:clickhouse:http://localhost:8123/default",
        "type": "dev",
        "auth-model": "native",
        "user": "default",
        "password": ""
      }
    }
  }
}

Field Reference

Root Level

Connection Entry

Configuration Block

Connection ID Format

Connection IDs must be unique and follow this pattern:

clickhouse-jdbc-{16-char-hex}

Example: clickhouse-jdbc-a1b2c3d4e5f67890

Generated using secrets.token_hex(8) in Python.

JDBC URL Format

Local (HTTP)

jdbc:clickhouse:http://{host}:{port}/{database}

Example: jdbc:clickhouse:http://localhost:8123/default

Cloud (HTTPS)

jdbc:clickhouse:https://{host}:{port}/{database}

Example: jdbc:clickhouse:https://xyz.clickhouse.cloud:8443/default

SSL Configuration

For cloud connections, add these fields to configuration:

{
  "handler-ssl": "openssl",
  "ssl-mode": "require"
}

SSL Modes:

Important Limitations

No Variable Substitution

DBeaver does NOT support environment variable substitution in data-sources.json:

// WRONG - will not work
{
  "host": "${CLICKHOUSE_HOST}",
  "password": "${CLICKHOUSE_PASSWORD}"
}

// CORRECT - pre-populate values at generation time
{
  "host": "localhost",
  "password": ""
}

Port as String

The port field must be a string, not an integer:

// WRONG
{ "port": 8123 }

// CORRECT
{ "port": "8123" }

Complete Examples

Local Development

{
  "folders": {},
  "connections": {
    "clickhouse-jdbc-abc123def456": {
      "provider": "clickhouse",
      "driver": "com_clickhouse",
      "name": "ClickHouse Local",
      "configuration": {
        "host": "localhost",
        "port": "8123",
        "database": "default",
        "url": "jdbc:clickhouse:http://localhost:8123/default",
        "type": "dev",
        "auth-model": "native",
        "user": "default",
        "password": ""
      }
    }
  }
}

ClickHouse Cloud

{
  "folders": {},
  "connections": {
    "clickhouse-jdbc-789xyz012abc": {
      "provider": "clickhouse",
      "driver": "com_clickhouse",
      "name": "ClickHouse Cloud",
      "configuration": {
        "host": "xyz.clickhouse.cloud",
        "port": "8443",
        "database": "default",
        "url": "jdbc:clickhouse:https://xyz.clickhouse.cloud:8443/default",
        "type": "prod",
        "auth-model": "native",
        "handler-ssl": "openssl",
        "ssl-mode": "require",
        "user": "readonly_user",
        "password": "secret-password"
      }
    }
  }
}

macOS Notes

DBeaver Binary Path

Use the full binary path, NOT open -a:

# CORRECT
/Applications/DBeaver.app/Contents/MacOS/dbeaver -data .dbeaver-workspace &

# WRONG - does not support -data flag
open -a DBeaver

Workspace Separation

Use -data flag to keep project-specific workspace:

dbeaver -data .dbeaver-workspace &

This prevents mixing connections across projects.

Related

• Pydantic Model Reference
• Parent Skill

Evolution Log

Convention: Reverse chronological order (newest on top, oldest at bottom). Prepend new entries.

---

2026-02-26: Initial Evolution Log

Status: Skill is in use and maintained. Track improvements here.

Purpose

This evolution log tracks updates to the skill. Each entry should note:

• What changed (content, structure, tooling)
• Why it changed (bug fix, feature request, best practice)
• Files affected

How to Use

1. When updating SKILL.md or references, add an entry here with the date 2. Keep entries reverse-chronological (newest first) 3. Link to ADRs or GitHub issues when relevant 4. Reference specific line changes when helpful

---

Skill: ClickHouse Pydantic Config

Pydantic Model Reference

<!-- ADR: 2025-12-09-clickhouse-pydantic-config-skill -->

Complete documentation for the ClickHouseConnection Pydantic v2 model.

Model Overview

The ClickHouseConnection model serves as the Single Source of Truth (SSoT) for ClickHouse connection configuration. It provides:

• Type-safe configuration with validation
• Computed fields for derived values
• Mode-aware defaults (local vs cloud)
• Environment variable loading

Fields

Computed Fields

jdbc_url

Generates the JDBC connection URL:

@computed_field
@property
def jdbc_url(self) -> str:
    protocol = "https" if self.ssl_enabled else "http"
    return f"jdbc:clickhouse:{protocol}://{self.host}:{self.port}/{self.database}"

Examples:

• Local: jdbc:clickhouse:http://localhost:8123/default
• Cloud: jdbc:clickhouse:https://xyz.clickhouse.cloud:8443/default

connection_id

Generates unique DBeaver connection ID:

@computed_field
@property
def connection_id(self) -> str:
    return f"clickhouse-jdbc-{secrets.token_hex(8)}"

Example: clickhouse-jdbc-a1b2c3d4e5f67890

Model Validator

The validate_mode_settings validator automatically applies cloud defaults:

@model_validator(mode='after')
def validate_mode_settings(self) -> 'ClickHouseConnection':
    if self.mode == ConnectionMode.CLOUD:
        self.port = 8443
        self.ssl_enabled = True
        self.ssl_mode = "require"
    return self

Factory Methods

from_env()

Creates a connection from environment variables:

@classmethod
def from_env(cls, prefix: str = "CLICKHOUSE_") -> 'ClickHouseConnection':
    return cls(
        name=os.environ.get(f"{prefix}NAME", "clickhouse-local"),
        mode=ConnectionMode(os.environ.get(f"{prefix}MODE", "local")),
        host=os.environ.get(f"{prefix}HOST", "localhost"),
        port=int(os.environ.get(f"{prefix}PORT", "8123")),
        database=os.environ.get(f"{prefix}DATABASE", "default"),
        connection_type=os.environ.get(f"{prefix}TYPE", "dev")
    )

Environment Variables:

Instance Methods

to_dbeaver_config()

Generates DBeaver connection entry with mode-aware credential handling:

def to_dbeaver_config(self) -> dict:
    config = {
        "provider": "clickhouse",
        "driver": "com_clickhouse",
        "name": self.name,
        "configuration": {
            "host": self.host,
            "port": str(self.port),
            "database": self.database,
            "url": self.jdbc_url,
            "type": self.connection_type,
            "auth-model": "native"
        }
    }

    # Credential handling by mode
    if self.mode == ConnectionMode.LOCAL:
        config["configuration"]["user"] = "default"
        config["configuration"]["password"] = ""
    elif self.mode == ConnectionMode.CLOUD:
        config["configuration"]["user"] = os.environ.get("CLICKHOUSE_USER_READONLY", "default")
        config["configuration"]["password"] = os.environ.get("CLICKHOUSE_PASSWORD_READONLY", "")

    return config

Usage Examples

Basic Local Connection

conn = ClickHouseConnection()
print(conn.jdbc_url)  # jdbc:clickhouse:http://localhost:8123/default

Cloud Connection

conn = ClickHouseConnection(
    mode=ConnectionMode.CLOUD,
    host="xyz.clickhouse.cloud"
)
# Automatically sets port=8443, ssl_enabled=True, ssl_mode="require"
print(conn.jdbc_url)  # jdbc:clickhouse:https://xyz.clickhouse.cloud:8443/default

From Environment

# With mise [env] or exported variables
conn = ClickHouseConnection.from_env()
config = conn.to_dbeaver_config()

ConnectionMode Enum

class ConnectionMode(str, Enum):
    LOCAL = "local"
    CLOUD = "cloud"

Related

• DBeaver Format Reference
• Parent Skill