sqlc vs GORM

Field	Value
Type	Skill Resource
Source	~/.copilot/skills/backend/references/sqlc.md
Description	Not specified

Source Content

sqlc vs GORM

Priya inherited a service where every query was a GORM method chain. She needed one report that joined five tables with a window function. The chain grew three levels of nested Preload calls. She gave up and dropped to raw SQL anyway.

Across the hall, Devon’s team had started that same service with sqlc. The report was a .sql file, a generated Go function, and a five-minute PR.

This file gives the decision criteria up front, then the concrete setup for sqlc: config, a query file, and the generated-code usage pattern. See references/database.md for transaction, pagination, and scanning patterns that apply to either tool once you’re inside the repository layer.

Decision criteria: sqlc vs GORM

sqlc

sqlc compiles hand-written SQL into typed Go functions at build time. You write the query; it writes the struct and the method.

• Query is complex: joins across 3+ tables, window functions, CTEs, or anything the ORM’s query builder fights you on.
• The team already knows SQL and wants to review the actual statement in the PR, not a chain of method calls that hides it.
• Compile-time safety matters more than convenience — a column rename breaks the build, not a 2 a.m. page.
• Read-heavy services with a handful of write paths, where most of the value is in query shape, not object graph management.

GORM

GORM is an ORM: it maps Go structs to tables and gives you method chains (.Where(...).Preload(...).Find(...)) instead of SQL text.

• Rapid CRUD scaffolding where most queries are single-table Find/Create/Update and speed of first draft matters more than query control.
• The domain model changes shape often during early development. Migrations need to follow suit with minimal ceremony (AutoMigrate for prototypes only, never production — see references/database.md and the database skill for real migration discipline).
• The team is small and already fluent in GORM; switching tools has a real cost that a straightforward CRUD service may not justify.
• Associations (belongs_to, has_many) are used heavily and Preload genuinely simplifies the code, not just defers the problem.

The one-question test

Ask one question: “Do I want to read the SQL, or a method chain that generates it?” A join, a GROUP BY, or a window function reads better as SQL — reach for sqlc. A single-table CRUD path reads fine as a chain — GORM is fine.

sqlc setup

sqlc.yaml sits at the repo root. It points at a schema (for column types) and a queries directory, and picks the pgx driver so generated code returns pgx-compatible types.

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    schema: "migrations"
    queries: "internal/repository/queries"
    gen:
      go:
        package: "sqlcgen"
        out: "internal/repository/sqlcgen"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_pointers_for_null_types: true
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"

Query file and generated usage

Write the query once, with a name comment sqlc parses to name the generated function.

-- internal/repository/queries/invoices.sql

-- name: GetInvoiceWithLineItems :one
SELECT
    i.id, i.status, i.total_cents,
    COALESCE(json_agg(li.*) FILTER (WHERE li.id IS NOT NULL), '[]') AS line_items
FROM invoices i
LEFT JOIN line_items li ON li.invoice_id = i.id
WHERE i.id = $1
GROUP BY i.id;

-- name: ListInvoicesByCursor :many
SELECT id, status, total_cents
FROM invoices
WHERE id > $1
ORDER BY id
LIMIT $2;

sqlc generate emits a typed method per query. The repository wraps the generated call, mapping to domain types at the boundary — the same boundary rule as oapi-codegen output in references/openapi.md.

internal/repository/invoice.go

func (r *InvoiceRepository) ByID(ctx context.Context, id domain.InvoiceID) (domain.Invoice, error) {
  row, err := r.q.GetInvoiceWithLineItems(ctx, id.String())
  if err != nil {
    if errors.Is(err, pgx.ErrNoRows) {
      return domain.Invoice{}, domain.ErrNotFound
    }
    return domain.Invoice{}, fmt.Errorf("get invoice %s: %w", id, err)
  }
  return row.toDomain(), nil
}

Regeneration is a build step, same pattern as OpenAPI codegen: sqlc generate, then commit the output so diffs are reviewable and CI is hermetic. Add a task sqlc target and a CI drift check (sqlc generate && git diff --exit-code).

Migration path

GORM to sqlc

Move one repository at a time, starting with the query that hurts most: the deepest Preload chain, or the report that dropped to raw SQL anyway. Keep the repository’s public interface unchanged so the service layer never notices. Only the implementation swaps, from GORM calls to generated sqlc calls. Mixing sqlc and GORM in one service is fine during the transition — run both side by side until every query has moved.

sqlc to GORM

Rare, but happens when a service pivots toward heavy association-driven CRUD and the hand-written query set balloons. Same rule in reverse: migrate repository-by-repository behind the existing interface, and keep GORM’s AutoMigrate off in production regardless of which tool you land on.

How to use this reference

• Ask the one-question test above before writing the first query in a new service.
• New service, complex reporting/joins → sqlc; new service, straightforward CRUD → GORM (SKILL.md’s scaffolding step defaults to sqlc for exactly this reason).
• Existing service in pain → migrate the worst query first, interface-first, one repository at a time.
• Either tool: transaction, pagination, and scanning discipline in references/database.md still applies.