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

Barolet

do you want to do it on "server" side or on mysql side? – Pierpont

Server side is preferrable. – Barolet

if you are implementing your project right now. you can create one entry point in your application where sqlx used. you can do logging there. is it works for you? – Pierpont

There are a lot of places we are calling sqlx APIs. That is not a very appealing solution for me. – Barolet

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.

Envenom

I'm stuck on queries with arguments like: ("select * from item where id=?", nId). I'm getting result like: select * from item where id=?%!(EXTRA int=2). Logger.Printf doesn't understand character '?'. Please help. – Cartomancy

@RamAshishPal I provided a very basic implementation of such logging. If you want to substitute '?' arguments with the actual values, you'll have to parse SQL strings manually and do the replacement yourself. If you need help writing a function for this, you should ask a new question. – Envenom