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

Question

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

Benjamin Kadish · Accepted Answer · 2016-07-15 13:03:25Z

2

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

answered Jul 15, 2016 at 13:03

Benjamin Kadish

1,5201 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jussius Over a year ago

Oh, looks like you were faster. But yea, this is the idiomatic way I think.

jussius · Accepted Answer · 2016-07-15 13:07:07Z

1

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.

answered Jul 15, 2016 at 13:07

jussius

3,28418 silver badges22 bronze badges

3 Comments

aleksandr Over a year ago

Agree, but i can use QueryRow method inside createClient for example to get generated id of new client. QueryRow is not an interface method

aleksandr Over a year ago

I use Postgres where LastInsertId is not defined as i know and you must use RETURNING id clause explicitly

jussius Over a year ago

@aleksandr QueryRow method is also available to both Db and Tx, so if you need it you can add that (and any other methods implemented by both Db and Tx) to the interface. And then you should probably name the interface something other than Execer. But if you need the method just for getting the last id, you could just use RETURNING id and read it from the sql.Result.LastInsertId().

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related