Nov 19, 20267 min read

How to Use sqlc with Postgres in Go

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.

Illustration for How to Use sqlc with Postgres in Go

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.

What sqlc needs at a glance

PieceWhat it isIn this guide
SchemaDDL sqlc parses to learn tables and typesmigrations/*.up.sql
QueriesSQL with -- name: X :kind commentsqueries.sql
Configsqlc.yaml, version 2engine, paths, Go package
Driverpgx/v5 or database/sqlboth shown
Outputdb.go, models.go, queries.sql.gopackage db

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.

How do you set up sqlc in a Go project?

sqlc and Postgres

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.

Writing queries

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:

~/orders
$ sqlc generate# package dbqueries.sql:35:29: column "custmer_id" does not exist

Calling the generated code

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 }

How do you write optional filters and transactions?

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) }

How do you run sqlc on a Postgres that is set up for you?

Encore

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.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account.
Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create shop --example=hello-world$ cd shop && mkdir -p orders/migrations

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.

Common sqlc problems and fixes

  • 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.
  • Nullable columns as 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.
  • Generated code out of date in CI: run sqlc diff, which exits non-zero when the checked-in code does not match the queries.

Frequently asked questions

How do I use sqlc with Postgres in Go?

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.

Should sqlc use pgx/v5 or database/sql?

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.

How do I write an optional filter in sqlc?

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.

Can sqlc read golang-migrate migration files?

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.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders