dbtools

README

dbtools

This library contains goroutine safe helpers for retrying transactions until they succeed and handles errors in a developer friendly way. There are helpers for using with go-sqlmock in tests. There is also a Mocha inspired reporter for spec BDD library.

This library supports Go >= 1.17.

1. Transaction
   • PGX Pool
   • Standard Library
2. SQLMock Helpers
   • ValueRecorder
   • OkValue
3. Spec Reports
   • Usage
4. Development
5. License

Transaction

Transaction helps you reduce the amount of code you put in the logic by taking care of errors. For example instead of writing:

tx, err := db.Begin()
if err != nil {
    return errors.Wrap(err, "starting transaction")
}
err := firstQueryCall(tx)
if err != nil {
    e := errors.Wrap(tx.Rollback(), "rolling back transaction")
    return multierror.Append(err, e).ErrorOrNil()
}
err := secondQueryCall(tx)
if err != nil {
    e := errors.Wrap(tx.Rollback(), "rolling back transaction")
    return multierror.Append(err, e).ErrorOrNil()
}
err := thirdQueryCall(tx)
if err != nil {
    e := errors.Wrap(tx.Rollback(), "rolling back transaction")
    return multierror.Append(err, e).ErrorOrNil()
}

return errors.Wrap(tx.Commit(), "committing transaction")

You will write:

// for using with pgx connections:
tr, err := dbtools.NewTransaction(conn)
// handle error, and reuse tr
return tr.PGX(ctx, firstQueryCall, secondQueryCall, thirdQueryCall)

// or to use with stdlib sql.DB:
tr, err := dbtools.NewTransaction(conn)
// handle error, and reuse tr
return tr.DB(ctx, firstQueryCall, secondQueryCall, thirdQueryCall)

At any point a transaction function returns an error, the whole transaction is started over.

You may set the retry count, delays, and the delay method by passing dbtools.ConfigFunc functions to the constructor. If you don't pass any config, PGX and DB methods will run only once.

You can prematurely stop retrying by returning a retry.StopError error:

err = tr.PGX(ctx, func(tx pgx.Tx) error {
    _, err := tx.Exec(ctx, query)
    return retry.StopError{Err: err}
})

See retry library for more information.

PGX Pool

Your transaction functions should be of func(pgx.Tx) error type. To try up to 20 time until your queries succeed:

// conn is a *sql.DB instance
tr, err := dbtools.NewTransaction(conn, dbtools.Retry(20))
// handle error
err = tr.PGX(ctx, func(tx pgx.Tx) error {
    // use tx to run your queries
    return err
}, func(tx pgx.Tx) error {
    return err
})
// handle error

Standard Library

Your transaction functions should be of func(dbtools.Tx) error type. To try up to 20 time until your queries succeed:

// conn is a *pgxpool.Pool instance
tr, err := dbtools.NewTransaction(conn, dbtools.Retry(20))
// handle error
err = tr.DB(ctx, func(tx dbtools.Tx) error {
    // use tx to run your queries
    return err
}, func(tx dbtools.Tx) error {
    return err
})
// handle error

SQLMock Helpers

There a couple of helpers for using with go-sqlmock test cases for cases that values are random but it is important to check the values passed in queries.

ValueRecorder

If you have an value and use it in multiple queries, and you want to make sure the queries are passed with correct values, you can use the ValueRecorder. For example UUIDs, time and random values.

For instance if the first query generates a random number but it is essential to use the same value on next queries:

import "database/sql"

func TestFoo(t *testing.T) {
    // ...
    // assume num has been generated randomly
    num := 666
    _, err := tx.ExecContext(ctx, "INSERT INTO life (value) VALUE ($1)", num)
    // error check
    _, err = tx.ExecContext(ctx, "INSERT INTO reality (value) VALUE ($1)", num)
    // error check
    _, err = tx.ExecContext(ctx, "INSERT INTO everywhere (value) VALUE ($1)", num)
    // error check
}

Your tests can be checked easily like this:

import (
    "github.com/arsham/dbtools/dbtesting"
    "github.com/DATA-DOG/go-sqlmock"
)

