gem-hunter

Golang SQL database client for ClickHouse.

The client works with both ClickHouse Cloud and self-hosted ClickHouse. You can start a free Cloud trial or install ClickHouse locally.

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

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

• Uses ClickHouse native format for optimal performance. Utilises low level 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 (slower than native interface!)
• database/sql supports both native TCP and HTTP protocols for transport.
• Marshal rows into structs (ScanStruct, Select)
• Unmarshal struct to row (AppendStruct)
• Connection pool (for both TCP-Native and HTTP)
• Failover and load balancing
• Bulk write support (for database/sql use begin->prepare->(in loop exec)->commit)
• PrepareBatch options
• AsyncInsert (more details in Async insert section)
• Named and numeric placeholders support
• LZ4/ZSTD/LZ4HC/GZIP/Deflate/Brotli compression support
• External data
• Server-side query parameters
• Structured logging via log/slog (Logger option)
• Arbitrary input/output formats — 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
• OpenTelemetry
• Execution events:
  • Logs
  • Progress
  • Profile info
  • Profile events

The client is tested against the currently supported versions of ClickHouse

https://clickhouse.com/docs/integrations/language-clients/go/index

	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())

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)

• 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 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)

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

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

• 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_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

• 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)

• block_buffer_size - Size of block buffer (default: 2)

• debug - Enable debug output (boolean value)

• secure - Establish secure connection (default: false)
• skip_verify - Skip certificate verification (default: false)

• client_info_product - Comma-separated list of product name and version pairs (e.g., my_app/1.0,my_module/0.1)

• http_proxy - HTTP proxy address for HTTP protocol connections

Example:

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

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 or via load balancers.

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

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

Alternatively, use OpenDB and specify the interface type.

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,
})

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

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.

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.

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 section for details.

At a low level all client connect methods (DSN/OpenDB/Open) will use the Go tls package 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:

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.

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:

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:

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.

To connect using HTTPS either:

• Use https in your dsn string e.g.

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

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

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

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

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

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

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:

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

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:

• Source the secret from a secret manager, not from environment variables baked into container images. Rotate the secret on any application compromise; rotation is cluster-wide (every server config and every client app must be updated together).
• Scope blast radius with per-cluster-name secrets. ClickHouse <remote_servers> accepts multiple cluster entries; give each trusted application its own <my_app_cluster><secret>…</secret></my_app_cluster> so a leaked secret only impersonates within that one application boundary, not across the whole cluster.
• Prefer per-cluster-name secrets over per-tenant if you operate multi-tenant infrastructure. The wire protocol does not give the server a way to scope which initial_user a holder of a given secret may claim — that is an operational boundary, not a cryptographic one.
• Require TLS for any non-loopback connection. The protocol the driver speaks is V1 (no nonce); without TLS, an on-path attacker who captures a signed query frame can replay it on the same connection. The driver emits a Warn log line when interserver-secret mode is used without TLS.
• Audit system.query_log regularly for unexpected (user, address) combinations. Interserver-secret queries appear with is_initial_query = 0 and the impersonated user in both user and initial_user, so they are distinguishable from normal logins.
• Never put the secret in a DSN. See Why not DSN below.

The driver enforces fail-closed defaults at Open():

The third check exists because Auth.Username defaults to "default" when blank. Without that check, a caller who configures Cluster.Secret and forgets WithInitialUser would silently run queries as default — typically a superuser. The driver therefore requires you to name the fallback user explicitly.

ClusterCredentials.String() and GoString() redact Secret, so accidental logging via slog.Any("opt", opt) or fmt.Sprintf("%+v", opt) cannot leak it.

Cluster.Secret is sensitive cluster-wide credential material. DSNs are passed as connection strings to database/sql, and frequently end up in startup logs, error messages, config files, and stack traces. The driver intentionally has no DSN parameter for the cluster secret — configure it via Options{} only, sourcing the value from a secret manager or env var that your process loads at startup.

ClickHouse 25.11 introduced EXECUTE AS for in-SQL impersonation. It is the right choice for many cases, but the two features have different tradeoffs:

Honest framing of the security trade. EXECUTE AS has better authorization grain and a better per-holder audit story; cluster interserver-secret has a coarser scope by design. Neither credential is more or less phishable than the other in absolute terms — both live as material in process memory or a secret manager, both can be stolen, both should be rotated on compromise. The interserver-secret advantage is not "no credential to steal" — it is operational: connection-pool reuse, no SQL surgery, no SQL-injection surface for the identity field, version coverage that includes every supported ClickHouse release, and reuse of a code path that ClickHouse itself runs continuously.

In short: prefer EXECUTE AS when (a) you are on 25.11+, (b) impersonation pairs are few and stable enough to express as GRANT IMPERSONATE statements, (c) you want fine-grained per-pair authorization, and (d) you can tolerate the connection-pool friction. Reach for cluster interserver-secret when you need a uniform, version-portable mechanism in a trusted internal service that runs queries under many short-lived initial_user identities and is willing to accept the coarser grain in exchange for protocol-level efficiency.

