The connection leak that kills your app
You write a query, run it, and the program crashes with a nil pointer dereference. Or worse, the program hangs because a goroutine is waiting for a result that never arrives. The database/sql package is the standard way to talk to SQL databases in Go, but it hides a lot of machinery behind a simple API. If you treat it like a synchronous function call, you'll leak connections and miss errors.
The most common bug isn't a syntax error. It's forgetting to close the result set. When that happens, the database connection stays checked out. After a few hundred requests, the pool runs dry, every new query blocks, and the server stops responding. The fix is a single line, but you have to understand why it's there.
The pool behind the query
The sql.DB type is not a single connection. It's a pool of connections. When you create a sql.DB with sql.Open, Go doesn't connect to the database immediately. It sets up the pool manager. The first query triggers the actual connection.
Think of sql.DB like a library card system. The library has a limited number of books. When you want to read, you check out a book. You read it, then you return it. If you walk out with the book and never return it, the library eventually has no books left. Other patrons stand in line waiting for a book that never comes back.
In Go, db.Query checks out a connection. The returned sql.Rows object holds that connection hostage. The connection stays checked out until you call rows.Close(). If you forget to close the rows, the connection leaks. The pool shrinks. Eventually, db.Query blocks waiting for a free connection that will never arrive.
The sql.DB object is safe for concurrent use. Create one per database and share it across your application. Don't open a new sql.DB for every request. That defeats the pool and creates too many connections. Trust gofmt to format your code, and trust sql.DB to manage the pool. Just return the connections.
Minimal example: fetching rows
Here's the standard pattern for fetching multiple rows: query, defer close, iterate, scan, check final error.
// ListUsers fetches all users and prints their names.
// This pattern shows the mandatory defer and the final rows.Err() check.
func ListUsers(db *sql.DB) error {
// Query returns a Rows object that holds a connection from the pool.
rows, err := db.Query("SELECT name FROM users")
if err != nil {
return err
}
// Defer close immediately to return the connection to the pool.
// Even if the loop panics, the connection gets back.
defer rows.Close()
for rows.Next() {
var name string
// Scan copies the current row's data into the variable.
// Pass pointers so Scan can write the values.
if err := rows.Scan(&name); err != nil {
return err
}
fmt.Println(name)
}
// Check for errors that happened during iteration, not just Scan.
return rows.Err()
}
The defer rows.Close() goes right after the error check on Query. If Query fails, there are no rows to close, so the defer is skipped. If Query succeeds, the defer runs when the function returns, no matter how it returns. This guarantees the connection returns to the pool.
The rows.Err() call at the end catches errors that happened during the stream. Scan returns errors for the current row. rows.Err returns errors that broke the stream, like a network drop or a driver panic. You need both.
Walkthrough: what happens under the hood
When you call db.Query, the pool manager grabs a connection. The driver sends the SQL to the database. The database starts executing the query and streaming results.
rows.Next fetches the next batch of rows from the database. It returns true if there's a row, false if the stream is done. You call Next in a loop. Inside the loop, Scan maps the columns to Go variables. Scan is strict about types. An integer column must go into an int or int64. A string column goes into a string. If the types don't match, Scan returns an error.
The compiler won't catch a type mismatch at runtime. If the database returns a timestamp and you scan into a string, the program fails with sql: Scan error on column index 0, name "created_at": converting driver.Value type time to a string: invalid syntax. You have to match the Go types to the database types.
When the loop finishes, rows.Close sends a cancel signal to the database if there are leftover results, and returns the connection to the pool. If you skip Close, the connection stays busy.
The if err != nil { return err } pattern is verbose by design. The Go community accepts the boilerplate because it makes the unhappy path visible. You can't accidentally swallow an error. Every error gets handled on the same line.
Realistic example: context and structs
Real code uses context for cancellation, scans into structs, and accumulates results in a slice.
// User represents a row from the users table.
type User struct {
ID int
Name string
Age int
}
// FetchUsersByAge returns users within an age range.
// It uses context for cancellation and scans into a struct.
func FetchUsersByAge(ctx context.Context, db *sql.DB, minAge, maxAge int) ([]User, error) {
// QueryContext allows the query to be cancelled if the caller gives up.
// Placeholders prevent SQL injection.
rows, err := db.QueryContext(ctx, "SELECT id, name, age FROM users WHERE age BETWEEN $1 AND $2", minAge, maxAge)
if err != nil {
return nil, err
}
defer rows.Close()
var users []User
for rows.Next() {
var u User
// Scan into struct fields. Order must match the SELECT list.
if err := rows.Scan(&u.ID, &u.Name, &u.Age); err != nil {
return nil, err
}
users = append(users, u)
}
// rows.Err catches network errors or driver issues during the loop.
if err := rows.Err(); err != nil {
return nil, err
}
return users, nil
}
QueryContext takes a context.Context as the first argument. This is a convention: context always goes first, named ctx. If the context is cancelled, the query stops. This prevents hanging queries when a client disconnects or a timeout fires. Functions that take a context should respect cancellation and deadlines.
The Scan call maps columns to struct fields. The order of arguments to Scan must match the order of columns in the SELECT list. database/sql doesn't use reflection to match names. You have to keep the order correct.
Pitfalls and errors
Databases have NULL values. Go has zero values. They don't match. If a column is NULL and you scan into a plain int, Scan fails. You need sql.NullInt64. It has an Int64 field and a Valid bool. Check Valid before using the value.
var age sql.NullInt64
if err := rows.Scan(&age); err != nil {
return err
}
if age.Valid {
fmt.Println(age.Int64)
} else {
fmt.Println("age is null")
}
Forgetting defer rows.Close() causes connection leaks. The compiler won't warn you. The program runs fine for a while, then the pool exhausts and requests start blocking. The worst goroutine bug is the one that never logs. A leaked connection doesn't panic. It just waits.
Forgetting rows.Err() causes silent failures. If the network drops mid-stream, Next returns false. The loop ends. You think you got all the rows. You missed the error. Always check rows.Err after the loop.
Using Query for a single row is inefficient. Query returns a Rows object that you have to close. QueryRow wraps the query and row iteration internally. It returns a *sql.Row that you scan once. If no row exists, Scan returns sql.ErrNoRows.
row := db.QueryRow("SELECT name FROM users WHERE id = $1", id)
var name string
err := row.Scan(&name)
if err == sql.ErrNoRows {
// Handle missing user
} else if err != nil {
return err
}
Placeholders vary by driver. database/sql doesn't rewrite queries. The driver handles placeholders. MySQL uses ?. PostgreSQL uses $1, $2. SQLite uses ? or $1. Your code is slightly driver-dependent on syntax, though the API is uniform. Check your driver docs for the placeholder format.
Decision: choosing the right method
Use db.QueryRow when you expect exactly one row and want a clean error if the row is missing. Use db.Query when you expect multiple rows and need to iterate over the result set. Use db.QueryContext when the query might take a long time or runs inside a request that can be cancelled. Use db.Exec when you are inserting, updating, or deleting data and don't need to read results. Use db.Prepare when you run the same query structure many times with different parameters. Use sql.Null* types when a column can be NULL and you need to distinguish between zero and null.
Exec returns a sql.Result. You can call RowsAffected or LastInsertId on the result. These methods return errors too. Exec is for writes. Query is for reads. Don't mix them up.
Prepare sends the query plan to the database. You get a *sql.Stmt. You call Exec or Query on the statement with parameters. Close the statement when you're done. Prepared statements save parsing overhead. They're useful for loops, but database/sql caches prepared statements internally, so you don't always need to prepare manually.
Context is plumbing. Run it through every long-lived call site. Pass context to every query. Respect cancellation. The database won't stop on its own.