clickhouse-pydantic-config

ClickHouse Pydantic Config

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

Generate DBeaver database client configurations from Pydantic v2 models using environment variables as the 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
• 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
uv run scripts/generate_dbeaver_config.py --output .dbeaver/data-sources.json

# Generate cloud connection config
uv run scripts/generate_dbeaver_config.py --mode cloud --output .dbeaver/data-sources.json

# Preview without writing
uv run scripts/generate_dbeaver_config.py --dry-run

# Launch DBeaver
open -a 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 uv run scripts/generate_dbeaver_config.py --mode cloud

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

Environment Variables as Single Source of Truth

All configurable values are environment variables; export them in your shell or keep them in the gitignored .env:

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

Scripts read from os.environ.get() with backward-compatible defaults, so every variable is optional.

Credential Handling by Mode

Mode	Approach	Rationale
Local	Hardcode default user, empty password	Zero friction, no security concern
Cloud	Pre-populate from .env	Read from environment, write to gitignored JSON

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 ".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

Discovery Finding	Adaptation Action
Existing .env at repo root	Extend it with CLICKHOUSE_* vars, don't create a new file
Existing .dbeaver/ directory	Merge connections, preserve existing entries
Non-standard CLICKHOUSE_* vars	Map to repository's naming convention
Multiple databases (local + cloud)	Generate multiple connection entries
No ClickHouse available	Warn and generate placeholder config

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)
• The generator runs without error (uv run scripts/generate_dbeaver_config.py --dry-run)
• .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

Skill	Integration
devops-tools:clickhouse-cloud-management	Credential retrieval for cloud mode
quality-tools:clickhouse-architect	Schema design context

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

Reference	Content
references/pydantic-model.md	Complete model documentation
references/dbeaver-format.md	DBeaver JSON format spec

---

Troubleshooting

Issue	Cause	Solution
DBeaver can't connect	Port mismatch (8123 vs 9000)	HTTP uses 8123, native uses 9000 - check config
Credentials not loading	.env not sourced	set -a; source .env; set +a, then re-run
JSON validation fails	Invalid data-sources.json	Validate with jq . .dbeaver/data-sources.json
Cloud SSL error	Missing SSL on port 8443	Cloud mode auto-enables SSL - verify port is 8443
.dbeaver/ in git	Missing gitignore entry	Add .dbeaver/ to .gitignore
Connection ID conflict	Duplicate connection names	Each connection needs unique ID (random hex)
Config not updating	DBeaver caching	Restart DBeaver to reload data-sources.json

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.