How can I log all outgoing SQL statements from Go / MySQL?

Question

I'm using a non-framework Go stack with sqlx, and MySQL for a web project.

I would like to log all outgoing SQL statements for debugging. Is it possible to do this? Hopefully looking to get output like this (copied from a Rails project):

  User Load (94.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'bondnewyork' LIMIT 1
  User Load (16.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'mkovarik' LIMIT 1
  User Load (0.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'mkovarik' LIMIT 1
  User Load (0.3ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
  User Load (0.4ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1

Answer 1

sqlx has a very interesting abstraction in the form of the following interfaces:

• sqlx.Execer
• sqlx.Queryer

They are used all through the library as the interfaces representing the functionality of using strings as SQL queries.

For example:

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

// exec the schema or fail; multi-statement Exec behavior varies between
// database drivers;  pq will exec them all, sqlite3 won't, ymmv
db.MustExec("CREATE TABLE person (first_name text)")

The last line is in fact equivalent to:

sqlx.MustExec(db, "CREATE TABLE person (first_name text)")

Where db is used as an Execer.

In the same vein, this:

people := []Person{}
db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")

is equivalent to:

sqlx.Select(db, &people, "SELECT * FROM person ORDER BY first_name ASC")

where db is used as a Queryer.

So if you are willing not to use the DB type directly but instead use the underlying free functions of the library, you can use the following structure to wrap your db into objects that perform logging:

type QueryLogger struct {
    queryer sqlx.Queryer
    logger  *log.Logger
}

func (p *QueryLogger) Query(query string, args ...interface{}) (*sql.Rows, error) {
    p.logger.Print(query, args...)
    return p.queryer.Query(query, args...)
}

func (p *QueryLogger) Queryx(query string, args ...interface{}) (*Rows, error) {
    p.logger.Print(query, args...)
    return p.queryer.Queryx(query, args...)
}

func (p *QueryLogger) QueryRowx(query string, args ...interface{}) *Row {
    p.logger.Print(query, args...)
    return p.queryer.QueryRowx(query, args...)
}

And when connecting to your database:

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

ql := &QueryLogger{db, yourLogger}

sqlx.Select(ql, &people, "SELECT * FROM person ORDER BY first_name ASC")

Of course this only works when using the free functions of the sqlx library, so if your code has a large amount of calls using the sqlx.DB type, this will probably not be convenient enough.