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 exampleopen 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. })