Build a REST API in Go With sqlc Chi and Postgres Together

A bookmarks API in Go with sqlc, chi and pgx on PostgreSQL: typed queries, a transaction, an optional filter, keyset paging and statement counting tests

18 min read

Summarize in AI

Quick Summary

Write the schema and named queries as SQL, run sqlc generate, and call the typed methods from chi handlers. Use a pgx transaction for writes across tables, load related rows for a page with one any query, and page with a keyset cursor. Listing 5 or 25 bookmarks cost exactly 2 statements.

Key takeaways

Three tables and nine named queries became typed Go methods, and the handlers contain no SQL strings, only calls such as CreateBookmark(ctx, db.CreateBookmarkParams{...}).
The list endpoint used one query for the page and one bookmark_id = any($1::bigint[]) query for every tag on it, so listing 5 bookmarks and listing 25 both cost exactly 2 statements, counted by a wrapper around the pool.
An optional search filter was one SQL statement with sqlc.narg('q'): a nil pgtype.Text skipped the ilike condition, and q=item 5 matched exactly 1 of 7 bookmarks.
Creating a bookmark with tags ran in one pgx transaction, UpsertTag plus LinkTag normalised the tags (Lang and lang became one lang), and the duplicate URL returned 409 from SQLSTATE 23505.
A keyset walk with limit=3 returned 7 bookmarks in 3 pages, and DELETE removed the links and the row in one transaction and returned 404 on the second call.

A REST API on sqlc has a particular feel. There is no query builder and no model layer to learn: the SQL is the interface, the generated functions are the data access layer, and the handlers are plain Go that call them. This article builds a small bookmarks API that way, with chi for routing and pgx for the driver, and tests it against PostgreSQL 18.6 with a schema per test. It is the sqlc counterpart of the GORM blog backend and the chi and Ent API, so the three can be compared line for line.

Tested with Go 1.25.6, sqlc v1.30.0, chi v5.2.1, pgx v5.11.0, PostgreSQL 18.6 in Docker, macOS on Apple Silicon (arm64)

It assumes the basics from the sqlc tutorial and builds on sqlc transactions, sqlc pagination and sqlc dynamic queries, which each go deeper on one technique used here.

What it does

Route	Result
`POST /bookmarks`	Save a URL with a title and tags, in one transaction. 201; 409 if the URL exists; 422 if a field is missing
`GET /bookmarks/{id}`	One bookmark with its tags. 404 if absent
`GET /bookmarks?limit=&before=&q=`	Newest first, keyset paged, with an optional title search
`DELETE /bookmarks/{id}`	Remove the links and the bookmark in one transaction. 204; 404 if absent

Step 1: schema and queries

The schema is three tables: bookmarks, tags and the join table with a composite primary key.

schema.sqlText

create table bookmarks (
  id         bigserial primary key,
  url        text not null unique,
  title      text not null,
  created_at timestamptz not null default now()
);
create table tags (
  id   bigserial primary key,
  name text not null unique
);
create table bookmark_tags (
  bookmark_id bigint not null references bookmarks(id),
  tag_id      bigint not null references tags(id),
  primary key (bookmark_id, tag_id)
);

The queries are the data access layer. Each has a name and a result shape in a comment, and sqlc turns each into a method:

query.sqlText

-- name: CreateBookmark :one
insert into bookmarks (url, title) values ($1, $2)
returning id, url, title, created_at;

-- name: GetBookmark :one
select id, url, title, created_at from bookmarks where id = $1;

-- name: ListBookmarks :many
select id, url, title, created_at from bookmarks
where (sqlc.arg('before')::bigint = 0 or id < sqlc.arg('before'))
  and (sqlc.narg('q')::text is null or title ilike '%' || sqlc.narg('q') || '%')
order by id desc
limit sqlc.arg('max');

-- name: DeleteBookmark :execrows
delete from bookmarks where id = $1;

-- name: UpsertTag :one
insert into tags (name) values ($1)
on conflict (name) do update set name = excluded.name
returning id;

