How database sql Works in Go and What Each Type Is For

The database/sql package is the standard way Go talks to a relational database, and nearly everything built on it, from sqlx to sqlc to ORMs, relies on the same few types. Misunderstanding what each one is produces the classic production problems: leaked connections, a pool that stops answering, a transaction that holds a lock for an hour. This article walks through the types in the order a request meets them, using the pool's own counters as evidence, on Go 1.25.6.

It sits under the database cornerstone. The next articles take the parts one at a time: connecting to PostgreSQL, pool settings and closing rows.

The types, in one table

The driver sits below all of this. sql.Open("pgx", dsn) looks the name up in the registry that the driver filled in its init function, which is why drivers are imported for side effects with a blank identifier. The driver implements a small set of interfaces and database/sql supplies the pooling, the retries on bad connections, the context handling and the type conversions on top.

Watching the pool

package main

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

	_ "modernc.org/sqlite"
)

func stats(label string, db *sql.DB) {
	s := db.Stats()
	fmt.Printf("%-34s open=%d inUse=%d idle=%d\n", label, s.OpenConnections, s.InUse, s.Idle)
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("sqlite", "file:lab575.db?mode=memory&cache=shared")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	stats("after sql.Open (nothing dialled)", db)

	if err := db.PingContext(ctx); err != nil {
		log.Fatal(err)
	}
	stats("after Ping", db)

	if _, err := db.ExecContext(ctx, `CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)`); err != nil {
		log.Fatal(err)
	}
	stats("after Exec (connection returned)", db)

	// Query holds a connection until Rows is closed.
	rows, err := db.QueryContext(ctx, `SELECT 1 UNION SELECT 2`)
	if err != nil {
		log.Fatal(err)
	}
	stats("Query open, Rows not closed", db)
	rows.Close()
	stats("after Rows.Close", db)

	// A transaction pins one connection until Commit or Rollback.
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatal(err)
	}
	stats("inside a transaction", db)
	_ = tx.Rollback()
	stats("after Rollback", db)

	// Conn pins a connection for several statements (session state such as pragmas).
	c, err := db.Conn(ctx)
	if err != nil {
		log.Fatal(err)
	}
	stats("with a pinned Conn", db)
	c.Close()
	stats("after Conn.Close", db)

	// QueryRow closes itself when Scan returns.
	var n int
	if err := db.QueryRowContext(ctx, `SELECT 42`).Scan(&n); err != nil {
		log.Fatal(err)
	}
	stats("after QueryRow.Scan", db)

	// A prepared statement is bound to the pool, not to one connection.
	st, err := db.PrepareContext(ctx, `SELECT ?+1`)
	if err != nil {
		log.Fatal(err)
	}
	_ = st.QueryRowContext(ctx, 1).Scan(&n)
	st.Close()
	fmt.Printf("prepared statement result: %d\n", n)

	db.SetMaxOpenConns(1)
	s := db.Stats()
	fmt.Printf("MaxOpenConnections now %d\n", s.MaxOpenConnections)
}

after sql.Open (nothing dialled)   open=0 inUse=0 idle=0
after Ping                         open=1 inUse=0 idle=1
after Exec (connection returned)   open=1 inUse=0 idle=1
Query open, Rows not closed        open=1 inUse=1 idle=0
after Rows.Close                   open=1 inUse=0 idle=1
inside a transaction               open=1 inUse=1 idle=0
after Rollback                     open=1 inUse=0 idle=1
with a pinned Conn                 open=1 inUse=1 idle=0
after Conn.Close                   open=1 inUse=0 idle=1
after QueryRow.Scan                open=1 inUse=0 idle=1
prepared statement result: 2
MaxOpenConnections now 1

The program prints db.Stats() after each step. OpenConnections is how many connections exist, InUse is how many are checked out, and Idle is how many are waiting in the pool. Read the output in order.

sql.Open does not connect. The first line shows all zeros. Open validates its arguments and returns a *sql.DB. A wrong password or a closed port is not an error yet. That is why servers call PingContext at start-up: it forces the first connection and turns a misconfiguration into an error at boot instead of at the first request.

A finished call returns the connection. After Ping the pool has one open connection and it is idle. Exec borrows it and gives it back in the same call, so open=1 inUse=0 idle=1 again. For Exec, QueryRow(...).Scan and Prepare with a one-shot execution, there is nothing to release.

Query holds a connection until Rows is closed. With a Rows open the counters read inUse=1 idle=0. The connection is streaming results, so nothing else can use it. Rows.Close returns it. Iterating to the end with rows.Next closes the rows automatically, but an early return or break leaves it open, which is the leak behind most "the pool is exhausted" incidents; the whole story is in closing rows.

A transaction pins a connection. Between BeginTx and Commit or Rollback, inUse=1. Every statement in the transaction must go through the Tx so they share that connection, and a transaction held open across a slow operation, such as an HTTP call, keeps both the connection and any locks. Transactions are the topic of the transaction article.

Conn pins a connection for session state. db.Conn(ctx) gives you one connection for as long as you hold it. Use it when you need several statements on the same session without a transaction: SET commands, advisory locks, temporary tables. The cost is the same as a transaction: a connection that is not available to others. It must be closed.

QueryRow closes itself. After Scan the connection is idle again, because Scan closes the underlying rows. QueryRow never returns an error of its own; a failure appears from Scan, including sql.ErrNoRows when nothing matched.

A prepared statement is bound to the pool. PrepareContext returns a *sql.Stmt that is not tied to one connection: when you run it, the package prepares it on the connection it picked if needed, and remembers. The program prints the result of SELECT ?+1 through one. Whether preparing helps at all is measured in prepared statements.

What the pool does for you

The package keeps up to MaxOpenConns connections, defaults to unlimited, and up to MaxIdleConns idle ones, defaults to two. When all are in use and the limit is reached, a caller waits for a free one until its context ends. A connection older than ConnMaxLifetime, or idle longer than ConnMaxIdleTime, is closed and replaced. If a driver reports driver.ErrBadConn, the package discards the connection and retries on a fresh one, up to a few times, but only for operations it can safely repeat.

The last line of the program sets SetMaxOpenConns(1) and reads MaxOpenConnections back as 1. That setting, with the others, is the subject of pool settings, where the numbers are measured against a real PostgreSQL.

Rules that follow from the model

1. Create one *sql.DB per database and share it. Do not open and close one per request; each Open creates a new pool.
2. Close what you open. Rows, Stmt, Conn and Tx each need defer x.Close() or an explicit end. The counters make forgotten ones visible.
3. Pass a context to every call. The Context variants let a timeout or a cancelled request stop the query and release the connection; see query timeouts.
4. Keep transactions short and do nothing slow inside them.
5. Do not rely on connection state. Two calls on a *sql.DB may use different connections, so a SET on one is not visible on the next. Use a Conn or a Tx when state matters.
6. Export Stats() as metrics. InUse, WaitCount and WaitDuration are the early warning of a pool that is too small or a leak.

Limits of this lab

The lab uses an in-memory SQLite database with a shared cache so that the pool has something to connect to. SQLite has its own concurrency rules, which the SQLite article covers, and the pool behaviours shown here are the same for PostgreSQL and MySQL. The package is documented in the database/sql reference, and the Go team's guide to accessing a database is the official walkthrough.