An earlier post, Modern API Development with TypeSpec and OpenAPI
, covered generating a typed HTTP boundary from a TypeSpec schema: OpenAPI in the middle, oapi-codegen
on the Go backend, and a typed client on the frontend. That post stopped at the handler. The handlers had generated request and response types, but the other side of them was still hand-rolled. In the example, the search model came from a models package that someone had to write and keep aligned with the database by hand.
This post closes that gap. The data store gets the same treatment as the API surface: the schema and queries are written in SQL, and sqlc generates the Go types and functions to read and write them. Between the two generated edges sits the business logic, written against clear definitions of what enters and leaves the system on both sides.
Two Generated Boundaries
Most service code is plumbing. At the HTTP edge, you parse JSON, check required fields, validate formats, and marshal responses. At the data edge, you write the same structs a second time, then fill them with rows from a scanner and translate them back into query parameters. The actual decisions, the part you were hired to write, are a thin layer in between.
The approach here is to stop writing both edges by hand. TypeSpec describes what enters and leaves the process over HTTP. SQL describes what enters and leaves the database. Both get compiled into Go types, and the business logic in the middle is written against those two sets of types.
.
├── backend
│ ├── db
│ │ ├── db.go
│ │ ├── models.go
│ │ └── query.sql.go
│ ├── generated
│ │ └── server.go
│ ├── handlers
│ │ └── search.go
│ ├── query.sql
│ ├── schema.sql
│ └── sqlc.yaml
├── docs
├── frontend
└── schema
The generated directory comes from oapi-codegen, the db directory from sqlc, and handlers is the only code in this picture that a person writes day to day.
The Data Edge with sqlc
sqlc takes your SQL schema and your SQL queries and generates Go code for the exact shapes they describe. The queries are the source of truth. There is no query builder and no mapping annotations. If the SQL is valid against the schema, sqlc emits structs and functions for it. If a column or parameter does not line up, sqlc generate fails before the compiler ever sees it.
The configuration is small:
version: "2"
sql:
- engine: "postgresql"
schema: "schema.sql"
queries: "query.sql"
gen:
go:
package: "db"
out: "db"
sql_package: "pgx/v5"
emit_pointers_for_null_types: true
The schema is plain SQL:
CREATE TABLE saved_searches (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL,
name TEXT NOT NULL,
companies TEXT[],
countries TEXT[],
states TEXT[],
cities TEXT[],
title TEXT,
subscribed BOOLEAN NOT NULL DEFAULT false,
create_date TIMESTAMPTZ NOT NULL DEFAULT now()
);
The queries are annotated SQL. The :one, :many, and :exec comments decide what each generated function returns:
-- name: GetSearch :one
SELECT * FROM saved_searches
WHERE email = $1 AND name = $2
LIMIT 1;
-- name: ListSearches :many
SELECT * FROM saved_searches
WHERE email = $1
ORDER BY create_date DESC;
-- name: SaveSearch :one
INSERT INTO saved_searches (
email, name, companies, countries, states, cities, title, subscribed
) VALUES (
$1, $2, $3, $4, $5, $6, $7, $8
)
RETURNING *;
Running sqlc generate produces the models and the query methods:
type SavedSearch struct {
ID int64
Email string
Name string
Companies []string
Countries []string
States []string
Cities []string
Title *string
Subscribed bool
CreateDate time.Time
}
The type mapping is the useful part. TEXT becomes string, nullable TEXT becomes *string because of emit_pointers_for_null_types, TEXT[] becomes []string, and TIMESTAMPTZ becomes time.Time. GetSearch, ListSearches, and SaveSearch come out as methods with parameter structs, so the call sites are typed rather than positional.
The HTTP Edge
The other edge is the one from that earlier post. TypeSpec compiles to OpenAPI, and oapi-codegen turns that into server.go with the request and response models and strict handler interfaces. Incoming JSON is parsed and validated before a handler runs. Responses have to match the schema or the code does not compile.
That leaves the handler as the seam between two generated type systems.
The Filling
Here is the read path, from HTTP request to database row to HTTP response:
func (s *Server) SearchGetSearch(ctx context.Context, request generated.SearchGetSearchRequestObject) (generated.SearchGetSearchResponseObject, error) {
user := helpers.UserContextFromContext(ctx)
search, err := s.db.GetSearch(ctx, db.GetSearchParams{
Email: user.Email,
Name: request.Name,
})
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return generated.SearchGetSearch200JSONResponse{
StatusCode: 404,
Error: helpers.SearchNotFoundError,
Data: generated.SearchModelSearch{},
}, nil
}
return nil, err
}
return generated.SearchGetSearch200JSONResponse{
StatusCode: 200,
Data: generated.SearchModelSearch{
Name: search.Name,
Companies: helpers.Ptr(search.Companies),
Countries: helpers.Ptr(search.Countries),
States: helpers.Ptr(search.States),
Cities: helpers.Ptr(search.Cities),
Title: search.Title,
Subscribed: search.Subscribed,
CreateDate: search.CreateDate.UTC().Format(time.RFC3339),
},
Error: generated.DefaultResponseError{},
}, nil
}
The write path is the mirror image:
func (s *Server) SearchSaveSearch(ctx context.Context, request generated.SearchSaveSearchRequestObject) (generated.SearchSaveSearchResponseObject, error) {
user := helpers.UserContextFromContext(ctx)
body := request.Body
_, err := s.db.SaveSearch(ctx, db.SaveSearchParams{
Email: user.Email,
Name: body.Name,
Companies: helpers.Slice(body.Companies),
Countries: helpers.Slice(body.Countries),
States: helpers.Slice(body.States),
Cities: helpers.Slice(body.Cities),
Title: body.Title,
Subscribed: body.Subscribed,
})
if err != nil {
return nil, err
}
return generated.SearchSaveSearch200JSONResponse{
StatusCode: 200,
Data: true,
Error: generated.DefaultResponseError{},
}, nil
}
The two type systems do not always spell things the same way. The API model says an optional array, which oapi-codegen renders as *[]string. The database model says an array column, which sqlc renders as []string. The API model says createDate is a string; the database model says time.Time. Those differences are the mapping, and the mapping is the one place they should exist. Two small helpers absorb the repetition:
func Ptr[T any](v T) *T { return &v }
func Slice[T any](p *[]T) []T {
if p == nil {
return nil
}
return *p
}
Everything else in those handlers is a decision: a missing row becomes a 404, a write error propagates, and the response shape is whatever the schema allows.
What This Buys You
The plumbing that used to live in every handler is gone: rows.Next() loops, Scan calls, struct tags kept in sync by memory, hand-written JSON validation. The handlers read like a translation between two well-defined vocabularies.
The bigger payoff shows up when something changes. Rename a field in TypeSpec, regenerate the server, and every handler that touches it stops compiling until it is fixed. Add a column to a table, update the query, run sqlc generate, and the compiler points at the call sites that need the new value. Both directions fail at build time instead of in production.
The middle also stays small because the edges are honest. The handler does not need to defend against malformed input, because validation already happened. Remembering which columns exist is not its job either, since the query method takes a typed params struct. What is left is the actual work: “look this up, or return 404 if it is not there.”
AI in the Middle
Generated edges change what it means to ask an AI for code. The assistant fills a handler whose input and output types already exist. It cannot invent a request shape or a database model, because both come from generated files. The task shrinks to the mapping and the decisions, and the compiler checks the result.
TDD fits that shape well. Write the test first, then the handler. The test drives the business logic through the handler with a fake or a real database, and the assertions are typed. A failing test says which behavior is missing. A passing test plus a green build says the types line up.
The rest of the loop runs locally in seconds: go build ./... (and go vet ./...), go test ./..., golangci-lint
run, and nilaway
./.... Each reports a file and a line, which is the feedback an AI agent can act on. nilaway is useful here because the generated models contain pointers (*string for nullable columns), and a handler that dereferences one without checking gets flagged before it runs. The Ptr and Slice helpers keep those pointers contained, and nilaway enforces that containment.
What makes an AI productive in this setup is the same thing that makes a person productive: a small surface, typed on both sides, with a compiler and a linter watching the seams.
Where It Stops Being Free
sqlc is SQL-first, and that is the point and the limit. If you need a query built dynamically from an unknown set of filters, you either write several SQL variants or reach for a query builder alongside the generated code. sqlc will not compose queries for you the way an ORM would.
Nullability also deserves a decision up front. The default for a nullable column under pgx/v5 is a pgtype type, which is correct but awkward to pass around. Setting emit_pointers_for_null_types gives you *string and friends instead, and that happens to line up with how oapi-codegen spells optional fields. Configure it before the first migration, not after fifty handlers exist.
There is also a regenerate step in the loop. It is fast, but it is a step, and it means the generated files are build artifacts, not places to patch by hand. Treat them that way.
The value of the sandwich is that both edges of the system are described in files a code generator understands. TypeSpec describes the conversation with the outside world, SQL describes the conversation with the database, and the Go compiler checks the middle. When a field is renamed on either side, the next build fails in the handler that maps between them, which is exactly where the failure belongs.