Writing Queries

Design

Bun’s 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 CLI for your database (for example, psql), and then re-construct resulting queries using Bun’s query builder.

The main features are:

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

For example, the following Go code:

  1. err := db.NewSelect().
  2.     Model(book).
  3.     ColumnExpr("lower(name)").
  4.     Where("? = ?", bun.Ident("id"), "some-id").
  5.     Scan(ctx)

Unsurprsingly generates the following query:

  1. SELECT lower(name)
  2. FROM "books"
  3. WHERE "id" = 'some-id'

Scan and Exec

You can create queries using bun.DBWriting queries - 图1open in new window, bun.TxWriting queries - 图2open in new window, or bun.ConnWriting queries - 图3open in new window:

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

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

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

  1. 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:

  1. 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.
  1. // Scan into a map.
  2. m := make(map[string]interface{})
  3. err := db.NewSelect().Model(&user).Where("id = 1").Scan(ctx, &m)
  5. // Scan into a slice of maps.
  6. ms := make([]map[string]interface{}, 0)
  7. err := db.NewSelect().Model(&user).Limit(100).Scan(ctx, &ms)
  9. // Scan into a var.
  10. var name string
  11. err := db.NewSelect().Model(&user).Column("name").Where("id = 1").Scan(ctx, &name)
  13. // Scan columns into separate slices.
  14. var ids []int64
  15. var names []string
  16. err := db.NewSelect().Model(&user).Column("id", "name").Limit(100).Scan(ctx, &ids, &names)

bun.IDB

Bun provides bun.IDB interface which you can use to accept bun.DB, bun.Tx, and bun.Conn:

  1. func InsertUser(ctx context.Context, db bun.IDB, user *User) error {
  2.     _, err := db.NewInsert().Model(user).Exec(ctx)
  3.     return err
  4. }
  6. err := InsertUser(ctx, db, user)
  8. err := db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
  9.     return InsertUser(ctx, tx, user)
  10. })

Scanning rows

To execute custom query and scan all rows:

  1. rows, err := db.QueryContext(ctx, "SELECT * FROM users")
  2. if err != nil {
  3.     panic(err)
  4. }
  6. err = db.ScanRows(ctx, rows, &users)

To scan row by row:

  1. rows, err := db.NewSelect().Model((*User)(nil)).Rows(ctx)
  2. if err != nil {
  3.     panic(err)
  4. }
  5. defer rows.Close()
  7. for rows.Next() {
  8.     user := new(User)
  9.     if err := db.ScanRow(ctx, rows, user); err != nil {
  10.         panic(err)
  11.     }
  12. }
  14. if err := rows.Err(); err != nil {
  15.     panic(err)
  16. }

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:

  1. type Model struct {
  2.     Foo string
  3. -    Bar string `"bun:"-"`
  4. +    Bar string `"bun:",scanonly"`
  5. }

Ignoring unknown columns

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

  1. db := bun.NewDB(sqldb, pgdialect.New(), bun.WithDiscardUnknownColumns())

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

  1. err := db.NewSelect().
  2.     ColumnExpr("1 AS _rank"). // ignore the column when scanning
  3.     OrderExpr("_rank DESC").  // but use it for sorting
  4.     Scan(ctx)