ClickHouse Writing Queries


The goal is to help you write idiomatic SQL, not to hide it behind awkward constructs. It is a good idea to start writing and testing queries using ClickHouse CLI, and then re-construct resulting queries using go-clickhouse query builder.

The main features are:

  • Splitting long queries into logically separated blocks.
  • Replacing placeholders with properly escaped values (using ch.Ident and ch.Safe).
  • Generating a list of columns from struct-based models.

For example, the following Go code:

err := db.NewSelect().
	Where("? = ?", ch.Ident("id"), "some-id").

Unsurprsingly generates the following query:

SELECT lower(name)
FROM "books"
WHERE "id" = 'some-id'

Scan and Exec

You can create queries using ch.DBopen in new window:

Once you have a query, you can execute it with Exec:

result, err := db.NewInsert().Model(&user).Exec(ctx)

Or use Scan which does the same but omits the sql.Result (only available for selects):

err := db.NewSelect().Model(&user).Where("id = 1").Scan(ctx)

By default Exec scans columns into the model, but you can specify a different destination too:

err := db.NewSelect().Model((*User)(nil)).Where("id = 1").Scan(ctx, &user)

You can scan into:

  • a struct,
  • a map[string]interface{},
  • scalar types,
  • slices of the types above.
// Scan into a map.
m := make(map[string]interface{})
err := db.NewSelect().Model(&user).Where("id = 1").Scan(ctx, &m)

// Scan into a slice of maps.
ms := make([]map[string]interface{}, 0)
err := db.NewSelect().Model(&user).Limit(100).Scan(ctx, &ms)

// Scan into a var.
var name string
err := db.NewSelect().Model(&user).Column("name").Where("id = 1").Scan(ctx, &name)

// Scan columns into separate slices.
var ids []int64
var names []string
err := db.NewSelect().Model(&user).Column("id", "name").Limit(100).Scan(ctx, &ids, &names)

Scanning rows

To execute custom query and scan all rows:

var users []User
err := db.NewRaw("SELECT * FROM users").Scan(ctx, &users)


Sometimes, you want to ignore some fields when inserting or updating data, but still be able to scan columns into the ignored fields. You can achieve that with scanonly option:

type Model struct {
    Foo string
-    Bar string `"ch:"-"`
+    Bar string `"ch:",scanonly"`

Ignoring unknown columns

To discard unknown SQL columns, you can use WithDiscardUnknownColumns db option:

db := ch.Connect(ch.WithDiscardUnknownColumns())

If you want to ignore a single column, just underscore it:

err := db.NewSelect().
    ColumnExpr("1 AS _rank"). // ignore the column when scanning
    OrderExpr("_rank DESC").  // but use it for sorting
Last Updated: