CODEWITHSUNDEEP
go
aleksandr
aleksandr

Reputation: 87

Idiomatic way to run several funcs with *sql.DB object in one transaction in Go lang

Let we have 2 methods

func createClient(db *sql.DB, ...) error // creates a new client
func createOrder(db *sql.DB, ...) error // creates a new order

Each of these method can be run on some *sql.DB, for example,

var mainDb *sql.DB // initialized somewhere in main() method

func orderHandler(r,w) {
   ...
   err := createOrder(mainDb, ...)
   ...
}

But what if i want to run both these methods in one transaction. For example,

func importOrdersHandler(r,w) {
     ...
     tx, err:= mainDb.Begin()
     ...
     err = createClient(tx, clientData) // but method defined on *sql.DB, not *sql.Tx ?!
     err = createOrder(tx, orderData)
     ...

My solution:

Define a wrapper around *sql.DB, *sql.Tx:

type Database struct {
   db *sql.DB
   tx *sql.Tx
}

// Redefine all standart methods from sql package, such as
// Exec(...)
// Query(..)
// QueryRow(...)

// Also method to run commands in one transaction:

func TransactionDo(db *sql.DB, body func(*Database) error) error {
  tx, err :=  db.Begin()
  ...
  d, err := NewDb(nil, tx)
  ....
  err = body(d)
  ...
  return tx.Commit()
}

In this way our ordersImportHandler can be realized like that:

 func importOrdersHandler(r,w) {

   for row := range parseFile(formFile(r)) {
     ...
     err := TransactionDo(mainDb, func(d *Database) error {
        err = createClient(d, clientData)
        ...
        err = createOrder(d, orderData)
    // if an error occurs in TransactionDo then transaction wiil be
    // rollbacked, else commit it

createClient, createOrder must be rewrited to use *Database object insted of *sql.DB

What do you think about such solution? May be there another better and idiomatic way to do that

Upvotes: 0

Views: 396

Answers (2)

jussius
jussius

Reputation: 3274

Simpler way is to use interfaces.

type Execer interface {
    Exec(query string, args ...interface{}) (sql.Result, error)
}

Both sql.Tx and sql.Db satisfy this interface so you can redefine you createClient and createOrder funtions to take an Execer as an argument and there, you can now pass either Tx or Db to them.

Upvotes: 1

Benjamin Kadish
Benjamin Kadish

Reputation: 1500

I used an interface (that the library squirrel also uses)

type Database interface {
    Exec(query string, args ...interface{}) (sql.Result, error)
    Query(query string, args ...interface{}) (*sql.Rows, error)
    QueryRow(query string, args ...interface{}) *sql.Row
}

Then you can just pass write the functions 

func createClient(db *Database, ...) error // creates a new client
func createOrder(db *Database, ...) error // creates a new order

Upvotes: 2

Related Questions