Wrapping a db object in Go and running two methods in the same transaction

Answer #1 100 %

I've done something like this in the past (using the standard sql package, you may need to adapt it to your needs):

var ErrNestedTransaction = errors.New("nested transactions are not supported")

// abstraction over sql.TX and sql.DB
// a similar interface seems to be already defined in go-pg. So you may not need this. 
type executor interface {
    Exec(query string, args ...interface{}) (sql.Result, error)
    Query(query string, args ...interface{}) (*sql.Rows, error)
    QueryRow(query string, args ...interface{}) *sql.Row
}

type Store struct {
    // this is the actual connection(pool) to the db which has the Begin() method
    db       *sql.DB
    executor executor
}

func NewStore(dsn string) (*Store, error) {
    db, err := sql.Open("sqlite3", dsn)
    if err != nil {
         return nil, err
    }      
    // the initial store contains just the connection(pool)
    return &Store{db, db}, nil
}

func (s *Store) RunInTransaction(f func(store *Store) error) error {
    if _, ok := s.executor.(*sql.Tx); ok {
        // nested transactions are not supported!
        return ErrNestedTransaction
    }

    tx, err := s.db.Begin()
    if err != nil {
        return err
    }

    transactedStore := &Store{
        s.db,
        tx,
    }

    err = f(transactedStore)
    if err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

func (s *Store) CreateA(thing A) error {
    // your implementation
    _, err := s.executor.Exec("INSERT INTO ...", ...)
    return err
}

And then you use it like

// store is a global object
store.RunInTransaction(func(store *Store) error { 
    // this instance of Store uses a transaction to execute the methods
    err := store.CreateA(a)
    if err != nil {
        return err
    }
    return store.CreateB(b)
})

The trick is to use the executor instead of the *sql.DB in your CreateX methods, which allows you to dynamically change the underlying implementation (tx vs. db). However, since there is very little information out there on how to deal with this issue, I can't assure you that this is the "best" solution. Other suggestions are welcome!

Tags: gogo-pg

You’ll also like:


© 2023 CodeForDev.com -