New Sqlc Query

-- name: <QueryName> :<annotation>
SELECT ...
FROM <table>
WHERE ...;

Common patterns

Filter/search

-- name: ListUsersByName :many
SELECT * FROM users
WHERE name ILIKE '%' || $1 || '%'
ORDER BY created_at ASC;

Paginated list

-- name: ListUsersPaginated :many
SELECT * FROM users
ORDER BY created_at ASC
LIMIT $1 OFFSET $2;

Count

-- name: CountUsers :one
SELECT COUNT(*) FROM users;

Soft delete (add deleted_at column first)

-- name: SoftDeleteUser :exec
UPDATE users SET deleted_at = $1 WHERE id = $2;

Upsert

-- name: UpsertUser :one
INSERT INTO users (id, name, email, created_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING *;

Workflow

1. Add the query to pkg/postgres/queries/<domain>.sql
2. Run:

   go tool sqlc generate
   make check
   

3. The generated method appears in pkg/postgres/db/<domain>.sql.go
4. Call it from the service internal/<domain>/core/service.go via s.q.<QueryName>(ctx, ...)

If adding a new column (migration required)

1. Add migration to pkg/postgres/migrations/<domain>.sql:

   ALTER TABLE <domain>s ADD COLUMN IF NOT EXISTS <field> TEXT;
   

   Or for a fresh project, edit the CREATE TABLE directly.
2. Re-run go tool sqlc generate to pick up schema changes.
3. Migrations run automatically on app startup (postgres.Migrate in main).

sqlc.yaml reference (project PostgreSQL block)

- engine: "postgresql"
  queries: "pkg/postgres/queries"
  schema: "pkg/postgres/migrations"
  gen:
    go:
      package: "pgdb"
      out: "pkg/postgres/db"
      sql_package: "pgx/v5"
      overrides:
        - db_type: "timestamptz"
          go_type: "time.Time"

pgx vs database/sql differences for service code

// DeleteUser with pgconn.CommandTag (no error from RowsAffected):
result, err := s.q.DeleteUser(ctx, id)
if err != nil { ... }
if result.RowsAffected() == 0 {
    return ErrNotFound
}

// GetByID — use pgx.ErrNoRows not sql.ErrNoRows:
import "github.com/jackc/pgx/v5"
if errors.Is(err, pgx.ErrNoRows) {
    return nil, ErrNotFound
}