Go + Postgres without an ORM: sqlc and pgx

ORMs in Go abstract away Postgres — its types, its planner, its error codes, its transaction semantics. When they work, they save boilerplate. When they don't, you're debugging generated SQL you didn't write and can't easily change. sqlc and pgx give you type safety and generated code without hiding Postgres from you.

Why not an ORM

GORM and Ent are reasonable tools. The problem is what they hide. N+1 queries are invisible until you add logging. Complex joins become application-layer loops. Postgres-specific features (arrays, JSONB, RLS, full-text search) require escape hatches that are often worse than raw SQL anyway.

Once your queries are non-trivial, you end up with a mix of ORM queries and raw SQL — the worst of both. sqlc commits to raw SQL from the start.

The sqlc workflow

sqlc reads your SQL schema and your queries, then generates Go functions with correct input and output types. The workflow:

1. Write the migration SQL (see the gomigrate post).
2. Write queries in .sql files with sqlc annotations.
3. Run sqlc generate — get Go functions.
4. Call those functions from your stores.

-- query.sql
-- name: GetInvoice :one
SELECT id, customer_id, amount, status, created_at
FROM invoices
WHERE id = $1 AND deleted_at IS NULL;

-- name: ListInvoicesByCustomer :many
SELECT id, customer_id, amount, status, created_at
FROM invoices
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3;

sqlc generates GetInvoice(ctx, id) and ListInvoicesByCustomer(ctx, params) with correct Go types — no casting, no interface{}, no runtime surprises.

pgx and pgxpool setup

Use pgxpool, not database/sql. pgxpool is Postgres-native: it understands Postgres types, supports pgx-specific features, and manages the connection pool correctly.

func NewPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
  cfg, err := pgxpool.ParseConfig(dsn)
  if err != nil {
    return nil, err
  }
  cfg.MaxConns = 20
  cfg.MinConns = 2
  cfg.MaxConnLifetime = 30 * time.Minute
  cfg.HealthCheckPeriod = 1 * time.Minute
  return pgxpool.NewWithConfig(ctx, cfg)
}

Error handling with pgx

pgx wraps Postgres error codes. Use them instead of string-matching error messages — they're stable across Postgres versions.

import "github.com/jackc/pgx/v5/pgconn"

func isUniqueViolation(err error) bool {
  var pgErr *pgconn.PgError
  return errors.As(err, &pgErr) && pgErr.Code == "23505"
}

// In your store:
if err := store.CreateUser(ctx, email, hash); err != nil {
  if isUniqueViolation(err) {
    return nil, ErrEmailTaken
  }
  return nil, err
}

Transactions

pgx transactions are explicit. sqlc can generate transactional versions of your queries, or you can wrap calls manually.

func (s *Store) CreateInvoiceWithItems(ctx context.Context, ...) error {
  tx, err := s.db.Begin(ctx)
  if err != nil {
    return err
  }
  defer tx.Rollback(ctx) // no-op after Commit

  q := s.queries.WithTx(tx)
  if _, err := q.CreateInvoice(ctx, ...); err != nil {
    return err
  }
  if err := q.InsertItems(ctx, ...); err != nil {
    return err
  }
  return tx.Commit(ctx)
}

The defer tx.Rollbackpattern is idiomatic Go: it's a no-op after a successful commit, and it ensures cleanup on any error path without explicit rollback calls everywhere.