How to log queries with pgx?

Question

I couldn't find it documentation how to log sql queries if I use pgx pool. For example I have created pool like this:

func DB() *pgxpool.Pool {
    connStr := os.Getenv("DATABASE_URL")
    conn, err := pgxpool.Connect(context.Background(), connStr)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    return conn
}

Please tell me how to log my queries then?

Answer 1

Full credit goes to @mystdeim, who answered above.

Reason for copying: Clear explanation of imports

Let's begin

Original answer:

func DB() *pgxpool.Pool {
config, err := pgxpool.ParseConfig(connStr)
if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
    os.Exit(1)
}
looger := &log.Logger{
    Out:          os.Stderr,
    Formatter:    new(log.JSONFormatter),
    Hooks:        make(log.LevelHooks),
    Level:        log.InfoLevel,
    ExitFunc:     os.Exit,
    ReportCaller: false,
}
config.ConnConfig.Logger = logrusadapter.NewLogger(looger)
conn, err := pgxpool.ConnectConfig(context.Background(), config)
if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
    os.Exit(1)
}
return conn
}

The above code is ok, but I will talk about two points here

1. Import: log

There is a confusing import of log

lets have a closer look

    looger := &log.Logger{
    Out:          os.Stderr,
    Formatter:    new(log.JSONFormatter),
    Hooks:        make(log.LevelHooks),
    Level:        log.InfoLevel,
    ExitFunc:     os.Exit,
    ReportCaller: false,
}
config.ConnConfig.Logger = logrusadapter.NewLogger(looger)

First, let's talk about the log package import. Assuming from the last line, he is using logrus

So

import (
"log"
)

is out of the question, because you will lose the power of logrus then.

Now if you rename logrus to log by using

import (
log "github.com/sirupsen/logrus"
)

It will generate another error:

LstdFlags not declared by package logrus (UndeclaredImportedName)

2. Import logrusadapter

no longer works:

    import (
"github.com/jackc/pgx/log/logrusadapter"
)

currently working:

        import (
"github.com/jackc/pgx/v4/log/logrusadapter"
)

[ well, it seems it is in v4 in 2021, make sure to check your version before importing in the future]

1. My modified solution

You don't need to remame logrus, keep it as it is.

                import (
 "github.com/sirupsen/logrus"
 "github.com/jackc/pgx/v4/pgxpool"
"github.com/jackc/pgx/v4/log/logrusadapter"

)

and finally

func DB() *pgxpool.Pool {
    config, err := pgxpool.ParseConfig(connStr)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
        os.Exit(1)
    }
    logrusLogger := &logrus.Logger{
    Out:          os.Stderr,
    Formatter:    new(logrus.JSONFormatter),
    Hooks:        make(logrus.LevelHooks),
    Level:        logrus.InfoLevel,
    ExitFunc:     os.Exit,
    ReportCaller: false,
   }
    config.ConnConfig.Logger = logrusadapter.NewLogger(logrusLogger)
    conn, err := pgxpool.ConnectConfig(context.Background(), config)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    return conn
}

Big thanks to @mystdeim for helping me to find a good logging system

Answer 2

You could also want to have a proper control over the logged queries, and for that you can use a tracer:

Here I use a zap.SugaredLogger as a logger but it could be anything else.

type myQueryTracer struct {
    log *zap.SugaredLogger
}

func (tracer *myQueryTracer) TraceQueryStart(
    ctx context.Context,
    _ *pgx.Conn,
    data pgx.TraceQueryStartData) context.Context {
    tracer.log.Infow("Executing command", "sql", data.SQL, "args", data.Args)

    return ctx
}

func (tracer *myQueryTracer) TraceQueryEnd(ctx context.Context, conn *pgx.Conn, data pgx.TraceQueryEndData) {
}

func DB() *pgxpool.Pool {
    connStr := os.Getenv("DATABASE_URL")
    dbConfig, err := pgx.ParseConfig(connStr)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to parse connString: %v\n", err)
        os.Exit(1)
    }

    dbConfig.Tracer = &myQueryTracer{
        log: log,
    }

    conn, err := pgx.ConnectConfig(
        context.Background(),
        dbConfig,
    )

    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }

    return conn
}

Answer 3

I ended up with the following solution:

func DB() *pgxpool.Pool {
    config, err := pgxpool.ParseConfig(connStr)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
        os.Exit(1)
    }
    looger := &log.Logger{
        Out:          os.Stderr,
        Formatter:    new(log.JSONFormatter),
        Hooks:        make(log.LevelHooks),
        Level:        log.InfoLevel,
        ExitFunc:     os.Exit,
        ReportCaller: false,
    }
    config.ConnConfig.Logger = logrusadapter.NewLogger(looger)
    conn, err := pgxpool.ConnectConfig(context.Background(), config)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    return conn
}