Building Robust SQL Transaction Execution in Go with a Generic Framework

When working with SQL databases in Go, ensuring atomicity and managing rollbacks during multi-step transactions can be challenging. In this article, I'll guide you through creating a robust, reusable, and testable framework for executing SQL transactions in Go, using generics for flexibility.

We'll build a SqlWriteExec utility for executing multiple dependent database operations within a transaction. It supports both stateless and stateful operations, enabling sophisticated workflows like inserting related entities while managing dependencies seamlessly.

Why Do We Need a Framework for SQL Transactions?

In real-world applications, database operations are rarely isolated. Consider these scenarios:

Inserting a user and updating their inventory atomically.
Creating an order and processing its payment, ensuring consistency.
With multiple steps involved, managing rollbacks during failures becomes crucial to ensure data integrity.

Working with go in Txn management.

If you are writing a database txn there might be several boiler plates that you might need to consider before writing the core logic. While this txn management is managed by spring boot in java and you never bothered much on those while writing code in java but this is not the case in golang. A simple example is provided below

func basicTxn(db *sql.DB) error {
    // start a transaction
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer func() {
        if r := recover(); r != nil {
            tx.Rollback()
        } else if err != nil {
            tx.Rollback()
        } else {
            tx.Commit()
        }
    }()

    // insert data into the orders table
    _, err = tx.Exec("INSERT INTO orders (id, customer_name, order_date) VALUES (1, 'John Doe', '2022-01-01')")
    if err != nil {
        return err
    }
    return nil
}

We cannot expect to repeat the rollback/commit code for every function. We have two options here either create a class which will provide a function as a return type which when executed in the defer will commit/rollback txn or create a wrapper class which will wrap all txn funcs together and execute in one go.

I went with the later choice and the change in code can be seen below.

func TestSqlWriteExec_CreateOrderTxn(t *testing.T) {

    db := setupDatabase()
    // create a new SQL Write Executor
    err := dbutils.NewSqlTxnExec[OrderRequest, OrderProcessingResponse](context.TODO(), db, nil, &OrderRequest{CustomerName: "CustomerA", ProductID: 1, Quantity: 10}).
        StatefulExec(InsertOrder).
        StatefulExec(UpdateInventory).
        StatefulExec(InsertShipment).
        Commit()
    // check if the transaction was committed successfully
    if err != nil {
        t.Fatal(err)
        return
    }
    verifyTransactionSuccessful(t, db)
    t.Cleanup(
        func() { 
            cleanup(db)
            db.Close() 
        },
    )
}

func InsertOrder(ctx context.Context, txn *sql.Tx, order *OrderRequest, orderProcessing *OrderProcessingResponse) error {
    // Insert Order
    result, err := txn.Exec("INSERT INTO orders (customer_name, product_id, quantity) VALUES (, , )", order.CustomerName, order.ProductID, order.Quantity)
    if err != nil {
        return err
    }
    // Get the inserted Order ID
    orderProcessing.OrderID, err = result.LastInsertId()
    return err
}

func UpdateInventory(ctx context.Context, txn *sql.Tx, order *OrderRequest, orderProcessing *OrderProcessingResponse) error {
    // Update Inventory if it exists and the quantity is greater than the quantity check if it exists
    result, err := txn.Exec("UPDATE inventory SET product_quantity = product_quantity -  WHERE id =  AND product_quantity >= ", order.Quantity, order.ProductID)
    if err != nil {
        return err
    }
    // Get the number of rows affected
    rowsAffected, err := result.RowsAffected()
    if rowsAffected == 0 {
        return errors.New("Insufficient inventory")
    }
    return err
}

func InsertShipment(ctx context.Context, txn *sql.Tx, order *OrderRequest, orderProcessing *OrderProcessingResponse) error {
    // Insert Shipment
    result, err := txn.Exec("INSERT INTO shipping_info (customer_name, shipping_address) VALUES (, 'Shipping Address')", order.CustomerName)
    if err != nil {
        return err
    }
    // Get the inserted Shipping ID
    orderProcessing.ShippingID, err = result.LastInsertId()
    return err
}

This code will be very much more precise and concise.

How the core logic is implemented

The idea is to isolate the txn to a single go struct such that it can accept multiple txns. By txn I mean functions which will do action with the txn that we created for the class.

type TxnFn[T any] func(ctx context.Context, txn *sql.Tx, processingReq *T) error
type StatefulTxnFn[T any, R any] func(ctx context.Context, txn *sql.Tx, processingReq *T, processedRes *R) error

These two are function types which will take in a txn to process something. Now in the data layer implementing a create a function like this and pass it to the executor class which takes care of injecting the args and executing the function.

// SQL Write Executor is responsible when executing write operations
// For dependent writes you may need to add the dependent data to processReq and proceed to the next function call
type SqlTxnExec[T any, R any] struct {
    db               *sql.DB
    txn              *sql.Tx
    txnFns         []TxnFn[T]
    statefulTxnFns []StatefulTxnFn[T, R]
    processingReq    *T
    processedRes     *R
    ctx              context.Context
    err              error
}

This is where we store all the txn_fn details and we will have Commit() method to try committing the txn.

func (s *SqlTxnExec[T, R]) Commit() (err error) {
    defer func() {
        if p := recover(); p != nil {
            s.txn.Rollback()
            panic(p)
        } else if err != nil {
            err = errors.Join(err, s.txn.Rollback())
        } else {
            err = errors.Join(err, s.txn.Commit())
        }
        return
    }()

    for _, writeFn := range s.txnFns {
        if err = writeFn(s.ctx, s.txn, s.processingReq); err != nil {
            return
        }
    }

    for _, statefulWriteFn := range s.statefulTxnFns {
        if err = statefulWriteFn(s.ctx, s.txn, s.processingReq, s.processedRes); err != nil {
            return
        }
    }
    return
}

You can find more examples and tests in the repo -
https://github.com/mahadev-k/go-utils/tree/main/examples

Though we bias towards distributed systems and consensus protocol nowadays, we still use sql and it still exists.

Let me know if anyone wish to contribute and build on top of this!!
Thanks for reading this far!!
https://in.linkedin.com/in/mahadev-k-934520223
https://x.com/mahadev_k_

The above is the detailed content of Building Robust SQL Transaction Execution in Go with a Generic Framework. For more information, please follow other related articles on the PHP Chinese website!