Alright, let’s get our hands dirty with the three workhorses of the database/sql package: Query, QueryRow, and Exec. These are the functions you’ll use 99% of the time to actually talk to your database. They might look similar at a glance, but they serve distinct purposes and, more importantly, they have wildly different failure modes. Using the wrong one is like using a screwdriver to hammer a nail—it might eventually work, but you’re going to have a bad time and probably damage something.

First, a crucial piece of context that the documentation buries in the fine print: under the hood, these methods don’t directly execute anything. They prepare your SQL statement, execute it, and then close the prepared statement—all in one go. This is a design choice that saves you from the boilerplate of Prepare, but it means you’re potentially re-preparing the same SQL string over and over. For most OLTP workloads, this is fine. For a tight loop executing the same query a million times, you’d want to manually Prepare and reuse that statement. But I’m getting ahead of myself.

Exec: When You’re Changing Things

Use Exec for statements that modify data and don’t return rows. This is your go-to for INSERT, UPDATE, DELETE, and any DDL statements like CREATE TABLE.

The key return values are Result and an error. The Result interface gives you two useful methods:

  • LastInsertId(): Gets the integer (e.g., auto-increment ID) generated by an INSERT operation. Heads up: This doesn’t work with all databases (like PostgreSQL). It’s a classic case of the Go SQL package aiming for the lowest common denominator. Rely on it at your peril.
  • RowsAffected(): Tells you how many rows were changed by your UPDATE or DELETE.
result, err := db.Exec(
    "INSERT INTO users (name, email) VALUES ($1, $2)",
    "Darth Vader",
    "dvader@empire.gov",
)
if err != nil {
    // This is usually a syntax error in your SQL, a constraint violation, or a connection issue.
    log.Fatal(err)
}

id, err := result.LastInsertId()
if err != nil {
    // This will likely fail if you're using PostgreSQL. You'd need to use QueryRow with RETURNING.
    log.Println("LastInsertId is not supported:", err)
} else {
    fmt.Printf("New user has ID %d\n", id)
}

rowsAffected, err := result.RowsAffected()
if err != nil {
    // This is rare, but can happen if the driver doesn't support it.
    log.Println("Couldn't get rows affected:", err)
}
fmt.Printf("Query affected %d row(s)\n", rowsAffected)

Query: For When You Expect Many Rows

Use Query when you expect a result set with multiple rows. Think SELECT * FROM users.

It returns a *Rows object and an error. The Rows object is an iterator; you must call rows.Next() to advance through each row. This is non-negotiable. If you don’t Next() through all the rows, you will leak the database connection. It’s not a garbage-collected resource; it’s a finite pool resource. Leak enough and your entire application will hang, waiting for a connection that’s never coming back.

rows, err := db.Query("SELECT name, email FROM users WHERE planet = $1", "Tatooine")
if err != nil {
    log.Fatal(err) // Again, could be a SQL error or connection issue.
}
// DEFER THIS. This is the most important line in this code block.
// It closes the rows object (and the underlying connection) when we're done.
// Even if we break the loop or return early, this defer will run.
defer rows.Close()

// Now we iterate.
for rows.Next() {
    var name, email string
    // Scan copies the current row's columns into the pointers you provide.
    err := rows.Scan(&name, &email)
    if err != nil {
        log.Fatal(err) // This could be a type mismatch or a conversion error.
    }
    fmt.Printf("User %s has email %s\n", name, email)
}

// DON'T FORGET THIS. rows.Next() breaks the loop on error or when done.
// This checks which one it was. If there was an error during iteration,
// it will be reported here.
if err = rows.Err(); err != nil {
    log.Fatal(err)
}

The defer rows.Close() and the rows.Err() check after the loop are your best defense against cryptic, hard-to-debug connection pool exhaustion.

QueryRow: For That One Row You Promised

Use QueryRow when you expect exactly one row. This is for those SELECT ... WHERE id = $1 queries. It’s a convenience method that hides the Rows iterator because, hey, you only asked for one thing.

It returns a *Row object. Here’s the kicker: QueryRow does not return an error directly. Instead, any error that occurs during execution (bad SQL, no rows found, connection drop) is stored on the Row object and only surfaces when you call Scan().

var email string
var age int
// This query promises to return exactly one row. What if it doesn't?
err := db.QueryRow(
    "SELECT email, age FROM users WHERE id = $1",
    42,
).Scan(&email, &age) // The error is right here.

if err != nil {
    if err == sql.ErrNoRows {
        // This is the specific error for a well-formed query that returned nothing.
        // You MUST handle this. It is not an exceptional situation; it's a business logic outcome.
        log.Println("No user found with ID 42.")
    } else {
        // This is any other error (syntax, connection, etc.)
        log.Fatal(err)
    }
} else {
    fmt.Printf("User 42 has email %s and is %d years old\n", email, age)
}

The most common pitfall here is forgetting that sql.ErrNoRows is a possible and often valid outcome. If you don’t explicitly check for it, your code will treat a missing user the same way it treats a malformed SQL query, which is… not ideal.