Transactions Usage

[sachnk]

Hi,

I'm trying to use transactions with sqlboiler with psql and I want to confirm my usage of Rollback() and Confirm()

For example, if I begin a transaction and I detect an error and want to abort the transaction, should I call Rollback()? Or is it sufficient to simply not call Commit()?

tx, _ := db.BeginTx(...)

// on error, abort the transaction
if some_error {
  tx.Rollback() // <-- do we need to rollback, or can we just return out?
  return
}

// no error, commit the transaction
tx.Commit()

[aarondl]

You always need to explicitly rollback or commit a transaction (this isn't really a part of sqlboiler fwiw). The way I handle this in a program is with a helper:

func Tx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	err = fn(tx)
	if err != nil {
		tx.Rollback()
		return err
	}

	return tx.Commit()
}

Then you call it like this:

helpers.Tx(ctx, db, true, func (tx *sql.Tx) error {
   err := // do something databasey
   if err != nil {
     return err // The helper will automatically rollback for you here
   }

   return nil // The helper will automatically call commit since you returned nil
})