Using GORM – Part 2: Transactions and Save Points

2023-03-04 Development Tips & Tricks No Comments 6 minute read

Previous posts in the series:
Part 1: Introduction

Now that we know the basics of working with GORM, let’s cover a few more advanced topics.

Transactions

Transactions are an essential concept in relational databases that allow developers to ensure data consistency and integrity. A transaction is a sequence of operations that are executed as a single unit of work. The ACID (Atomicity, Consistency, Isolation, Durability) properties of a transaction ensure that either all of the operations in the transaction are executed successfully or none of them are. This guarantees that the database remains in a consistent state, even if an error occurs during the execution of the transaction. Transactions are particularly important in scenarios where multiple concurrent operations may access the same data, such as in a web application or an e-commerce platform. In such cases, transactions provide a way to manage concurrent access to the database and prevent conflicts that could lead to data corruption. Overall, transactions are a powerful tool for maintaining data integrity in relational databases, and they are a crucial component of any robust database-driven application.

GORM provides us with an easy way of creating a transaction:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
func TestCreateItemsInTransaction(t *testing.T) {
    db, err := connectToDB()
    require.NoError(t, err)

    err = db.Transaction(func(tx *gorm.DB) error {
        user := &User{FirstName: "Alexander", LastName: "Sirotin"}
        if err := tx.Create(user).Error; err != nil {
            return err
        }

        bill := &Bill{UserID: user.UUID, Amount: 100, DueDate: time.Now().Add(7 * 24 * time.Hour)}
        return tx.Create(bill).Error
    })
    require.NoError(t, err)
}

In this example, if the user creation fails, the bill won’t be created, and if the bill creation fails, the user will be rolled back. Note that I am using the “tx” object instead of “db” as part of the transaction body, and this is GORM’s way of keeping the isolation.
Let’s have an example to illustrate this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
func TestCreateItemsInTransaction(t *testing.T) {
    db, err := connectToDB()
    require.NoError(t, err)

    err = db.Transaction(func(tx *gorm.DB) error {
        user := &User{FirstName: "Alexander", LastName: "Sirotin"}
        if err := tx.Create(user).Error; err != nil {
            return err
        }

        var result *User
        err := tx.Where("uuid = ?", user.UUID).First(&result).Error
        require.NoError(t, err)
        assert.Equal(t, *user, *result)

        err = db.First(&result).Error
        require.Error(t, err)
        assert.EqualError(t, err, "record not found")

        bill := &Bill{UserID: user.UUID, Amount: 100, DueDate: time.Now().Add(7 * 24 * time.Hour)}
        return tx.Create(bill).Error
    })
    require.NoError(t, err)
}

The generated SQL queries are:

1
2
3
4
5
6
7
8
9
10
11
-- user creation
[2.081ms] [ROWS:1] INSERT INTO "users" ("uuid","first_name","last_name") VALUES ('2ccf9973-8736-45bf-b956-f84877115cdb','Alexander','Sirotin')

-- lookup the user, within the transaction isolation - found
[1.930ms] [ROWS:1] SELECT * FROM "users" WHERE uuid = '2ccf9973-8736-45bf-b956-f84877115cdb' ORDER BY "users"."uuid" LIMIT 1

-- lookup the user outside of the transaction isolation - not found
[10.643ms] [ROWS:0] SELECT * FROM "users" WHERE "users"."uuid" = '2ccf9973-8736-45bf-b956-f84877115cdb' ORDER BY "users"."uuid" LIMIT 1

-- bill creation
[2.002ms] [ROWS:1] INSERT INTO "bills" ("uuid","user_id","amount","due_date") VALUES ('cac4e7b0-efd2-41c7-b07d-016bc136a806','2ccf9973-8736-45bf-b956-f84877115cdb',100,'2023-03-11 03:23:21.803')

A few nuances to notice here. When we tried to look for the user by providing its uuid on the tx object, we were within the transaction isolation, so we got the result back as expected. Then, when using the db object (outside of the transaction isolation), we didn’t have a match as the user record was not committed yet. It’s also good to notice that when looking for the user object for the second time, I haven’t used any “Where” expressions, but the generated SQL query is correct. The reason is that GORM will look at the object passed within “First” (in this case, the actual user object preloaded) and will try to use its primary key (in this case, the user uuid) for the lookup.

GORM transaction wrapper is basically doing the following (with more error handling and panic handling):

1
2
3
4
5
6
7
tx := db.Begin()
err := user_provided_fund(tx)
if err != nil {
    tx.Rollback()
} else {
    tx.Commit()
}

As the transaction session can lock different records in different tables, it’s important not to have any network calls or any other long operations inside, as it may affect the overall performance of the system.

Save Points

