otsql

README

otsql

Add an otsql wrapper to your existing database code to hook any sql command.

• Support tracing with OpenTelemetry by otsql/hook/trace.
• Support monitor latency and connection pool stats with Prometheus by otsql/hook/metric.
• Support acess log with zerolog by otsql/hook/trace.

First version transformed from ocsql.

Install

$ go get -u github.com/j2gg0s/otsql

Usage

To use otsql with your application, register an otsql wrapper of a database driver as shown below.

Example:

import (
    "database/sql"

    "github.com/j2gg0s/otsql"
    "github.com/j2gg0s/otsql/hook/trace"
    "github.com/j2gg0s/otsql/hook/metric"
    "github.com/j2gg0s/otsql/hook/log"
    _ "github.com/go-sql-driver/mysql"
)

var dsn = "postgres://otsql_user:otsql_password@localhost/otsql_db?sslmode=disable"

metricHook, err := metric.New()
if err != nil {
    return "", fmt.Errorf("new metric hook: %w", err)
}

driverName, err := otsql.Register(
    name,
    otsql.WithHooks(
        trace.New(
            trace.WithAllowRoot(true),
            trace.WithQuery(true),
            trace.WithQueryParams(true),
        ),
        metricHook,
        log.New(),
    ),
)
if err != nil {
    panic(err)
}

db, err := sql.Open(driverName, dsn)
if err != nil {
    panic(err)
}
defer db.Close()

Finally database drivers that support the driver.Connector interface can be wrapped directly by otsql without the need for otsql to register a driver.Driver.

Example:

import (
    "database/sql"

    "github.com/j2gg0s/otsql"
    "github.com/j2gg0s/otsql/hook/trace"
    _ "github.com/lib/pq"
)

var dsn = "postgres://otsql_user:otsql_password@localhost/otsql_db?sslmode=disable"

connector, err := pq.NewConnector(dsn)
if err != nil {
    panic(err)
}

db := sql.OpenDB(
    WrapConnector(connector, otsql.Hooks(trace.new())))
defer db.Close()

See more specific case in example/.

Trace with opentelemetry

otsql support trace with opentelemetry by hook/trace.

Metric with prometheus

otsql support metric with prometheus by hook/metric.

Metric	Search suffix	Tags
Latency in millisecond	go_sql_latency	sql_instance, sql_method, sql_status

You can use metric.Stats to monitor connection pool, all metric supprt tag sql_instance.

Metric	Search suffix
The number of connections currently in use	go_sql_conn_in_use
The number of idle connections	go_sql_conn_idle
The total number of connections wait for	go_sql_conn_wait
The total number of connections closed because of SetMaxIdleConns	go_sql_conn_idle_closed
The total number of connections closed because of SetConnMaxLifetime	go_sql_conn_lifetime_closed
The total time blocked by waiting for a new connection, nanosecond	go_sql_conn_wait_ns

Test

Test by bun's unittest with a special branch otsql@bun.

Test by gorm's unittest with a special branch otsql@gorm. It also includes example for pgx.

Benchmark

Run benchmark with bun:

goos: darwin
goarch: amd64
pkg: github.com/uptrace/bun/internal/dbtest
cpu: Intel(R) Core(TM) i7-8557U CPU @ 1.70GHz
BenchmarkSelectOne/pg-8             4579            281209 ns/op
BenchmarkSelectOne/mysql-8                  4146            292964 ns/op
BenchmarkSelectOne/sqlite-8                37898             28012 ns/op
BenchmarkSelectSlice/mysql-8                2874            397543 ns/op
BenchmarkSelectSlice/sqlite-8               6517            184153 ns/op
BenchmarkSelectSlice/pg-8                   2806            425935 ns/op
BenchmarkSelectError/pg-8                   4027            314399 ns/op
BenchmarkSelectError/mysql-8                2426            476420 ns/op
BenchmarkSelectError/sqlite-8             166348              7320 ns/op
PASS
ok      github.com/uptrace/bun/internal/dbtest  79.494s

Run benchmark with otsql@bun:

goos: darwin
goarch: amd64
pkg: github.com/uptrace/bun/internal/dbtest
cpu: Intel(R) Core(TM) i7-8557U CPU @ 1.70GHz
BenchmarkSelectOne/pg-8             3014            332012 ns/op
BenchmarkSelectOne/mysql-8                  3382            339056 ns/op
BenchmarkSelectOne/sqlite-8                25057             46897 ns/op
BenchmarkSelectSlice/pg-8                   2284            468973 ns/op
BenchmarkSelectSlice/mysql-8                3074            426501 ns/op
BenchmarkSelectSlice/sqlite-8               4725            234213 ns/op
BenchmarkSelectError/pg-8                   3760            337402 ns/op
BenchmarkSelectError/mysql-8                2647            485405 ns/op
BenchmarkSelectError/sqlite-8              73551             15706 ns/op
PASS
ok      github.com/uptrace/bun/internal/dbtest  67.224s

Documentation

Index

