SQL placeholders
Introduction
go-clickhouse recognizes ?
in queries as placeholders and replaces them with provided args. It quotes and escapes stringly values and removes null bytes.
Basic and positional placeholders
To use basic placeholders:
// SELECT 'foo', 'bar'
db.ColumnExpr("?, ?", 'foo', 'bar')
To use positional placeholders:
// SELECT 'foo', 'bar', 'foo'
db.ColumnExpr("?0, ?1, ?0", 'foo', 'bar')
ch.Ident
To quote SQL identifiers, for example, a column or a table name, use ch.Ident
:
q.ColumnExpr("? = ?", ch.Ident("foo"), "bar")
"foo" = 'bar'
ch.Safe
To disable quotation altogether, use ch.Safe
:
q.TableExpr("(?) AS foo", ch.Safe("numbers(1000)"))
FROM (numbers(1000)) AS foo
IN
Provides a ch.In
helper to generate IN (...)
queries:
// WHERE foo IN ('hello', 'world')
q.Where("foo IN (?)", ch.In([]string{"hello", "world"}))
For composite (multiple) keys you can use nested slices:
// WHERE (foo, bar) IN (('hello', 'world'), ('hell', 'yeah'))
q.Where("(foo, bar) IN (?)", ch.In([][]string{
{"hello", "world"},
{"hell", "yeah"},
}))
Model placeholders
go-clickhouse also supports the following model placeholders:
?TableName
- model table name, for example,"users"
.?TableAlias
- model table alias, for example,"user"
.?PKs
- table primary keys, for example,"id"
?TablePKs
- table primary keys with the alias, for example,"user"."id"
?Columns
- table columns, for example,"id", "name", "emails"
.?TableColumns
- table columns with the alias, for example,"user"."id", "user"."name", "user"."emails"
.
See placeholders example for details.
Global placeholders
Ch also supports global placeholders:
// db1 and db2 share the same *sql.DB, but have different named args.
db1 := db.WithNamedArg("DB", "foo")
db2 := db.WithNamedArg("DB", "bar")
// FROM foo.table
db1.NewSelect().TableExpr("?DB.table")
// FROM bar.table
db2.NewSelect().TableExpr("?DB.table")