Tracing & MetricsSQL clientHTTP router
ClickHouse for Go

Getting started

Installation

go get github.com/uptrace/go-clickhouse@latest

Connecting to ClickHouse

To connect to ClickHouse:

import "github.com/uptrace/go-clickhouse/ch"

db := ch.Connect(
	// clickhouse://<user>:<password>@<host>:<port>/<database>?sslmode=disable
	ch.WithDSN("clickhouse://localhost:9000/default?sslmode=disable"),
)

To see the executed queries in the console, install chdebug query hook:

import "github.com/uptrace/go-clickhouse/chdebug"

db.AddQueryHook(chdebug.NewQueryHook(
	chdebug.WithVerbose(true),
	chdebug.FromEnv("CHDEBUG"),
))

Now you are ready to execute queries using database/sql API:

res, err := db.ExecContext(ctx, "SELECT 1")

var num int
err := db.QueryRowContext(ctx, "SELECT 1").Scan(&num)

Or using the query builder:

res, err := db.NewSelect().ColumnExpr("1").Exec(ctx)

var num int
err := db.NewSelect().ColumnExpr("1").Scan(ctx, &num)

Options

You can specify the following options in a DSN (connection string):

  • ?sslmode=verify-full - enable TLS.
  • ?sslmode=disable - disables TLS.
  • ?dial_timeout=5s - timeout for establishing new connections.
  • ?read_timeout=5s - timeout for socket reads.
  • ?write_timeout=5s - timeout for socket writes.
  • ?timeout=5s - sets all three timeouts described above.

go-clickhouse treats all unknown options as ClickHouse query settings, for example, ?prefer_column_name_to_alias=1 enables the corresponding ClickHouse settingopen in new window.

In addition to DSN, you can also use ch.Optionopen in new window to configure the client:

db := ch.Connect(
	ch.WithAddr("localhost:9000"),
	ch.WithTLSConfig(&tls.Config{InsecureSkipVerify: true}),
	ch.WithUser("test"),
	ch.WithPassword("test"),
	ch.WithDatabase("test"),
	ch.WithTimeout(5 * time.Second),
	ch.WithDialTimeout(5 * time.Second),
	ch.WithReadTimeout(5 * time.Second),
	ch.WithWriteTimeout(5 * time.Second),
	ch.WithQuerySettings(map[string]interface{}{
		"prefer_column_name_to_alias": 1,
	}),
)

Or use a DSN and options together:

db := ch.Connect(
	ch.WithDSN("clickhouse://default:@localhost:9000/uptrace?sslmode=verify-full"),
	ch.WithTLSConfig(tlsConfig),
)

Server timezone

go-clickhouse only works with UTC server timezone. To configure ClickHouse server to use UTC timezone, edit config.xml:

<?xml version="1.0" ?>
<clickhouse>
  <timezone>UTC</timezone>
</clickhouse>

Models

go-clickhouse uses struct-based models to build queries and scan results. A typical model looks like this:

type Span struct {
	ch.CHModel `ch:"partition:toYYYYMM(time)"`

	ID   uint64
	Text string    `ch:",lc"` // low cardinality column
	Time time.Time `ch:",pk"` // ClickHouse primary key to be used in order by
}

Having a model, you can create and drop tables:

// Create spans table.
res, err := db.NewCreateTable().Model((*Span)(nil)).Exec(ctx)

// Drop spans table.
res, err := db.NewDropTable().Model((*Span)(nil)).Exec(ctx)

// Drop table if they exist and create new tables.
err := db.ResetModel(ctx, (*Model1)(nil), (*Model2)(nil))

Insert rows:

// Insert a single span.
span := &Span{Name: "admin"}
res, err := db.NewInsert().Model(span).Exec(ctx)

// Insert multiple spans (bulk-insert).
spans := []Span{span1, span2}
res, err := db.NewInsert().Model(&spans).Exec(ctx)

And select rows scanning the results:

// Select a span by an id.
span := new(Span)
err := db.NewSelect().Model(span).Where("id = ?", 1).Scan(ctx)

// Select first 10 spans.
var spans []Span
err := db.NewSelect().Model(&spans).OrderExpr("id ASC").Limit(10).Scan(ctx)

Scanning query results

When it comes to scanning query results, go-clickhouse is very flexible and allows scanning into structs:

span := new(Span)
err := db.NewSelect().Model(span).Limit(1).Scan(ctx)

Into scalars:

var id int64
var name string
err := db.NewSelect().Model((*Span)(nil)).Column("id", "name").Limit(1).Scan(ctx, &id, &name)

Into a map[string]interface{}:

var m map[string]interface{}
err := db.NewSelect().Model((*Span)(nil)).Limit(1).Scan(ctx, &m)

And into slices of the types above:

var spans []Span
err := db.NewSelect().Model(&spans).Limit(1).Scan(ctx)

var ids []int64
var names []string
err := db.NewSelect().Model((*Span)(nil)).Column("id", "name").Limit(1).Scan(ctx, &ids, &names)

var ms []map[string]interface{}
err := db.NewSelect().Model((*Span)(nil)).Scan(ctx, &ms)

What's next

By now, you should have basic understanding of the API. Next, learn how to define models and write queries.