sqldblogger

README

SQLDB-Logger

A logger for Go SQL database driver without modify existing *sql.DB stdlib usage.

Colored console writer output above only for sample/development

FEATURES

• Leveled, detailed and configurable logging.
• Keep using (or re-use existing) *sql.DB as is.
• Bring your own logger backend via simple log interface.
• Trackable log output:
  • Every call has its own unique ID.
  • Prepared statement and execution will have same ID.
  • On execution/result error, it will include the query, arguments, params, and related IDs.

INSTALL

go get -u -v github.com/simukti/sqldb-logger

Version pinning using dependency manager such as Mod or Dep is highly recommended.

USAGE

As a start, Logger is just a simple interface:

type Logger interface {
	Log(ctx context.Context, level Level, msg string, data map[string]interface{})
}

There are 4 included basic implementation that uses well-known JSON structured logger for quickstart:

• Zerolog adapter: Using rs/zerolog as its logger.
• Onelog adapter: Using francoispqt/onelog as its logger.
• Zap adapter: Using uber-go/zap as its logger.
• Logrus adapter: Using sirupsen/logrus as its logger.

Note: those adapters does not use given context, you need to modify it and adjust with your needs. (example: add http request id/whatever value from context to query log when you call QueryerContext andExecerContext methods)

Then for that logger to works, you need to integrate with a compatible driver which will be used by *sql.DB.

INTEGRATE WITH EXISTING SQL DB DRIVER

Re-use from existing *sql.DB driver, this is the simplest way:

For example, from:

dsn := "username:passwd@tcp(mysqlserver:3306)/dbname?parseTime=true"
db, err := sql.Open("mysql", dsn) // db is *sql.DB
db.Ping() // to check connectivity and DSN correctness

To:

// import sqldblogger "github.com/simukti/sqldb-logger"
// import "github.com/simukti/sqldb-logger/logadapter/zerologadapter"
dsn := "username:passwd@tcp(mysqlserver:3306)/dbname?parseTime=true"
db, err := sql.Open("mysql", dsn) // db is *sql.DB
// handle err
loggerAdapter := zerologadapter.New(zerolog.New(os.Stdout))
db = sqldblogger.OpenDriver(dsn, db.Driver(), loggerAdapter/*, using_default_options*/) // db is STILL *sql.DB
db.Ping() // to check connectivity and DSN correctness

That's it, all *sql.DB interaction now logged.

INTEGRATE WITH SQL DRIVER STRUCT

It is also possible to integrate with following public empty struct driver directly:

MySQL (go-sql-driver/mysql)

db := sqldblogger.OpenDriver(dsn, &mysql.MySQLDriver{}, loggerAdapter /*, ...options */)

PostgreSQL (lib/pq)

db := sqldblogger.OpenDriver(dsn, &pq.Driver{}, loggerAdapter /*, ...options */) 

SQLite3 (mattn/go-sqlite3)

db := sqldblogger.OpenDriver(dsn, &sqlite3.SQLiteDriver{}, loggerAdapter /*, ...options */)

Following struct drivers maybe compatible:

SQL Server (denisenkom/go-mssqldb)

db := sqldblogger.OpenDriver(dsn, &mssql.Driver{}, loggerAdapter /*, ...options */)

Oracle (mattn/go-oci8)

db := sqldblogger.OpenDriver(dsn, oci8.OCI8Driver, loggerAdapter /*, ...options */)

LOGGER OPTIONS

When using sqldblogger.OpenDriver(dsn, driver, logger, opt...) without 4th variadic argument, it will use default options.

Here is sample of OpenDriver() using all available options and use non-default value:

db = sqldblogger.OpenDriver(
    dsn, 
    db.Driver(), 
    loggerAdapter,
    // AVAILABLE OPTIONS
    sqldblogger.WithErrorFieldname("sql_error"),                    // default: error
    sqldblogger.WithDurationFieldname("query_duration"),            // default: duration
    sqldblogger.WithTimeFieldname("log_time"),                      // default: time
    sqldblogger.WithSQLQueryFieldname("sql_query"),                 // default: query
    sqldblogger.WithSQLArgsFieldname("sql_args"),                   // default: args
    sqldblogger.WithMinimumLevel(sqldblogger.LevelTrace),           // default: LevelDebug
    sqldblogger.WithLogArguments(false),                            // default: true
    sqldblogger.WithDurationUnit(sqldblogger.DurationNanosecond),   // default: DurationMillisecond
    sqldblogger.WithTimeFormat(sqldblogger.TimeFormatRFC3339),      // default: TimeFormatUnix
    sqldblogger.WithLogDriverErrorSkip(true),                       // default: false
    sqldblogger.WithSQLQueryAsMessage(true),                        // default: false
    sqldblogger.WithUIDGenerator(sqldblogger.UIDGenerator),         // default: *defaultUID
    sqldblogger.WithConnectionIDFieldname("con_id"),                // default: conn_id
    sqldblogger.WithStatementIDFieldname("stm_id"),                 // default: stmt_id
    sqldblogger.WithTransactionIDFieldname("trx_id"),               // default: tx_id
    sqldblogger.WithWrapResult(false),                              // default: true
    sqldblogger.WithIncludeStartTime(true),                         // default: false
    sqldblogger.WithStartTimeFieldname("start_time"),               // default: start
    sqldblogger.WithPreparerLevel(sqldblogger.LevelDebug),          // default: LevelInfo
    sqldblogger.WithQueryerLevel(sqldblogger.LevelDebug),           // default: LevelInfo
    sqldblogger.WithExecerLevel(sqldblogger.LevelDebug),            // default: LevelInfo
)

