How do you use the "database/sql" package to access a SQL database in Go?

Using the 'database/sql' package in Go to access SQL databases is a standard practice. This package provides a set of standard interfaces that enable Go applications to interact with various SQL databases, such as MySQL, PostgreSQL, and SQLite. The following outlines the basic steps and examples for using this package:

1. Import the database/sql package and database driver

First, import the 'database/sql' package and the database driver you select. For example, with MySQL, you must also import the MySQL driver, such as 'github.com/go-sql-driver/mysql'.

go
import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

Note that an underscore is used before the import path for the database driver because we only need the driver's initialization effect and do not directly use the package.

2. Establish a database connection

Use the sql.Open() function to establish a connection to the database. This function requires two parameters: the driver name and the connection string.

go
db, err := sql.Open("mysql", "user:password@/dbname")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

Here, 'mysql' is the driver name, and 'user:password@/dbname' is the connection string, which may vary depending on the database and configuration.

3. Execute queries

You can use db.Query() or db.QueryRow() to execute SQL queries. db.Query() returns multiple rows, whereas db.QueryRow() returns a single row.

go
rows, err := db.Query("SELECT id, name FROM users WHERE id = ?", 1)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var id int
    var name string
    if err := rows.Scan(&id, &name); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("ID: %d, Name: %s\n", id, name)
}

if err := rows.Err(); err != nil {
    log.Fatal(err)
}

4. Insert and update data

Use db.Exec() to execute INSERT, UPDATE, or DELETE statements.

go
stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

res, err := stmt.Exec("John Doe")
if err != nil {
    log.Fatal(err)
}

lastId, err := res.LastInsertId()
if err != nil {
    log.Fatal(err)
}

fmt.Printf("Inserted ID: %d\n", lastId)

5. Error handling

Error handling is essential at every step to ensure timely detection and resolution of issues.

This brief introduction demonstrates how to use the database/sql package for basic database operations. In real-world projects, you may also need to consider additional advanced features such as connection pool management, transaction handling, security, and performance optimization.