Database access in Go: a practical guide for developers

Introduction

Database access in Go: a practical guide for developers. By day 26 of building production APIs, you've likely realized that how you handle database connections and queries can make or break your service's reliability and performance. Go's ecosystem offers several mature options, each with different trade-offs between simplicity, performance, and type safety.

In this guide, we'll walk through the practical realities of database access in Go production systems—covering the standard library's database/sql package, the powerful pgx driver for PostgreSQL, and sqlc for compile-time SQL verification. We'll focus on patterns that keep your code maintainable, your queries fast, and your error handling solid.

The database/sql package: foundation and limitations

Go's database/sql package is the standard abstraction layer for relational databases. It defines an interface that database drivers implement, allowing you to switch drivers without rewriting application code. For development teams building microservices, this abstraction layer has been reliable for over a decade.

However, database/sql alone has limitations. It doesn't provide connection pooling beyond basic idle connection management, no built-in support for advanced PostgreSQL features, and minimal help with query result mapping. You'll often find yourself writing repetitive code to scan rows into structs.

That's where specialized drivers like pgx come in. Rather than replacing database/sql entirely, think of it as the foundation that pgx builds upon.

pgx for Postgres: fast driver with pooling via pgxpool

If your team uses PostgreSQL—and statistically, many production services do—pgx offers substantial improvements over the pure database/sql approach. pgx is a native PostgreSQL driver written in Go that speaks the PostgreSQL protocol directly, avoiding the abstraction overhead of database/sql when you don't need it.

The killer feature for production APIs is pgxpool, pgx's connection pooling implementation. Unlike the generic pooling behavior of database/sql, pgxpool gives you explicit control over pool size, connection timeout, idle timeout, and health checking. For high-concurrency services, this control matters.

Here's a real-world setup for a REST API handling thousands of concurrent requests:

package database

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5/pgxpool"
)

type Pool struct {
	*pgxpool.Pool
}

func New(ctx context.Context, dsn string) (*Pool, error) {
	config, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		return nil, fmt.Errorf("parse config: %w", err)
	}

	// Production tuning
	config.MaxConns = 25
	config.MinConns = 5
	config.MaxConnLifetime = 5 * time.Minute
	config.MaxConnIdleTime = 1 * time.Minute
	config.HealthCheckPeriod = 30 * time.Second

	pool, err := pgxpool.NewWithConfig(ctx, config)
	if err != nil {
		return nil, fmt.Errorf("create pool: %w", err)
	}

	// Verify connection works
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}

	return &Pool{pool}, nil
}

func (p *Pool) Close() {
	p.Pool.Close()
}

This pattern gives you explicit pool management. Set MaxConns to 2–3 times the number of application threads (for goroutine-heavy apps, start conservative and monitor). The HealthCheckPeriod ensures that idle connections don't become stale—important when your database is behind a load balancer or has strict connection limits.

Using the pool in a query handler is straightforward:

func (p *Pool) GetUserByID(ctx context.Context, id string) (*User, error) {
	var user User
	err := p.QueryRow(ctx,
		"SELECT id, email, created_at FROM users WHERE id = $1",
		id,
	).Scan(&user.ID, &user.Email, &user.CreatedAt)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrUserNotFound
		}
		return nil, fmt.Errorf("query user: %w", err)
	}
	return &user, nil
}

Notice we're using QueryRow directly on the pool. pgx implements the standard rows interface, so the scanning logic is familiar. The real win is in how pgx handles prepared statements internally—it caches them per connection, reducing parse overhead on repeated queries.

sqlc: write SQL, generate type-safe Go code

Manually scanning rows into structs works, but it's error-prone. If you rename a column in your schema but forget to update the corresponding Go field, you won't discover the bug until runtime. This is where sqlc changes the game.

sqlc reads your SQL queries and your database schema, then generates type-safe Go code. You write plain SQL; sqlc generates the scanning boilerplate and ensures types match at compile time.

Here's the workflow. First, set up sqlc with a sqlc.yaml configuration:

version: "2"
sql:
  - engine: "postgresql"
    queries: "./queries"
    schema: "./schema"
    gen:
      go:
        package: "database"
        out: "./generated"

Write your schema in schema/001_init.sql:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Write your queries in queries/users.sql:

-- name: GetUserByID :one
SELECT id, email, created_at FROM users WHERE id = $1;

-- name: ListUserOrders :many
SELECT id, user_id, total, status, created_at
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC;

-- name: CreateUser :one
INSERT INTO users (email) VALUES ($1)
RETURNING id, email, created_at;

Run sqlc generate, and you get type-safe functions in Go:

type GetUserByIDRow struct {
	ID        uuid.UUID
	Email     string
	CreatedAt time.Time
}

func (q *Queries) GetUserByID(ctx context.Context, id uuid.UUID) (GetUserByIDRow, error) {
	// Generated implementation
}

type ListUserOrdersRow struct {
	ID        uuid.UUID
	UserID    uuid.UUID
	Total     decimal.Decimal
	Status    string
	CreatedAt time.Time
}

