Writing queries

Design

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().
	Model(book).
	ColumnExpr("lower(name)").
	Where("? = ?", ch.Ident("id"), "some-id").
	Scan(ctx)

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:

rows, err := db.QueryContext(ctx, "SELECT * FROM users")
if err != nil {
    panic(err)
}

err = db.ScanRows(ctx, rows, &users)

To scan row by row:

rows, err := db.NewSelect().Model((*User)(nil)).Rows(ctx)
if err != nil {
	panic(err)
}
defer rows.Close()

for rows.Next() {
	user := new(User)
	if err := db.ScanRow(ctx, rows, user); err != nil {
		panic(err)
	}
}

if err := rows.Err(); err != nil {
	panic(err)
}

Scanonly

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

# Writing queries

# Design

# Scan and Exec

# Scanning rows