sqlmw

README

sqlmw

sqlmw provides an absurdly simple API that allows a caller to wrap a database/sql driver with middleware.

This provides an abstraction similar to http middleware or GRPC interceptors but for the database/sql package. This allows a caller to implement observability like tracing and logging easily. More importantly, it also enables powerful possible behaviors like transparently modifying arguments, results or query execution strategy. This power allows programmers to implement functionality like automatic sharding, selective tracing, automatic caching, transparent query mirroring, retries, fail-over in a reuseable way, and more.

Usage

• Define a new type and embed the sqlmw.NullInterceptor type.
• Add a method you want to intercept from the sqlmw.Interceptor interface.
• Wrap the driver with your interceptor with sqlmw.Driver and then install it with sql.Register.
• Use sql.Open on the new driver string that was passed to register.

Here's a complete example:

func run(dsn string) {
        // install the wrapped driver
        sql.Register("postgres-mw", sqlmw.Driver(pq.Driver{}, new(sqlInterceptor)))
        db, err := sql.Open("postgres-mw", dsn)
        ...
}

type sqlInterceptor struct {
        sqlmw.NullInterceptor
}

func (in *sqlInterceptor) StmtQueryContext(ctx context.Context, conn driver.StmtQueryContext, query string, args []driver.NamedValue) (driver.Rows, error) {
        startedAt := time.Now()
        rows, err := conn.QueryContext(ctx, args)
        log.Debug("executed sql query", "duration", time.Since(startedAt), "query", query, "args", args, "err", err)
        return rows, err
}

