A few years ago, I was reviewing a PR for a junior dev who had built a sleek little API for a logistics company. On the surface, the code was clean. However, I noticed they were calling sql.Open() inside their HTTP handler function. In their staging environment with two users, it worked perfectly. But the moment they deployed to production, the app crashed within ten minutes. Why? Because they were opening a new connection pool for every single incoming request, quickly exhausting the database's max connection limit. The database didn't just slow down; it started rejecting every single attempt to connect, effectively DOSing itself.

The biggest mental shift you need when using the database/sql package is realizing that a *sql.DB object is not a single connection to the database. It is a pool of connections. You should create it once when your application starts and pass that same instance around to your services or repositories. Go manages the opening and closing of individual connections under the hood; your job is just to tell it which driver to use and how to connect.

Managing the Connection Pool and Drivers

Go's database/sql package provides a generic interface, but it doesn't actually know how to "talk" to PostgreSQL, MySQL, or SQLite. For that, you need a driver. You'll notice a weird pattern in Go database code where you import a driver with an underscore, like _ "github.com/lib/pq". This is a "blank import"—it tells Go to execute the driver's init() function to register itself with the sql package without you having to call any functions from that package directly.

import (
    "database/sql"
    _ "github.com/lib/pq" // The driver registers itself here
)

func main() {
    // Open doesn't actually connect; it just validates the arguments
    db, err := sql.Open("postgres", "user=postgres password=secret dbname=bookstore sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // This is where the first actual connection is attempted
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
}

Querying Data and the Scanning Ritual

When you want to get data out of the database, you'll mostly use Query, QueryRow, or Exec. If you expect multiple rows, use Query. If you're looking for a specific record by ID, QueryRow is your friend because it simplifies the logic by returning a single row object.

The part that trips most people up is Scan(). Go cannot magically map a database row to a struct. You have to explicitly tell Go which column goes into which variable, and the order must match your SQL query exactly. I've spent way too many hours debugging a "sql: Scan error" only to realize I had swapped the Author and Title columns in my SELECT statement.

type Book struct {
    ID    int
    Title string
    Stock int
}

func getBook(db *sql.DB, id int) (*Book, error) {
    var b Book
    // QueryRow is perfect for single-record lookups
    err := db.QueryRow("SELECT id, title, stock FROM books WHERE id = $1", id).Scan(&b.ID, &b.Title, &b.Stock)
    if err != nil {
        return nil, err
    }
    return &b, nil
}

Avoiding the Memory Leak Trap

When you use db.Query(), it returns a *sql.Rows object. This object holds a database connection open until it is closed. If you forget to call rows.Close(), you're leaking connections. Eventually, your app will hang because the pool is exhausted, and you'll be right back in that nightmare scenario I mentioned at the start.

Always use defer rows.Close() immediately after checking the error from Query(). Also, remember that rows.Next() prepares the next row for reading, but you still need to check rows.Err() after the loop finishes. A network glitch could terminate the stream early, and if you don't check the error, you might return a partial list of results as if it were a complete set.

func listLowStockBooks(db *sql.DB) ([]Book, error) {
    rows, err := db.Query("SELECT id, title, stock FROM books WHERE stock < 5")
    if err != nil {
        return nil, err
    }
    defer rows.Close() // Crucial: prevents connection leaks

    var books []Book
    for rows.Next() {
        var b Book
        if err := rows.Scan(&b.ID, &b.Title, &b.Stock); err != nil {
            return nil, err
        }
        books = append(books, b)
    }

    // Check for errors that happened during iteration
    if err = rows.Err(); err != nil {
        return nil, err
    }

    return books, nil
}

📋 Practical Task

Exercise: Build a Bookstore Inventory Manager

Your task is to create a small CLI tool that manages a bookstore's inventory using a database (SQLite is recommended for this exercise via github.com/mattn/go-sqlite3). Your program should implement the following functionality:

• Initialization: Create a table named books with columns id (INTEGER PRIMARY KEY), title (TEXT), and stock (INTEGER).
• Add Book: A function addBook(db *sql.DB, title string, stock int) that uses db.Exec() to insert a new book into the database.
• Update Stock: A function updateStock(db *sql.DB, id int, newStock int) that updates the quantity of a specific book.
• Low Stock Report: A function getLowStock(db *sql.DB, threshold int) ([]Book, error) that returns all books with stock below the given threshold, ensuring you properly close the rows and check for iteration errors.

Requirements: 1. Use a single *sql.DB instance throughout the life of the program. 2. Use parameterized queries (e.g., ? or $1) to prevent SQL injection. 3. Ensure rows.Close() is handled correctly in the reporting function.