Data Access in Go: database/sql as the Foundation

Go keeps database access explicit.

The standard library database/sql package defines how applications talk to SQL databases, and separate drivers (Postgres, MySQL, SQLite, and others) implement the actual protocol.

ORMs like GORM and helpers like sqlx sit on top of that same foundation - they do not replace the pool, transactions, or context-aware APIs.

Databases Basics collects runnable snippets; sibling articles cover pooling, transactions, migrations, caching, and team conventions.

Summary

• database/sql is a small, driver-agnostic API for connection pooling, queries, transactions, and prepared statements. Your code imports a driver with a blank import and calls sql.Open.
• Insight: Staying close to database/sql keeps you portable across drivers, testable with fakes, and aligned with how Go HTTP and gRPC stacks propagate context.Context into storage calls.
• Key Concepts: driver, DB pool, Rows, Scan, Tx, Stmt, QueryContext, repository pattern.
• When to Use: Default for services that speak SQL. Reach for an ORM when migrations, associations, and codegen dominate; reach for raw SQL when queries are hand-tuned and team SQL skill is high.
• Limitations/Trade-offs: No query builder in stdlib; sql.Null* types are verbose; driver behavior differs for cancellation and prepared statement caching; N+1 risks move to ORM layers if you are not careful.
• Related Topics: context deadlines, connection pool tuning, golang-migrate, embed for migrations, Redis cache-aside, observability for slow queries.

Foundations

Picture your service as layers: HTTP handler, domain service, storage repository, then *sql.DB.

The handler parses the request and passes r.Context() down.

The repository owns SQL strings (or generated SQL) and maps rows to domain types.

*sql.DB is a connection pool, not a single socket.

sql.Open("postgres", dsn) registers the driver name and returns a pool handle.

The first real connection often appears on Ping, Query, or Exec.

Drivers live in separate modules:

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib" // Postgres
)

The blank import runs the driver's init() so sql.Open can resolve "pgx".

Swapping databases means swapping the driver import and DSN, not rewriting application types if repositories hide SQL details.

Mechanics & Interactions

The idiomatic read path:

func GetUser(ctx context.Context, db *sql.DB, id int64) (User, error) {
    var u User
    err := db.QueryRowContext(ctx,
        `SELECT id, email FROM users WHERE id = $1`, id,
    ).Scan(&u.ID, &u.Email)
    if errors.Is(err, sql.ErrNoRows) {
        return User{}, ErrNotFound
    }
    return u, err
}

QueryRowContext expects at most one row.

Scan copies column values into Go variables and reports type mismatches immediately.

Multi-row reads use QueryContext, iterate rows.Next(), and always rows.Close().

Writes use ExecContext; batch jobs may use transactions via BeginTx.

Frameworks (chi, gin, echo) do not change the storage contract: repositories still accept ctx and *sql.DB (or an interface wrapping them).

google.golang.org/grpc services pass the RPC context into repository methods the same way HTTP handlers do.

Advanced Considerations & Applications

Production services combine pool settings with per-query context:

• SetMaxOpenConns caps simultaneous connections to the database.
• SetMaxIdleConns keeps warm connections for bursty traffic.
• SetConnMaxLifetime rotates connections behind load balancers.
• QueryContext ties work to client disconnect and service deadlines.

ORMs (GORM) generate SQL and manage associations; sqlx adds struct scanning helpers while staying close to raw SQL.

Teams often choose:

Observability belongs at the repository boundary: log query name, latency, and ctx deadline remaining; expose pool stats (db.Stats()) to metrics.

controller-runtime reconcilers should pass reconcile context into repository calls so database work stops when the manager shuts down.

Common Misconceptions

• "sql.Open connects immediately" - It allocates a pool; errors often appear on first use. Always PingContext at startup.
• "ORMs replace database/sql" - They wrap it. You still need pool tuning and driver upgrades.
• "One global *sql.DB is an anti-pattern" - A single pool per process is normal; the anti-pattern is hidden init() wiring without tests.
• "Context is optional for fast queries" - Client cancel should stop work even for millisecond queries under load.
• "Repositories must return sql.Rows" - Return domain types or slices; keep Rows inside the repository.
• "Prepared statements are always faster" - Depends on driver caching and query shape; measure before blanket Prepare.

FAQs

Related

• Databases Basics - runnable Open, Query, Scan examples
• database/sql Connection Pooling & Timeouts - pool and deadline tuning
• Transactions, Prepared Statements & sql.Tx - Tx boundaries
• GORM vs sqlx vs Raw database/sql - stack comparison
• context in database/sql & gRPC - cancellation integration
• Databases & Data Access Best Practices - team conventions

Stack versions: This page was written for Go 1.26.x (Green Tea GC default, go fix modernizers - verify patch at build), chi (latest - verify at build), gin (latest - verify at build), echo (latest - verify at build), google.golang.org/grpc (latest - verify at build), sigs.k8s.io/controller-runtime (latest - verify at build), kubebuilder (latest - verify at build), tinygo (latest - verify board targets at build), wazero (latest - verify at build), and golangci-lint (latest - verify linter set at build).