-- name: LinkTag :exec
insert into bookmark_tags (bookmark_id, tag_id) values ($1, $2)
on conflict do nothing;

-- name: DeleteLinks :exec
delete from bookmark_tags where bookmark_id = $1;

-- name: TagsForBookmarks :many
select bt.bookmark_id, t.name
from bookmark_tags bt join tags t on t.id = bt.tag_id
where bt.bookmark_id = any($1::bigint[])
order by bt.bookmark_id, t.name;

Points to read closely:

returning clauses (CreateBookmark) return the new row from the insert, with the generated id and timestamp, in one round trip.
The optional filter in ListBookmarks is one statement, not two. sqlc.arg('before') and sqlc.narg('q') give the parameters names, and narg makes q nullable: the generated parameter is a pgtype.Text, and a NULL turns title ilike ... off through sqlc.narg('q')::text is null or .... This is the standard sqlc pattern for optional filters (dynamic queries covers its limits).
limit sqlc.arg('max') names the page size. Without names sqlc would call the parameters $1, $2 and the struct fields Column1, which is unreadable.
:execrows on DeleteBookmark returns the number of rows removed, which is how the handler tells 204 from 404.
UpsertTag is insert ... on conflict (name) do update set name = excluded.name returning id. The no-op update makes returning return the existing row's id, which do nothing would not.
TagsForBookmarks takes an array (any($1::bigint[])), which loads the tags of a whole page in one query.

The configuration generates the db package with pgx types and JSON tags:

sqlc.yamlText

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: db
        out: db
        sql_package: pgx/v5
        emit_json_tags: true

Run sqlc generate and the db package appears: a Bookmark struct, parameter structs such as CreateBookmarkParams and ListBookmarksParams (fields Before int64, Q pgtype.Text, Max int32), one method per query, and a Queries type with WithTx for transactions.

Step 2: the handlers

api.goGo

package main

