Interceptors for database/sql

GoDoc

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

Comments
  • Allow interceptor to edit wrapped contexts

    Allow interceptor to edit wrapped contexts

    Allows editing the transaction and statement context to add, for example, trace timestamp or other user data that we can retrieve on statement close, commit or rollback.

    Signed-off-by: Antoine Deschênes [email protected]

  • stmt: export WrappedParentStmt to allow Stmt wrapping in interceptors

    stmt: export WrappedParentStmt to allow Stmt wrapping in interceptors

        stmt: export WrappedParentStmt to allow Stmt wrapping in interceptors
    
        In some scenarios it is required to annotate Stmt, Rows and Tx objects
        with additional data in an interceptor.
        Usecases can be:
        - relating a PrepareContext() call with operations on the Stmt in
          traces,
        - forwarding the query string passed to PrepareContext() to methods of
          the Stmt
    
        For Rows and Tx objects it is possible in an interceptor by creating a
        struct that wraps the original objects and has additional user-defined
        fields.
        The user-wrapped object is passed to the RowsNext, RowsClose, TxCommit
        and TxRollback interceptor methods. In those a type-conversion can be
        done and the user-defined data accessed.
    
        For Stmt objects this worked only for the StmtClose call.
        When the StmtQueryContext StmtExecContext methods of the interceptor
        were called, the driver.Stmt that was passed as parameter was wrapped
        into the private wrappedStmt struct by sqlmw.
        Because it was private, an interceptor implementation could not convert
        the Stmt parameter type and access it's own wrapped Stmt.
    
        This commit allows also to wrap Stmts in an interceptor and annotate it
        with additional data.
        This is done by making WrappedParentStmt public and changing the
        interceptor interface to  pass stmt of this type.
        Additionally:
        - wrappedParentStmt is renamed to Stmt,
        - a Stmt.Parent() helper method is added,
        - a testcase and example is added
    

    This resolves https://github.com/ngrok/sqlmw/issues/10

  • interceptor: remove query arg from StmtExecContext and StmtQueryContext

    interceptor: remove query arg from StmtExecContext and StmtQueryContext

    The middleware was storing the query that was passed to PrepareContext in the wrapped Stmt and then passing it to StmtExecContext and StmtQueryContext.

    Remove the query argument. It is a remaining of the instrumentedsql pkg, were the query was stored and passed to annotate tracing spans with it.

    Removing it has the advantage that the interface is more close to the db object interface of the stdlib. The string parameter was also undocumented in the interface, it was only possible to figure out what it is by reading the source code.

    If the user needs to forward custom data he can wrap Rows, Stmt, Tx on his own or better the middleware can provide in the future a mechanism to forward arbitrary user defined data between those calls.


    Analogous to https://github.com/ngrok/sqlmw/pull/14

  • support intercepting Rows.Close() calls

    support intercepting Rows.Close() calls

    Add a new RowsClose() method to the interceptor interface to which rows.Close() calls are forwarded.

    Intercepting rows.Close() can be useful in tracing middlewares. It allows to create a parent span when the db Rows object is created, create child spans for operations on the Rows and finish the parent span when rows.Close() is called.

  • middlware for other methods?

    middlware for other methods?

    I am excited that this project exists and it gives me the hooks I need; thanks! One thing we'd like to do at Zip is forbid the use of non-Context methods, so that we always are able to hook the methods being used. Would you be interested in another interface, maybe CompleteInterceptor, that allows users to hook more than just the Context functions?

  • interceptor: remove ctx parameter from db methods that do not have it

    interceptor: remove ctx parameter from db methods that do not have it

    The middleware was storing the context that was passed to methods creating Rows, Tx and Stmts in the wrapped versions of those. The ctx was then forwarded to operations like RowsNext, RowsClose, StmtClose. The related methods do not have a context parameter in the related db methods in the stdlib. That ctx was stored in the wrapped objects is probably a remaining of the instrumentedsql package, that this package is forked from. For the instrumentedsql package it made sense to store the ctx in the wrapped object, because it contained tracing spans that could then be accessed in RowsNext, RowsClose, etc.

    For sqlmw it does not. That the method signatures differs and context from different methods were forwarded can be confusing. It was not clear for the user which context he received in those interceptor methods. The signatures of DB methods of the interceptor should ideally be as close as possible to those of the stdlib db object.

  • Check both statement and connection

    Check both statement and connection

    I would like to use sqlmw with MySQLDriver ( https://github.com/go-sql-driver/mysql ), but I got the following error.

    Code

    import (
    	"context"
    	"database/sql"
    	"database/sql/driver"
    	"fmt"
    	"time"
    
    	"github.com/go-sql-driver/mysql"
    	"github.com/ngrok/sqlmw"
    )
    
    func run(dsn string) {
            // install the wrapped driver
            sql.Register("mysql-mw", sqlmw.Driver(mysql.MySQLDriver{}, new(sqlInterceptor)))
            sql.Open("mysql-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
    }
    

    error:

    panic: sql: converting argument $1 type: driver ColumnConverter error converted uint64 to unsupported type uint64
    

    According to my investigation, it was not forwarded to mysql.mysqlConn ( sqlmw.wrappedConn ) from mysql.mysqlStmt ( sqlmw.wrappedStmt ) in the following places, so the type conversion did not seem to be performed. https://github.com/golang/go/blob/release-branch.go1.9/src/database/sql/convert.go#L136-L139

    So, I changed CheckNamedValue of wrappedStmt to check both statement and connection.

  • Should this include tracing middleware?

    Should this include tracing middleware?

    Although the example is good enough for tracing I think providing a middleware for tracing or metrics of popular libraries is worth. I could come up with a PR for this.

  • Setup CI using github-actions

    Setup CI using github-actions

    The PR introduces a basic CI setup using github-actions. sqlmw is build and tested with the newest go version, static checks are run via golangci-lint.

    Code annotations were added to silence non-relevant golangci-lint warnings and an unused private method was removed that was found by golangci-lint

  • call stmt.ColumnConverter when implemented by parent statement

    call stmt.ColumnConverter when implemented by parent statement

    According to the stdlib driver documentation (https://github.com/golang/go/blob/bc51e930274a5d5835ac8797978afc0864c9e30c/src/database/sql/driver/driver.go#L385) value checkers should be called in the following order:

    [..] stopping at the first found match: Stmt.NamedValueChecker, Conn.NamedValueChecker, Stmt.ColumnConverter, DefaultParameterConverter.

    sqlmw was not calling Stmt.ColumnConverter when it was implemented.

    This commit changes the behavior to call Stmt.ColumnConverter, if it is implemented and the NamedValueCheckers are not implemented. This is done by returning ErrSkip in wrappedStmt.CheckNamedValue() if neither the parent statement nor the conn implements CheckNamedValue. The sql package will call wrappedStmt.ColumnConverter() if ErrSkip was returned.

    wrappedStmt.CheckNamedValue() can not check only if the stmt implements CheckNamedValue and return ErrSkip. It must also call CheckNamedValue() on the connection if it was not implemented for the stmt. This is because the stdlib sql package calls wrappedStmt.CheckNamedValue() if is implemented on the stmt OR on the connection.

    The commit also adds a testcase to verify that ColumnConverter is called.

  • Is there any methods to modify the result? when query ?

    Is there any methods to modify the result? when query ?

    hi, i want to know how to modify the result when i query the table. such as

    SELECT * FROM `account`
    

    i tried to modify the dest, but failed, the code as follows

    func (in *SqlInterceptor) RowsNext(ctx context.Context, rows driver.Rows, dest []driver.Value) error {
    	// Edit the dest
    	return rows.Next(dest)
    }
    
    

    can you help me? thx!

  • Cut a major version v1.0.0

    Cut a major version v1.0.0

    Not sure if the libraries API is considered fully stable at this point, but it would be nice to have an actual semver version so that imports in go.mod don't have those long v0.0.0-xxxxxxxxxxxxx-xxxxxx strings.

  • Store user-defined data in Rows, Stmt and Tx objects

    Store user-defined data in Rows, Stmt and Tx objects

    Hello,

    I'm implementing an interceptor with sqlmw that records traces for database operations (instrumentedsql->sqlmw->tracing with sqlmw :-)).

    When a Rows, Stmt or Tx object is created I create a parent tracing span. Operations on the Rows, Stmt and Tx structs should be created as child spans. When the Rows, Stmt, Tx operations is finished (Close(), Commit(), Rollback(), etc), the parent span is also finished.

    To be able to create a child span, the parent span must be available in the methods of the Rows, Stmt and Tx objects. One way to to achieve this would be to wrap the Rows, Stmt and Tx objects again in my interceptor implementation and store the additional information in the struct.

    I would like to avoid having to wrap those structs again in my interceptor. Wrapping it also requires to implement the fallback logic for the Stmt.QueryContext() and Stmt.ExecContext() methods to their non context aware variants again. This means maintaining another copy of namedValueToValue.

    It would be great if sqlmw would support the usecase to store user-defined data when creating Rows, Stmt and Tx structs and access it in their methods instead. sqlmw is doing something similiar already. When for example QueryContext() is called, it stores the cx in the wrappedRows objects and passes it as parameters to methods like RowsNext() as argument, which do not have a ctx argument in the stdlib implementation.

    What do you think about the idea? Does somebody have an suggestion for how to implement it? (I'll extend this issue or create a PR, when I have an idea.)

  • Missing interceptors OpenConnector, Connect, ConnClose

    Missing interceptors OpenConnector, Connect, ConnClose

Related tags
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

Oct 20, 2021
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

Jan 25, 2022
sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jan 7, 2023
Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

?? Dumpling Dumpling is a tool and a Go library for creating SQL dump from a MySQL-compatible database. It is intended to replace mysqldump and mydump

Nov 9, 2022
Additions to Go's database/sql for super fast performance and convenience.

gocraft/dbr (database records) gocraft/dbr provides additions to Go's database/sql for super fast performance and convenience. $ go get -u github.com/

Jan 1, 2023
OpenTelemetry instrumentation for database/sql

otelsql It is an OpenTelemetry instrumentation for Golang database/sql, a port from https://github.com/open-telemetry/opentelemetry-go-contrib/pull/50

Dec 28, 2022
Scan database/sql rows directly to structs, slices, and primitive types

Scan Scan standard lib database rows directly to structs or slices. For the most comprehensive and up-to-date docs see the godoc Examples Multiple Row

Dec 28, 2022
Prometheus metrics for Go database/sql via VictoriaMetrics/metrics

sqlmetrics Prometheus metrics for Go database/sql via VictoriaMetrics/metrics Features Simple API. Easy to integrate. Install Go version 1.16+ go get

Dec 16, 2022
Attach hooks to any database/sql driver

sqlhooks Attach hooks to any database/sql driver. The purpose of sqlhooks is to provide a way to instrument your sql statements, making really easy to

Jan 6, 2023
A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

ploto A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management It's not an ORM. wo

Nov 3, 2022
Go database/sql

Go database/sql 源码阅读 基于 release-branch.go1.17 Benchmark 连接池测试 简单介绍 database/sql 库,包括结构体和主要的方法 介绍主要函数的调用逻辑 用OneNote看源码:Link 介绍最近几个版本的commit changes 结合实

Dec 18, 2021
Sqlbench runs benchmarks on an SQL database

sqlbench runs benchmarks on an SQL database. Right now this works for PostgreSQL

Oct 13, 2022
Lightweight SQL database written in Go for prototyping and playing with text (CSV, JSON) data

gopicosql Lightweight SQL database written in Go for prototyping and playing wit

Jul 27, 2022
BigQuery database/sql golang driver

BigQuery SQL Driver This library is compatible with Go 1.17+ Please refer to CHA

Dec 7, 2022
A minimal, single-table No-SQL database.

SimpleDB SimpleDB is a very basic No-SQL database format for long-term data storage in Golang. It is WIP, has a LOT of drawbacks, and definitely is no

Jan 16, 2022
Otelsql - OpenTelemetry SQL database driver wrapper for Go
Otelsql - OpenTelemetry SQL database driver wrapper for Go

OpenTelemetry SQL database driver wrapper for Go Add a OpenTelemetry wrapper to

Dec 15, 2022
A go package to add support for data at rest encryption if you are using the database/sql.

go-lockset A go package to add support for data at rest encryption if you are using the database/sql to access your database. Installation In your Gol

Jan 30, 2022
Package dbi implements an experimental database/sql wrapper.

dbi Package dbi implements a database/sql wrapper. This is an EXPERIMENTAL package used for experimenting. Installation The recommended way to install

Feb 8, 2022
Google Cloud Spanner driver for Go's database/sql package.

go-sql-spanner Google Cloud Spanner driver for Go's database/sql package. This support is currently in the Preview release status. import _ "github.co

Dec 11, 2022