Attach hooks to any database/sql driver

sqlhooks Build Status Coverage Status Go Report Card

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 log queries or measure execution time without modifying your actual code.

Install

go get github.com/gchaincl/sqlhooks/v2

Requires Go >= 1.8.x

Breaking changes

V2 isn't backward compatible with previous versions, if you want to fetch old versions, you can use go modules or get them from gopkg.in

go get github.com/gchaincl/sqlhooks
go get gopkg.in/gchaincl/sqlhooks.v1

Usage GoDoc

// This example shows how to instrument sql queries in order to display the time that they consume
package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"github.com/gchaincl/sqlhooks/v2"
	"github.com/mattn/go-sqlite3"
)

// Hooks satisfies the sqlhook.Hooks interface
type Hooks struct {}

// Before hook will print the query with it's args and return the context with the timestamp
func (h *Hooks) Before(ctx context.Context, query string, args ...interface{}) (context.Context, error) {
	fmt.Printf("> %s %q", query, args)
	return context.WithValue(ctx, "begin", time.Now()), nil
}

// After hook will get the timestamp registered on the Before hook and print the elapsed time
func (h *Hooks) After(ctx context.Context, query string, args ...interface{}) (context.Context, error) {
	begin := ctx.Value("begin").(time.Time)
	fmt.Printf(". took: %s\n", time.Since(begin))
	return ctx, nil
}

func main() {
	// First, register the wrapper
	sql.Register("sqlite3WithHooks", sqlhooks.Wrap(&sqlite3.SQLiteDriver{}, &Hooks{}))

	// Connect to the registered wrapped driver
	db, _ := sql.Open("sqlite3WithHooks", ":memory:")

	// Do you're stuff
	db.Exec("CREATE TABLE t (id INTEGER, text VARCHAR(16))")
	db.Exec("INSERT into t (text) VALUES(?), (?)", "foo", "bar")
	db.Query("SELECT id, text FROM t")
}

/*
Output should look like:
> CREATE TABLE t (id INTEGER, text VARCHAR(16)) []. took: 121.238µs
> INSERT into t (text) VALUES(?), (?) ["foo" "bar"]. took: 36.364µs
> SELECT id, text FROM t []. took: 4.653µs
*/

Benchmarks

 go test -bench=. -benchmem
 BenchmarkSQLite3/Without_Hooks-4                  200000              8572 ns/op             627 B/op         16 allocs/op
 BenchmarkSQLite3/With_Hooks-4                     200000             10231 ns/op             738 B/op         18 allocs/op
 BenchmarkMySQL/Without_Hooks-4                     10000            108421 ns/op             437 B/op         10 allocs/op
 BenchmarkMySQL/With_Hooks-4                        10000            226085 ns/op             597 B/op         13 allocs/op
 BenchmarkPostgres/Without_Hooks-4                  10000            125718 ns/op             649 B/op         17 allocs/op
 BenchmarkPostgres/With_Hooks-4                      5000            354831 ns/op            1122 B/op         27 allocs/op
 PASS
 ok      github.com/gchaincl/sqlhooks    11.713s
