sqlc + pgx over an orm: type-safe sql that fails at compile time

the endpoint was slow and i couldn’t tell you which query to blame, because i hadn’t written any. that’s the deal you sign with a reflection-based orm: you describe some structs, you call db.Preload("Photos").Find(&pets), and somewhere behind the curtain the library assembles sql you never see, fires off a number of round trips you never counted, and hands you back objects. when it’s fast, it’s lovely. when it’s slow, you’re debugging a library’s imagination.

i built the backend for a pet-adoption side project last year — go 1.25, postgres 16 with postgis, a flutter app riding on top — and i made one rule early: every query is sql i wrote, in a file i can open. no orm. the punchline, which i only found months later during a cleanup, is that i’d been keeping that rule by accident even harder than i thought. there was an orm sitting in go.mod the entire time. it had never run a single query.

the orm that was literally dead code

when i audited the module before cutting a release, i grepped for the orm’s import across every active repository. zero hits. it was in the dependency graph, it compiled, it downloaded on every go mod download in ci — and nothing called it. two leftover files imported it; nothing imported them. it was scaffolding from an afternoon i’d since walked away from.

so i wrote it down, because decisions you don’t record you get to re-litigate at 2am six months later:

docs/architecture/adrs/001-sqlc-over-gorm.md

## Context
The codebase has both GORM (in go.mod) and sqlc/pgx (actually used).
The audit confirmed GORM is dead code — only sqlc/pgx is used in all
active repositories.

## Decision
Standardize on sqlc + pgx. Remove GORM entirely.

## Rationale
- sqlc generates type-safe Go code from SQL — catches query errors at compile time
- pgx is the most performant PostgreSQL driver for Go (native protocol, no database/sql overhead)
- GORM's reflection-based approach is slower and hides query complexity
- sqlc's explicit SQL is easier to audit for security and performance

“formalizing reality,” i wrote in the consequences section, which is the politest way i could find to say the orm had been decorative. an earlier project of mine — the one from the hexagonal architecture post — leaned on gorm properly, many2many tags and all, and it was fine for what it was. but that project also had a domain struct, a persistence struct, and a pile of mappers specifically to keep the orm’s tags out of the business logic. here i skipped the middleman. the sql is the interface.

sqlc is not an orm, it’s a compiler

the pitch for sqlc ↗ is one sentence: it “generates fully type-safe idiomatic Go code from SQL.” you write the query. it reads your schema, type-checks the query against it, and emits the go — the params struct, the row struct, the scan. you don’t write the plumbing and you don’t get a runtime query builder. the sql is the source of truth and go is the output.

here’s the whole config:

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "api/internal/infrastructure/database/queries"
    schema: "api/internal/infrastructure/database/migrations"
    gen:
      go:
        package: "sqlc"
        out: "api/internal/infrastructure/database/sqlc"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_interface: true
        emit_empty_slices: true
        emit_pointers_for_null_types: true
        overrides:
          - db_type: "geography"
            go_type: "string"
            nullable: true
          - db_type: "geometry"
            go_type: "string"
            nullable: true

two lines earn their keep here. sql_package: "pgx/v5" tells sqlc to generate against pgx’s native api instead of database/sql — we’ll get to why that matters. and emit_interface: true, which outputs a Querier interface ↗ alongside the concrete type. hold that thought too.

the overrides block is the postgis tax, paid up front. postgres has no idea how to hand a geography column to go, and neither does sqlc out of the box, so you override the type mapping ↗ — here, treat geography as an opaque string (the WKT/EWKT text) and let the query do the spatial math in sql. note it’s declared nullable: true; a db_type override applies to nullable or non-nullable columns but not both, so if you wanted it either way you’d write it twice. small gotcha, cost me twenty minutes once.

a query is just a file

here’s about the simplest query in the codebase — how many adoptable pets exist per species:

internal/infrastructure/database/queries/pets.sql

-- name: CountAvailablePetsBySpecies :many
SELECT
    species,
    COUNT(*) as count
FROM pets
WHERE deleted_at IS NULL AND status = 'available'
GROUP BY species
ORDER BY count DESC;

the magic comment is the whole api. -- name: CountAvailablePetsBySpecies :many says: make a method with this name that returns a slice. :one returns a single row, :exec returns just an error. run sqlc generate and you get:

internal/infrastructure/database/sqlc/pets.sql.go

type CountAvailablePetsBySpeciesRow struct {
	Species string `json:"species"`
	Count   int64  `json:"count"`
}

