Logging SQL statements executed

[miguel....@gmail.com]

Is it possible to log SQL statements executed with the sql package?

For instance, given the following small code block:

err := conn.QueryRow(`
SELECT foo, bar
FROM baz
WHERE
  zab = ?
`), zab).Scan(&foo, &bar)

Would it be possible to retrieve the SQL string after the statement was executed by the driver for the purpose of, say, logging an error that may occur during development?  Have had a look at the sql package and glanced at the source code but couldn't find anything.

[Giulio Iotti]

On Monday, October 26, 2015 at 8:04:56 PM UTC+2, miguel....@gmail.com wrote:

Is it possible to log SQL statements executed with the sql package?

There isn't and the reason is that prepared statements are handled differently by the various drivers. Often, the arguments of a prepared statement are never substituted inside the query so that not even the database sees the query the way you want to log it.

If you have a query and all it's arguments, it's trivial to replace all ? with %q and then call fmt.Fprint or similar. It might need some more attention (don't log blobs...) but otherwise, should work.

-- 

Giulio Iotti

[Jack Christensen]

You can do logging with database/sql if you use pgx (https://github.com/jackc/pgx) as your driver.

1. Create a pgx.ConnPool with a logger -- https://godoc.org/github.com/jackc/pgx#hdr-Logging
2. Create the database/sql connection from that pgx.ConnPool -- https://godoc.org/github.com/jackc/pgx/stdlib#OpenFromConnPool

Jack

Giulio Iotti wrote:

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[ran...@gmail.com]

I came up with the following implementation which works for MySQL.

// SQLQueryDebugString formats an sql query inlining its arguments

// The purpose is debug only - do not send this to the database!

// Sending this to the DB is unsafe and un-performant.

func SQLQueryDebugString(query string, args ...interface{}) string {

var buffer bytes.Buffer

nArgs := len(args)

// Break the string by question marks, iterate over its parts and for each 

// question mark - append an argument and format the argument according to

// it's type, taking into consideration NULL values and quoting strings.

for i, part := range strings.Split(query, "?") {

buffer.WriteString(part)

if i < nArgs {

switch a := args[i].(type) {

case int64:

buffer.WriteString(fmt.Sprintf("%d", a))

case bool:

buffer.WriteString(fmt.Sprintf("%t", a))

case sql.NullBool:

if a.Valid {

buffer.WriteString(fmt.Sprintf("%t", a.Bool))

} else {

buffer.WriteString("NULL")

}

case sql.NullInt64:

if a.Valid {

buffer.WriteString(fmt.Sprintf("%d", a.Int64))

} else {

buffer.WriteString("NULL")

}

case sql.NullString:

if a.Valid {

buffer.WriteString(fmt.Sprintf("%q", a.String))

} else {

buffer.WriteString("NULL")

}

case sql.NullFloat64:

if a.Valid {

buffer.WriteString(fmt.Sprintf("%d", a.Float64))

} else {

buffer.WriteString("NULL")

}

default:

buffer.WriteString(fmt.Sprintf("%q", a))

}

}

}

return buffer.String()

}

For postgres you'd have to do something a little different since it doesn't use "?", rather positional $s.