import (
	"context"
	"encoding/json"
	"errors"
	"net/http"
	"strconv"
	"strings"

	"example.test/lab/db"
	"github.com/go-chi/chi/v5"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

type API struct {
	pool *pgxpool.Pool
	q    db.DBTX // the pool, or a wrapper that counts statements in tests
}

func (a *API) Routes() http.Handler {
	r := chi.NewRouter()
	r.Post("/bookmarks", a.create)
	r.Get("/bookmarks", a.list)
	r.Get("/bookmarks/{id}", a.get)
	r.Delete("/bookmarks/{id}", a.remove)
	return r
}

type bookmarkJSON struct {
	ID    int64    `json:"id"`
	URL   string   `json:"url"`
	Title string   `json:"title"`
	Tags  []string `json:"tags"`
}

func write(w http.ResponseWriter, status int, v any) {
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(status)
	json.NewEncoder(w).Encode(v)
}

func fail(w http.ResponseWriter, err error) {
	var pg *pgconn.PgError
	switch {
	case errors.Is(err, pgx.ErrNoRows):
		write(w, http.StatusNotFound, map[string]string{"error": "not found"})
	case errors.As(err, &pg) && pg.Code == "23505":
		write(w, http.StatusConflict, map[string]string{"error": "url already saved"})
	default:
		write(w, http.StatusInternalServerError, map[string]string{"error": "internal error"})
	}
}

// withTags loads the tags of many bookmarks with one query.
func (a *API) withTags(ctx context.Context, bs []db.Bookmark) ([]bookmarkJSON, error) {
	ids := make([]int64, len(bs))
	out := make([]bookmarkJSON, len(bs))
	pos := map[int64]int{}
	for i, b := range bs {
		ids[i] = b.ID
		pos[b.ID] = i
		out[i] = bookmarkJSON{ID: b.ID, URL: b.Url, Title: b.Title, Tags: []string{}}
	}
	rows, err := db.New(a.q).TagsForBookmarks(ctx, ids)
	if err != nil {
		return nil, err
	}
	for _, r := range rows {
		out[pos[r.BookmarkID]].Tags = append(out[pos[r.BookmarkID]].Tags, r.Name)
	}
	return out, nil
}

func (a *API) create(w http.ResponseWriter, r *http.Request) {
	var in struct {
		URL   string
		Title string
		Tags  []string
	}
	if err := json.NewDecoder(r.Body).Decode(&in); err != nil || in.URL == "" || in.Title == "" {
		write(w, http.StatusUnprocessableEntity, map[string]string{"error": "url and title are required"})
		return
	}
	tx, err := a.pool.Begin(r.Context())
	if err != nil {
		fail(w, err)
		return
	}
	defer tx.Rollback(r.Context())
	q := db.New(a.q).WithTx(tx)
	b, err := q.CreateBookmark(r.Context(), db.CreateBookmarkParams{Url: in.URL, Title: in.Title})
	if err != nil {
		fail(w, err)
		return
	}
	for _, name := range in.Tags {
		id, err := q.UpsertTag(r.Context(), strings.ToLower(name))
		if err == nil {
			err = q.LinkTag(r.Context(), db.LinkTagParams{BookmarkID: b.ID, TagID: id})
		}
		if err != nil {
			fail(w, err)
			return
		}
	}
	if err := tx.Commit(r.Context()); err != nil {
		fail(w, err)
		return
	}
	out, err := a.withTags(r.Context(), []db.Bookmark{b})
	if err != nil {
		fail(w, err)
		return
	}
	write(w, http.StatusCreated, out[0])
}

func (a *API) get(w http.ResponseWriter, r *http.Request) {
	id, _ := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	b, err := db.New(a.q).GetBookmark(r.Context(), id)
	if err != nil {
		fail(w, err)
		return
	}
	out, err := a.withTags(r.Context(), []db.Bookmark{b})
	if err != nil {
		fail(w, err)
		return
	}
	write(w, http.StatusOK, out[0])
}

func (a *API) list(w http.ResponseWriter, r *http.Request) {
	before, _ := strconv.ParseInt(r.URL.Query().Get("before"), 10, 64)
	max, _ := strconv.Atoi(r.URL.Query().Get("limit"))
	if max < 1 || max > 100 {
		max = 20
	}
	var q pgtextArg
	if s := r.URL.Query().Get("q"); s != "" {
		q = pgtextArg{Valid: true, String: s}
	}
	bs, err := db.New(a.q).ListBookmarks(r.Context(), db.ListBookmarksParams{Before: before, Q: q.text(), Max: int32(max)})
	if err != nil {
		fail(w, err)
		return
	}
	out, err := a.withTags(r.Context(), bs)
	if err != nil {
		fail(w, err)
		return
	}
	next := int64(0)
	if len(bs) == max {
		next = bs[len(bs)-1].ID
	}
	write(w, http.StatusOK, map[string]any{"bookmarks": out, "next": next})
}

func (a *API) remove(w http.ResponseWriter, r *http.Request) {
	id, _ := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	tx, err := a.pool.Begin(r.Context())
	if err != nil {
		fail(w, err)
		return
	}
	defer tx.Rollback(r.Context())
	q := db.New(a.q).WithTx(tx)
	if err := q.DeleteLinks(r.Context(), id); err != nil {
		fail(w, err)
		return
	}
	n, err := q.DeleteBookmark(r.Context(), id)
	if err != nil {
		fail(w, err)
		return
	}
	if n == 0 {
		fail(w, pgx.ErrNoRows)
		return
	}
	if err := tx.Commit(r.Context()); err != nil {
		fail(w, err)
		return
	}
	w.WriteHeader(http.StatusNoContent)
}

How it fits together.

The connection. API holds the pgxpool.Pool (needed to begin transactions) and a db.DBTX for queries. In production they are the same pool. In the tests, q is a wrapper that counts statements, which is why the field is the interface and not the pool. This is one of sqlc's quiet strengths: the generated code depends on a four method interface, so decorating it for logging, tracing or counting needs no framework.

Create. create begins a transaction, makes a Queries bound to it with WithTx, inserts the bookmark, upserts each tag (lowercased, so Lang and lang are one tag) and links it, then commits. defer tx.Rollback(...) is safe after a commit (it returns an error that is ignored) and covers every early return. After the commit it reads the tags back with withTags so the response has the same shape as a later GET.

Reads. get loads one bookmark and calls withTags for it. list loads a page and calls withTags once for the whole page. withTags is the interesting function: it collects the page's ids, calls TagsForBookmarks once and distributes the rows to their bookmarks through a map. That is the N plus one fix applied by hand, which is what sqlc leaves to you; it costs ten lines, and the test below holds the line.

Errors. fail maps pgx.ErrNoRows to 404 and SQLSTATE 23505 (a unique violation, from errors.As into *pgconn.PgError) to 409. Everything else is a 500 with a generic message. The mapping is in one function, as in the GORM version, using pgx's error types instead of an ORM's (pgx error codes).

Paging. list takes before (the last id seen) and limit, passes them to the query and returns next: the last id on a full page, and 0 when the page was short. Keyset paging avoids the cost of deep offsets (the measurements).

The optional text. The search term travels as a pgtype.Text. The small helper in text.go turns "no search term" into a NULL, so the query skips the filter:

text.goGo

package main

import "github.com/jackc/pgx/v5/pgtype"

// pgtextArg turns "no search term" into a NULL for the optional filter.
type pgtextArg struct {
	Valid  bool
	String string
}

func (p pgtextArg) text() pgtype.Text { return pgtype.Text{String: p.String, Valid: p.Valid} }

Step 3: main

main.goGo

package main

import (
	"context"
	"log"
	"net/http"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	pool, err := pgxpool.New(context.Background(), "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	log.Fatal(http.ListenAndServe(":8080", (&API{pool: pool, q: pool}).Routes()))
}

main opens the pool and serves the router. The tests do not use it.

Step 4: the tests

Each test creates a schema from schema.sql, points a pool at it with search_path, and wraps the pool in a statement counter:

api_test.goGo

package main

import (
	"bytes"
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"net/http/httptest"
	"os"
	"sync/atomic"
	"testing"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

const base = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

var schemas atomic.Int64

// counting wraps the pool and counts every statement the generated code sends.
type counting struct {
	pool *pgxpool.Pool
	n    *atomic.Int64
}

func (c counting) Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error) {
	c.n.Add(1)
	return c.pool.Exec(ctx, sql, args...)
}

func (c counting) Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error) {
	c.n.Add(1)
	return c.pool.Query(ctx, sql, args...)
}

func (c counting) QueryRow(ctx context.Context, sql string, args ...any) pgx.Row {
	c.n.Add(1)
	return c.pool.QueryRow(ctx, sql, args...)
}

// newAPI makes a schema from schema.sql for this test and returns a handler with a statement counter.
func newAPI(t *testing.T) (http.Handler, *atomic.Int64) {
	t.Helper()
	ctx := context.Background()
	admin, err := pgxpool.New(ctx, base)
	if err != nil {
		t.Fatal(err)
	}
	schema := fmt.Sprintf("l0703_t%d", schemas.Add(1))
	ddl, _ := os.ReadFile("schema.sql")
	if _, err := admin.Exec(ctx, "drop schema if exists "+schema+" cascade; create schema "+schema+"; set search_path = "+schema+"; "+string(ddl)); err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { admin.Exec(ctx, "drop schema "+schema+" cascade"); admin.Close() })
	pool, err := pgxpool.New(ctx, base+"&search_path="+schema)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(pool.Close)
	var n atomic.Int64
	return (&API{pool: pool, q: counting{pool, &n}}).Routes(), &n
}

