CODEWITHSUNDEEP
sqldatabaselogginggo
FlowRaja
FlowRaja

Reputation: 727

How to log queries to database drivers?

I am trying to write a simple Database application in go which access multiple data servers, some MySQL, MSSQL and SqlLite3. I am using the "database/sql" package to access them.

db, err := sql.Open(driver, dataSourceName)
result, err := db.Exec(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    "gopher",
    27,
)

I need to log the SQL queries to the individual servers for debugging and auditing. How can I achieve that?

Upvotes: 10

Views: 20017

Answers (3)

Stephen
Stephen

Reputation: 4296

To add logging capabilities to sql.DB, you can either make a wrapper for sql.DB or use the driver interface.

For a driver you would wrap an existing driver and add logging hooks to each of the ways of executing queries. Thats what I've done in the QueryPulse driver.

Upvotes: 0

zuckermanori
zuckermanori

Reputation: 1755

There is now a solution for that without having to wrap sql.DB with a wrapper that adds the logging.

sqldblogger facilitates this without needing to change existing sql.DB code.

Upvotes: 3

Elwinar
Elwinar

Reputation: 9509

Assuming that you don't want to use the servers logging facilities, the obvious solution would be to simply log all queries as they are made.

db, err := sql.Open(driver, dataSourceName)
log.Println(dataSourceName, "INSERT INTO users (name, age) VALUES (?, ?)", "gopher", 27)
result, err := db.Exec(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    "gopher",
    27,
)

This is the basic solution for your problem. You can refine it in multiple ways:

  • Create a log.Logger exclusively for your queries, so you can direct it to a particular output destination
  • Wrap the said log.Logger and the sql.DB objects in a special struct that will log queries as they are done

Here is a rough example of the said struct:

type DB struct {
    db *sql.DB
    dsn string
    log *log.Logger
}

func NewDB(driver, dsn string, log *log.Logger) (*DB, error) {
    db, err := sql.Open(driver, dsn)
    if err != nil {
        return nil, err
    }

    return &DB {
        db: db,
        dsn: dsn,
        log: log,
    }
}

func (d DB) Exec(query string, args ...interface{}) (sql.Result, err) {
    d.log.Println(dsn, query, args)
    return d.db.Exec(query, args...)
}

And how you would use it:

l := log.New(os.Stdout, "[sql]", log.LstdFlags)

db, _ := NewDB(driver, dataSourceName, l)
result, _ := db.Exec(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    "gopher",
    27,
)

Obviously, you can refined this design again, by adding error reporting, duration of the queries, etc.

Upvotes: 0

Related Questions