Click here for options documentation.

MOTIVATION

I want to:

• Keep using *sql.DB.
• Have configurable output field.
• Leverage structured logging.
• Fetch and log context.Context value if needed.
• Re-use pgx log interface.

I haven't found Go *sql.DB logger with that features, so why not created myself?

REFERENCES

• Stdlib sql.DB
• SQL driver interfaces
• SQL driver implementation

CONTRIBUTE

If you found a bug, typo, wrong test, idea, help with existing issue, or anything constructive.

Don't hesitate to create an issue or pull request.

CREDITS

• pgx for awesome PostgreSQL driver.

LICENSE

MIT

Documentation

Overview

Package sqldblogger act as thin and transparent logger without having to change existing *sql.DB usage.

Index

• func OpenDriver(dsn string, drv driver.Driver, lg Logger, opt ...Option) *sql.DB
• type DurationUnit
• type Level
  • func (l Level) String() string
• type Logger
• type NullUID
  • func (u *NullUID) UniqueID() string
• type Option
  • func WithConnectionIDFieldname(name string) Option
  • func WithDurationFieldname(name string) Option
  • func WithDurationUnit(unit DurationUnit) Option
  • func WithErrorFieldname(name string) Option
  • func WithExecerLevel(lvl Level) Option
  • func WithIncludeStartTime(flag bool) Option
  • func WithLogArguments(flag bool) Option
  • func WithLogDriverErrorSkip(flag bool) Option
  • func WithMinimumLevel(lvl Level) Option
  • func WithPreparerLevel(lvl Level) Option
  • func WithQueryerLevel(lvl Level) Option
  • func WithSQLArgsFieldname(name string) Option
  • func WithSQLQueryAsMessage(flag bool) Option
  • func WithSQLQueryFieldname(name string) Option
  • func WithStartTimeFieldname(name string) Option
  • func WithStatementIDFieldname(name string) Option
  • func WithTimeFieldname(name string) Option
  • func WithTimeFormat(format TimeFormat) Option
  • func WithTransactionIDFieldname(name string) Option
  • func WithUIDGenerator(gen UIDGenerator) Option
  • func WithWrapResult(flag bool) Option
• type TimeFormat
• type UIDGenerator

Functions

func OpenDriver

func OpenDriver(dsn string, drv driver.Driver, lg Logger, opt ...Option) *sql.DB

OpenDriver wrap given driver with logger and return *sql.DB.

Types

type DurationUnit

type DurationUnit uint8

DurationUnit is total time spent on an actual driver function call calculated by time.Since(start).

const (
	// DurationNanosecond will format time.Since() result to nanosecond unit (1/1_000_000_000 second).
	DurationNanosecond DurationUnit = iota
	// DurationMicrosecond will format time.Since() result to microsecond unit (1/1_000_000 second).
	DurationMicrosecond
	// DurationMillisecond will format time.Since() result to millisecond unit (1/1_000 second).
	DurationMillisecond
)

type Level

type Level uint8

Level is a log level which filterable by minimum level option.

const (
	// LevelTrace is the lowest level and the most detailed.
	// Use this if you want to know interaction flow from prepare, statement, execution to result/rows.
	LevelTrace Level = iota
	// LevelDebug is used by non Queryer(Context) and Execer(Context) call like Ping() and Connect().
	LevelDebug
	// LevelInfo is used by Queryer, Execer, Preparer, and Stmt.
	LevelInfo
	// LevelError is used on actual driver error or when driver not implement some optional sql/driver interface.
	LevelError
)

func (Level) String

func (l Level) String() string

String implement Stringer to convert type Level to string. nolint // disable goconst check

type Logger

type Logger interface {
	Log(ctx context.Context, level Level, msg string, data map[string]interface{})
}

Logger interface copied from: https://github.com/jackc/pgx/blob/f3a3ee1a0e5c8fc8991928bcd06fdbcd1ee9d05c/logger.go#L46-L49

type NullUID

type NullUID struct{}

NullUID is used to disable unique id when set to WithUIDGenerator().

func (*NullUID) UniqueID

func (u *NullUID) UniqueID() string

UniqueID return empty string and unique id will not logged.

type Option

type Option func(*options)

Option is optional variadic type in OpenDriver().

func WithConnectionIDFieldname

func WithConnectionIDFieldname(name string) Option

WithConnectionIDFieldname to customize connection ID fieldname on log output.

Default: "conn_id"

func WithDurationFieldname

func WithDurationFieldname(name string) Option

WithDurationFieldname to customize duration fieldname on log output.

Default: "duration"

func WithDurationUnit