Owner
Gustavo Chaín
Technical Dealer
Gustavo Chaín
Comments
  • handler sql error

    handler sql error

    Now if any SQL error happens, After hook will not be called. It's cause OpenTracing span can't be finished normally. And the span will disappear on OpenTracing dashboard(I'm using Jaeger). It makes lot's of inconvenient because the only thing I really care about is the error message.

  • Return an ExecerContext if conn is an execer

    Return an ExecerContext if conn is an execer

    A driver.Conn can optionally implement driver.ExecerContext or driver.Execer. When they do, instead of creating a prepared statement and then executing that database/sql will directly call ExecContext or Exec respectively. We need to implement this since some drivers do not support all statements being prepared first. For example in postgresql you can run multiple statements in a conn.Exec, but it will fail if you try to prepare multiple statements in one call.

  • Actual resulting query?

    Actual resulting query?

    I was hoping the after hook would include the resulting query, as it is actually passed to the database.

    So instead of showing the original query like

    'select id, first, last from users where id=?', '12'
    

    It would include

    'select id, first, last from users where id=12'
    

    Useful for debugging edge cases, logging of all actual statements (to rebuild a database or specific situation) and other things.

  • allow composing Hooks

    allow composing Hooks

    adds func Compose that allows composing multiple Hooks into one. It runs every callback on every hook in argument order, even if previous hooks return an error. If multiple hooks return errors, the error return value will be MultipleErrors, which allows for introspecting the errors if necessary.

    I chose not to abort processing when any handler returns an error, because common usage would be to compose logging / tracing / metrics, and those kinds of handlers will want to run in every case to make sure that e.g. latencies are tracked correctly.

    I added compatibility with Go 1.13's errors.Is/errors.As methods under a conditional compilation flag.

    Fixes: #12

  • Change Notifications

    Change Notifications

    Great work! I think this could be great for data change notification events to various parts of an application. Is it possible to make the hooks pre or post execution with the ability to cancel a pre-hook query? For example, a use case might be to stop any query that contains "CREATE TABLE" for users that don't have that permission.

  • can't use transactions with sqlhooks

    can't use transactions with sqlhooks

    I can't start transaction with non default isolation level because connection returned from sqlhooks.Conn wrapper doesn't implement driver.ConnBeginTx interface https://github.com/golang/go/blob/master/src/database/sql/ctxutil.go#L100

  • driver.ErrSkip error with sqlx

    driver.ErrSkip error with sqlx

    The hooks get entered twice for each sql query because from my understanding, the first hit to the hook get cancelled because of driver.ErrSkip "driver: skip fast-path; continue as if unimplemented" It seems to happen with sqlx.

    More details is in the discussion over at data-dog https://github.com/DataDog/dd-trace-go/issues/270

    Right now a quick fix would be checking for driver.ErrSkip and ignore it in every execs and query

    results, err := conn.execContext(ctx, query, args)
    - if err != nil {
    + if err != nil && err != driver.ErrSkip {
        return results, handlerErr(ctx, conn.hooks, err, query, list...)
    }
    

    The other fix (from the thread) is to set interpolateParams=true is the database dsn.

  • Issues with gorm

    Issues with gorm

    I've recently tried out sqlhooks and encountered a problem:

    (/Users/dobegor/go/src/smartback/v2/service/label_service.go:65)
    [2018-05-23 14:51:15]  Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"labels"  WHERE (user_id = 0 AND is_system = 1 AND label IN (?,?,?,?))' at line 1
    
    (/Users/dobegor/go/src/smartback/v2/service/label_service.go:65)
    [2018-05-23 14:51:15]  [2.00ms]  SELECT * FROM "labels"  WHERE (user_id = 0 AND is_system = 1 AND label IN ('Inbox','Archive','Trash','Snoozed'))
    

    As you can see, for some reason requests went into mysql without substitution. This only occurs if I wrap the driver with sqlhooks:

    
    	sql.Register("mysqlWithHooks", sqlhooks.Wrap(&mysql.MySQLDriver{}, &Hooks{}))
    
    	db, err := gorm.Open("mysqlWithHooks", config.DB.DSN())
    	if err != nil {
    		return stacktrace.Propagate(err, "Failed to connect to DB")
    	}
    

    I wonder if I'm doing something wrong here.

  • QueryerContext interface and prepared statements

    QueryerContext interface and prepared statements

    If we don't implement QueryerContext or Queryer interface for connection, db.Query() will always do statement "prepare" and statement "exec". These extra roundtrips can add unneeded latency.

    Run this test program and check in wireshark: https://play.golang.org/p/wvmsGliLPpz

    A change like this fixes this: https://github.com/surki/sqlhooks/commit/98e9bd8ddee254ae4ae9ad8058c37e993ec097d8

    I could send a PR if you would like this fixed here

  • when using wraper driver, table name has  Double quotes

    when using wraper driver, table name has Double quotes

    when using wraper dierver, code like this: image sql exec err for table name has double quotes, image

    when not using wraper, code like this image sql exec success, table name change ok image

    so, what is the problem?

  • feat: support driver.DriverContext interface

    feat: support driver.DriverContext interface

    sql.Open internally makes use of the DriverContext interface, and if a Driver does not implement it, it creates a light wrapper1.

    This DriverContext provides better context support as well as providing some useful error checking upfront. For instance, the mysql driver can check the DSN value before opening a connection to see if it's well formed. That way you get an error instantly instead of an error when making your first sql query

    1: https://github.com/golang/go/blob/d0dd26a88c019d54f22463daae81e785f5867565/src/database/sql/sql.go#L832 https://github.com/golang/go/blob/d0dd26a88c019d54f22463daae81e785f5867565/src/database/sql/sql.go#L755-L766

  • Add Rows wrapper and safetyhooks

    Add Rows wrapper and safetyhooks

    Work in progress of safetyhooks, which panics when sql.Rows are not closed, this is useful to detect leaks. Based on sharp-edge finalizers https://crawshaw.io/blog/sharp-edged-finalizers.

  • SQL commands inside hook methods

    SQL commands inside hook methods

    I would like to execute SQL commands inside the hook methods. These commands must be on the same transaction of the original command. I mean the one triggered by the programmer using the database/sql API. I have to do this transparently. Do you think it is feseable and straightforward to implement? I guess I would have to add extra parameters to the hook methods' signatures. However, I am wondering how I could append these extra comands to the ongoing transaction.

Microsoft ActiveX Object DataBase driver for go that using exp/sql

go-adodb Microsoft ADODB driver conforming to the built-in database/sql interface Installation This package can be installed with the go get command:

Dec 30, 2022
Oracle driver for Go using database/sql

go-oci8 Description Golang Oracle database driver conforming to the Go database/sql interface Installation Install Oracle full client or Instant Clien

Dec 30, 2022
sqlite3 driver for go using database/sql

go-sqlite3 Latest stable version is v1.14 or later not v2. NOTE: The increase to v2 was an accident. There were no major changes or features. Descript

Jan 8, 2023
Go Sql Server database driver.

gofreetds Go FreeTDS wrapper. Native Sql Server database driver. Features: can be used as database/sql driver handles calling stored procedures handle

Dec 16, 2022
Pure Go Postgres driver for database/sql

pq - A pure Go postgres driver for Go's database/sql package Install go get github.com/lib/pq Features SSL Handles bad connections for database/sql S

Jan 2, 2023
Qmgo - The Go driver for MongoDB. It‘s based on official mongo-go-driver but easier to use like Mgo.

Qmgo English | 简体中文 Qmgo is a Go driver for MongoDB . It is based on MongoDB official driver, but easier to use like mgo (such as the chain call). Qmg

Dec 28, 2022
Go driver for PostgreSQL over SSH. This driver can connect to postgres on a server via SSH using the local ssh-agent, password, or private-key.

pqssh Go driver for PostgreSQL over SSH. This driver can connect to postgres on a server via SSH using the local ssh-agent, password, or private-key.

Nov 6, 2022
Mirror of Apache Calcite - Avatica Go SQL Driver

Apache Avatica/Phoenix SQL Driver Apache Calcite's Avatica Go is a Go database/sql driver for the Avatica server. Avatica is a sub-project of Apache C

Nov 3, 2022
Firebird RDBMS sql driver for Go (golang)

firebirdsql (Go firebird sql driver) Firebird RDBMS http://firebirdsql.org SQL driver for Go Requirements Firebird 2.5 or higher Golang 1.13 or higher

Dec 20, 2022
Microsoft SQL server driver written in go language

A pure Go MSSQL driver for Go's database/sql package Install Requires Go 1.8 or above. Install with go get github.com/denisenkom/go-mssqldb . Connecti

Dec 26, 2022
GoBigdis is a persistent database that implements the Redis server protocol. Any Redis client can interface with it and start to use it right away.

GoBigdis GoBigdis is a persistent database that implements the Redis server protocol. Any Redis client can interface with it and start to use it right

Apr 27, 2022
GO DRiver for ORacle DB

Go DRiver for ORacle godror is a package which is a database/sql/driver.Driver for connecting to Oracle DB, using Anthony Tuininga's excellent OCI wra

Jan 5, 2023
PostgreSQL driver and toolkit for Go

pgx - PostgreSQL Driver and Toolkit pgx is a pure Go driver and toolkit for PostgreSQL. pgx aims to be low-level, fast, and performant, while also ena

Jan 4, 2023
Lightweight Golang driver for ArangoDB

Arangolite Arangolite is a lightweight ArangoDB driver for Go. It focuses on pure AQL querying. See AranGO for a more ORM-like experience. IMPORTANT:

Sep 26, 2022
Go language driver for RethinkDB
Go language driver for RethinkDB

RethinkDB-go - RethinkDB Driver for Go Go driver for RethinkDB Current version: v6.2.1 (RethinkDB v2.4) Please note that this version of the driver on

Dec 24, 2022
goriak - Go language driver for Riak KV
goriak - Go language driver for Riak KV

goriak Current version: v3.2.1. Riak KV version: 2.0 or higher, the latest version of Riak KV is always recommended. What is goriak? goriak is a wrapp

Nov 22, 2022
Mongo Go Models (mgm) is a fast and simple MongoDB ODM for Go (based on official Mongo Go Driver)
Mongo Go Models (mgm) is a fast and simple MongoDB ODM for Go (based on official Mongo Go Driver)

Mongo Go Models Important Note: We changed package name from github.com/Kamva/mgm/v3(uppercase Kamva) to github.com/kamva/mgm/v3(lowercase kamva) in v

Jan 2, 2023
The MongoDB driver for Go

The MongoDB driver for Go This fork has had a few improvements by ourselves as well as several PR's merged from the original mgo repo that are current

Jan 8, 2023
The Go driver for MongoDB
The Go driver for MongoDB

MongoDB Go Driver The MongoDB supported driver for Go. Requirements Installation Usage Bugs / Feature Reporting Testing / Development Continuous Integ

Dec 31, 2022