如何将查询记录到数据库驱动程序？

Question

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?

Answer 1

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.