How to log and monitor SQL database interactions in Go applications?

There are several approaches you can take to log and monitor SQL database interactions in Go applications. Here's one possible way:

Start by importing the necessary packages:

import (
    "database/sql"
    "fmt"
    "log"
    "os"
    "time"

    _ "github.com/go-sql-driver/mysql" // Driver for MySQL
)

Initialize a logger. This can be done using the built-in log package or any other logging library of your choice. For example:

logger := log.New(os.Stdout, "", log.Ldate|log.Ltime)

Open a connection to your database using the appropriate driver for your database. In this example, we'll be using MySQL:

db, err := sql.Open("mysql", "username:password@tcp(localhost:3306)/database")
if err != nil {
    logger.Fatalf("Failed to connect to database: %v", err)
}
defer db.Close()

Wrap the db object with a custom logger that will intercept and log all database interactions. Here's an example of a basic logger that logs the SQL query and the time taken for each query execution:

type LoggingDB struct {
    *sql.DB
    logger *log.Logger
}

func (ldb *LoggingDB) Query(query string, args ...interface{}) (*sql.Rows, error) {
    start := time.Now()
    rows, err := ldb.DB.Query(query, args...)
    elapsed := time.Since(start)
    ldb.logger.Printf("Query: %s, Args: %v, Elapsed Time: %s\n", query, args, elapsed)
    return rows, err
}

func (ldb *LoggingDB) Exec(query string, args ...interface{}) (sql.Result, error) {
    start := time.Now()
    result, err := ldb.DB.Exec(query, args...)
    elapsed := time.Since(start)
    ldb.logger.Printf("Query: %s, Args: %v, Elapsed Time: %s\n", query, args, elapsed)
    return result, err
}

func NewLoggingDB(db *sql.DB, logger *log.Logger) *LoggingDB {
    return &LoggingDB{
        DB:     db,
        logger: logger,
    }
}

// Wrap the existing db object with the custom logger
db = NewLoggingDB(db, logger)

From this point on, all SQL queries and executions will be logged based on the Query and Exec methods defined in the LoggingDB struct.

Note: This is just a basic example to get you started. You can enhance the logging functionality based on your specific requirements, such as including additional information like transaction IDs or logging errors more comprehensively.