Go’s database/sql package provides a database-agnostic interface over any SQL driver. The package itself handles connection pooling, prepared statement caching, and concurrent access — you bring the driver. The same code works with PostgreSQL, MySQL, SQLite, and any other database that has a Go driver.
Understanding the package’s design choices — particularly connection pool configuration and how prepared statements interact with the pool — prevents a class of production problems that look like mysterious slowdowns or connection exhaustion.
For ORM-based access see Go GORM ORM. For query building see Go SQL query building.
Opening a Connection Pool
sql.Open doesn’t connect to the database — it just validates the driver name and DSN format, then returns a *sql.DB that manages a pool of connections. The actual connection happens lazily on the first query, or immediately if you call db.Ping():
import (
"database/sql"
_ "github.com/lib/pq" // PostgreSQL driver — blank import registers it
)
func openDB(dsn string) (*sql.DB, error) {
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, fmt.Errorf("sql.Open: %w", err)
}
// Verify the connection is actually reachable
if err := db.Ping(); err != nil {
db.Close()
return nil, fmt.Errorf("db.Ping: %w", err)
}
return db, nil
}
*sql.DB is safe for concurrent use and is designed to be shared. Create one instance at startup and pass it around — don’t create a new *sql.DB per request.
Connection Pool Configuration
The default pool is configured conservatively. For production services, tune it based on your database server’s connection limits and your application’s concurrency:
// SetMaxOpenConns: maximum total connections in the pool
// Set this to match your database's max_connections minus connections from other services
// Default: unlimited (dangerous — can exhaust database connections)
db.SetMaxOpenConns(25)
// SetMaxIdleConns: connections kept open in the idle pool
// Should be ≤ MaxOpenConns. Higher values reduce reconnection overhead
// Default: 2 (often too low for busy services)
db.SetMaxIdleConns(10)
// SetConnMaxLifetime: close and replace connections older than this
// Prevents stale connections, especially in environments that rotate credentials
// Default: unlimited
db.SetConnMaxLifetime(30 * time.Minute)
// SetConnMaxIdleTime: close connections idle longer than this
// Frees resources when traffic drops
// Default: unlimited
db.SetConnMaxIdleTime(10 * time.Minute)
To observe the pool in action, log db.Stats() periodically:
stats := db.Stats()
slog.Info("db pool",
slog.Int("open", stats.OpenConnections),
slog.Int("in_use", stats.InUse),
slog.Int("idle", stats.Idle),
slog.Int("wait_count", int(stats.WaitCount)),
slog.Duration("wait_duration", stats.WaitDuration),
)
WaitCount and WaitDuration are the key metrics — they show how often queries had to wait for a connection. If these are non-zero under normal load, increase MaxOpenConns or optimize query duration.
Querying
Single Row: QueryRowContext
For queries expected to return exactly one row, QueryRowContext is the ergonomic choice. It returns a *sql.Row that defers the error until Scan:
type User struct {
ID int
Name string
Email string
}
func getUser(ctx context.Context, db *sql.DB, id int) (*User, error) {
var u User
err := db.QueryRowContext(ctx,
"SELECT id, name, email FROM users WHERE id = $1", id,
).Scan(&u.ID, &u.Name, &u.Email)
if errors.Is(err, sql.ErrNoRows) {
return nil, fmt.Errorf("user %d: not found", id)
}
if err != nil {
return nil, fmt.Errorf("get user %d: %w", err)
}
return &u, nil
}
sql.ErrNoRows is not a database error — it means the query succeeded but returned no rows. Always check for it explicitly before returning the generic error.
Multiple Rows: QueryContext
func listUsers(ctx context.Context, db *sql.DB, minAge int) ([]User, error) {
rows, err := db.QueryContext(ctx,
"SELECT id, name, email FROM users WHERE age > $1 ORDER BY name", minAge,
)
if err != nil {
return nil, fmt.Errorf("list users: %w", err)
}
defer rows.Close() // must close even if you exit the loop early
var users []User
for rows.Next() {
var u User
if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
return nil, fmt.Errorf("scan user: %w", err)
}
users = append(users, u)
}
// rows.Err() captures errors that occurred during iteration
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("rows error: %w", err)
}
return users, nil
}
defer rows.Close() is essential. If you return early (e.g., due to a scan error) without closing, the connection stays checked out from the pool indefinitely.
rows.Err() after the loop catches errors from the database that interrupted iteration — network failures, server restarts. Always check it.
Inserts and Updates: ExecContext
func createUser(ctx context.Context, db *sql.DB, name, email string) (int64, error) {
result, err := db.ExecContext(ctx,
"INSERT INTO users (name, email) VALUES ($1, $2)", name, email,
)
if err != nil {
return 0, fmt.Errorf("create user: %w", err)
}
id, err := result.LastInsertId() // for MySQL; use RETURNING clause for PostgreSQL
if err != nil {
return 0, fmt.Errorf("last insert id: %w", err)
}
return id, nil
}
For PostgreSQL, use RETURNING in the query and QueryRowContext instead of ExecContext:
var id int64
err := db.QueryRowContext(ctx,
"INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
name, email,
).Scan(&id)
Prepared Statements
A prepared statement is compiled once by the database server and reused for multiple executions. This saves parsing overhead and is the correct way to parameterize queries — driver-level parameterization prevents SQL injection regardless of input content.
Create a prepared statement once and reuse it. For statements executed on every request, prepare them at startup:
type UserRepo struct {
db *sql.DB
getStmt *sql.Stmt
listStmt *sql.Stmt
}
func NewUserRepo(ctx context.Context, db *sql.DB) (*UserRepo, error) {
getStmt, err := db.PrepareContext(ctx,
"SELECT id, name, email FROM users WHERE id = $1")
if err != nil {
return nil, fmt.Errorf("prepare get: %w", err)
}
listStmt, err := db.PrepareContext(ctx,
"SELECT id, name, email FROM users WHERE age > $1 ORDER BY name LIMIT $2")
if err != nil {
getStmt.Close()
return nil, fmt.Errorf("prepare list: %w", err)
}
return &UserRepo{db: db, getStmt: getStmt, listStmt: listStmt}, nil
}
func (r *UserRepo) Get(ctx context.Context, id int) (*User, error) {
var u User
err := r.getStmt.QueryRowContext(ctx, id).Scan(&u.ID, &u.Name, &u.Email)
if errors.Is(err, sql.ErrNoRows) {
return nil, fmt.Errorf("user %d not found", id)
}
return &u, err
}
Note: prepared statements are associated with a specific database connection internally, but database/sql transparently re-prepares them if the connection is recycled. This is mostly transparent but means the first execution after a connection recycle has the prepare overhead.
Transactions
A transaction groups multiple operations into an atomic unit. Use db.BeginTx (context-aware) rather than db.Begin:
func transferFunds(ctx context.Context, db *sql.DB, fromID, toID int, amount int64) error {
tx, err := db.BeginTx(ctx, nil) // nil uses the default isolation level
if err != nil {
return fmt.Errorf("begin transaction: %w", err)
}
// If we return without committing, Rollback cleans up
// Rollback after a successful Commit is a no-op
defer tx.Rollback()
// Deduct from source — check balance first
var balance int64
if err := tx.QueryRowContext(ctx,
"SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", fromID,
).Scan(&balance); err != nil {
return fmt.Errorf("check balance: %w", err)
}
if balance < amount {
return fmt.Errorf("insufficient funds: have %d, need %d", balance, amount)
}
if _, err := tx.ExecContext(ctx,
"UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID,
); err != nil {
return fmt.Errorf("debit: %w", err)
}
if _, err := tx.ExecContext(ctx,
"UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID,
); err != nil {
return fmt.Errorf("credit: %w", err)
}
if err := tx.Commit(); err != nil {
return fmt.Errorf("commit: %w", err)
}
return nil
}
defer tx.Rollback() is the safe pattern — if any step fails and returns an error before Commit, the deferred Rollback cleans up. After Commit succeeds, calling Rollback on a committed transaction returns sql.ErrTxDone, which the defer discards.
FOR UPDATE locks the row for the duration of the transaction, preventing concurrent transfers from reading a stale balance.
SQL Injection Prevention
Always use parameterized queries — never concatenate user input into SQL strings:
// ✅ Safe: driver parameterizes the value
db.QueryRowContext(ctx, "SELECT * FROM users WHERE email = $1", email)
// ❌ SQL injection: user can pass "'; DROP TABLE users; --"
db.QueryRowContext(ctx, "SELECT * FROM users WHERE email = '"+email+"'")
Parameterization is not just about escaping — the database receives the query and the parameters as separate messages, so the parameter value is never interpreted as SQL syntax.
Summary
sql.Opendoesn’t connect — calldb.Ping()to verify the connection at startup- Configure
SetMaxOpenConns,SetMaxIdleConns, andSetConnMaxLifetime— defaults are wrong for production - Always
defer rows.Close()and always checkrows.Err()after the loop - Check
errors.Is(err, sql.ErrNoRows)explicitly — it’s not an error, it’s “not found” - Use
defer tx.Rollback()in transaction functions — it’s a no-op afterCommitsucceeds - Always parameterize queries — never concatenate user input into SQL
Resources
- database/sql package documentation
- Go database tutorial
- lib/pq PostgreSQL driver
- Go database/sql tutorial (jmoiron)
Comments