Set up sqlc for a Go service on Postgres: config, schema from migrations, query annotations, optional filters, transactions, and wiring the generated code to a managed database.

sqlc turns SQL files into typed Go code, so a Postgres query becomes a method like CreateOrder(ctx, params) that returns a struct. You keep writing SQL, and a renamed column breaks the build instead of a request.
Getting there takes a config file, a schema sqlc can read and a choice between pgx/v5 and database/sql, which this guide sets up for an orders service before wiring it to a managed Postgres.
The examples use sqlc v1.31, pgx v5 and Go 1.24 or later. Install sqlc with brew install sqlc or go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest, as the installation docs describe.
Start with the schema as a migration. sqlc reads migration directories from golang-migrate, goose, dbmate and others, applies the up files in order and ignores the down files, so the same files feed both the database and the code generator:
-- migrations/1_create_orders.up.sql
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_idx ON orders (customer_id, created_at DESC);
The config names the engine, the two inputs and where the Go package goes. sql_package: "pgx/v5" makes the generated code take a pgx pool or transaction:
# sqlc.yaml
version: "2"
sql:
- engine: "postgresql"
schema: "migrations"
queries: "queries.sql"
gen:
go:
package: "db"
out: "db"
sql_package: "pgx/v5"
A //go:generate sqlc generate line in any Go file of the package lets go generate ./... rerun it, which is easy to wire into CI with a check that the working tree is clean afterwards.
Each query starts with a comment that names the method and says what it returns. The query annotations used most are :one for a single row, :many for a slice, :exec for no result and :execrows for the affected row count:
-- queries.sql
-- name: CreateOrder :one
INSERT INTO orders (customer_id, total_cents)
VALUES ($1, $2)
RETURNING *;
-- name: GetOrder :one
SELECT * FROM orders
WHERE id = $1;
-- name: MarkPaid :execrows
UPDATE orders SET status = 'paid'
WHERE id = $1 AND status = 'pending';
-- name: CountByStatus :many
SELECT status, count(*) AS orders, sum(total_cents)::bigint AS total_cents
FROM orders
GROUP BY status
ORDER BY status;
sqlc generate writes models.go with an Order struct, and a method per query. Queries with more than one parameter get a params struct, and aggregate queries get a row struct named after the query, here CountByStatusRow with Status, Orders and TotalCents. The casts matter: without ::bigint, sum over a BIGINT is numeric in Postgres and sqlc types it accordingly.
A typo fails at generate time with the file and position:
With pgx, the pool goes straight into db.New. MarkPaid returns how many rows it updated, so a second call for the same order returns 0, which makes the endpoint safe to retry:
package main
import (
"context"
"fmt"
"log"
"os"
"example.com/orders/db"
"github.com/jackc/pgx/v5/pgxpool"
)
func main() {
ctx := context.Background()
pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer pool.Close()
q := db.New(pool)
o, err := q.CreateOrder(ctx, db.CreateOrderParams{CustomerID: "cus_42", TotalCents: 1999})
if err != nil {
log.Fatal(err)
}
first, _ := q.MarkPaid(ctx, o.ID)
second, _ := q.MarkPaid(ctx, o.ID)
fmt.Println(o.ID, first, second) // 1 1 0
}
sqlc has no query builder, so a filter that may or may not apply is written in SQL with a nullable parameter. sqlc.arg and sqlc.narg also give parameters readable names in the generated struct:
-- name: SearchOrders :many
SELECT * FROM orders
WHERE customer_id = sqlc.arg(customer_id)
AND (sqlc.narg(status)::text IS NULL OR status = sqlc.narg(status))
ORDER BY created_at DESC
LIMIT sqlc.arg(max_rows);
With pgx the Status field is a pgtype.Text, and with database/sql a sql.NullString. Leaving it invalid skips the filter. This works well for two or three optional filters; past that, the index cannot serve every combination, and a few dedicated queries are usually faster and easier to read.
Transactions use the generated WithTx, which returns the same methods bound to a transaction:
func payOrder(ctx context.Context, pool *pgxpool.Pool, q *db.Queries, id int64) error {
tx, err := pool.Begin(ctx)
if err != nil {
return err
}
defer tx.Rollback(ctx) // no-op after Commit
qtx := q.WithTx(tx)
if _, err := qtx.MarkPaid(ctx, id); err != nil {
return err
}
// Other writes that must commit together go through qtx too.
return tx.Commit(ctx)
}
The generated code needs a database that exists, has the migrations applied and gives you a connection. Encore is a backend framework with built-in infrastructure, and sqlc plugs into its Postgres databases without changes: the migrations directory sqlc reads is the one Encore applies, locally under encore run and on deploy to RDS or Cloud SQL in your own AWS or GCP account.