func WithDurationUnit(unit DurationUnit) Option

WithDurationUnit to customize log duration unit.

Options: DurationMillisecond | DurationMicrosecond | DurationNanosecond

Default: DurationMillisecond

func WithErrorFieldname

func WithErrorFieldname(name string) Option

WithErrorFieldname to customize error fieldname on log output.

Default: "error"

func WithExecerLevel

func WithExecerLevel(lvl Level) Option

WithExecerLevel set default level of Exec(Context) method calls.

Default: LevelInfo

func WithIncludeStartTime

func WithIncludeStartTime(flag bool) Option

WithIncludeStartTime flag to include actual start time before actual driver execution.

Can be useful if we want to combine Log implementation with tracing from context and set start time span manually.

Default: false

func WithLogArguments

func WithLogArguments(flag bool) Option

WithLogArguments set flag to log SQL query argument or not.

When set to false, any SQL and result/rows argument on Queryer(Context) and Execer(Context) will not logged.

When set to true, argument type string and []byte will subject to trim on parseArgs() log output.

Default: true

func WithLogDriverErrorSkip

func WithLogDriverErrorSkip(flag bool) Option

WithLogDriverErrorSkip set flag for driver.ErrSkip.

If driver not implement optional interfaces, driver will return driver.ErrSkip and sql.DB will handle that. driver.ErrSkip could be false alarm in log analyzer because it was not actual error from app.

When set to false, logger will log any driver.ErrSkip.

Default: true

func WithMinimumLevel

func WithMinimumLevel(lvl Level) Option

WithMinimumLevel set minimum level to be logged. Logger will always log level >= minimum level.

Options: LevelTrace < LevelDebug < LevelInfo < LevelError

Default: LevelDebug

func WithPreparerLevel

func WithPreparerLevel(lvl Level) Option

WithPreparerLevel set default level of Prepare(Context) method calls.

Default: LevelInfo

func WithQueryerLevel

func WithQueryerLevel(lvl Level) Option

WithQueryerLevel set default level of Query(Context) method calls.

Default: LevelInfo

func WithSQLArgsFieldname

func WithSQLArgsFieldname(name string) Option

WithSQLArgsFieldname to customize SQL query arguments fieldname on log output.

Default: "args"

func WithSQLQueryAsMessage

func WithSQLQueryAsMessage(flag bool) Option

WithSQLQueryAsMessage set SQL query as message in log output (only for function call with SQL query).

Default: false

func WithSQLQueryFieldname

func WithSQLQueryFieldname(name string) Option

WithSQLQueryFieldname to customize SQL query fieldname on log output.

Default: "query"

func WithStartTimeFieldname

func WithStartTimeFieldname(name string) Option

WithStartTimeFieldname to customize start time fieldname on log output.

If WithIncludeStartTime true, start time fieldname will use this value.

Default: "start"

func WithStatementIDFieldname

func WithStatementIDFieldname(name string) Option

WithStatementIDFieldname to customize prepared statement ID fieldname on log output.

Default: "stmt_id"

func WithTimeFieldname

func WithTimeFieldname(name string) Option

WithTimeFieldname to customize log timestamp fieldname on log output.

Default: "time"

func WithTimeFormat

func WithTimeFormat(format TimeFormat) Option

WithTimeFormat to customize log time format.

Options: TimeFormatUnix | TimeFormatUnixNano | TimeFormatRFC3339 | TimeFormatRFC3339Nano

Default: TimeFormatUnix

func WithTransactionIDFieldname

func WithTransactionIDFieldname(name string) Option

WithTransactionIDFieldname to customize database transaction ID fieldname on log output.

Default: "tx_id"

func WithUIDGenerator

func WithUIDGenerator(gen UIDGenerator) Option

WithUIDGenerator set custom unique id generator for context call (connection, statement, transaction).

To disable unique id in log output, use &NullUID{}.

Default: newDefaultUIDDGenerator() called from setDefaultOptions().

func WithWrapResult

func WithWrapResult(flag bool) Option

WithWrapResult set flag to wrap Queryer(Context) and Execer(Context) driver.Rows/driver.Result response.

When set to false, result returned from db (driver.Rows/driver.Result object), will returned as is without wrapped inside &rows{} and &result{}.

Default: true

type TimeFormat

type TimeFormat uint8

TimeFormat is time.Now() format when Log() deliver the log message.

const (
	// TimeFormatUnix will format log time to unix timestamp.
	TimeFormatUnix TimeFormat = iota
	// TimeFormatUnixNano will format log time to unix timestamp with nano seconds.
	TimeFormatUnixNano
	// TimeFormatRFC3339 will format log time to time.RFC3339 format.
	TimeFormatRFC3339
	// TimeFormatRFC3339Nano will format log time to time.RFC3339Nano format.
	TimeFormatRFC3339Nano
)

type UIDGenerator

type UIDGenerator interface {
	UniqueID() string
}

UIDGenerator is an interface to generate unique ID for context call (connection, statement, transaction). The point of having unique id per context call is to easily track and analyze logs.

Note: no possible way to track id when statement Execer(Context),Queryer(Context) called from under db.Tx.