How to log queries to database drivers?

I am trying to write a simple Database application in go which access multiple data servers, some MySQL, MSSQL and SqlLite3.
I am using the "database/sql" package to access them.

db, err := sql.Open(driver, dataSourceName)
result, err := db.Exec(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    "gopher",
    27,
)

I need to log the SQL queries to the individual servers for debugging and auditing.
How can I achieve that?

Assuming that you don't want to use the servers logging facilities, the obvious solution would be to simply log all queries as they are made.

db, err := sql.Open(driver, dataSourceName)
log.Println(dataSourceName, "INSERT INTO users (name, age) VALUES (?, ?)", "gopher", 27)
result, err := db.Exec(
	"INSERT INTO users (name, age) VALUES (?, ?)",
	"gopher",
	27,
)

This is the basic solution for your problem. You can refine it in multiple ways:

• Create a log.Logger exclusively for your queries, so you can direct it to a particular output destination
• Wrap the said log.Logger and the sql.DB objects in a special struct that will log queries as they are done

Here is a rough example of the said struct:

type DB struct {
	db *sql.DB
	dsn string
	log *log.Logger
}

func NewDB(driver, dsn string, log *log.Logger) (*DB, error) {
	db, err := sql.Open(driver, dsn)
	if err != nil {
		return nil, err
	}
	
	return &DB {
		db: db,
		dsn: dsn,
		log: log,
	}
}

func (d DB) Exec(query string, args ...interface{}) (sql.Result, err) {
	d.log.Println(dsn, query, args)
	return d.db.Exec(query, args...)
}

And how you would use it:

l := log.New(os.Stdout, "[sql]", log.LstdFlags)

db, _ := NewDB(driver, dataSourceName, l)
result, _ := db.Exec(
	"INSERT INTO users (name, age) VALUES (?, ?)",
	"gopher",
	27,
)

Obviously, you can refined this design again, by adding error reporting, duration of the queries, etc.

There is now a solution for that without having to wrap sql.DB with a wrapper that adds the logging.

sqldblogger facilitates this without needing to change existing sql.DB code.