A simple, neat way to wrap code in an sql transaction with proper committing/rollbacking and error handling.

tx.go

// TxFunc is a function that can be wrapped in a transaction
type TxFunc func(tx *sql.Tx) error

// WithTx wraps a function in an sql transaction. After the function returns, the transaction is
// committed if there's no error, or rolled back if there is one.
func WithTx(db *sql.DB, f TxFunc) (err error) {
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p)
		} else if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()

	return f(tx)
}

Here's a simple example:

db, _ := sql.Open("mysql", "localhost:3366")

var countMortys int

err := WithTx(db, func(tx *sql.Tx) error {
	result, err := tx.Exec(`INSERT INTO person SET first_name = "Rick", last_name = "Sanchez"`)
	if err != nil {
		return err
	}

	rickID, err := result.LastInsertId()
	if err != nil {
		return err
	}

	_, err = tx.Exec(`INSERT INTO person SET first_name = "Morty", last_name = "Smith", guardian_id = ?`, rickID)
	if err != nil {
		return err
	}

	// if you need to return other values, declare the variable outside the function, then set it inside
	err = tx.QueryRow(`SELECT count(*) from person where guardian_id = ?`).Scan(&countMortys)
	if err != nil {
		return err
	}

	// if there's an error, the whole transaction is rolled back
	_, err = tx.Exec(`INSERT INTO person SET guardian_id = "this is gonna error. wubbu lubba dub dub!"`)
	if err != nil {
		return err
	}

	// if no error, everything gets committed after the function returns
	return nil
})
if err != nil {
	panic(err)
}

Hi,

You're not checking errors on Rollback()'s. 'gometalinter' can help you with detecting these errors at compile-time.

Cheers,