SQL Placeholders

Introduction

Bun recognizes ? in queries as placeholders and replaces them with provided args. Bun quotes and escapes stringly values and removes null bytes.

Basic and positional placeholders

To use basic placeholders:

1. // SELECT 'foo', 'bar'
2. db.ColumnExpr("?, ?", 'foo', 'bar')

To use positional placeholders:

1. // SELECT 'foo', 'bar', 'foo'
2. db.ColumnExpr("?0, ?1, ?0", 'foo', 'bar')

bun.Ident

To quote SQL identifiers, for example, a column or a table name, use bun.Ident:

1. q.ColumnExpr("? = ?", bun.Ident("foo"), "bar")

1. "foo" = 'bar' -- PostgreSQL
2. `foo` = 'bar' -- MySQL

bun.Safe

To disable quotation altogether, use bun.Safe:

1. q.TableExpr("(?) AS foo", bun.Safe("generate_series(0, 10)"))

1. FROM (generate_series(0, 10)) AS foo

IN

Provides a bun.In helper to generate IN (...) queries:

1. // WHERE foo IN ('hello', 'world')
2. q.Where("foo IN (?)", bun.In([]string{"hello", "world"}))

For composite (multiple) keys you can use nested slices:

1. // WHERE (foo, bar) IN (('hello', 'world'), ('hell', 'yeah'))
2. q.Where("(foo, bar) IN (?)", bun.In([][]string{
3. {"hello", "world"},
4. {"hell", "yeah"},
5. }))

Model placeholders

Bun 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 placeholdersSQL placeholders - 图1open in new window example for details.

Global placeholders

Bun also supports global placeholders:

1. // db1 and db2 share the same *sql.DB, but have different named args.
2. db1 := db.WithNamedArg("SCHEMA", "foo")
3. db2 := db.WithNamedArg("SCHEMA", "bar")

5. // FROM foo.table
6. db1.NewSelect().TableExpr("?SCHEMA.table")

8. // FROM bar.table
9. db2.NewSelect().TableExpr("?SCHEMA.table")