func call(t *testing.T, h http.Handler, method, path, body string) (int, map[string]any) {
	t.Helper()
	req := httptest.NewRequest(method, path, bytes.NewBufferString(body))
	rec := httptest.NewRecorder()
	h.ServeHTTP(rec, req)
	var out map[string]any
	json.Unmarshal(rec.Body.Bytes(), &out)
	return rec.Code, out
}

func TestCreateReadAndConflict(t *testing.T) {
	h, _ := newAPI(t)
	code, b := call(t, h, "POST", "/bookmarks", `{"url":"https://go.dev","title":"Go","tags":["Lang","docs","lang"]}`)
	t.Logf("create: %d tags=%v", code, b["tags"])
	code, _ = call(t, h, "POST", "/bookmarks", `{"url":"https://go.dev","title":"Again"}`)
	t.Logf("same url again: %d", code)
	code, _ = call(t, h, "POST", "/bookmarks", `{"url":"","title":"x"}`)
	t.Logf("empty url: %d", code)
	code, g := call(t, h, "GET", "/bookmarks/1", "")
	t.Logf("get: %d %v", code, g["title"])
	code, _ = call(t, h, "GET", "/bookmarks/99", "")
	t.Logf("get missing: %d", code)
}

func TestListIsTwoStatements(t *testing.T) {
	h, n := newAPI(t)
	for i := 1; i <= 25; i++ {
		call(t, h, "POST", "/bookmarks", fmt.Sprintf(`{"url":"https://example.com/%d","title":"Post %d","tags":["t%d","all"]}`, i, i, i%3))
	}
	for _, limit := range []int{5, 25} {
		n.Store(0)
		code, out := call(t, h, "GET", fmt.Sprintf("/bookmarks?limit=%d", limit), "")
		t.Logf("list limit=%d: %d, %d rows, %d statements", limit, code, len(out["bookmarks"].([]any)), n.Load())
		if n.Load() != 2 {
			t.Fatalf("want 2 statements, got %d", n.Load())
		}
	}
}