You may override any subset of methods to intercept in the Interceptor interface (https://godoc.org/github.com/ngrok/sqlmw#Interceptor):

type Interceptor interface {
    // Connection interceptors
    ConnBeginTx(context.Context, driver.ConnBeginTx, driver.TxOptions) (driver.Tx, error)
    ConnPrepareContext(context.Context, driver.ConnPrepareContext, string) (driver.Stmt, error)
    ConnPing(context.Context, driver.Pinger) error
    ConnExecContext(context.Context, driver.ExecerContext, string, []driver.NamedValue) (driver.Result, error)
    ConnQueryContext(context.Context, driver.QueryerContext, string, []driver.NamedValue) (driver.Rows, error)

    // Connector interceptors
    ConnectorConnect(context.Context, driver.Connector) (driver.Conn, error)

    // Results interceptors
    ResultLastInsertId(driver.Result) (int64, error)
    ResultRowsAffected(driver.Result) (int64, error)

    // Rows interceptors
    RowsNext(context.Context, driver.Rows, []driver.Value) error

    // Stmt interceptors
    StmtExecContext(context.Context, driver.StmtExecContext, string, []driver.NamedValue) (driver.Result, error)
    StmtQueryContext(context.Context, driver.StmtQueryContext, string, []driver.NamedValue) (driver.Rows, error)
    StmtClose(context.Context, driver.Stmt) error

    // Tx interceptors
    TxCommit(context.Context, driver.Tx) error
    TxRollback(context.Context, driver.Tx) error
}

Bear in mind that because you are intercepting the calls entirely, that you are responsible for passing control up to the wrapped driver in any function that you override, like so:

func (in *sqlInterceptor) ConnPing(ctx context.Context, conn driver.Pinger) error {
    return conn.Ping(ctx)
}

Examples

Logging

func (in *sqlInterceptor) StmtQueryContext(ctx context.Context, conn driver.StmtQueryContext, query string, args []driver.NamedValue) (driver.Rows, error) {
    startedAt := time.Now()
    rows, err := conn.QueryContext(ctx, args)
    log.Debug("executed sql query", "duration", time.Since(startedAt), "query", query, "args", args, "err", err)
    return rows, err
}

Tracing

func (in *sqlInterceptor) StmtQueryContext(ctx context.Context, conn driver.StmtQueryContext, query string, args []driver.NamedValue) (driver.Rows, error) {
    span := trace.FromContext(ctx).NewSpan(ctx, "StmtQueryContext")
    span.Tags["query"] = query
    defer span.Finish()
    rows, err := conn.QueryContext(ctx, args)
    if err != nil {
            span.Error(err)
    }
    return rows, err
}

Retries

func (in *sqlInterceptor) StmtQueryContext(ctx context.Context, conn driver.StmtQueryContext, query string, args []driver.NamedValue) (driver.Rows, error) {
    for {
            rows, err := conn.QueryContext(ctx, args)
            if err == nil {
                    return rows, nil
            }
            if err != nil && !isIdempotent(query) {
                    return nil, err
            }
            select {
            case <-ctx.Done():
                    return nil, ctx.Err()
            case <-time.After(time.Second):
            }
    }
}

Comparison with similar projects

There are a number of other packages that allow the programmer to wrap a database/sql/driver.Driver to add logging or tracing.

Examples of tracing packages:

• github.com/ExpansiveWorlds/instrumentedsql
• contrib.go.opencensus.io/integrations/ocsql
• gopkg.in/DataDog/dd-trace-go.v1/contrib/database/sql

A few provide a much more flexible setup of arbitrary before/after hooks to facilitate custom observability.

Packages that provide before/after hooks:

• github.com/gchaincl/sqlhooks
• github.com/shogo82148/go-sql-proxy

None of these packages provide an interface with sufficient power. sqlmw passes control of executing the sql query to the caller which allows the caller to completely disintermediate the sql calls. This is what provides the power to implement advanced behaviors like caching, sharding, retries, etc.

Go version support

Go versions 1.9 and forward are supported.

Fork

This project began by forking the code in github.com/luna-duclos/instrumentedsql, which itself is a fork of github.com/ExpansiveWorlds/instrumentedsql

Documentation

Index

• func Driver(driver driver.Driver, intr Interceptor) driver.Driver
• type Interceptor
• type NullInterceptor
  • func (NullInterceptor) ConnBeginTx(ctx context.Context, conn driver.ConnBeginTx, txOpts driver.TxOptions) (context.Context, driver.Tx, error)
  • func (NullInterceptor) ConnExecContext(ctx context.Context, conn driver.ExecerContext, query string, ...) (driver.Result, error)
  • func (NullInterceptor) ConnPing(ctx context.Context, conn driver.Pinger) error
  • func (NullInterceptor) ConnPrepareContext(ctx context.Context, conn driver.ConnPrepareContext, query string) (context.Context, driver.Stmt, error)
  • func (NullInterceptor) ConnQueryContext(ctx context.Context, conn driver.QueryerContext, query string, ...) (context.Context, driver.Rows, error)
  • func (NullInterceptor) ConnectorConnect(ctx context.Context, connect driver.Connector) (driver.Conn, error)
  • func (NullInterceptor) ResultLastInsertId(res driver.Result) (int64, error)
  • func (NullInterceptor) ResultRowsAffected(res driver.Result) (int64, error)
  • func (NullInterceptor) RowsClose(ctx context.Context, rows driver.Rows) error
  • func (NullInterceptor) RowsNext(ctx context.Context, rows driver.Rows, dest []driver.Value) error
  • func (NullInterceptor) StmtClose(ctx context.Context, stmt driver.Stmt) error
  • func (NullInterceptor) StmtExecContext(ctx context.Context, stmt driver.StmtExecContext, _ string, ...) (driver.Result, error)
  • func (NullInterceptor) StmtQueryContext(ctx context.Context, stmt driver.StmtQueryContext, _ string, ...) (context.Context, driver.Rows, error)
  • func (NullInterceptor) TxCommit(ctx context.Context, tx driver.Tx) error
  • func (NullInterceptor) TxRollback(ctx context.Context, tx driver.Tx) error
• type RowsUnwrapper

Functions

func Driver

func Driver(driver driver.Driver, intr Interceptor) driver.Driver

Driver returns the supplied driver.Driver with a new object that has all of its calls intercepted by the supplied Interceptor object.

Important note: Seeing as the context passed into the various instrumentation calls this package calls, Any call without a context passed will not be intercepted. Please be sure to use the ___Context() and BeginTx() function calls added in Go 1.8 instead of the older calls which do not accept a context.

Types

type Interceptor

type Interceptor interface {
	// Connection interceptors
	ConnBeginTx(context.Context, driver.ConnBeginTx, driver.TxOptions) (context.Context, driver.Tx, error)
	ConnPrepareContext(context.Context, driver.ConnPrepareContext, string) (context.Context, driver.Stmt, error)
	ConnPing(context.Context, driver.Pinger) error
	ConnExecContext(context.Context, driver.ExecerContext, string, []driver.NamedValue) (driver.Result, error)
	ConnQueryContext(context.Context, driver.QueryerContext, string, []driver.NamedValue) (context.Context, driver.Rows, error)

	// Connector interceptors
	ConnectorConnect(context.Context, driver.Connector) (driver.Conn, error)

	// Results interceptors
	ResultLastInsertId(driver.Result) (int64, error)
	ResultRowsAffected(driver.Result) (int64, error)

	// Rows interceptors
	RowsNext(context.Context, driver.Rows, []driver.Value) error
	RowsClose(context.Context, driver.Rows) error

	// Stmt interceptors
	StmtExecContext(context.Context, driver.StmtExecContext, string, []driver.NamedValue) (driver.Result, error)
	StmtQueryContext(context.Context, driver.StmtQueryContext, string, []driver.NamedValue) (context.Context, driver.Rows, error)
	StmtClose(context.Context, driver.Stmt) error

	// Tx interceptors
	TxCommit(context.Context, driver.Tx) error
	TxRollback(context.Context, driver.Tx) error
}

type NullInterceptor

type NullInterceptor struct{}

NullInterceptor is a complete passthrough interceptor that implements every method of the Interceptor interface and performs no additional logic. Users should Embed it in their own interceptor so that they only need to define the specific functions they are interested in intercepting.

func (NullInterceptor) ConnBeginTx

func (NullInterceptor) ConnBeginTx(ctx context.Context, conn driver.ConnBeginTx, txOpts driver.TxOptions) (context.Context, driver.Tx, error)

func (NullInterceptor) ConnExecContext

func (NullInterceptor) ConnExecContext(ctx context.Context, conn driver.ExecerContext, query string, args []driver.NamedValue) (driver.Result, error)

func (NullInterceptor) ConnPing

func (NullInterceptor) ConnPing(ctx context.Context, conn driver.Pinger) error

func (NullInterceptor) ConnPrepareContext

func (NullInterceptor) ConnPrepareContext(ctx context.Context, conn driver.ConnPrepareContext, query string) (context.Context, driver.Stmt, error)

func (NullInterceptor) ConnQueryContext

func (NullInterceptor) ConnQueryContext(ctx context.Context, conn driver.QueryerContext, query string, args []driver.NamedValue) (context.Context, driver.Rows, error)

func (NullInterceptor) ConnectorConnect

func (NullInterceptor) ConnectorConnect(ctx context.Context, connect driver.Connector) (driver.Conn, error)

func (NullInterceptor) ResultLastInsertId

func (NullInterceptor) ResultLastInsertId(res driver.Result) (int64, error)

func (NullInterceptor) ResultRowsAffected

func (NullInterceptor) ResultRowsAffected(res driver.Result) (int64, error)

func (NullInterceptor) RowsClose

func (NullInterceptor) RowsClose(ctx context.Context, rows driver.Rows) error

func (NullInterceptor) RowsNext

func (NullInterceptor) RowsNext(ctx context.Context, rows driver.Rows, dest []driver.Value) error

func (NullInterceptor) StmtClose

func (NullInterceptor) StmtClose(ctx context.Context, stmt driver.Stmt) error

func (NullInterceptor) StmtExecContext

func (NullInterceptor) StmtExecContext(ctx context.Context, stmt driver.StmtExecContext, _ string, args []driver.NamedValue) (driver.Result, error)

func (NullInterceptor) StmtQueryContext

func (NullInterceptor) StmtQueryContext(ctx context.Context, stmt driver.StmtQueryContext, _ string, args []driver.NamedValue) (context.Context, driver.Rows, error)

func (NullInterceptor) TxCommit

func (NullInterceptor) TxCommit(ctx context.Context, tx driver.Tx) error

func (NullInterceptor) TxRollback

func (NullInterceptor) TxRollback(ctx context.Context, tx driver.Tx) error

type RowsUnwrapper

type RowsUnwrapper interface {
	Unwrap() driver.Rows
}

RowsUnwrapper must be used by any middleware that provides its own wrapping for driver.Rows. Unwrap should return the original driver.Rows the middleware received. You may wish to wrap the driver.Rows returned by the Query methods if you want to pass extra information from the Query call to the subsequent RowsNext and RowsClose calls.

sqlmw needs to retrieve the original driver.Rows in order to determine the original set of optional methods supported by the driver.Rows of the database driver in use by the caller.

If a middleware returns a custom driver.Rows, the custom implmentation must support all the driver.Rows optional interfaces that are supported by by the drivers that will be used with it. To support any arbitrary driver all the optional methods must be supported.