go / sql queries

updated May 19, 2026

This pattern keeps SQL files close to the Go code that uses them.

Each package that talks to Postgres owns its SQL files and embeds them with //go:embed. Callers use pgx directly, including generic row helpers such as pgx.RowToStructByName[T] and pgx.CollectRows. See pgx docs. For pgx setup and broader Postgres usage, see go / postgres.

Layout

Put queries next to the Go code that uses them:

people/
  queries/
    fetch_person.sql
    upsert_person.sql
  sql.go
  person.go

One query per file.

sql.go

Each package has a tiny loader:

package people

import "embed"

//go:embed queries/*.sql
var fs embed.FS

func sqlFile(name string) string {
	b, err := fs.ReadFile("queries/" + name + ".sql")
	if err != nil {
		panic(err)
	}
	return string(b)
}

var (
	qFetchPerson  = sqlFile("fetch_person")
	qUpsertPerson = sqlFile("upsert_person")
)

Missing SQL is a programmer error, so panic is fine.

Calling queries

Use Query, QueryRow, and Exec on your DB wrapper:

rows, err := db.Query(ctx, qFetchPerson, email)
if err != nil {
	return nil, err
}
return pgx.CollectRows(rows, pgx.RowToStructByName[PersonRow])

Map result columns with db:"column_name" tags.

Why this pattern

• No code generation step.
• No generated Go checked into the repo.
• No extra schema-sync artifact for codegen.
• Query, SQL file, and call site live together.

Refactors stay local. Rename a column, update the .sql file, then fix compile errors.

SQL file rules

• Plain SQL only. No codegen annotations.
• Name by behavior: fetch_<thing>.sql, insert_<thing>.sql, upsert_<thing>.sql, delete_<thing>.sql.
• Use positional params ($1, $2, ...).
• Cast when ambiguous ($1::bigint).
• Format with go / pgfmt:

go run ./cmd/pgfmt -w <pkg>/queries/*.sql

Adding a new query

1. Create <pkg>/queries/<name>.sql.
2. Add q<Name> = sqlFile("<name>") to <pkg>/sql.go.
3. Add a Go function in the same package that calls it.
4. Run pgfmt on that package's query files.

← All articles