# 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 Rush](https://cloud.getrush.ai/run/add-optional-filters/sqlc/). Opens 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`

```json
{
  "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.

## 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

- [Add Query Timeouts to a sqlc Project](https://getrush.ai/run/add-query-timeouts/sqlc/)
- [Add Soft Delete to a sqlc Project](https://getrush.ai/run/add-soft-delete/sqlc/)
- [Add Bulk Inserts to a sqlc and pgx Project](https://getrush.ai/run/add-bulk-inserts/sqlc/)

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

- [Email Customers When an Order Status Changes in a Filament Shop](https://getrush.ai/run/add-order-status-emails/filament/)
- [Add Order Webhooks to a Shopify App](https://getrush.ai/run/add-order-webhooks/shopify-app/)
- [Add Output Channel Logging to a VS Code Extension](https://getrush.ai/run/add-output-channel-logging/vscode-extension/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