func TestFoo(t *testing.T) {
    // ...
    rec := dbtesting.NewValueRecorder()
    mock.ExpectExec("INSERT INTO life .+").
        WithArgs(rec.Record("truth")).
        WillReturnResult(sqlmock.NewResult(1, 1))
    mock.ExpectExec("INSERT INTO reality .+").
        WithArgs(rec.For("truth")).
        WillReturnResult(sqlmock.NewResult(1, 1))
    mock.ExpectExec("INSERT INTO everywhere .+").
        WithArgs(rec.For("truth")).
        WillReturnResult(sqlmock.NewResult(1, 1))
}

Recorded values can be retrieved by casting to their types:

rec.Value("true").(string)

There are two rules for using the ValueRecorder:

1. You can only record for a value once.
2. You should record a value before you call For or Value.

It will panic if these requirements are not met.

OkValue

If you are only interested in checking some arguments passed to the Exec/Query functions and you don't want to check everything (maybe because thy are not relevant to the current test), you can use OkValue.

import (
    "github.com/arsham/dbtools/dbtesting"
    "github.com/DATA-DOG/go-sqlmock"
)

ok := dbtesting.OkValue
mock.ExpectExec("INSERT INTO life .+").
    WithArgs(
        ok,
        ok,
        ok,
        "important value"
        ok,
        ok,
        ok,
    )

Spec Reports

Mocha is a reporter for printing Mocha inspired reports when using spec BDD library.

Usage

import "github.com/arsham/dbtools/dbtesting"

func TestFoo(t *testing.T) {
    spec.Run(t, "Foo", func(t *testing.T, when spec.G, it spec.S) {
        // ...
    }, spec.Report(&dbtesting.Mocha{}))
}

You can set an io.Writer to Mocha.Out to redirect the output, otherwise it prints to the os.Stdout.

Development

Run the tests target for watching file changes and running tests:

make tests

You can pass flags as such:

make tests flags="-race -v -count=5"

You need to run the dependencies target for installing reflex task runner:

make dependencies

License

Use of this source code is governed by the Apache 2.0 license. License can be found in the LICENSE file.

Documentation

Index

• Variables
• type ConfigFunc
  • func DelayMethod(m retry.DelayMethod) ConfigFunc
  • func RetryCount(n int) ConfigFunc
  • func RetryDelay(d time.Duration) ConfigFunc
• type DB
• type Pool
• type Transaction
  • func NewTransaction(conn interface{}, conf ...ConfigFunc) (*Transaction, error)
  • func (t *Transaction) DB(ctx context.Context, transactions ...func(Tx) error) error
  • func (t *Transaction) PGX(ctx context.Context, transactions ...func(pgx.Tx) error) error
• type Tx

Examples

• NewTransaction
• Transaction.PGX
• Transaction.PGX (Panics)
• Transaction.PGX (Retries)
• Transaction.PGX (StopTrying)

Variables

var (
	// ErrEmptyDatabase is returned when no database connection is set.
	ErrEmptyDatabase = errors.New("no database connection is set")
)

Types

type ConfigFunc

type ConfigFunc func(*Transaction)

A ConfigFunc function sets up a Transaction.

func DelayMethod

func DelayMethod(m retry.DelayMethod) ConfigFunc

DelayMethod decides how to delay between each tries. Default is retry.StandardDelay.

func RetryCount

func RetryCount(n int) ConfigFunc

RetryCount defines a transaction should be tried n times. If n is 0, it will be set as 1.

func RetryDelay

func RetryDelay(d time.Duration) ConfigFunc

RetryDelay is the amount of delay between each unsuccessful tries. Set DelayMethod for the method of delay duration.

type DB

type DB interface {
	BeginTx(ctx context.Context, opts *sql.TxOptions) (Tx, error)
}

DB is the contract for beginning a transaction with a *sql.DB object.

type Pool

type Pool interface {
	Begin(ctx context.Context) (pgx.Tx, error)
}

Pool is the contract for beginning a transaction with a pgxpool db connection.

type Transaction

type Transaction struct {
	// contains filtered or unexported fields
}

Transaction is a concurrent-safe object that can retry a transaction on either a sql.DB or a pgxpool connection until it succeeds.

DB and PGX will try transaction functions one-by-one until all of them return nil, then commits the transaction. If any of the transactions return any error other than retry.StopError, it will retry the transaction until the retry count is exhausted. If a running function returns a retry.StopError, the transaction will be rolled-back and would stop retrying. Tryouts will be stopped when the passed contexts are cancelled.

