Golang Database Library Orm Example Transaction

Typically, to make a transaction, we first start by calling BeginTx() method in database/sql package which returns a Tx transaction struct.

Following the pattern from the Go blog at https://go.dev/doc/database/execute-transactions, we defer the rollback operation immediately after starting a transaction and ends with a commit. This ensures that a rollback is always called even if any error or panic happens in the function.

If you carefully analyse the pattern above, you will notice that a rollback will happen after a commit. Fortunately, if that transaction is already done, Go will ignore the rollback and does not make another round trip to the database - Rollback() will be ignored if the transaction has been committed as seen from the source code,

// rollback aborts the transaction and optionally forces the pool to discard
// the connection.
func (tx *Tx) rollback(discardConn bool) error {
	if !atomic.CompareAndSwapInt32(&tx.done, 0, 1) {
		return ErrTxDone
	}
	...

While knowing the pattern above is important, we want to know a couple of things when it comes to creating a transaction across multiple SQL queries.

1. Reuse existing methods, collect, and run them in a transaction.
2. Adjust transaction isolation levels.

It would be nice if we can just reuse existing methods because why should we repeat ourselves if the operations are the same, but we needed a transaction over them.

Understanding isolation level is essential when deciding which level we want our transaction to run on. Isolation is the ‘I’ in ACID. There are four isolation levels in SQL. In reality, there are only three in postgres as Read Uncommitted behaves like Read Committed. You can read more about isolation level in further details. The default in postgres is read committed. This means that a SELECT query will only see data committed before the query began. In other words, it will not see any in-progress uncommitted changes.

In this post, we will see how these libraries and ORMs fare with creating and using a transaction as well as changing an isolation level.

Table of Contents

sqlx

The pattern is pretty straight forward

1. Start a transaction

tx, err := r.db.Beginx()

2. Immediately defer Rollback(). Error is ignored because if rollback fails, it is not going to be committed anyway.

defer tx.Rollback()

3. Call sql queries

var u db.UserDB
err = tx.GetContext(ctx, &u, getSQLQuery, userID)

4. Commits

if err = tx.Commit(); err != nil {
    return nil, err
} 

Note that it is possible to use the Begin() function in database/sql package. We use sqlx’s Beginx() because we wanted to use GetContext() convenience function. Otherwise, Begin() is perfectly fine.

To answer the two questions we set in the opening introduction, let us see how they do.

Notice that we cannot compose existing Get() and Update() methods together and apply a transaction on them. We needed to either change the implementation of both methods to accept a tx transaction variable, or make the method receiver to include tx transaction.

Moving on to changing transaction level, there are two ways of doing it.

1. Execute an sql query, for example tx.Exec('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;')
2. Use sql.TxOptions{} struct from database/sql package.

There is no drama when setting the isolation level through an sql query:

_, err = tx.ExecContext(ctx, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;")

In the second option, the built-in package database/sql provides a struct called sql.TxOptions in which you can set the isolation level and read-only value.

tx, err := r.db.BeginTx(ctx, &sql.TxOptions{
	Isolation: sql.LevelRepeatableRead,
	ReadOnly:  false,
})

However, there is a caveat. We are now using a method from database/sql instead of sqlx. That means, we cannot use sqlx’s convenience scanning features like GetContext() that automatically scans results into a Go struct.

sqlc

The pattern for transaction is similar to sqlx’s by using methods provided by database/sql package with the addition of a constructor that accepts a transaction.

// Start transaction
tx, err := r.sqlx.DB.Begin()

// or
tx, err := r.sqlx.DB.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelReadCommitted,
    ReadOnly:  false,
})

// Immediately defer the rollback
defer tx.Rollback()

// query will transaction
qtx := pg.New(tx).WithTx(tx)


// Perform queries
currUser, err := qtx.GetUser(ctx, id)
err = qtx.UpdateUser(ctx, pg.UpdateUserParams{ ... })

// Commits transaction
err = tx.Commit()

The secret sauce is qtx := pg.New(tx). Unlike sqlx, now we can compose existing GetUser() and UpdateUser() methods with this tx variable transaction.

Just like sqlx, we can either use BeginTx() method to include isolation level or run an sql query as usual.

squirrel

Like sqlx and sqlx, we need to use the underlying transaction struct of database/sql.

If you look at the code below, it is similar to how we did a Get operation, but in the query builder, we tell squirrel to use tx transaction instead by passing it to the RunWith() method.

tx, err := r.forTx.Beginx()
if err != nil {
    return nil, fmt.Errorf("fails to start transaction error: %w", err)
}

defer tx.Rollback()

