clickhouse-go

About clickhouse-go

This large README is shown as a plain-text preview. Read the full README at the source

# ClickHouse [![run-tests](https://github.com/ClickHouse/clickhouse-go/actions/workflows/run-tests.yml/badge.svg?branch=v2)](https://github.com/ClickHouse/clickhouse-go/actions/workflows/run-tests.yml) [![Go Reference](https://pkg.go.dev/badge/github.com/ClickHouse/clickhouse-go/v2.svg)](https://pkg.go.dev/github.com/ClickHouse/clickhouse-go/v2)

Golang SQL database client for [ClickHouse](https://clickhouse.com/).

## Install

```sh
go get github.com/ClickHouse/clickhouse-go/v2
```

## Which interface should I use?

| | `clickhouse.Open` (native) | `sql.Open` / `clickhouse.OpenDB` (std) |
|---|---|---|
| Performance | Faster (direct column encoding) | Slower (see [benchmark](#benchmark)) |
| API | `driver.Conn` — ClickHouse-specific | Standard `database/sql` |
| Use when | new code, performance-sensitive work | existing `database/sql` tooling, ORMs |

Both support TCP and HTTP transport. When in doubt, use the native interface.

## Key features

* Uses ClickHouse native format for optimal performance. Utilises low level [ch-go](https://github.com/ClickHouse/ch-go) client for encoding/decoding and compression (versions >= 2.3.0).
* Supports both native ClickHouse TCP and HTTP client-server protocols
* Compatibility with [`database/sql`](#std-databasesql-interface) ([slower](#benchmark) than [native interface](#native-interface)!)
* [`database/sql`](#std-databasesql-interface) supports both native TCP and HTTP protocols for transport.
* Marshal rows into structs ([ScanStruct](examples/clickhouse_api/scan_struct.go), [Select](examples/clickhouse_api/select_struct.go))
* Unmarshal struct to row ([AppendStruct](benchmark/v2/write-native-struct/main.go))
* Connection pool (for both TCP-Native and HTTP)
* Failover and load balancing
* [Bulk write support](examples/clickhouse_api/batch.go) (for `database/sql` [use](examples/std/batch.go) `begin->prepare->(in loop exec)->commit`)
* [PrepareBatch options](#preparebatch-options)
* [AsyncInsert](benchmark/v2/write-async/main.go) (more details in [Async insert](#async-insert) section)
* Named and numeric placeholders support
* LZ4/ZSTD/LZ4HC/GZIP/Deflate/Brotli compression support
* External data
* [Server-side query parameters](https://clickhouse.com/docs/integrations/language-clients/go/clickhouse-api#server-side-query-parameters)
* Structured logging via `log/slog` ([Logger option](#logging))
* [Arbitrary input/output formats](#arbitrary-inputoutput-formats-experimental) — stream results or inserts as raw `CSV`, `JSONEachRow`, `Parquet`, ... (experimental, HTTP protocol only)
* JWT authentication support
* Wide type support: BFloat16, QBit, Dynamic, Variant, Time, Time64, LineString, MultiLineString, and more

Support for the ClickHouse protocol advanced features using `Context`:

* Query ID
* Quota Key
* Settings
* [Server-side query parameters](https://clickhouse.com/docs/integrations/language-clients/go/clickhouse-api#server-side-query-parameters)
* OpenTelemetry
* Execution events:
	* Logs
	* Progress
	* Profile info
	* Profile events


## Supported ClickHouse Versions

The client is tested against the currently [supported versions](https://github.com/ClickHouse/ClickHouse/blob/master/SECURITY.md) of ClickHouse

## Supported Golang Versions

| Client Version | Golang Versions        |
|----------------|------------------------|
| >= 2.0 <= 2.2  | 1.17, 1.18             |
| >= 2.3         | 1.18.4+, 1.19          |
| >= 2.14        | 1.20, 1.21             |
| >= 2.19        | 1.21, 1.22             |
| >= 2.28        | 1.22, 1.23             |
| >= 2.29        | 1.21, 1.22, 1.23, 1.24 |
| >= 2.41        | 1.24, 1.25             |

## Documentation

[https://clickhouse.com/docs/integrations/language-clients/go/index](https://clickhouse.com/docs/integrations/language-clients/go/index)

# `clickhouse` interface (formerly `native` interface)

```go
	conn, err := clickhouse.Open(&clickhouse.Options{
		Addr: []string{"127.0.0.1:9000"},
		Auth: clickhouse.Auth{
			Database: "default",
			Username: "default",
			Password: "",
		},
		DialContext: func(ctx context.Context, addr string) (net.Conn, error) {
			dialCount++
			var d net.Dialer
			return d.DialContext(ctx, "tcp", addr)
		},
		// Logger is the recommended way to enable logging (see Logging section).
		// Debug and Debugf are deprecated in favour of Logger.
		Logger: slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{
			Level: slog.LevelDebug,
		})),
		Settings: clickhouse.Settings{
			"max_execution_time": 60,
		},
		Compression: &clickhouse.Compression{
			Method: clickhouse.CompressionLZ4,
		},
		DialTimeout:      time.Second * 30,
		MaxOpenConns:     5,
		MaxIdleConns:     5,
		ConnMaxLifetime:  time.Duration(10) * time.Minute,
		ConnOpenStrategy: clickhouse.ConnOpenInOrder,
		BlockBufferSize: 10,
		MaxCompressionBuffer: 10240,
		ClientInfo: clickhouse.ClientInfo{ // optional, please see Client info section in the README.md
			Products: []struct {
				Name    string
				Version string
			}{
				{Name: "my-app", Version: "0.1"},
			},
		},
	})
	if err != nil {
		return err
	}
	return conn.Ping(context.Background())
```

# `database/sql` interface

## OpenDB

```go
conn := clickhouse.OpenDB(&clickhouse.Options{
	Addr: []string{"127.0.0.1:9999"},
	Auth: clickhouse.Auth{
		Database: "default",
		Username: "default",
		Password: "",
	},
	TLS: &tls.Config{
		InsecureSkipVerify: true,
	},
	Settings: clickhouse.Settings{
		"max_execution_time": 60,
	},
	DialTimeout: time.Second * 30,
	Compression: &clickhouse.Compression{
		Method: clickhouse.CompressionLZ4,
	},
	// Debug is deprecated; use Logger instead (see Logging section).
	BlockBufferSize: 10,
	MaxCompressionBuffer: 10240,
	ClientInfo: clickhouse.ClientInfo{ // optional, please see Client info section in the README.md
		Products: []struct {
			Name    string
			Version string
		}{
			{Name: "my-app", Version: "0.1"},
		},
	},
})
conn.SetMaxIdleConns(5)
conn.SetMaxOpenConns(10)
conn.SetConnMaxLifetime(time.Hour)
```

## DSN

* hosts/alt_hosts - comma-separated lists of additional single address hosts for load-balancing and failover; `hosts` are prepended before, and `alt_hosts` appended after, the host(s) given in the URL authority (which may itself be a comma-separated list)
* username/password - auth credentials
* database - select the current default database
* dial_timeout -  a duration string is a possibly signed sequence of decimal numbers, each with optional fraction and a unit suffix such as "300ms", "1s". Valid time units are "ms", "s", "m". (default 30s)
* connection_open_strategy - random/round_robin/in_order (default in_order).
    * random      - choose random server from the set
    * round_robin - choose a round-robin server from the set
    * in_order    - first live server is chosen in specified order
* debug - enable debug output (boolean value)
* compress - specify the compression algorithm: `none` (default), `zstd`, `lz4`, `lz4hc`, `gzip`, `deflate`, `br`. If set to `true`, `lz4` will be used. For HTTP connections, `gzip`/`deflate`/`br` use HTTP web compression, while `lz4`/`zstd` use ClickHouse native block compression over HTTP (`lz4hc` is native-only).
* compress_level - Level of compression (algorithm-specific, default is 3 when compression is enabled):
  - `gzip`/`deflate`: `-2` (Best Speed) to `9` (Best Compression)
  - `br`: `0` (Best Speed) to `11` (Best Compression)
  - `zstd`/`lz4`/`lz4hc`: ignored
* block_buffer_size - size of block buffer (default 2)
* read_timeout - a duration string is a possibly signed sequence of decimal numbers, each with optional fraction and a unit suffix such as "300ms", "1s". Valid time units are "ms", "s", "m" (default 5m).
* max_compression_buffer - max size (bytes) of compression buffer during column by column compression (default 10MiB)
* client_info_product - optional list (comma separated) of product name and version pair separated with `/`. This value will be passed as part of client info. e.g. `client_info_product=my_app/1.0,my_module/0.1` More details in [Client info](#client-info) section.
* http_proxy - HTTP proxy address
* http_path - URL path for HTTP requests (e.g. for proxies or custom endpoints that require a specific path)
* tls_server_name - set TLS SNI/verification name (sets `tls.Config.ServerName` when `secure=true`)

## Connection Settings Reference

The following connection settings are available in both DSN strings and the `clickhouse.Options` struct:

### Timeout Settings
* **dial_timeout** - Connection timeout for establishing a connection to the server (default: 30s)
* **read_timeout** - Timeout for reading server responses (default: 5m)

### Connection Pool Settings
* **max_open_conns** - Maximum number of open connections to the database (default: MaxIdleConns + 5)
* **max_idle_conns** - Maximum number of idle connections in the pool (default: 5)
* **conn_max_lifetime** - Maximum amount of time a connection may be reused (default: 1h)

### Connection Strategy
* **connection_open_strategy** - Strategy for selecting servers from the connection pool:
  * `in_order` - Choose the first available server in the specified order (default)
  * `round_robin` - Choose servers in a round-robin fashion
  * `random` - Choose a random server from the pool

### Compression Settings
* **compress** - Enable compression with a specific algorithm: `none`, `zstd`, `lz4`, `lz4hc`, `gzip`, `deflate`, `br`. If set to `true`, `lz4` will be used (default: `none`). For HTTP connections, `gzip`/`deflate`/`br` use HTTP web compression, while `lz4`/`zstd` use ClickHouse native block compression over HTTP (`lz4hc` is native-only).
* **compress_level** - Compression level (algorithm-specific):
  * `gzip`/`deflate`: `-2` (Best Speed) to `9` (Best Compression)
  * `br`: `0` (Best Speed) to `11` (Best Compression)
  * `zstd`/`lz4`: ignored
* **max_compression_buffer** - Maximum size of compression buffer in bytes (default: 10MiB)

### Buffer Settings
* **block_buffer_size** - Size of block buffer (default: 2)

### Debug Settings
* **debug** - Enable debug output (boolean value)

### SSL/TLS Settings
* **secure** - Establish secure connection (default: false)
* **skip_verify** - Skip certificate verification (default: false)

### Client Information
* **client_info_product** - Comma-separated list of product name and version pairs (e.g., `my_app/1.0,my_module/0.1`)

### HTTP Settings
* **http_proxy** - HTTP proxy address for HTTP protocol connections

Example:

```sh
clickhouse://username:password@host1:9000,host2:9000/database?dial_timeout=200ms&read_timeout=30s&max_execution_time=60
```

### HTTP Support

The native format can be used over the HTTP protocol. This is useful in scenarios where users need to proxy traffic e.g. using [ChProxy](https://www.chproxy.org/) or via load balancers.

This can be achieved by modifying the DSN to specify the HTTP protocol.

```sh
http://host1:8123,host2:8123/database?dial_timeout=200ms&max_execution_time=60
```

Alternatively, use `OpenDB` and specify the interface type.

```go
conn := clickhouse.OpenDB(&clickhouse.Options{
	Addr: []string{"127.0.0.1:8123"},
	Auth: clickhouse.Auth{
		Database: "default",
		Username: "default",
		Password: "",
	},
	Settings: clickhouse.Settings{
		"max_execution_time": 60,
	},
	DialTimeout: 30 * time.Second,
	Compression: &clickhouse.Compression{
		Method: clickhouse.CompressionLZ4,
	},
	Protocol:  clickhouse.HTTP,
})
```

#### Proxy support

HTTP proxy can be set in the DSN string by specifying the `http_proxy` parameter.
(make sure to URL encode the proxy address)

```sh
http://host1:8123,host2:8123/database?dial_timeout=200ms&max_execution_time=60&http_proxy=http%3A%2F%2Fproxy%3A8080
```

If you are using `clickhouse.OpenDB`, set the `HTTPProxyURL` field in the `clickhouse.Options`.

An alternative way is to enable proxy by setting the `HTTP_PROXY` (for HTTP) or `HTTPS_PROXY` (for HTTPS) environment variables.
See more details in the [Go documentation](https://pkg.go.dev/net/http#ProxyFromEnvironment).

## Compression

Compression is supported over native and HTTP protocols.

Native protocol supports `lz4`, `lz4hc`, and `zstd`.

HTTP protocol supports `lz4` and `zstd` via ClickHouse native block compression over HTTP, and `gzip`, `deflate`, and `br` via HTTP web compression.

### HTTP: Web Compression vs Native Block Compression

When using the HTTP protocol there are two independent compression layers:

1. **HTTP web compression** (whole request/response body). This uses HTTP headers (`Accept-Encoding` and `Content-Encoding`). In ClickHouse, response compression is controlled by the `enable_http_compression` setting (pass it via `Options.Settings` or DSN query params). In clickhouse-go this mode is used when `Compression.Method` is `gzip`, `deflate`, or `br`.

2. **ClickHouse native block compression over HTTP** (Native format blocks). This uses ClickHouse HTTP query parameters: `compress=1` (server compresses response blocks) and `decompress=1` (server expects a compressed request body), plus `network_compression_method` to select the block codec (`LZ4` or `ZSTD`). In clickhouse-go this mode is used when `Compression.Method` is `lz4` or `zstd`.

Avoid enabling both at the same time unless you've measured it, as it can waste CPU by compressing already-compressed native blocks.

Note: you normally don't need to set `compress=1` or `decompress=1` yourself when using clickhouse-go; selecting an appropriate `Compression.Method` will configure the HTTP request correctly.

When using a DSN, compression can be enabled via the `compress` parameter. Set it to a specific algorithm name (`zstd`, `lz4`, `lz4hc`, `gzip`, `deflate`, `br`) or to `true` as shorthand for `lz4`. See the [DSN](#dsn) section for details.

## TLS/SSL

At a low level all client connect methods (DSN/OpenDB/Open) will use the [Go tls package](https://pkg.go.dev/crypto/tls) to establish a secure connection. The client knows to use TLS if the Options struct contains a non-nil tls.Config pointer.

Setting secure in the DSN creates a minimal tls.Config struct with only the InsecureSkipVerify field set (either true or false).  It is equivalent to this code:

```go
conn := clickhouse.OpenDB(&clickhouse.Options{
	...
    TLS: &tls.Config{
            InsecureSkipVerify: false
	}
	...
    })
```
This minimal tls.Config is normally all that is necessary to connect to the secure native port (normally 9440) on a ClickHouse server. If the ClickHouse server does not have a valid certificate (expired, wrong host name, not signed by a publicly recognized root Certificate Authority), InsecureSkipVerify can be set to `true`, but that is strongly discouraged.

If additional TLS parameters are necessary the application code should set the desired fields in the tls.Config struct. That can include specific cipher suites, forcing a particular TLS version (like 1.2 or 1.3), adding an internal CA certificate chain, adding a client certificate (and private key) if required by the ClickHouse server, and most of the other options that come with a more specialized security setup.

### Server Certificate SAN (Go)

Go does not fall back to the certificate Common Name (CN) for hostname verification. If your ClickHouse server certificate does not contain a matching Subject Alternative Name (SAN), you may see:

```text
tls: failed to verify certificate: x509: certificate relies on legacy Common Name field, use SANs instead
```

Fix: regenerate the **server** certificate with SANs matching how you connect (DNS and/or IP). For example:

```bash
openssl req -newkey rsa:2048 -nodes \
  -subj "/CN=clickhouse" \
  -addext "subjectAltName = DNS:clickhouse.local,IP:127.0.0.1" \
  -keyout clickhouse.key -out clickhouse.csr

openssl x509 -req -in clickhouse.csr -out clickhouse.crt \
  -CA CAroot.crt -CAkey CAroot.key -days 3650 -copy_extensions copy
```

If you must connect to an IP address but your certificate SAN only contains a DNS name, set `tls_server_name` in the DSN (or `tls.Config.ServerName` in code) to the DNS name in the certificate.

### HTTPS

To connect using HTTPS either:

- Use `https` in your dsn string e.g.

    ```sh
    https://host1:8443,host2:8443/database?dial_timeout=200ms&max_execution_time=60
    ```

- Use `Protocol: clickhouse.HTTP` with a `TLS` config e.g.

```go
conn := clickhouse.OpenDB(&clickhouse.Options{
	Addr: []string{"127.0.0.1:8443"},
	Auth: clickhouse.Auth{
		Database: "default",
		Username: "default",
		Password: "",
	},
	Protocol:  clickhouse.HTTP,
})
```

## Cluster interserver-secret authentication

Native protocol only. Lets a client authenticate as a trusted ClickHouse cluster peer using the cluster's shared secret instead of a user password, then run each query as an arbitrary `initial_user` chosen per-call. This is the same wire protocol that ClickHouse itself uses for distributed queries.

> **Read [Security model](#security-model) before adopting this feature.** The cluster secret authorizes impersonation of any user on the cluster — including superusers — and so any process holding it must be treated as a cluster-admin-equivalent service.

### When to use it

- You operate the ClickHouse cluster yourself (you control `<remote_servers><secret>…</secret></remote_servers>`).
- You run a *trusted internal service* — query router, multi-tenant gateway, internal admin tool — that authenticates end-users at its own layer and needs to attribute queries back to those users in `system.query_log`.
- You target ClickHouse versions older than 25.11 (where `EXECUTE AS` does not exist) or want a single mechanism that works across the whole supported range.

This feature is **not** intended to be exposed to arbitrary end-user clients.

### Usage

Configure on `Options` (never via DSN — see *Why not DSN* below):

```go
conn, err := clickhouse.Open(&clickhouse.Options{
    Addr: []string{"clickhouse:9440"},
    TLS:  &tls.Config{ServerName: "clickhouse.internal"}, // strongly recommended
    Auth: clickhouse.Auth{
        Database: "default",
        Username: "router_fallback_user", // fallback when WithInitialUser is not set; cannot default to "default"
    },
    Cluster: clickhouse.ClusterCredentials{
        Name:   "my_cluster",                                  // matches <remote_servers> entry
        Secret: os.Getenv("CLICKHOUSE_CLUSTER_SECRET"),
    },
})

ctx := clickhouse.Context(context.Background(),
    clickhouse.WithInitialUser("alice"),
)
rows, err := conn.Query(ctx, "SELECT 1")
```

Server side, the named cluster must have a `<secret>` matching what the client sends:

```xml
<remote_servers>
    <my_cluster>
        <secret>same-secret-as-client</secret>
        <shard><replica><host>clickhouse</host><port>9000</port></replica></shard>
    </my_cluster>
</remote_servers>
```

The user named in `WithInitialUser` (or `Auth.Username` if no `WithInitialUser` is set) must exist on the server. The server runs the query as that user without a password check, since the cluster-secret signature already proves the caller is trusted.

Each query is signed with `SHA256(salt + secret + body + id + initial_user)` (the layout `TCPHandler::processQuery` expects). The driver advertises protocol revision 54460, which omits the V2 nonce/external-roles fields added in 54462+ — sufficient for typical impersonation use, and compatible with any modern server.

A runnable example: [examples/clickhouse_api/cluster_secret.go](examples/clickhouse_api/cluster_secret.go).

### Security model

**The cluster secret is the highest-privilege credential in your ClickHouse deployment.** Anything that holds it can become any user on the cluster, including superusers. Treat any process holding it as a cluster-admin-equivalent service.

Concrete operating practices:

Frequently Asked Questions