If all attempts return errors, the last error is returned. If a retry.StopError is returned, transaction is rolled back and the Err inside the retry.StopError is returned. There will be delays between tries defined by the retry.DelayMethod and Delay duration.

Any panic in transactions will be wrapped in an error and will be counted as an error, either being retried or returned.

It's an error to invoke the methods without their respective connections are set.

func NewTransaction

func NewTransaction(conn interface{}, conf ...ConfigFunc) (*Transaction, error)

NewTransaction returns an error if conn is not a DB, Pool, or *sql.DB connection.

Example

// This setup tries the transaction only once.
dbtools.NewTransaction(&exampleConn{})

// This setup tries 100 times until succeeds. The delay is set to 10ms and
// it uses the retry.IncrementalDelay method, which means every time it
// increments the delay between retries with a jitter to avoid thunder herd
// problem.
dbtools.NewTransaction(&exampleConn{},
	dbtools.RetryCount(100),
	dbtools.RetryDelay(10*time.Millisecond),
	dbtools.DelayMethod(retry.IncrementalDelay),
)

func (*Transaction) DB

func (t *Transaction) DB(ctx context.Context, transactions ...func(Tx) error) error

DB returns an error if a sql.DB connection is not set.

func (*Transaction) PGX

func (t *Transaction) PGX(ctx context.Context, transactions ...func(pgx.Tx) error) error

PGX returns an error if a pgxpool connection is not set.

Example

tr, err := dbtools.NewTransaction(&exampleConn{})
if err != nil {
	panic(err)
}
err = tr.PGX(context.Background(), func(pgx.Tx) error {
	fmt.Println("Running first query.")
	return nil
}, func(pgx.Tx) error {
	fmt.Println("Running second query.")
	return nil
})
fmt.Printf("Transaction's error: %v", err)

Output:

Running first query.
Running second query.
Transaction's error: <nil>

Example (Panics)

tr, err := dbtools.NewTransaction(&exampleConn{}, dbtools.RetryCount(10))
if err != nil {
	panic(err)
}
calls := 0
err = tr.PGX(context.Background(), func(pgx.Tx) error {
	calls++
	fmt.Printf("Call #%d.\n", calls)
	if calls < 5 {
		panic("We have a panic!")
	}
	fmt.Println("All done.")
	return nil
})
fmt.Printf("Transaction's error: %v\n", err)
fmt.Printf("Called %d times.\n", calls)

Output:

Call #1.
Call #2.
Call #3.
Call #4.
Call #5.
All done.
Transaction's error: <nil>
Called 5 times.

Example (Retries)

tr, err := dbtools.NewTransaction(&exampleConn{}, dbtools.RetryCount(10))
if err != nil {
	panic(err)
}
called := false
err = tr.PGX(context.Background(), func(pgx.Tx) error {
	fmt.Println("Running first query.")
	return nil
}, func(pgx.Tx) error {
	if !called {
		called = true
		fmt.Println("Second query error.")
		return assert.AnError
	}
	fmt.Println("Running second query.")
	return nil
})
fmt.Printf("Transaction's error: %v", err)

Output:

Running first query.
Second query error.
Running first query.
Running second query.
Transaction's error: <nil>

Example (StopTrying)

// This example shows how to stop trying when we know an error is not
// recoverable.
tr, err := dbtools.NewTransaction(&exampleConn{},
	dbtools.RetryCount(100),
	dbtools.RetryDelay(time.Second),
)
if err != nil {
	panic(err)
}
err = tr.PGX(context.Background(), func(pgx.Tx) error {
	fmt.Println("Running first query.")
	return nil
}, func(pgx.Tx) error {
	fmt.Println("Running second query.")
	return &retry.StopError{Err: assert.AnError}
})
fmt.Printf("Transaction returns my error: %t", strings.Contains(err.Error(), assert.AnError.Error()))

Output:

Running first query.
Running second query.
Transaction returns my error: true

type Tx

type Tx interface {
	Commit() error
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	Prepare(query string) (*sql.Stmt, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
	Rollback() error
	Stmt(stmt *sql.Stmt) *sql.Stmt
	StmtContext(ctx context.Context, stmt *sql.Stmt) *sql.Stmt
}

Tx is a transaction began with sql.DB.