How to print the SQL log info?

[yinhuanyi]

If we execute a sql, how can we print the raw SQL into my log file.

[0x457]

me too

[shyandsy]

+1

[rowanseymour]

Would be nice to have an easy way to hook or wrap queries so we can catch and log long running queries at an application level

[BlackSinny]

+1

[simukti]

@yinhuanyi I also have this case some time ago.

If you don't mind adding an external library, I wrote a transparent logger for standard library sql.DB which you can pass it to sqlx. (https://github.com/simukti/sqldb-logger)

Here is the sample integration with sqlx:

package main

import (
    "database/sql"
    "os"

    "github.com/jmoiron/sqlx"
    _ "github.com/mattn/go-sqlite3"
    "github.com/rs/zerolog"
    sqldblogger "github.com/simukti/sqldb-logger"
    "github.com/simukti/sqldb-logger/logadapter/zerologadapter"
)

func main() {
    // initiate *sql.DB
    driverName := "sqlite3"
    dsn := "file:sample.db"
    db, err := sql.Open(driverName, dsn)
    if err != nil {
        return
    }
    // initiate zerolog
    zerolog.SetGlobalLevel(zerolog.DebugLevel)
    zlogger := zerolog.New(os.Stdout).With().Timestamp().Logger()
    // prepare logger
    loggerOptions := []sqldblogger.Option{
        sqldblogger.WithSQLQueryFieldname("sql"),
        sqldblogger.WithWrapResult(false),
        sqldblogger.WithExecerLevel(sqldblogger.LevelDebug),
        sqldblogger.WithQueryerLevel(sqldblogger.LevelDebug),
        sqldblogger.WithPreparerLevel(sqldblogger.LevelDebug),
    }
    // wrap *sql.DB to transparent logger
    db = sqldblogger.OpenDriver(dsn, db.Driver(), zerologadapter.New(zlogger), loggerOptions...)
    // pass it sqlx
    sqlxDB := sqlx.NewDb(db, driverName)
    // use sqlxDB as usual, no need any more change
    _ = sqlxDB.Ping()
    var res int
    _ = sqlxDB.QueryRowx(`SELECT 1+2`).Scan(&res)
}

Log output:

{"level":"debug","conn_id":"G6Oyg4iqK6SOO6au","duration":1.057364,"time":1660201999,"time":"2022-08-11T14:13:19+07:00","message":"Connect"}
{"level":"debug","conn_id":"G6Oyg4iqK6SOO6au","duration":0.000671,"time":1660201999,"time":"2022-08-11T14:13:19+07:00","message":"Ping"}
{"level":"debug","conn_id":"G6Oyg4iqK6SOO6au","duration":0.026285,"sql":"SELECT 1+2","time":1660201999,"time":"2022-08-11T14:13:19+07:00","message":"QueryContext"}

If you want to output the logs to a file, pass a file writer to the logger.

I hope that helps.

[kecci]

I found another library sql for hook: https://github.com/qustavo/sqlhooks they can do before & after for the query executed/queried

[Bjohnson131]

+1

[BlackSinny]

but i can not print real code line, i see gorm had been realized yet