Database savepoints are a mechanism that allows developers to create intermediate checkpoints within a transaction, which can be used to roll back a part of the transaction instead of rolling back the entire transaction. Savepoints can be useful in situations where a complex transaction has multiple stages, and it may be desirable to roll back to a specific point in the transaction rather than starting from the beginning. For example, if a database operation fails midway through a transaction, the use of savepoints can allow developers to roll back to the most recent savepoint and retry only the failed operation, rather than restarting the entire transaction from scratch. Savepoints can be created and released using SQL commands within a transaction and are supported by most modern relational databases. Overall, the use of savepoints provides developers with greater flexibility and control over transactions, making it easier to handle errors and ensure data consistency.

Here is a simple example of using save points with GORM:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
func TestTransactionWithSavePoints(t *testing.T) {
    db, err := connectToDB()
    require.NoError(t, err)

    tx := db.Begin()

    user := &User{FirstName: "Alexander", LastName: "Sirotin"}
    err = tx.Create(user).Error
    require.NoError(t, err)

    tx.SavePoint("user_created")

    bill := &Bill{UserID: user.UUID, Amount: 100, DueDate: time.Now().Add(7 * 24 * time.Hour)}
    err = tx.Create(bill).Error
    require.NoError(t, err)

    err = tx.RollbackTo("user_created").Error
    require.NoError(t, err)

    tx.Commit()

    var userResult *User
    err = db.Where("uuid = ?", user.UUID).First(&userResult).Error
    require.NoError(t, err)

    var billResult *Bill
    err = db.Where("uuid = ?", bill.UUID).First(&billResult).Error
    require.Error(t, err)
    assert.EqualError(t, err, "record not found")
}

In this example, we started a transaction ourselves, created a user and then took a save point. Then, we created a bill and rolled it back to the save point. At this stage, we do still have an open transaction, but the bill is not there anymore. Once we commit the transaction, we verify that the user exists and the bill is gone.
The created SQL queries are:

1
2
3
4
5
6
[2.173ms] [ROWS:1] INSERT INTO "users" ("uuid","first_name","last_name") VALUES ('1b8aeecd-0452-4adc-a739-cb2b5d115aaa','Alexander','Sirotin')
[0.533ms] [ROWS:0] SAVEPOINT user_created
[2.066ms] [ROWS:1] INSERT INTO "bills" ("uuid","user_id","amount","due_date") VALUES ('e8d1bcde-b0a3-40c6-b479-214fcc663fcf','1b8aeecd-0452-4adc-a739-cb2b5d115aaa',100,'2023-03-11 03:48:01.588')
[0.572ms] [ROWS:0] ROLLBACK TO SAVEPOINT user_created
[1.593ms] [ROWS:1] SELECT * FROM "users" WHERE uuid = '1b8aeecd-0452-4adc-a739-cb2b5d115aaa' ORDER BY "users"."uuid" LIMIT 1
[1.110ms] [ROWS:0] SELECT * FROM "bills" WHERE uuid = 'e8d1bcde-b0a3-40c6-b479-214fcc663fcf' ORDER BY "bills"."uuid" LIMIT 1

Tip: When running unit tests that talk with the database, you want to remove any dependencies between the tests by keeping them isolated, and rolling back the changes once the test is done. For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
func createIsolatedDatabaseConnection(t *testing.T) *gorm.DB {
    t.Helper()

    db, err := connectToDB()
    require.NoError(t, err)

    tx := db.Begin()
    t.Cleanup(func() {
        tx.Rollback()
    })
    return tx
}

func TestCreateUserInIsolation(t *testing.T) {
    db := createIsolatedDatabaseConnection(t)

    user := &User{FirstName: "Bob", LastName: "Marley"}
    err := db.Create(user).Error
    require.NoError(t, err)
}

func TestCheckIfBobMarleyExists(t *testing.T) {
    db, err := connectToDB()
    require.NoError(t, err)

    var user *User
    err = db.First(&user, User{FirstName: "Bob", LastName: "Marley"}).Error
    require.Error(t, err)
    assert.EqualError(t, err, "record not found")
}

Here, we basically run the test in isolation so regardless of the test execution order, Bob Marley won’t be available for other tests. When you have different modules with multiple tables, this becomes more important, as cleaning up the tables between executions won’t help much as you still cannot run things in parallel, and it is a way more time-consuming task than just using the existing database rollback functionality.

Note: You need to be careful to always close the transaction, either by committing it or by rolling it back, even if something fails or panic.

Additional information about using transactions with GORM can be found in the official documentation.

– Alexander

Oh hi there 👋
It’s nice to meet you.

Sign up to receive a notification when new posts are published!

We don’t spam!

Using GORM - Part 1: Introduction

Using GORM - Part 3: Models and Idempotency
Leave a Reply