Working with Databases in Go

Most Go services I have worked on spend more time waiting on a database than doing anything clever in memory. That makes the database layer one of the first places where small mistakes become production incidents.

This is the setup and the habits I keep coming back to with database/sql and pgx.

database/sql is a pool, not a connection

This still catches people:

db, err := sql.Open("pgx", dsn)

Open does not guarantee a live connection. It prepares a pool. The first real check is:

if err := db.PingContext(ctx); err != nil {
	return err
}

Then you configure the pool. Defaults are rarely what you want.

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

A few rules of thumb:

• MaxOpenConns should stay well below the database’s max connections, especially if you run many replicas of the service.
• Too few connections and requests queue on the pool. Too many and you just move the queue to Postgres.
• ConnMaxLifetime helps you recycle connections before a load balancer or failover leaves you with a dead one.

If you are on Kubernetes with 10 pods and MaxOpenConns=50, that is 500 potential connections. Do the math before copying a snippet from a blog post.

Always pass context

rows, err := db.QueryContext(ctx, query, args...)

Use QueryContext, ExecContext, QueryRowContext. The request context is how timeouts and cancellations actually reach the database driver.

If a client disconnects and you keep running a heavy query, you are paying for work nobody will see. Worse, those queries keep occupying pool connections.

I also set a statement timeout at the database or on the connection. Application timeouts and database timeouts should not be far apart.

Scanning without making a mess

The naive version:

row := db.QueryRowContext(ctx, `SELECT id, email FROM users WHERE id = $1`, id)

var u User
if err := row.Scan(&u.ID, &u.Email); err != nil {
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrUserNotFound
	}
	return nil, err
}

Map sql.ErrNoRows to a domain error. Do not leak driver errors up to the HTTP layer as 500s when the record is simply missing.

For nullable columns, sql.NullString and friends work, but they make the code noisy. I prefer a thin scan helper or a type that already matches the schema. What I avoid is scanning into *string everywhere just to dodge null handling, then forgetting which fields can actually be null.

When a query returns many rows:

defer rows.Close()
for rows.Next() {
	// scan
}
if err := rows.Err(); err != nil {
	return nil, err
}

Forgetting rows.Err() and rows.Close() is a classic way to leak pool connections. defer rows.Close() immediately after the query succeeds.

Transactions

The pattern I use:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer tx.Rollback() // no-op after a successful Commit

if _, err := tx.ExecContext(ctx, q1, a1); err != nil {
	return err
}
if _, err := tx.ExecContext(ctx, q2, a2); err != nil {
	return err
}

return tx.Commit()

defer tx.Rollback() is the part people skip. If you return early after BeginTx, that connection stays busy until something else cleans it up.

A few extra things that matter:

• Do not hold a transaction open while you call another service or do slow CPU work.
• If you need a specific isolation level, set it explicitly. Do not assume the default is the one you thought it was.
• Pass the tx down, not the *sql.DB. Mixing them in the same use case is how you get half-committed state.

pgx vs database/sql

I still use database/sql when I want portability or a codebase that already depends on it. For PostgreSQL-heavy services, I prefer pgx directly.

What I actually gain from pgx:

• better control over the pool (pgxpool)
• native support for more Postgres types
• less overhead on the hot path
• clearer behavior around prepared statements and simple protocol

A typical pool setup:

cfg, err := pgxpool.ParseConfig(dsn)
if err != nil {
	return nil, err
}
cfg.MaxConns = 25
cfg.MinConns = 5
cfg.MaxConnLifetime = 30 * time.Minute
cfg.MaxConnIdleTime = 5 * time.Minute

pool, err := pgxpool.NewWithConfig(ctx, cfg)

pgx errors are different from sql.ErrNoRows. Use errors.Is(err, pgx.ErrNoRows).

If you need database/sql compatibility, github.com/jackc/pgx/v5/stdlib is the adapter. It is a reasonable compromise, not the same as using pgx natively.

Prepared statements and query strings

For hot queries, prepared statements help. With database/sql the driver may already cache them per connection. With pgx you can be more explicit.

What I care about more than micro-optimizing prepares:

• keep SQL in named constants or small files, not assembled with fmt.Sprintf
• never interpolate user input into the query string
• if the query is dynamic (optional filters), build the argument list carefully and keep placeholders consistent

Dynamic SQL is where I have seen the most bugs. A helper that returns (query string, args []any) is fine. A helper that concatenates strings from request params is not.

N+1 and “just one more query”

ORMs make this easy to miss. Raw SQL makes it easier to see, but you can still do it by hand:

for _, id := range ids {
	user, _ := repo.FindByID(ctx, id)
	// ...
}

If you already have the IDs, fetch them in one query. Same for associations. I would rather write an extra WHERE id = ANY($1) than hide 200 queries behind a clean-looking loop.

When I use GORM, I still look at the query log in development. The API can look fine while the generated SQL is not.

Timeouts, retries, and what not to retry

Retry a query after a serialization failure or a brief network blip. Do not blindly retry an INSERT unless it is idempotent.

Context timeout plus a retry without backoff is a good way to amplify load when the database is already unhappy. If the pool is exhausted, retrying immediately makes it worse.

What I check when a service is “slow”

1. Pool wait time vs query time. If you are waiting for a connection, it is not the SQL.
2. Slow query log / pg_stat_statements.
3. Missing indexes, obviously, but also indexes that do not match the actual filter.
4. Transactions that are too wide.
5. Queries running after the request context was already canceled.

The application metrics I want on the pool:

• open connections
• idle connections
• wait count / wait duration
• query latency by statement name

Without those, people guess.

Closing

The database layer does not need to be fancy. It needs to be boring and explicit: bounded pools, context on every call, transactions that are short, errors that mean something, and queries you can actually read.

database/sql is enough for a lot of services. pgx is worth it when Postgres is the center of the system. Either way, the failures I see in production are almost never about the choice of library. They are about connections, timeouts, and queries that only look cheap in development.