Database SQL transactions

Starting transactions

bun.Tx is a thin wrapper around sql.Tx. In addition to the features provided by sql.Tx, bun.Tx also supports query hooks and provides helpers to build queries.

  1. type Tx struct {
  2.     *sql.Tx
  3.     db *DB
  4. }

To start a transaction:

  1. tx, err := db.BeginTx(ctx, &sql.TxOptions{})

To commit/rollback the transaction:

  1. err := tx.Commit()
  3. err := tx.Rollback()

Running queries in a trasaction

Just like with bun.DB, you can use bun.Tx to run queries:

  1. res, err := tx.NewInsert().Model(&models).Exec(ctx)
  3. res, err := tx.NewUpdate().Model(&models).Column("col1", "col2").Exec(ctx)
  5. err := tx.NewSelect().Model(&models).Limit(100).Scan(ctx)

RunInTx

Bun provides RunInTx helpers that runs the provided function in a transaction. If the function returns an error, the transaction is rolled back. Otherwise, the transaction is committed.

  1. err := db.RunInTx(ctx, &sql.TxOptions{}, func(ctx context.Context, tx bun.Tx) error {
  2.     _, err := tx.Exec(...)
  3.     return err
  4. })

IDB interface

Bun provides bun.IDB interface so the same methods can work with *bun.DB, bun.Tx, and bun.Conn. The following exampleTransactions - 图1open in new window demonstrates how InsertUser uses the bun.IDB to support transactions:

  1. // Insert single user using bun.DB.
  2. err := InsertUser(ctx, db, user1)
  4. // Insert several users in a transaction.
  5. err := db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
  6.     if err := InsertUser(ctx, tx, user1); err != nil {
  7.         return err
  8.     }
  9.     if err := InsertUser(ctx, tx, user2); err != nil {
  10.         return err
  11.     }
  12.     return nil
  13. })
  15. func InsertUser(ctx context.Context, db bun.IDB, user *User) error {
  16.     _, err := db.NewInsert().Model(user).Exec(ctx)
  17.     return err
  18. }

PostgreSQL advisory locks

You can ackquire a PostgreSQL advisory lock using the following code:

  1. err := db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
  2.     if _, err := tx.ExecContext(ctx, "SELECT pg_advisory_xact_lock(1)"); err != nil {
  3.         return err
  4.     }
  5.     if _, err := db.NewSelect().ColumnExpr("pg_advisory_xact_lock(2)").Exec(ctx); err != nil {
  6.         return err
  7.     }
  8.     return nil
  9. })