func (q *Queries) ListUserOrders(ctx context.Context, userID uuid.UUID) ([]ListUserOrdersRow, error) {
	// Generated implementation
}

You integrate sqlc-generated code with pgxpool like this:

type Store struct {
	db *database.Queries
}

func New(pool *pgxpool.Pool) *Store {
	return &Store{
		db: database.New(pool),
	}
}

func (s *Store) GetUserWithOrders(ctx context.Context, userID uuid.UUID) (*User, error) {
	user, err := s.db.GetUserByID(ctx, userID)
	if err != nil {
		return nil, err
	}

	orders, err := s.db.ListUserOrders(ctx, userID)
	if err != nil {
		return nil, err
	}

	return &User{
		ID:        user.ID,
		Email:     user.Email,
		CreatedAt: user.CreatedAt,
		Orders:    orders,
	}, nil
}

The safety gain is significant. Schema changes that break your queries are caught at generation time, not at 3 AM in production. Your team gets IDE autocomplete on column names because they're real Go struct fields. And you spend no time writing scanning boilerplate.

Always use context: QueryContext lets requests cancel queries

One of Go's greatest strengths is built-in support for timeouts and cancellation through the context package. Every database operation in production should respect context deadlines. This is non-negotiable when building APIs.

Consider a request handler that might execute multiple queries. If the client disconnects or the request times out, you want database queries to stop immediately rather than consuming resources:

func (h *Handler) ListOrders(w http.ResponseWriter, r *http.Request) {
	ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
	defer cancel()

	userID := chi.URLParam(r, "user_id")
	orders, err := h.store.ListUserOrders(ctx, userID)
	if err != nil {
		if errors.Is(err, context.DeadlineExceeded) {
			http.Error(w, "request timeout", http.StatusRequestTimeout)
			return
		}
		http.Error(w, "error fetching orders", http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(orders)
}

All your database methods should accept and use context:

func (s *Store) ListUserOrders(ctx context.Context, userID string) ([]Order, error) {
	// pgx methods respect context deadline
	rows, err := s.pool.Query(ctx,
		"SELECT id, user_id, total FROM orders WHERE user_id = $1",
		userID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var orders []Order
	for rows.Next() {
		var o Order
		if err := rows.Scan(&o.ID, &o.UserID, &o.Total); err != nil {
			return nil, err
		}
		orders = append(orders, o)
	}

	return orders, rows.Err()
}

pgx automatically cancels query execution if the context deadline is exceeded. This prevents orphaned queries from piling up on your database.

Close rows: defer rows.Close() and check rows.Err()

A subtle but critical pattern: always defer rows.Close() immediately after receiving a rows result, and always check rows.Err() after iteration completes.

Rows hold database connections from the pool. If you forget to close them, connections leak and your pool eventually exhausts. Here's the correct pattern:

func (s *Store) GetActiveUsers(ctx context.Context) ([]User, error) {
	rows, err := s.pool.Query(ctx, "SELECT id, email FROM users WHERE active = true")
	if err != nil {
		return nil, fmt.Errorf("query: %w", err)
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Email); err != nil {
			return nil, fmt.Errorf("scan: %w", err)
		}
		users = append(users, u)
	}

	// Critical: check for errors during iteration
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iteration: %w", err)
	}

	return users, nil
}

The rows.Err() call captures any errors that occurred during row iteration—things like I/O failures mid-result set. Without this check, silent failures are possible.

If you're using sqlc, this is handled for you. But when writing raw queries, it's easy to overlook. Make it muscle memory.

Common mistakes

• Not deferring rows.Close(): This is the #1 source of connection pool exhaustion in Go services. Every Query() must have a deferred Close().
• Ignoring context deadlines: Passing context.Background() to queries defeats the timeout system. Use the request context or explicit timeout contexts.
• Skipping rows.Err() checks: Iteration errors silently fail without this check, leading to incomplete result sets being treated as successful.
• Over-pooling connections: Setting MaxConns too high causes resource exhaustion on the database side. Start conservative (10–25 for most services) and monitor.
• Not handling pgx.ErrNoRows: It's a specific error type. Check for it when you expect at most one row, and distinguish it from other errors in your API responses.
• Reusing a closed pool: Once pool.Close() is called, new queries fail. Ensure graceful shutdown happens before the pool closes.

Key takeaways

• Use pgx and pgxpool for PostgreSQL services; it's faster, has better pooling, and supports advanced features like LISTEN/NOTIFY.
• sqlc generates type-safe database code and catches schema mismatches at compile time—a significant reliability improvement for teams managing schema migrations.
• Always pass context to database operations; it enables timeout enforcement and graceful cancellation when clients disconnect.
• Defer rows.Close() immediately after receiving a result set, and check rows.Err() after iteration—these two habits prevent connection leaks and silent failures.
• Configure pgxpool explicitly for your workload: set appropriate MaxConns, health check periods, and idle timeouts based on your concurrent request patterns.
• Handle specific errors like pgx.ErrNoRows intentionally in your API handlers; distinguish "not found" from "database error" for better HTTP status codes.