func TestKeysetAndSearch(t *testing.T) {
	h, _ := newAPI(t)
	for i := 1; i <= 7; i++ {
		call(t, h, "POST", "/bookmarks", fmt.Sprintf(`{"url":"https://example.com/%d","title":"Item %d"}`, i, i))
	}
	seen, before, pages := 0, int64(0), 0
	for {
		_, out := call(t, h, "GET", fmt.Sprintf("/bookmarks?limit=3&before=%d", before), "")
		bs := out["bookmarks"].([]any)
		seen += len(bs)
		pages++
		next := int64(out["next"].(float64))
		if next == 0 {
			break
		}
		before = next
	}
	t.Logf("keyset walk: %d bookmarks in %d pages", seen, pages)
	_, out := call(t, h, "GET", "/bookmarks?q=item%205", "")
	t.Logf("search 'item 5': %d match", len(out["bookmarks"].([]any)))
}

func TestDelete(t *testing.T) {
	h, _ := newAPI(t)
	call(t, h, "POST", "/bookmarks", `{"url":"https://a.example","title":"A","tags":["x"]}`)
	code, _ := call(t, h, "DELETE", "/bookmarks/1", "")
	t.Logf("delete: %d", code)
	code, _ = call(t, h, "DELETE", "/bookmarks/1", "")
	t.Logf("delete again: %d", code)
}

Text

=== RUN   TestCreateReadAndConflict
    api_test.go:80: create: 201 tags=[docs lang]
    api_test.go:82: same url again: 409
    api_test.go:84: empty url: 422
    api_test.go:86: get: 200 Go
    api_test.go:88: get missing: 404
--- PASS: TestCreateReadAndConflict (0.10s)
=== RUN   TestListIsTwoStatements
    api_test.go:99: list limit=5: 200, 5 rows, 2 statements
    api_test.go:99: list limit=25: 200, 25 rows, 2 statements
--- PASS: TestListIsTwoStatements (0.10s)
=== RUN   TestKeysetAndSearch
    api_test.go:123: keyset walk: 7 bookmarks in 3 pages
    api_test.go:125: search 'item 5': 1 match