Run in your terminal to get started locally.
Place the migration and queries.sql in the orders service, set schema: "orders/migrations", queries: "orders/queries.sql" and out: "orders/db" in sqlc.yaml, and drop the sql_package line so sqlc generates database/sql code. Encore's Stdlib() returns the *sql.DB that code expects:
// orders/orders.go
package orders
import (
"context"
"database/sql"
"errors"
"encore.app/orders/db"
"encore.dev/beta/errs"
"encore.dev/storage/sqldb"
)
// Postgres locally under encore run, RDS or Cloud SQL after deploy.
var ordersDB = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{
Migrations: "./migrations",
})
var queries = db.New(ordersDB.Stdlib())
//encore:api public method=GET path=/orders/:id
func Get(ctx context.Context, id int64) (*db.Order, error) {
o, err := queries.GetOrder(ctx, id)
if errors.Is(err, sql.ErrNoRows) {
return nil, &errs.Error{Code: errs.NotFound, Message: "order not found"}
}
if err != nil {
return nil, err
}
return &o, nil
}
type SearchParams struct {
CustomerID string `query:"customer_id"`
Status string `query:"status"`
}
type SearchResponse struct {
Orders []db.Order `json:"orders"`
}
//encore:api public method=GET path=/orders
func Search(ctx context.Context, p *SearchParams) (*SearchResponse, error) {
rows, err := queries.SearchOrders(ctx, db.SearchOrdersParams{
CustomerID: p.CustomerID,
Status: sql.NullString{String: p.Status, Valid: p.Status != ""},
MaxRows: 50,
})
if err != nil {
return nil, err
}
return &SearchResponse{Orders: rows}, nil
}
Add emit_json_tags: true to the Go options so the generated Order struct serializes with snake_case fields in API responses. Encore's sqlc example app uses the same wiring with emit_interface: true, so a service struct can hold the generated Querier interface and tests can swap it out. Schema changes follow the usual migration flow: add 2_add_currency.up.sql, rerun sqlc generate, and the next encore run applies the migration before the service starts.
The whole loop stays inside the repo, which suits a coding agent well: it adds the migration and the query, regenerates, and calls GET /orders?customer_id=cus_42 against the local Postgres, then the same declaration creates the database in your cloud on deploy.
Best fit: Go services that want sqlc's typed SQL and a Postgres that is created, migrated and connected for them locally and in their own AWS or GCP account.
relation "x" does not exist at generate time: the schema path does not include the migration that creates the table, often because it points at one file instead of the whole directory.numeric where you expected int64: cast aggregates in SQL, such as sum(total_cents)::bigint, or add a type override in sqlc.yaml.pgtype.Text everywhere: set emit_pointers_for_null_types: true with pgx to get *string, or keep the column NOT NULL where it should be.sqlc diff, which exits non-zero when the checked-in code does not match the queries.Write the schema and your queries in SQL, add a sqlc.yaml with engine postgresql, the schema and queries paths and a Go output package, and run sqlc generate. It writes a Queries type with one typed method per named query, which you construct with db.New and a pgx pool or a *sql.DB.
Use pgx/v5 when you own the connection pool and want pgx types and features such as CopyFrom. Use database/sql when the connection comes from a framework or library that hands you a *sql.DB, which is the case with Encore's Stdlib() handle.
Use sqlc.narg for a nullable parameter and compare it with IS NULL, for example WHERE (sqlc.narg(status)::text IS NULL OR status = sqlc.narg(status)). The generated parameter is a sql.NullString or pgtype.Text, and leaving it invalid skips the filter.
Yes. Point schema at the migrations directory and sqlc applies the up migrations in order to build its view of the schema, skipping the down files. It never runs migrations against a database.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.