Attach hooks to any database/sql driver

Overview

sqlhooks

Build Status Go Report Card Coverage Status

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/qustavo/sqlhooks/v2

Requires Go >= 1.14.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/qustavo/sqlhooks
go get gopkg.in/qustavo/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/qustavo/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/qustavo/sqlhooks    11.713s
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.

    opened by xuzhenglun 19
  • 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.

    opened by keegancsmith 12
  • 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.

    opened by Gys 9
  • 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

    opened by ccmtaylor 8
  • 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.

    opened by nkev 7
  • 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

    opened by minaevmike 6
  • 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.

    opened by gmhafiz 4
  • 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.

    opened by dobegor 3
  • 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

    opened by surki 1
  • 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?

    opened by huangyong-170207 1
  • 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

    opened by conradludgate 2
  • 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.

    opened by qustavo 2
  • 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.

    opened by fadc80 1
Releases(v2.0.0)
Owner
Gustavo Chaín
Technical Dealer
Gustavo Chaín
BigQuery database/sql golang driver

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

Viant, Inc 6 Aug 31, 2022
Otelsql - OpenTelemetry SQL database driver wrapper for Go

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

Nhat 37 Nov 29, 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

Google APIs 77 Nov 22, 2022
Go-driver-benchmark - Driver benchmark with golang

We use ClickkHouse for time-series databases, and the driver's performance is ve

null 4 Sep 5, 2022
sqlcomment is an ent driver that adds SQL comments following sqlcommenter specification.

sqlcomment sqlcomment is an ent driver that adds SQL comments following sqlcommenter specification. sqlcomment includes support for OpenTelemetry and

ariga 52 Nov 14, 2022
A fully-featured AWS Athena database driver (+ athenareader https://github.com/uber/athenadriver/tree/master/athenareader)

?? athenadriver - A fully-featured AWS Athena database driver for Go ?? athenareader - A moneywise command line utililty to query athena in command li

Uber Open Source 116 Oct 23, 2022
Apache H2 Database Go Driver

Apache H2 Database Go Driver This driver is VERY experimental state NOT use for production yet Introduction Apache H2 Database is a very-low footprint

JM Robles 39 Oct 26, 2022
Golang database driver for SQLite

go-sqlite Golang database driver for SQLite. Does not use cgo. This driver is based on pure-Go SQLite implementation (https://gitlab.com/cznic/sqlite)

glebarez 147 Nov 20, 2022
SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call.

SQL API SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call. The request contains the DB credenti

Çiçeksepeti Tech 25 Nov 15, 2022
This is the code example how to use SQL to query data from any relational databases in Go programming language.

Go with SQL example This is the code example how to use SQL to query data from any relational databases in Go programming language. To start, please m

Muhammad Uzair Mohd Faizul 1 Mar 12, 2022
write APIs using direct SQL queries with no hassle, let's rethink about SQL

SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val

Mohammed Al Ashaal 2k Nov 23, 2022
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

null 0 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

null 0 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.

Jason Moiron 12.9k Nov 26, 2022
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

PingCAP 267 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/

null 1.7k Nov 30, 2022
Interceptors for database/sql

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

null 426 Nov 25, 2022
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

Sam Xie 123 Nov 29, 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

Brett Jones 318 Nov 21, 2022