--- PASS: TestKeysetAndSearch (0.06s)
=== RUN   TestDelete
    api_test.go:132: delete: 204
    api_test.go:134: delete again: 404
--- PASS: TestDelete (0.05s)
PASS
ok  	example.test/lab	1.647s

Shell

set -e
~/go/bin/sqlc generate
go mod tidy > /dev/null 2>&1
go test -count=1 -race -v . 2>&1 | grep -vE "^(=== |--- PASS: Test.*/)"

Text

    api_test.go:80: create: 201 tags=[docs lang]
    api_test.go:82: same url again: 409
    api_test.go:84: empty url: 422
    api_test.go:86: get: 200 Go
    api_test.go:88: get missing: 404
--- PASS: TestCreateReadAndConflict (0.07s)
    api_test.go:99: list limit=5: 200, 5 rows, 2 statements
    api_test.go:99: list limit=25: 200, 25 rows, 2 statements
--- PASS: TestListIsTwoStatements (0.11s)
    api_test.go:123: keyset walk: 7 bookmarks in 3 pages
    api_test.go:125: search 'item 5': 1 match
--- PASS: TestKeysetAndSearch (0.06s)
    api_test.go:132: delete: 204
    api_test.go:134: delete again: 404
--- PASS: TestDelete (0.05s)
PASS
ok  	example.test/lab	1.688s

What the run proved

Create and conflict. POST with tags Lang, docs, lang returned 201 and tags=[docs lang]: the duplicate was merged by the upsert and the link's on conflict do nothing, and the tags came back sorted by the query's order by. Posting the same URL again returned 409. An empty URL returned 422 from the handler's check. GET /bookmarks/1 returned 200 with the title, and /bookmarks/99 returned 404.

Statement count. After creating 25 bookmarks with tags, listing 5 and listing 25 each ran 2 statements. The test fails if the count differs from 2. A handler that loaded tags per bookmark would run 6 and 26.

Keyset paging. With limit=3, seven bookmarks came back in 3 pages. The walk ends when next is 0. The search q=item 5 returned 1 match: the optional filter worked with a real term and, in the other tests, with none.

Delete. DELETE /bookmarks/1 returned 204, and the second call 404 because :execrows returned 0. The first call removed the link rows before the bookmark in one transaction, since the join table's foreign key would otherwise block the delete.

Choices made on purpose

Pagination on id. Ids grow with time on a bigserial, so ordering by id is ordering by creation, and a single unique column makes the cursor trivial. Order by a non unique column and the cursor needs two values.
No ORM-style associations. The tags for a page are a separate query that the handler assembles. It is more code than a Preload call, and every query that runs is a query you can read.
SQL does the lowercasing? No: the handler lowercases tags in Go before the upsert. If tags must be case insensitive in every writer, make the column citext or add a check constraint.
A unique URL. The database enforces it, so two simultaneous saves of the same URL cannot both succeed.

The same API in three libraries

This is the third version of a small API in this cluster, and the three are worth reading side by side because the differences are structural.

	sqlc	GORM (blog backend)	Ent (tasks API)
Source of truth	SQL files	Go structs	Go schema
Query code	generated from your SQL	built at run time	generated builders
Loading children	one `any($1)` query, assembled by you	`Preload`	`WithX()`
Partial update	a query per shape, or `coalesce`	`Updates` with a map	`SetNillableX`
Mistake found	at `sqlc generate`	at run time	at compile time
Statements per list	2, by construction	3, by `Joins` plus `Preload`	2, by `WithX`

The sqlc version is the most code in the handler (the withTags function) and the least magic: the two statements for the list are the two queries you wrote, and nothing else can run. The GORM and Ent versions hide that behind a library call that is shorter and less obvious. Neither is better in the abstract. A team that wants to read every statement picks the first; a team that wants less code picks one of the others.

Adding ownership

