Query Rows Scan and Close in Go Without Leaking Connections

database/sql has a handful of idioms that are easy to get slightly wrong, and the one with the worst consequences is reading rows. The mistake is small, a missing Close on an early return, and the symptom is far away: the pool slowly runs out of connections, requests start timing out, and nothing in the logs points at the function that leaked. This article shows the leak with the pool's own counters, the correct loop, and the rules around it, on Go 1.25.6.

The leak

package main

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

	_ "modernc.org/sqlite"
)

func main() {
	db, err := sql.Open("sqlite", "file:lab582?mode=memory&cache=shared")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(1) // one connection makes a leak visible at once
	_, _ = db.Exec(`create table n (v int); insert into n values (1),(2),(3)`)

	// Leak: return early without closing rows.
	leaky := func() {
		rows, err := db.Query(`select v from n`)
		if err != nil {
			log.Fatal(err)
		}
		if rows.Next() {
			return // forgot rows.Close()
		}
	}
	leaky()
	fmt.Printf("after a leaked Rows: inUse=%d\n", db.Stats().InUse)

	ctx, cancel := context.WithTimeout(context.Background(), 200*time.Millisecond)
	defer cancel()
	var x int
	err = db.QueryRowContext(ctx, `select 1`).Scan(&x)
	fmt.Printf("next query with the pool exhausted: %v\n", err)

	// The corrected version, on a fresh pool.
	db2, _ := sql.Open("sqlite", "file:lab582b?mode=memory&cache=shared")
	defer db2.Close()
	db2.SetMaxOpenConns(1)
	_, _ = db2.Exec(`create table n (v int); insert into n values (1),(2),(3)`)
	demoFixed(db2)
}

leaky runs db.Query, calls rows.Next() once, and returns without closing. The code looks plausible. In a real function it would be an if that finds the row it wants and returns early, or an error path in the middle of the loop. The pool is set to a single connection so the effect is immediate; with twenty connections the same leak would need twenty leaks to show.

package main

import (
	"database/sql"
	"fmt"
)

func sumAll(db *sql.DB) (int, error) {
	rows, err := db.Query(`select v from n`)
	if err != nil {
		return 0, err
	}
	defer rows.Close() // runs on every return path
	total := 0
	for rows.Next() {
		var v int
		if err := rows.Scan(&v); err != nil {
			return 0, err
		}
		total += v
	}
	return total, rows.Err() // an error that ended the loop early shows up here
}

func demoFixed(db *sql.DB) {
	total, err := sumAll(db)
	fmt.Printf("fixed version: total=%d err=%v inUse=%d\n", total, err, db.Stats().InUse)
}

go run . 2>&1

after a leaked Rows: inUse=1
next query with the pool exhausted: context deadline exceeded
fixed version: total=6 err=<nil> inUse=0

The first line of output says after a leaked Rows: inUse=1. The connection is checked out and nobody will return it: the Rows value went out of scope, but it is not closed, and the garbage collector does not close it. With the only connection in use, the next query, run under a 200 ms deadline, produced:

next query with the pool exhausted: context deadline exceeded

The error mentions a deadline, not a connection, because the query was waiting for a connection to be free. Without the context deadline it would have waited forever. In production this is a service that handles requests fine for an hour and then goes quiet as each leaked query takes one more connection out of the pool.

The correct loop

sumAll in main2.go is the pattern to copy:

rows, err := db.Query(`select v from n`)
if err != nil {
	return 0, err
}
defer rows.Close()
for rows.Next() {
	var v int
	if err := rows.Scan(&v); err != nil {
		return 0, err
	}
	total += v
}
return total, rows.Err()

Four details, each load-bearing.

Check the error from Query before deferring Close. If Query fails, rows is nil and rows.Close() would panic. The defer goes after the if err != nil check.

defer rows.Close() runs on every return path. That is what fixes the leak: the early return in leaky would be safe with a defer, and so would a panic further down. Calling Close several times is allowed; the second call does nothing, so there is no need to avoid the defer because the loop also closes at the end.

Iterating to the end closes the rows. When rows.Next() returns false because there are no more rows, database/sql closes them. This is why the leak does not show in a loop that always runs to completion, and why code that was correct becomes a leak when someone adds a break or a return inside the loop.

rows.Err() after the loop. rows.Next() returns false both at the end of the data and when something went wrong: a dropped connection, a context that was cancelled, a decoding error in the driver. The loop cannot tell you which. rows.Err() returns the error that ended the iteration, or nil. Skipping it turns a truncated result into a silent success: a report computed from half the rows. The corrected program prints fixed version: total=6 err=<nil> inUse=0, where err is exactly that value.

Rules for the rest of the API

QueryRow closes for you. db.QueryRow(...).Scan(&x) reads one row and releases the connection, so there is nothing to close. If no row matches, Scan returns sql.ErrNoRows; handle it explicitly.

Exec is also self-contained, and returns a sql.Result with RowsAffected and, on some drivers, LastInsertId.

Stmt, Tx, Conn need their own end. Stmt.Close, Tx.Commit or Tx.Rollback, Conn.Close. The counters in the explainer show each holding a connection.

Do not keep rows open across slow work. Reading a row and then calling an API for each one holds the connection for the whole loop. Read all the rows into memory, close, then do the slow work; or process in batches.

Do not share a Rows between goroutines. It is a cursor tied to one connection and not safe for concurrent use.

Multiple result sets. With drivers that support them, rows.NextResultSet() moves on. The same close rule applies.

Finding a leak in a running service

The pool tells you: export db.Stats() and watch InUse. A healthy pool's InUse rises and falls with traffic. A leak is a floor that only rises. When you see it, find the callers:

1. Search for .Query( and .QueryContext( and check each has a defer rows.Close() directly after its error check. A linter helps: sqlclosecheck (a golangci-lint plugin) flags Rows and Stmt values that are not closed, and rowserrcheck flags missing rows.Err() calls. Both are described here and were not run.
2. In a test, wrap the database with a pool of one connection and run the code path twice. A leak makes the second call time out, as in the lab.
3. On PostgreSQL, pg_stat_activity shows connections in the state idle with a query that finished long ago, or idle in transaction for leaked transactions.
4. Give every request a deadline so the failure is a fast error in a log, not a hang.

The test in item 2 is cheap and catches the bug at the source. A helper newTestDB(t) that sets SetMaxOpenConns(1) makes every leak visible in the unit tests, and costs nothing when the code is correct.

What about sqlx and pgx

sqlx.Select and sqlx.Get read and close for you, and sqlx.Queryx returns a Rows that you must close like the standard one. pgx.CollectRows consumes the rows and closes them, so it cannot leak. With pgx's native API, rows.Close() is also required, and rows.Err() is the same check. Helpers that gather rows into a slice are a good way to remove the hand-written loop, as the struct scanning article shows.

The five lines to remember

Open, check, defer close, loop, check the iteration error. Five lines that never change. The Go documentation for sql.Rows states the closing behaviour, and the PostgreSQL view of connection states is the place to look when a leak is suspected. For the pool arithmetic that makes a leak urgent, see pool settings.