Database Access with sqlc

Gofs applications query databases with plain, standard SQL. sqlc reads your database schema and your raw SQL queries, checks the queries against the schema for syntax and type errors at compile time, and generates idiomatic, type-safe Go functions for each query.

You write SQL; sqlc writes the boilerplate.

Why sqlc Fits the Gofs Philosophy

1. Zero Runtime Dependencies

Traditional Object-Relational Mapping (ORM) frameworks (such as GORM, Hibernate, or Prisma) bundle massive runtime dependencies, heavy reflection engines, and proprietary query DSLs. They introduce:

• Maintenance Burden: Upgrading an ORM often breaks queries, alters mapping behaviors, or causes hidden performance regressions.
• Supply Chain Risks: Large ORMs pull in numerous transitive dependencies, increasing the attack surface of your production binary.
• Leaky Abstractions: Developers spend more time learning the ORM’s proprietary syntax and debugging N+1 query pitfalls than writing effective database queries.

In contrast, sqlc bundles zero runtime dependencies. The code generated by sqlc imports only Go’s standard library packages (database/sql, context, and encoding/json). It introduces zero third-party packages to your production binary.

┌─────────────────────────────────────────────────────────────┐
│                 sqlc Zero-Dependency Flow                   │
│                                                             │
│   Schema (migrations.sql) ─┐                                │
│                            ├──► [ sqlc compile check ]      │
│   Queries (queries.sql)   ─┘                │               │
│                                             ▼               │
│                                Generated Go Code            │
│                       (Imports ONLY standard database/sql!) │
│                                             │               │
│                                             ▼               │
│                                   Committed to Git          │
│                      (Builds with pure Go compiler, 0 deps!)│
└─────────────────────────────────────────────────────────────┘

2. Committed, Standalone Go Code

sqlc is strictly a development tool, not a runtime dependency:

• The generated code is committed to your repository.
• Anyone can clone your project and compile it immediately using standard go build. They do not even need sqlc installed.
• If sqlc were discontinued tomorrow, your application would continue to compile and function without alteration, and the generated code could easily be maintained by hand.

3. AI-Assisted SQL Authoring

Historically, developers turned to ORMs because writing raw SQL and mapping database rows to structs by hand felt cumbersome.

Today, modern AI assistants make SQL authoring effortless. AI excels at generating, optimizing, and indexing standard SQL queries because SQL is a 50-year-old open standard with vast documentation. AI easily writes complex joins, CTEs, PostgreSQL generated columns, and JSONB queries. Combined with sqlc’s automatic Go struct and function generation, you achieve all the speed of an ORM with none of the runtime bloat or breaking changes.

Configuration

The gofs template includes a sqlc.yaml file at the project root. Each backend package containing queries has an entry. Schema definitions are read directly from your database migrations, ensuring your queries are validated against the exact schema deployed to production:

version: "2"
sql:
  - engine: "postgresql"
    schema: "internal/db/migrations"
    queries: "internal/app/list/queries.sql"
    gen:
      go:
        package: "query"
        out: "internal/app/list/query"
        sql_package: "database/sql"

The template’s Makefile runs make generate (or go generate ./...), and the generate directive is defined in /internal/db/db.go:

//go:generate go tool sqlc generate

Writing Queries

Queries live in a queries.sql file inside the backend package that owns them. Each query is preceded by an annotation declaring its name and result shape:

• :one (expects a single row)
• :many (returns a slice of rows)
• :exec (executes without returning rows)
• :execrows (returns the count of affected rows)

-- name: SearchLists :many
SELECT blob FROM lists
WHERE name ILIKE '%' || sqlc.arg(term)::text || '%'
ORDER BY name;

This generates:

func (q *Queries) SearchLists(ctx context.Context, term string) ([]json.RawMessage, error)

The official sqlc documentation provides extensive guides for inserting data, updating records, and using named parameters.

Executing Generated Code in Transactions

The generated query.New function accepts any type satisfying the DBTX interface, which includes both *sql.DB and *sql.Tx.

In gofs, handlers wrap database operations in the transaction helper from /internal/db:

err := db.Transaction(r.Context(), func(tx *sql.Tx) error {
	lists, err = query.New(tx).GetLists(r.Context())
	return err
})

Core Conventions

1. Encapsulate Query Code: Keep queries in queries.sql within the package that owns them, and generate code into a private query subpackage. Only the parent package imports query; HTTP handlers call the public package API and never query the database directly.
2. Never Edit Generated Code: Always modify the queries.sql or migration files, then re-run make generate.
3. Commit Generated Files: Ensure all generated Go files are tracked in git so that CI and team members can build with standard go build.
4. Leverage Postgres Generated Columns for JSON Blobs: In our hybrid JSON-relational model, expose fields you need to index, sort, or filter on as PostgreSQL generated columns. This provides sqlc with strongly typed columns while keeping the JSON blob as the canonical source of truth.
5. Pin sqlc with the Go Tool Directive: Pin sqlc in go.mod using Go 1.24+’s go get -tool so every developer and CI pipeline generates identical code.

Tip: Run go tool sqlc vet to validate your SQL queries against a live running database during CI to detect syntax or type discrepancies before merging.