Real bookmark services belong to users. In an SQL-first design the change is visible everywhere it matters. Add user_id bigint not null to bookmarks, put where user_id = $n into GetBookmark, ListBookmarks, DeleteBookmark and TagsForBookmarks (through a join), and add the parameter to CreateBookmark. Then run sqlc generate: every call site that does not pass the new parameter stops compiling. That is the generator's best trick. The compiler lists every handler that must change, and a forgotten filter is a build error and not a data leak. Add a unique index on (user_id, url) so two users can save the same page, and put user_id first in the composite indexes the listing uses.

For defence in depth, add PostgreSQL row level security keyed on a session setting and run the handlers as a non owner role (GORM multi tenant patterns runs the experiment). The generated code does not change: the rules live below it.

Partial updates

The API has no PATCH, because partial updates are where SQL first design shows its cost. A PATCH that sets only the fields present can be written as one query with coalesce: update bookmarks set title = coalesce(sqlc.narg('title'), title) where id = $1 returning .... That works for nullable inputs and cannot distinguish "leave it" from "set it to null", which matters for optional columns. The alternative is one query per combination of fields, which is fine for two fields and silly for ten. A third path builds the statement at run time with a query builder for just this endpoint (dynamic SQL builders), keeping sqlc for everything else. Decide per endpoint; mixing is normal.

Performance and the plan

The two statements per list are cheap if the indexes exist. bookmarks.id has the primary key for the keyset condition and the ordering. The title search uses ilike '%term%', which cannot use a normal index and scans the table, so for more than a few thousand rows add a trigram index (pg_trgm with a GIN index on title) or move to full text search (PostgreSQL full text search). The tag lookup uses the join table's primary key (bookmark_id, tag_id), which serves the any($1) query by its first column. Check each with EXPLAIN before shipping (the guide).

One caution on the optional filter. A query written as ($1 = 0 or id < $1) and (... is null or ...) is easy to read and gives the planner a harder job than two simple queries, because the generic plan must cope with every combination. For a hot endpoint with a large table, measure it, and consider two named queries (with and without the search) chosen in Go.

What to add before production

Authentication and per user ownership (a user_id column and a where user_id = $n in every query), request size limits, stricter JSON decoding, a pool sized for your database (pool settings covers the arithmetic), structured logging with the route pattern, sqlc vet and sqlc diff in CI (vet), and versioned migrations instead of loading schema.sql. And a request timeout: pass r.Context() to every query, as the handlers do, and add middleware.Timeout so a slow query is cancelled.

Running it by hand

Start the server with go run . against the lab database and exercise it with curl: post a bookmark with a few tags, list the newest ones, search by a title fragment, then delete it. Compare the JSON that comes back with the shapes the tests assert, and watch the statement log if you enable pgx's tracer: a list request is two statements, a create is a transaction with one insert and two statements per tag. Reading that sequence once is the quickest way to trust the handlers.

Checklist

Write the SQL first and let sqlc generate the Go.
Name parameters with sqlc.arg and make optional ones sqlc.narg.
Load child rows for a whole page with one any($1) query.
Use WithTx for writes that span tables, and defer tx.Rollback.
Decorate DBTX to count statements in tests, and assert the count.
Map pgx.ErrNoRows and SQLSTATE 23505 in one fail function.
Page with a keyset cursor.

The tool is documented at docs.sqlc.dev, and the router in the chi repository.

Also inAPIs Chi

Taggedproject

Build a REST API in Go With sqlc Chi and Postgres Together

What it does

Step 1: schema and queries

Step 2: the handlers

Step 3: main

Step 4: the tests

What the run proved

Choices made on purpose

The same API in three libraries

Adding ownership

Partial updates

Performance and the plan

What to add before production

Running it by hand

Checklist

Keep reading

Ent Schema Fields Edges and Indexes Explained in Detail

GORM Hooks and Callbacks and When You Should Avoid Them

Ent Testing With Enttest and Real Databases in Go Tests

Get new articles by email