Relational databases from Go

Overview

Relational access in Go starts from database/sql, a stdlib interface with a built-in pool that any driver can register into — the closest thing Go has to JDBC. Unlike the JVM, the layers above it never consolidated into a standard: the ecosystem settled into a spectrum from raw driver through struct-scanning helpers (sqlx) and SQL-first code generation (sqlc) to full ORMs (GORM, ent), with SQL-first firmly the community's centre of gravity. For PostgreSQL specifically, pgx is the de-facto driver and increasingly used natively, bypassing database/sql altogether.

Key points

• database/sql: sql.DB is a pooled handle, not a connection — safe for concurrent use; tune with SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime/IdleTime. Its cost is the lowest common denominator: any-based scanning, no access to driver-specific features.
• pgx (jackc/pgx v5) is the Postgres default: native wire protocol, binary encoding for Postgres types, SendBatch for pipelined batches, LISTEN/NOTIFY, CopyFrom for bulk loads, and pgxpool as its own pool. Use it natively for full power, or through the pgx/v5/stdlib shim when a library insists on *sql.DB.
• sqlc is the community darling: write real SQL in .sql files, run sqlc generate, get typed Go functions and structs. Compile-time-checked queries, zero runtime reflection, no DSL to learn — the inverse of an ORM.
• sqlx extends database/sql in place: Get/Select scan rows into structs via db tags, named parameters, StructScan. Minimal buy-in, good retrofit path.
• GORM is the full ORM: convention-based mapping, associations, hooks, auto-migration. Trade-offs: reflection-heavy, chatty SQL unless watched, interface{}-typed APIs that defer errors to runtime — the standard critique in a statically-typed culture.
• ent (from Meta) is schema-as-code: define the schema as Go code, generate a typed, graph-traversal-flavoured client. Heavier codegen than sqlc, stronger typing than GORM.
• Builders: squirrel and goqu compose SQL programmatically — the right tool for genuinely dynamic filters, not a general replacement for written SQL.
• Migrations live in dedicated tools: golang-migrate (versioned up/down SQL, CLI or library), goose (SQL or Go migrations, simpler), atlas (declarative diffing, powers ent).
• Transactions are explicit: no @Transactional — you begin a tx, pass it down, and commit/rollback yourself. The discipline is API design, not annotations.

Details

The spectrum, one axis

Rule of thumb: greenfield Postgres service → pgx + sqlc; retrofitting typed scans onto existing database/sql code → sqlx; genuinely dynamic search filters → add squirrel for those queries; large CRUD surface with a team that wants an ORM → ent before GORM.

database/sql mechanics worth knowing

• Prepared statements are managed per-connection under the hood; a Query on a busy pool may re-prepare on another connection — pgx native avoids this with its statement cache.
• QueryRow(...).Scan(...) defers errors to Scan, including sql.ErrNoRows — check for it explicitly; it is the idiomatic "not found".
• Nullable columns need sql.NullString/Null[T] (generic since Go 1.22) or pointers — there is no entity lifecycle to absorb nulls for you.

Transactions without magic

The dominant patterns for tx plumbing, in rising order of ceremony:

1. Pass pgx.Tx/*sql.Tx explicitly to every repository function that needs it.
2. Interface trick: repositories accept a Querier interface satisfied by both the pool and a tx (sqlc generates exactly this), so the same code runs in or out of a transaction.
3. Tx-runner closure: store.WithTx(ctx, func(q *Queries) error { ... }) — begin, defer-rollback, commit on nil error; retry wrapper for serialization failures goes here.

Context cancellation aborts in-flight queries and rolls back — plumb ctx end to end.

The contrast with JPA's managed-entity world (relational databases from Java) is total: no persistence context, no dirty checking, no lazy proxies — and therefore no N+1-by-accident or LazyInitializationException class of bugs. You write the joins; you also get to forget one.

Examples

// pgx + sqlc-style Querier interface, explicit transaction
func (s *Store) TransferFunds(ctx context.Context, from, to int64, amount int64) error {
    tx, err := s.pool.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx) // no-op after successful Commit

    q := s.queries.WithTx(tx)
    if err := q.Debit(ctx, DebitParams{AccountID: from, Amount: amount}); err != nil {
        return err
    }
    if err := q.Credit(ctx, CreditParams{AccountID: to, Amount: amount}); err != nil {
        return err
    }
    return tx.Commit(ctx)
}

-- sqlc input: queries.sql
-- name: GetOrder :one
SELECT id, customer_id, status FROM orders WHERE id = $1;

-- name: ListOpenOrders :many
SELECT id, customer_id, status FROM orders WHERE status = 'open' ORDER BY id;

Related

• Storage access from Go — the map — parent overview by storage kind.
• Databases and other storage systems — engine internals, indexing, and isolation beneath the driver.
• Integration testing — Testcontainers against a real Postgres is how this stack is tested.
• Relational databases from Java — the managed-entity contrast: JDBC/JPA where Go passes explicit tx values.