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