Clickhouse-go implements client info as a part of language client specification. client_name for native protocol and HTTP User-Agent header values are provided with the exact client info string.

Users can extend client options with additional product information included in client info. This might be useful for analysis on a server side.

Products are ordered from the highest to the lowest abstraction level, left to right.

Usage examples for native API and database/sql are provided.

Structured logging is supported via Go's standard log/slog package. Set the Logger field in Options to enable it:

conn, err := clickhouse.Open(&clickhouse.Options{
	Addr: []string{"127.0.0.1:9000"},
	Logger: slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{
		Level: slog.LevelDebug,
	})),
})

The Debug and Debugf fields in Options are deprecated in favour of Logger.

Async insert is supported via WithAsync() helper on both Native and HTTP protocols. You can use it for both Go standard interface OpenDB and also ClickHouse interface Open().

NOTE: You can use WithSettings() manually to add any async related settings. WithAsync() is just a simple wrapper that does that for you.

We have the following examples to show Async Insert in action.

1. Native with Open
2. HTTP with Open
3. Native with OpenDB
4. HTTP with OpenDB

NOTE: The old AsyncInsert() api is deprecated and will be removed in future versions. We highly recommend using the WithAsync() api for all the Async Insert use cases.

QueryFormat and InsertFormat on the native clickhouse.Conn interface stream query results and insert payloads as raw bytes in any format the server supports (CSV, JSONEachRow, Parquet, ArrowStream, ...), with all encoding and parsing done server-side:

// Results as a raw byte stream in the requested format.
stream, err := conn.QueryFormat(ctx, "Parquet", "SELECT * FROM events WHERE date = {date:Date}", date)
defer stream.Close() // holds a connection until closed
_, err = io.Copy(file, stream)

// Insert a payload pre-encoded in the given format from any io.Reader.
err = conn.InsertFormat(ctx, "Parquet", "INSERT INTO events", file)

See format.go for runnable examples and the driver.Conn godoc for the full contract. Key points:

• Experimental: the API may change or be removed in a future minor release.
• HTTP protocol only: over the native TCP protocol both methods return ErrFormatNativeUnsupported. Connect with Options{Protocol: clickhouse.HTTP} or an http:// DSN.
• Native API only: database/sql has no representation for raw format streams; open a native connection for this workload.
• Pass the format as the argument — a trailing FORMAT clause in the query is rejected, since the server would honour it over the requested format.
• The payload is always the raw, uncompressed format bytes. Wire compression via Options.Compression is transparent (the driver compresses inserts and decompresses results itself) — do not pass pre-compressed data such as a .parquet.gz file, it would be compressed twice.

ClickHouse supports server-side parameterized queries using the {name:Type} syntax (requires ClickHouse ≥ 22.8). Parameters are sent separately from the query text — the server substitutes them after parsing, which prevents SQL injection.

Native interface — pass parameters via context:

ctx := clickhouse.Context(context.Background(), clickhouse.WithParameters(clickhouse.Parameters{
    "id":   "42",
    "name": "Alice",
}))
row := conn.QueryRow(ctx, "SELECT {id:UInt64}, {name:String}")

Or use clickhouse.Named as query arguments:

row := conn.QueryRow(ctx,
    "SELECT {id:UInt64}, {name:String}",
    clickhouse.Named("id", "42"),
    clickhouse.Named("name", "Alice"),
)

database/sql interface — use sql.Named:

row := db.QueryRowContext(ctx,
    "SELECT {id:UInt64}, {name:String}",
    sql.Named("id", 42),
    sql.Named("name", "Alice"),
)

There are two ways to supply parameter values and they differ in how escaping is handled.

Named (or the std API's sql.Named) with a string/*string or []byte/*[]byte value treats the Go value as the literal parameter value. Control characters — tab, newline, carriage return, NUL — and backslashes are escaped automatically, so the value round-trips byte-for-byte on both protocols; a literal tab or newline no longer needs manual escaping:

row := conn.QueryRow(ctx,
    "SELECT {s:String}",
    clickhouse.Named("s", "line 1\nline 2"), // literal newline — works as-is
)

WithParameters/Parameters sends values as pre-formatted server-side text (Escaped format). Nothing is escaped for you — pass an already-escaped value (e.g. ['a', 'b'] for an Array(String), or a literal \n for a newline), a raw tab/newline is rejected, and a top-level NULL uses the \N marker. Callers who need the literal-value behavior should prefer Named.

Named and WithParameters share the same transport; the encoding still differs by protocol:

See full examples: native API · database/sql

Available options:

• WithReleaseConnection - after PrepareBatch connection will be returned to the pool. It ca