Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A simple, neat way to wrap code in an sql transaction with proper committing/rollbacking and error handling.
// 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)
}
@lyoshenka

This comment has been minimized.

Copy link
Owner Author

commented Sep 4, 2017

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)
}
@rbastic

This comment has been minimized.

Copy link

commented Sep 11, 2017

Hi,

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

Cheers,

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.