func (q *Queries) CountAvailablePetsBySpecies(ctx context.Context) ([]CountAvailablePetsBySpeciesRow, error) {
	rows, err := q.db.Query(ctx, countAvailablePetsBySpecies)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []CountAvailablePetsBySpeciesRow{}
	for rows.Next() {
		var i CountAvailablePetsBySpeciesRow
		if err := rows.Scan(&i.Species, &i.Count); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	// ...
	return items, nil
}

look at the row struct. Count int64, because sqlc knows COUNT(*) in postgres is a bigint. the species alias becomes Species. that items := []...{} instead of a nil slice is emit_empty_slices doing its job, so “no pets” serializes to [] and not null. every field is a type the schema guarantees, not one i asserted and hoped for.

now the part in the title. rename that sql column from count to total and regenerate — the field becomes Total, and every .Count reader across the codebase stops compiling. type a column that isn’t in your schema and you never even reach the go compiler: sqlc generate fails right there, having checked your sql against your migrations. two gates, both before runtime. the class of bug where a migration renames a column and a string-concatenated query keeps compiling and blows up in production at 200 rps — that bug cannot exist here. it’s not that i’m careful. it’s that the door is locked.

pgx, and why the native protocol matters

sql_package: "pgx/v5" changes what the generated code talks to. instead of the lowest-common-denominator database/sql, sqlc targets pgx ↗ directly. you can see it in the tiny interface every generated method is written against:

internal/infrastructure/database/sqlc/db.go

type DBTX interface {
	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
	QueryRow(context.Context, string, ...interface{}) pgx.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

those are pgx types — pgconn.CommandTag, pgx.Rows — not database/sql. pgx speaks the postgres wire protocol natively ↗ and skips the database/sql abstraction layer, which buys you real binary encoding for types like numeric and timestamptz and a connection pool that isn’t fighting a generic interface. you wire the pool once at boot:

cmd/server/main.go

poolConfig, err := pgxpool.ParseConfig(cfg.DatabaseURL)
if err != nil {
	log.Fatalf("Failed to parse database URL: %v", err)
}
poolConfig.MaxConns = 25
poolConfig.MinConns = 5
poolConfig.MaxConnLifetime = 60 * time.Minute

dbPool, err := pgxpool.NewWithConfig(ctx, poolConfig)
// ...

and hand that pool straight to sqlc, because *pgxpool.Pool satisfies DBTX:

cmd/server/deps.go

queries := sqlc.New(dbPool)

petRepo := repositories.NewSqlcPetRepository(dbPool)
matchApprovalRepo := repositories.NewSqlcMatchApprovalRepository(queries)
// ...

no orm session, no AutoMigrate, no lifecycle to babysit. a pool, and a struct of methods that hold a reference to it.

the interface you get for free

remember emit_interface? it generates a Querier interface listing every method, so your code can depend on the behaviour instead of the concrete *Queries:

internal/infrastructure/database/sqlc/querier.go

type Querier interface {
	CountAvailablePetsBySpecies(ctx context.Context) ([]CountAvailablePetsBySpeciesRow, error)
	DiscoverPets(ctx context.Context, arg DiscoverPetsParams) ([]DiscoverPetsRow, error)
	// Approve a match approval (will trigger match creation)
	ApproveMatchApproval(ctx context.Context, arg ApproveMatchApprovalParams) error
	// ...
}

the sales pitch is that you can mock this for unit tests. and you can. i’ll be honest, though — i mostly don’t. my repositories inject the concrete *sqlc.Queries, and i test them against a real postgres in a container instead, because a mock that returns whatever i tell it to can’t catch a broken ST_DWithin or a foreign key i forgot. that’s the whole point of the testcontainers setup : the queries run against postgis/postgis:16-3.4-alpine, migrations and spatial extension and all, so the thing under test is the actual sql, not my impression of it. the interface is still worth emitting — it documents the surface and it’s there the day i want a fake — but i want to be straight that “you can mock it” and “you should mock it” are different sentences.

keep an eye on that middle method, though. ApproveMatchApproval — “will trigger match creation.” that comment is not decoration.

invariants belong in the database

here’s the rule the product actually cares about: when a shelter approves an adopter’s application, a match exists — exactly one, no duplicates, no matter what. that is an invariant, and invariants that live in application code are invariants that get skipped the one time someone writes a second code path.

so it doesn’t live in application code. it lives in a migration:

internal/infrastructure/database/migrations/000004_create_swipes_and_matches_tables.up.sql

CREATE OR REPLACE FUNCTION create_match_on_approval()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'approved' AND (OLD.status IS NULL OR OLD.status != 'approved') THEN
        INSERT INTO matches (user_id, pet_id, shelter_id, status, matched_at)
        VALUES (NEW.user_id, NEW.pet_id, NEW.shelter_id, 'active', CURRENT_TIMESTAMP)
        ON CONFLICT (user_id, pet_id) DO NOTHING;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_create_match_on_approval
AFTER UPDATE ON match_approvals
FOR EACH ROW
EXECUTE FUNCTION create_match_on_approval();

a trigger function ↗ gets NEW and OLD — the row after and before the update — so the guard NEW.status = 'approved' AND OLD.status != 'approved' fires exactly on the transition into approved, not on every save of an already-approved row. the AFTER UPDATE ... FOR EACH ROW trigger ↗ runs it in the same transaction as the update. and ON CONFLICT (user_id, pet_id) DO NOTHING ↗ , backed by a unique constraint, makes the whole thing idempotent: two concurrent approvals can’t produce two matches. the database won’t let them.

do this in an orm and you’re tempted into a service method that loads the approval, flips a field, then does a separate db.Create(&Match{}) — two round trips with a race window between them, or a lifecycle hook like gorm’s AfterUpdate that buries business logic inside a persistence model. i complained about exactly that hook-hidden-logic pattern in the hexagonal post, so let me be fair about the tension: a postgres trigger is also action at a distance. you have to know it exists; nothing in the go code says “approving here silently writes a matches row.” the difference i’ll defend is that the trigger is a versioned migration enforcing a data invariant that’s true no matter which service, script, or psql session touches the row — where the orm hook is go pretending to be a schema. one is a guarantee. the other is a convention.

the honest part: where sqlc bites

it is not free. three places it drew blood.

dynamic queries are a genuine weakness. the moment a query wants optional filters, you can’t just build a string — sqlc needs to see the whole statement at generate time. so you contort. this is the real DiscoverPets filter, and it’s ugly on purpose:

internal/infrastructure/database/queries/pets.sql

WHERE p.deleted_at IS NULL
  AND p.status = 'available'
  AND ($3::text = '' OR p.species = $3)
  AND ($4::text = '' OR p.size = $4)
  AND ($6::int = 0 OR p.age <= $6)
  AND ST_DWithin(
      p.location::geography,
      ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
      $7::float8 * 1000.0
  )

every “optional” filter is a $3 = '' OR ... sentinel, and sorting by a runtime choice becomes a stack of CASE WHEN $10 = 'distance' THEN ... clauses in the ORDER BY. it works, ST_DWithin ↗ does the radius search honestly, but for genuinely variable shapes you eventually reach for a real query builder or squirrel and step outside sqlc’s guarantees. sqlc is superb at fixed queries and merely tolerable at dynamic ones.

postgis makes sqlc generate nonsense names. because those params are wrapped in expressions, sqlc can’t infer names for them, so the generated struct is this:

internal/infrastructure/database/sqlc/pets.sql.go

type DiscoverPetsParams struct {
	StMakepoint   interface{} `json:"st_makepoint"`
	StMakepoint_2 interface{} `json:"st_makepoint_2"`
	Column3       string      `json:"column_3"`
	// ...
	Column7       float64     `json:"column_7"`
	Limit         int32       `json:"limit"`
	Offset        int32       `json:"offset"`
	Column10      string      `json:"column_10"`
}

Column3. StMakepoint interface{}. the coordinates going into ST_MakePoint come out as untyped interface{} because sqlc can’t see through the postgis function signature, and the sentinel filters become positional ColumnN. i have a mapping layer in the repository that turns these back into named, typed arguments, and that layer is pure boilerplate that exists solely because postgis and sqlc don’t fully speak. it’s the tax i mentioned in the config, arriving with interest.

the codegen step is a step. you change sql, you run sqlc generate, you commit the output. forget it and your build is out of sync with your schema. it’s a go:generate line and a ci check, not a crisis — but it’s a ritual an orm doesn’t ask of you, and someone new to the repo will forget it once.

and the thing sqlc pointedly does not do: it type-checks your sql, it does not logic-check it. a query that’s valid sql and returns the wrong rows compiles clean and generates clean. the compile-time safety is about shapes and columns, not about whether your WHERE is correct. that’s what the tests are for. sqlc narrows the failure surface; it doesn’t erase it.

so, is it worth it?

for this project, unreservedly yes — but the honest scorecard is what makes the case, not the enthusiasm.

• the value is visibility, not speed. yes, pgx over the native protocol is quicker than a reflection-based orm, but that’s not why i’d choose it. i’d choose it because every query is a file i can open, read, EXPLAIN, and hand to a review. the orm’s cost model is invisible until it’s a latency graph; this one is on disk.
• compile-time shape safety is a real, load-bearing guarantee — schema drift breaks the build instead of production — and it costs me a codegen step, which is a chore, not a risk.
• push invariants down to where they can’t be skipped. the match-on-approval trigger is three code paths’ worth of correctness enforced in one place the database itself defends. an orm would’ve tempted me to scatter it.
• and it is not all upside. dynamic queries fight you, postgis generates Column3 and interface{}, and you maintain a mapping layer to paper over it. i’d make the same trade again, but i’d be lying if i called it frictionless.

the orm was dead code, and deleting it changed nothing at runtime — which is the most damning thing you can say about a dependency. the sql was doing all the work the whole time. i just had to admit it.

next up: those spatial queries are only as good as the EXPLAIN behind them, so — indexing postgis for real, GiST vs SP-GiST, and how i stopped a radius search from sequential-scanning the whole pets table.

it’s a side project — warts, Column3, and all — which, around here, is the point.