database/sql

A driver-agnostic layer

database/sql is the standard library’s SQL interface — it knows about pools, statements, rows, and transactions, but it does not know how to talk to any specific database. That job belongs to a third-party driver you import for its side effects (_ "github.com/jackc/pgx/v5/stdlib"), which registers itself by name. Your application code only ever touches database/sql types, so switching databases is mostly a one-line change.

graph LR
APP["your code<br/>Query · Exec · Scan"] --> SQL["database/sql<br/>(pool, rows, tx)"]
SQL --> DRV["driver<br/>(pgx, mysql, sqlite)"]
DRV --> POOL["connection pool"]
POOL --> DB[("database")]

sql.Open returns a pool, not a connection

This is the single most misunderstood fact about the package:

import (
	"database/sql"

	_ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver
)

// dsn is a placeholder — never commit real credentials
dsn := "postgres://user:pass@localhost:5432/app?sslmode=disable"

db, err := sql.Open("pgx", dsn)
if err != nil {
	log.Fatal(err) // only fires for a bad driver name / DSN syntax
}
defer db.Close()

// sql.Open did NOT connect. Verify connectivity explicitly:
if err := db.PingContext(ctx); err != nil {
	log.Fatal(err)
}

// Tune the pool for your workload:
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

*sql.DB is a pool of connections that is safe for concurrent use by many goroutines. Open it once at startup and pass it around (or inject it) — do not open a new *sql.DB per request. The pool opens, reuses, and retires real connections for you.

Query, scan, and always close

A single row with QueryRow, many rows with Query, and no rows (INSERT/UPDATE/DELETE) with Exec. Use the …Context variants so a cancelled request cancels the query too:

// One row: QueryRow + Scan. ErrNoRows means "not found".
var name string
var age int
err := db.QueryRowContext(ctx,
	"SELECT name, age FROM users WHERE id = $1", id).Scan(&name, &age)
if errors.Is(err, sql.ErrNoRows) {
	return ErrUserNotFound
} else if err != nil {
	return err
}

// Many rows: Query, then ALWAYS close and check rows.Err().
rows, err := db.QueryContext(ctx,
	"SELECT id, name FROM users WHERE age > $1 ORDER BY id", minAge)
if err != nil {
	return err
}
defer rows.Close() // returns the connection to the pool

var users []User
for rows.Next() {
	var u User
	if err := rows.Scan(&u.ID, &u.Name); err != nil {
		return err
	}
	users = append(users, u)
}
if err := rows.Err(); err != nil { // the loop can stop on an ERROR, not just EOF
	return err
}

// No rows back: Exec, then inspect the result.
res, err := db.ExecContext(ctx,
	"UPDATE users SET age = $1 WHERE id = $2", age, id)
if err != nil {
	return err
}
n, _ := res.RowsAffected() // 0 means the WHERE matched nothing

Each placeholder ($1, $2, … for Postgres; ? for MySQL/SQLite) is filled from the trailing arguments. The values travel separately from the SQL text, so a malicious string like '; DROP TABLE users; -- is stored as plain data, never executed.

NULLs and the sql.Null types

A SQL column that can be NULL won’t Scan into a plain string or int. Use the nullable wrappers — sql.NullString, sql.NullInt64, sql.NullTime, and friends (Go 1.22+ also has the generic sql.Null[T]):

var nickname sql.NullString
err := db.QueryRowContext(ctx,
	"SELECT nickname FROM users WHERE id = $1", id).Scan(&nickname)
if nickname.Valid {
	fmt.Println("nickname:", nickname.String)
} else {
	fmt.Println("no nickname set")
}

Reference

See also

• SQL transactions — grouping statements into all-or-nothing units.
• Postgres & Redis — picking a driver and a datastore.
• web security — parameterized queries as injection defense.
• project layout & DI — injecting the *sql.DB into your services.

Next: grouping statements that must all succeed or all fail — SQL Transactions.

Related topics