Simple DB transaction handling in Go
Transactions are almost an essential part of any application.
Oh no, one more article about handling db transactions in Go.
Yes there are a plenty articles on internet how should we treat and write transaction code and where should be placed in our project structure. I am not sure why people always put so much effort and complexity in writing basic articles about basic things in Go like db transaction. This article will not cover how transactions should be written in Clean Architecture, Hexagonal Architecture or layers like repository, usecase, domain etc. It will follow top-down approach so first part explains very simple API usage and then I will try to explain implementation. API described here is very simple and contains just 2 methods and one package function. Method names are self explanatory, Acquire method just start the transaction but doesn’t complete it and Tx method which process function fn in transaction. The Tx method is usually used, only if you need custom flow then you can use Acquire.
func (t *TxProvider) Acquire(ctx context.Context) (*Tx, error)
func (t *TxProvider) Tx(ctx context.Context, fn func(TxContext) error) error
func FromCtxOrDB(ctx context.Context, db Access) AccessExample:
// main app or application layer
conn, err := sql.Open("sqlite3", "./test.db")
if err != nil {
log.Fatal(err)
}
ctx, cancel := context.WithTimeout(context.Background(), time.Second*30)
defer cancel()
provider := db.NewTxProvider(conn)
// all dao calls will be in one transaction
provider.Tx(ctx, func(ctx db.TxContext) error {
err := dao.CreateUser(ctx, user)
if err != nil {
return err
}
err = dao.CreateProfile(ctx, id, userProfile)
if err != nil {
return err
}
return nil
})If an error occurred, the transaction is rolled back, otherwise it is committed. One thing to note we need to be sure the DAO functions must read context and work with tx object. For example:
// dao layer or repository
CreateProfile(ctx context.Context, id string, userProfile *UserProfile) error {
tx := FromCtxOrDB(ctx, db)
_, err := tx.ExecContext(ctx, query, args...)
if err != nil {
return err
}
return nil
}Can we do better? Yes, we can replace context.Context with TxContext interface which holds context and additional db access methods and there is no needs to get tx value from context. Function FromCtxOrDB is used only for already created DAO/Repo functions. The new API functions should always use db.TxContext
// dao layer or repository
CreateProfile(ctx db.TxContext, id string, userProfile *UserProfile) error {
_, err := ctx.Exec(query, args...)
if err != nil {
return err
}
return nil
}In above methods signatures there are no options or configurations parameters like readonly or isolationLevel, so I introduced alternative methods with sql.TxOptions:
func (t *TxProvider) AcquireWithOpts(ctx context.Context, opts sql.TxOptions) (*Tx, error)
func (t *TxProvider) TxWithOpts(ctx context.Context, fn func(TxContext) error, opts *sql.TxOptions) erroror you can change package variable DefaultTxOpts:
db.DefaultTxOpts = sql.TxOptions{
Isolation: sql.LevelDefault,
ReadOnly: false,
}What is Tx struct? Tx struct holds Context and sql.Tx and it can be passed as argument to any function or method which accepts context.Context or *sql.Tx, some methods are introduced without context because Tx itself contains Context.
type Tx struct {
context.Context
*sql.Tx
}
func (t *Tx) Prepare(query string) (*sql.Stmt, error) {
return t.Tx.PrepareContext(t.Context, query)
}
func (t *Tx) Exec(query string, args ...any) (sql.Result, error) {
return t.Tx.ExecContext(t.Context, query, args...)
}
func (t *Tx) Query(query string, args ...any) (*sql.Rows, error) {
return t.Tx.QueryContext(t.Context, query, args...)
}
func (t *Tx) QueryRow(query string, args ...any) *sql.Row {
return t.Tx.QueryRowContext(t.Context, query, args...)
}This approach allows to use API for already defined db store functions or methods in your project and provide new functionality with the first argument as TxContext type
type TxContext interface {
context.Context
Prepare(query string) (*sql.Stmt, error)
Exec(query string, args ...any) (sql.Result, error)
Query(query string, args ...any) (*sql.Rows, error)
QueryRow(query string, args ...any) *sql.Row
}Tx provider accepts interface connector with one method for starting new transaction
type Connector interface {
BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
}
// TxProvider ...
type TxProvider struct {
dbtx Connector
}
// NewTxProvider ...
func NewTxProvider(dbtx Connector) *TxProvider {
return &TxProvider{
dbtx: dbtx,
}
}Acquire transaction implementation
type txKeyType struct{}
// AcquireWithOpts ...
func (t *TxProvider) AcquireWithOpts(ctx context.Context, opts sql.TxOptions) (*Tx, error) {
tx, err := t.conn.BeginTx(ctx, &opts)
if err != nil {
return nil, err
}
return &Tx{
Context: context.WithValue(ctx, txKeyType{}, Access(tx)),
Tx: tx,
}, nil
}
// Acquire ...
func (t *TxProvider) Acquire(ctx context.Context) (*Tx, error) {
return t.AcquireWithOpts(ctx, DefaultTxOpts)
}TxWithOpts is base method for decorator Tx with additional params sql.TxOptions
// DoWithOpts ...
func (t *TxProvider) TxWithOpts(ctx context.Context, fn func(TxContext) error, opts sql.TxOptions) error {
tx, err := t.AcquireWithOpts(ctx, opts)
if err != nil {
return err
}
defer func() {
if r := recover(); r != nil {
log.Printf("Recovering from panic in TxWithOpts error is: %v \n", r)
_ = tx.Rollback()
err, _ = r.(error)
} else if err != nil {
err = tx.Rollback()
} else {
err = tx.Commit()
}
if ctx.Err() != nil && errors.Is(err, context.DeadlineExceeded) {
log.Printf("query response time exceeded the configured timeout")
}
}()
err = fn(tx)
return err
}
func (t *TxProvider) Tx(ctx context.Context, fn func(TxContext) error) error {
return t.ProcessWithOpts(ctx, fn, DefaultTxOpts)
}FromCtxOrDB is package level function which returns if there is transaction object in context otherwise return db.
type Access interface {
PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}
func FromCtxOrDB(ctx context.Context, db Access) Access {
value, ok := ctx.Value(txKeyType{}).(Access)
if ok {
return value
}
return db
}Conclusion
In most cases I call provider in controller or in core of app logic but if you follow DDD, Clean architecture or Hexagonal architecture and want to isolate db layer then transaction should be there. We dont want to see sql.DB and sql.Tx in upper layers of our project structure.
