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 ($1, $2, $3)", 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 - $1 WHERE id = $2 AND product_quantity >= $1", 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 ($1, '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_
Top comments (0)