• func ErrToCode(err error) codes.Code
• func Register(driverName string, options ...Option) (string, error)
• func Wrap(dri driver.Driver, opts ...Option) driver.Driver
• func WrapConn(c driver.Conn, opts ...Option) driver.Conn
• func WrapConnector(dc driver.Connector, opts ...Option) driver.Connector
• type Event
• type Hook
• type Method
• type Option
  • func WithDatabaase(name string) Option
  • func WithHooks(hooks ...Hook) Option
  • func WithInstance(name string) Option
  • func WithLastInsertID(b bool) Option
  • func WithOptions(options Options) Option
  • func WithPing(b bool) Option
  • func WithResetSession(b bool) Option
  • func WithRowsAffected(b bool) Option
  • func WithRowsClose(b bool) Option
  • func WithRowsNext(b bool) Option
• type Options

Functions

func ErrToCode

func ErrToCode(err error) codes.Code

func Register

func Register(driverName string, options ...Option) (string, error)

Register initializes and registers our otsql wrapped database driver identified by its driverName and using provided Options. On success it returns the generated driverName to use when calling sql.Open. It is possible to register multiple wrappers for the same database driver if needing different Options for different connections.

func Wrap

func Wrap(dri driver.Driver, opts ...Option) driver.Driver

Wrap takes a SQL driver and wraps it with hook enabled.

func WrapConn

func WrapConn(c driver.Conn, opts ...Option) driver.Conn

WrapConn allows an existing driver.Conn to be wrapped by otsql.

func WrapConnector

func WrapConnector(dc driver.Connector, opts ...Option) driver.Connector

WrapConnector allows wrapping a database driver.Connector which eliminates the need to register otsql as an available driver.Driver.

Types

type Event

type Event struct {
	Instance string
	Database string

	Method  Method
	Query   string
	Args    interface{}
	BeginAt time.Time

	Err error

	CloseFuncs []func(context.Context, error)

	Conn string
}

type Hook

type Hook interface {
	Before(context.Context, *Event) context.Context
	After(context.Context, *Event)
}

type Method

type Method string

var (
	MethodPing Method = "ping"

	MethodExec     Method = "exec"
	MethodQuery    Method = "query"
	MethodPrepare  Method = "prepare"
	MethodBegin    Method = "begin"
	MethodCommit   Method = "commit"
	MethodRollback Method = "rollback"

	MethodLastInsertId Method = "last_insert_id"
	MethodRowsAffected Method = "rows_affected"
	MethodRowsClose    Method = "rows_close"
	MethodRowsNext     Method = "rows_next"

	MethodCreateConn   Method = "create_conn"
	MethodCloseConn    Method = "close_conn"
	MethodResetSession Method = "reset_session"
)

type Option

type Option func(*Options)

Option allows for managing otsql configuration using functional options.

func WithDatabaase

func WithDatabaase(name string) Option

WithDatabase sets instance name, default parse from dsn when create conn.

func WithHooks

func WithHooks(hooks ...Hook) Option

WithHooks set hook.

func WithInstance

func WithInstance(name string) Option

WithInstance sets instance name, default parse from dsn when create conn.

func WithLastInsertID

func WithLastInsertID(b bool) Option

WithLastInsertID if set to true, will enable the hook of LastInsertId calls.

func WithOptions

func WithOptions(options Options) Option

WithOptions sets our otsql options through a single Options object.

func WithPing

func WithPing(b bool) Option

WithPing if set to true, will enable the hook of Ping requests.

func WithResetSession

func WithResetSession(b bool) Option

WithResetSession if set to true, will enable the hook of ResetSession calls.

func WithRowsAffected

func WithRowsAffected(b bool) Option

WithRowsAffected if set to true, will enable the of RowsAffected calls.

func WithRowsClose

func WithRowsClose(b bool) Option

WithRowsClose if set to true, will enable the of RowsClose calls.

func WithRowsNext

func WithRowsNext(b bool) Option

WithRowsNext if set to true, will enable of RowsNext calls. This can result in many calls.

type Options

type Options struct {
	// Instance, default parse from dsn.
	Instance string

	// Database, default parse from dsn.
	Database string

	// PingB, if set to true, will enable the hook of Ping requests.
	PingB bool

	// RowsAffectedB, if set to true, will enable the hook of RowsAffected calls.
	RowsAffectedB bool

	// LastInsertIdB, if set to true, will enable the hook LastInsertId calls.
	LastInsertIdB bool

	// RowsNextB, if set to true, will enable the hook of calls.
	// This can result in many calls.
	RowsNextB bool

	// RowsCloseB, if set to true, will enable the hook of RowsClose calls.
	RowsCloseB bool

	// ResetSessionB, if set to true, will enable the hook of ResetSession calls.
	ResetSessionB bool

	// Hooks, enabled hooks.
	Hooks []Hook
}

Options holds configuration of our otsql hook. By default all options are set to false intentionally when creating a wrapped driver and provide the most sensible default with both performance and security in mind.