Add ClickHouse Engine Support to sqlc

[mgilbir]

Overview

This PR introduces comprehensive support for ClickHouse as a database engine in sqlc. It enables users to write ClickHouse queries in .sql files and generate type-safe Go code using sqlc's familiar workflow.

Key Features

• ClickHouse SQL Parser: Full SQL parsing support using the github.com/AfterShip/clickhouse-sql-parser library
• Type Mapping: ClickHouse types mapped to Go types with support for ClickHouse-specific types (Array, LowCardinality, Map, etc.)
• Code Generation: Generates Go database interfaces, model types, and query functions for ClickHouse operations
• JOIN USING Support: New SQL feature supporting JOIN...USING syntax, working with both ClickHouse and PostgreSQL
• Comprehensive Testing: 25+ end-to-end test cases covering basic operations, advanced SQL features, type handling, and functions

Production Status

This implementation has been in production use for approximately one month. The core parser and code generation have proven stable for our queries, but I'm eager for community feedback on edge cases and other query patterns.

Large PR Notice

⚠️ This is a substantial PR (~17,000 lines across 13 commits), but the impact on existing code is minimal:

• New engine implementation is isolated in /internal/engine/clickhouse/
• Code generation templates are isolated in /internal/codegen/golang/templates/clickhouse/
• Core changes to existing files are limited to:
  • Adding ClickHouse engine registration in the compiler
  • Minor type system extensions for ClickHouse types
  • Refactoring and improving output_columns.go (backward compatible)
  • Adding JOIN...USING support (benefits multiple engines)

The majority of the PR size comes from test data and generated code examples, which are valuable for validation and user documentation.

Feedback Welcome

I'm committed to making this PR production-ready and welcome:

• Architecture review of the engine implementation
• Bug reports for queries that fail to parse or generate incorrectly
• Feature requests for missing ClickHouse capabilities
• Code quality feedback and suggestions for improvement
• Test cases for any edge cases you encounter

If you have ClickHouse queries that don't work with this implementation, please share them. I'm particularly interested in:

• Complex subqueries and CTEs
• Advanced window functions
• ClickHouse-specific features (PREWHERE, SAMPLE, array operations, etc.)
• Edge cases in type resolution

Commit Structure

The 13 commits are organized to facilitate review, with test data grouped at the end:

1. Core Engine Implementation (2 commits)

• Add ClickHouse engine: parser, converter, and catalog - Foundation for ClickHouse SQL parsing
• Add ClickHouse engine unit tests - Comprehensive unit tests for the parser

2. Integration Layer (2 commits)

• Register ClickHouse engine in compiler - Wires the engine into the compiler pipeline
• Add ClickHouse type mapping for Go code generation - Maps ClickHouse types to Go types

3. Code Generation (2 commits)

• Add ClickHouse code generation templates - Templates for generating Go code
• Add JOIN USING support and refactor output column handling - New SQL feature and compiler improvements

4. Infrastructure & Documentation (2 commits)

• Add ClickHouse test database adapters - Docker and local test containers
• Add ClickHouse documentation and example project - User-facing docs and complete working example

5. Generated Code & Examples (1 commit)

• Add ClickHouse example project generated code - Shows what users get from sqlc generate

6. End-to-End Tests (4 commits)

• Add end-to-end test for JOIN...USING syntax - Validates feature works with PostgreSQL
• Add end-to-end tests for ClickHouse core SQL features - SELECT, DML, pagination, etc.
• Add end-to-end tests for ClickHouse advanced SQL features - JOINs, subqueries, CTEs, aggregates
• Add end-to-end tests for ClickHouse types and functions - Type handling, arrays, functions, named parameters

Review Strategy: Early commits establish the core logic before test data, making the implementation easy to understand and validate.

What's Included

• Full ClickHouse SQL parser integration
• 25 end-to-end test cases with complete schemas and generated code
• Working example project in /examples/clickhouse/
• Getting started documentation and API reference updates
• Docker Compose configuration for testing with ClickHouse
• Support for: SELECT, INSERT, UPDATE, DELETE, CTEs, JOINs, subqueries, aggregates, type casting, built-in functions, and more

Review suggestion

1. Start with commits 1-2 to understand the parser architecture
2. Review commits 3-5 for integration and code generation
3. Check commit 6 for the JOIN USING implementation (useful for all databases)
4. Skim the test data commits (7-13) to see coverage breadth

Looking forward to your feedback and excited to bring ClickHouse support to sqlc!