rows := r.db.
    Select("*").
    From("users").
    Where(sq.Eq{"id": id}).
    RunWith(tx). // <- ensures that you use transaction. 
    QueryRowContext(ctx)
    
...

_ = tx.Commit()    

using RunWith() means we cannot compose existing methods. Fortunately, setting isolation is as simple as executing the sql query.

gorm

There are two ways do perform database transactions, the first being like the pattern described in the Go blog, and the second way is through anonymous function.

Anonymous Function

Transaction() accepts an anonymous function and provides transaction tx from gorm.

var u User

err := r.db.Transaction(func(tx *gorm.DB) error {
    if err := tx.First(&u).Error; err != nil {
        return err
    }

    u.FirstName = req.FirstName
    u.MiddleName = req.MiddleName
    u.LastName = req.LastName
    u.Email = req.Email
    u.FavouriteColour = req.FavouriteColour

    err := tx.WithContext(ctx).Save(&u).Error
    if err != nil {
        return err
    }

    return nil
})
if err != nil {
    return nil, err
}

return &u, nil

This approach means no composing of existing methods. To change isolation level, simply execute said sql query.

Sqlboiler

Like above, sqlboiler can accept a transaction instead of normal sql.DB thanks to sqlboiler’s methods accepting ContextExecutor interface.

tx, err := r.db.BeginTx(ctx, nil)
if err != nil {
    return nil, fmt.Errorf("fails to start transaction error: %w", err)
}

defer tx.Rollback()

user, err := models.FindUser(ctx, tx, id) // pass in tx instead of sql.DB
if err != nil {
    return nil, err
}

...

_ = tx.Commit()

However, like squirrel, you cannot compose existing Get() and Update() methods. We would need to refactor both Get() and Update() methods.

For example, we can add a new field in our database struct so that we can ‘save’ a transaction to be used in our refactored Get() method.

type database struct {
	db   *sqlx.DB
	exec boil.ContextExecutor // create a new field
}

Set the exec field with this transaction.

tx, err := r.db.BeginTx(ctx, nil)

r.exec = tx // Set our executor with this transaction

Our refactored Get() method now uses exec instead.

func (r *database) GetForTransaction(ctx context.Context, userID int64) (*models.User, error) {
	user, err := models.FindUser(ctx, r.exec, userID) // <- `r.exec`, instead of `r.db`
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, errors.New("no record found")
		}
		return nil, err
	}

	return user, nil
}

A somewhat similar to the Gorm pattern above, we can use a helper as shown in https://github.com/volatiletech/sqlboiler/issues/396#issuecomment-425754304:

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()
}

It begins by creating a transaction. Any errors that happen within the calling the anonymous function fn(tx) will be rolled back. Otherwise, transaction is committed.

To use, we call Tx() function that returns an error. Within the business logic, we replace all r.db with the tx parameter. Note that we needed to declare modesl.User outside of this function, and assign the result we wanted with u = user.

func (r *database) TransactionUsingHelper(ctx context.Context, id int64, req db.UserUpdateRequest) (*models.User, error) {
	u := &models.User{}

	err := Tx(ctx, r.db.DB, func(tx *sql.Tx) error {
		user, err := models.FindUser(ctx, tx, id)

		if err != nil {
			return err
		}

		user.FirstName = req.FirstName
		user.MiddleName = null.String{
			String: req.MiddleName,
			Valid:  req.MiddleName != "",
		}
		user.LastName = req.LastName
		user.Email = req.Email
		user.FavouriteColour = null.String{
			String: req.FavouriteColour,
			Valid:  req.FavouriteColour != "",
		}

		_, err = user.Update(ctx, tx, boil.Infer())
		if err != nil {
			return err
		}

		u = user // assigns value we wanted with `u` outside of this `Tx()`  function.

		return nil

	})
	if err != nil {
		return nil, err
	}

	return u, nil
}

Ent

The documentation at https://entgo.io/docs/transactions describes many ways to perform a transaction. The following method is what has been established:

tx, err := r.db.Tx(ctx)
if err != nil {
    return nil, fmt.Errorf("fails to start transaction error: %w", err)
}

defer tx.Rollback()

user, err := tx.User.UpdateOneID(uint(id)).
    SetFirstName(req.FirstName).
    SetNillableMiddleName(&req.MiddleName).
    SetLastName(req.LastName).
    SetEmail(req.Email).
    Save(ctx)
if err != nil {
    return nil, err
}

_ = tx.Commit()

return user, nil

In conclusion, creating a transaction is rather straight forward. We can use the established pattern from https://go.dev/doc/database/execute-transactions with all of these libraries and ORMs. However, only sqlc easily allows us to reuse existing Get() and Update() methods by composing them and apply a transaction.

Next > Golang Database Library ORM Example - SQL Injection