All jobs

Add Optional Query Filters to a sqlc and Go API

Yes. An agent adds an optional filter to a sqlc query with sqlc.narg, regenerates the Go code and mocks, updates the handler and tests, and opens a pull request.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

sqlc has no query builder, so an optional filter has to be written into the SQL itself, and the agent begins by reading how this repository already does that. In a service like techschool/simplebank, db/query/user.sql uses the pattern for partial updates: COALESCE(sqlc.narg(full_name), full_name) and four more like it in UpdateUser. The list queries do not: ListAccounts in db/query/account.sql is WHERE owner = $1 ORDER BY id LIMIT $2 OFFSET $3, and the handler in api/account.go binds only page_id and page_size from the query string. sqlc.yaml generates into db/sqlc with sql_package: "pgx/v5" and emit_interface: true, the generated files were written by sqlc v1.22.0, and api/account_test.go drives the handler through a gomock MockStore with gomock.Eq on the exact db.ListAccountsParams value.

For a request such as filtering GET /accounts by currency, the agent rewrites the query with named parameters and one nullable one: the owner, then (sqlc.narg(currency)::varchar IS NULL OR currency = sqlc.narg(currency)), then the limit and offset. The cast is not decoration. Written without it, sqlc 1.22.0 prints unsupported reference type, exits 0, and generates a ListAccountsParams with no Currency field at all while the SQL still refers to the parameter; PostgreSQL, asked to prepare that statement, answers that it could not determine the parameter's data type. With the cast the struct gains Currency pgtype.Text, the nullable text type of the pgx v5 driver this project generates for. The agent runs make sqlc with the same sqlc version as the committed headers, so the diff in db/sqlc is the query, the struct and the call, not a version bump across every file.

Changing a generated struct can ripple outward, so the agent checks how far this one goes. The method keeps its name and its signature, so querier.go and the gomock MockStore in db/mock/store.go are unaffected, and db/sqlc/account.sql.go is the only generated file that changes. The agent does not regenerate the mock: a fresh make mock with the mockgen v1.6.0 the README installs would rewrite 51 comment lines that the committed file got from an older mockgen, which is noise in a pull request about a filter. In api/account.go it adds Currency string to listAccountRequest with form:"currency" and binding:"omitempty,currency", reusing the currency validator that api/validator.go defines and api/server.go registers, so an unsupported code is a 400 before any query runs. The handler builds pgtype.Text{String: req.Currency, Valid: req.Currency != ""}. The existing cases in TestListAccountsAPI build the params struct by field name, so they compile and pass as they are: a request without the parameter produces a pgtype.Text that is not valid, and the gomock.Eq expectation still matches. New cases cover a supported currency reaching the store as a valid value and an unsupported one returning 400 with the store never called.

The database tests need PostgreSQL. db/sqlc/main_test.go connects to the DB_SOURCE in app.env, user root, database simple_bank, and the Makefile's postgres target assumes Docker, which the sandbox does not have. The agent installs PostgreSQL from the system packages, creates that role and database, applies db/migration with golang-migrate as make migrateup does, and extends TestListAccounts in db/sqlc/account_test.go: accounts in two currencies for one owner, a filtered call returning only the matching one, an unfiltered call returning both. It runs make test. While checking the feature the agent also reads 000002_add_users.up.sql, which adds a unique constraint on owner and currency, and util/currency.go, which lists three currencies. An owner can therefore hold at most three accounts, and the pull request says plainly that the filter is correct but small in effect on this table.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add an optional currency filter to GET /accounts in this Go service, which generates its database code with sqlc. Read sqlc.yaml, db/query/account.sql, db/query/user.sql, the header of the generated files in db/sqlc, db/sqlc/main_test.go, db/sqlc/account_test.go, db/mock/store.go, api/account.go, api/account_test.go, api/validator.go, api/server.go, app.env, Makefile and the migrations first. Change ListAccounts to use sqlc.arg for owner, limit and offset and sqlc.narg for currency, so that a null currency returns every account of the owner; cast the nullable parameter so PostgreSQL can infer its type, and check the generated ListAccountsParams actually contains the field. Regenerate with the same sqlc version as the committed headers (make sqlc). Check whether the Querier interface changed; regenerate the mocks only if it did, and keep unrelated mock differences out of the diff. In the handler, accept an optional currency query parameter validated with the existing currency validator, and pass it as a nullable value. Keep the existing handler tests passing and add cases for a valid currency, an invalid one (400, store not called) and none. There is no Docker here: install PostgreSQL from the system packages, create the role and database from app.env, apply db/migration with golang-migrate, and extend the query test to cover filtered and unfiltered calls. Run make test. If the schema limits how useful the filter is, say so in the pull request. Open a pull request.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

A pull request touching one query file, one generated file (db/sqlc/account.sql.go), the handler and the two test files. The description shows the SQL before and after and the generated struct, and explains the cast with the output sqlc gives without it. It carries the make test output from the sandbox's PostgreSQL, names the sqlc version used, says why the mock file was left alone, and includes the note about the owner and currency constraint.

A reviewer checks that the generated diff is confined to ListAccounts in one file. If every file in db/sqlc changed, the wrong sqlc version was used, and the description's version line is there to rule that out. They check the three meanings of the parameter: absent means no filter, a supported code filters, and an unsupported code is rejected by the validator instead of quietly returning an empty list. They also look at how the handler builds the nullable value, since a pgtype.Text with Valid true and an empty string would match nothing.

Run it on every Linear issue labelled api-filter

Filter requests arrive as a stream of small tickets: by status, by date range, by owner. In the console, add an automation on the Project that holds the service with a linear trigger on linear.issue.labeled, the label api-filter, and this prompt generalised to the endpoint and column the issue names. When a person in your Rush org applies the label, the issue is added after the prompt and the run opens one pull request for that filter. The automation's runner needs a Linear connection, which is made in the console under Settings → Connections.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

Why run it on Rush?

The failure this job is prone to is silent. sqlc exits 0 on the uncast parameter, the Go code compiles, and nothing complains until the query runs against a database. The agent has one: a PostgreSQL in its own sandbox with the repository's migrations applied, where the generated code is exercised by the query tests and not just built. The run keeps each command and its output, so the pull request can show the test that calls the filtered query and passes.

It also holds the toolchain steady. Generated code is only reviewable when it was produced by the versions the repository already uses, and a developer's machine tends to have whatever sqlc was installed last. The sandbox starts empty, the agent installs the versions it read from the files, and the diff a reviewer sees is the change they asked for.

Frequently asked questions

Why sqlc.narg and not sqlc.arg?

sqlc.arg gives a parameter a name and keeps the nullability sqlc infers from the column, which for a NOT NULL column is a plain Go string. sqlc.narg marks it nullable, so the generated field can carry "no value". Without it there is no way to tell an absent filter from an empty one.

Why does the nullable parameter need a cast?

In $2 IS NULL nothing tells the parser what type $2 is. On sqlc 1.22.0 with the pgx v5 package, leaving the cast out produced a warning, a zero exit code and a params struct without the field, and PostgreSQL refused to prepare the statement. Casting the first use to the column's type fixes both.

Does adding a filter change the existing ListAccounts callers?

The params struct gains a field, and Go's zero value for pgtype.Text is not valid, which the query treats as no filter. Callers that build the struct by field name keep compiling and behave as before. The existing mock expectations, which compare the whole struct, still match, because a request without the parameter produces that same zero value.

More sqlc jobs

More jobs like this: Turn a Linear label into a PR

Keep reading