# Add Query Timeouts to a sqlc Project

Yes. An agent sets a statement timeout on the pgx pool behind your sqlc queries, bounds each transaction with a deadline, proves both with pg_sleep, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-query-timeouts/sqlc/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent starts with how the pool is opened. In a service laid out like techschool/simplebank, `main.go` calls `pgxpool.New(ctx, config.DBSource)` with the URL from `app.env` and nothing else, hands the pool to `db.NewStore`, and `store.go` wraps it in `SQLStore` with the sqlc-generated `Queries`; `sqlc.yaml` generates for `pgx/v5` with `emit_interface` on. `exec_tx.go` begins a transaction with `store.connPool.Begin(ctx)`, runs the callback, and commits or rolls back; `TransferTx` in `tx_transfer.go` runs five statements inside it, the last two `AddAccountBalance` updates ordered by account id to avoid deadlocks. Every query takes the `ctx` the gRPC or gateway handler passed in, and the asynq task processor passes its own, but no deadline is set on any of them and no `statement_timeout` is sent to Postgres, so a lock wait or a slow plan holds a connection until the client gives up. `db/sqlc/main_test.go` opens the same pool for the tests against the Postgres the `test.yml` workflow starts.

It sets the limit at the pool, once. `main.go` and `main_test.go` move from `pgxpool.New` to `pgxpool.ParseConfig`, set `ConnConfig.RuntimeParams["statement_timeout"]` from a new `DB_STATEMENT_TIMEOUT` in `app.env` and `util.Config`, and open with `pgxpool.NewWithConfig`. That makes Postgres itself cancel any statement that runs longer than the limit, on every connection the pool hands out, with the SQLSTATE `57014` that pgx surfaces as a `*pgconn.PgError`. The value is a `time.Duration` like the token durations already in `Config`, formatted to milliseconds for the parameter.

Transactions get a deadline as a whole. `execTx` wraps its `ctx` in `context.WithTimeout` from a second setting, `DB_TX_TIMEOUT`, so the five statements of a transfer share one budget rather than each getting the full statement timeout; on expiry pgx cancels the in-flight statement and the existing rollback path runs. `db/sqlc/error.go` gains an `ErrorCode` case for `57014` beside the unique-violation and foreign-key codes it already names, and `gapi/error.go` maps it to `codes.DeadlineExceeded` so a client sees a timeout rather than an internal error. The sqlc-generated files are not edited.

In its sandbox the agent starts Postgres with `make postgres`, `make createdb` and `make migrateup`, then adds `TestStatementTimeout` to `db/sqlc/store_test.go`: it opens a pool with a 200 ms timeout, runs `SELECT pg_sleep(1)` through `connPool.Exec`, and asserts the `PgError` code is `57014`; a second test runs `TransferTx` with a 100 ms context against an account row another transaction holds locked and asserts the error and that the balances did not change. It runs `make test`, which is `go test -v -cover -short ./...`, and `make sqlc` to show the generated code is unchanged.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add query timeouts to this sqlc project. Read main.go, util/config.go, app.env, db/sqlc/store.go, db/sqlc/exec_tx.go, db/sqlc/tx_transfer.go, db/sqlc/error.go, db/sqlc/main_test.go, gapi/error.go and sqlc.yaml first. Add DB_STATEMENT_TIMEOUT and DB_TX_TIMEOUT to app.env and Config as durations. In main.go and main_test.go switch to pgxpool.ParseConfig and NewWithConfig and set statement_timeout in RuntimeParams from the first setting. In execTx wrap the context with context.WithTimeout from the second. Add the 57014 code to error.go and map it to codes.DeadlineExceeded in gapi/error.go. Do not edit sqlc-generated files. Start Postgres with the Makefile targets, write TestStatementTimeout using pg_sleep and a TransferTx test against a locked row, run make test and make sqlc, and quote the results 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

What comes back is a pull request touching `main.go`, `util/config.go`, `app.env`, `db/sqlc/exec_tx.go`, `db/sqlc/error.go`, `db/sqlc/main_test.go`, `db/sqlc/store_test.go` and `gapi/error.go`. The description quotes the `make test` output with the two new tests and the package coverage lines, the `make sqlc` run showing no diff under `db/sqlc/`, and the exact `PgError` message Postgres returned for the cancelled `pg_sleep`.

The run's record keeps the Postgres container's log, where the `canceling statement due to statement timeout` line appears at the moment the test expected it, and the migration run from `make migrateup`. A reviewer does not have to take the error code on faith.

Before merging, a reviewer picks the two durations for production. A 5 s statement timeout and a 10 s transaction timeout suit this schema; a service with a long reporting query sets a longer value on that one call through a context deadline rather than raising the pool-wide limit.

## Run it on every database alert

This is the job to run when the monitor reports connections piling up or a lock wait that never ends. In the Project for the repository, an automation with the prompt above and a `webhook` trigger takes the alert body from Grafana or the database provider; the handler sends the trigger's URL an `Idempotency-Key` made from the alert id, so a re-fired alert does not start a second run. The trigger adds the payload after the prompt, so the run reads which query or table the alert named.

The first run adds the timeouts. A later run on a repository that already has them reads the alert, reproduces the slow statement in the sandbox, and opens a pull request that gives that one call its own deadline or an index, each on its own branch and linked to the alert.

## Why run it on Rush?

The timeout is one runtime parameter and one `WithTimeout`, and the work is in proving they fire: a `statement_timeout` set on the wrong config object applies to nothing, a context deadline on a transaction that is already past `Begin` leaves the rollback path untested, and the error code that comes back has to be the one the gRPC layer maps. In a Rush sandbox the agent runs real Postgres, makes a statement sleep past the limit, holds a lock against a transfer, and quotes what came back.

The container log and the tests stay with the run, so the next person who raises the limit for one slow report can read what the default protects. Across many services on the same sqlc layout the job runs in parallel, one sandbox each, with the Postgres error line as the evidence in every pull request.

## Frequently asked questions

### Why set statement_timeout on the pool instead of a context per query?

Because a context deadline cancels on the client side and the server may keep running the statement until it notices; `statement_timeout` makes Postgres stop it. The pool setting covers every sqlc query at once, and a context deadline is still used for the transaction as a whole.

### Does this change the generated sqlc code?

No. The generated `Queries` already take a `context.Context` on every method, and the timeout is applied by the pool and by `execTx`, both hand-written. `make sqlc` in the sandbox shows no diff under `db/sqlc/`.

### What does a client see when a transfer times out?

A gRPC `DeadlineExceeded` from the mapping in `gapi/error.go`, and through the gateway an HTTP 504. The transaction is rolled back by the existing path in `execTx`, and the test asserts both account balances are unchanged.

### Will the asynq worker's tasks be affected?

Yes, in the same way: the processor's queries go through the same pool and get the same statement timeout. A task that legitimately needs longer passes a context with its own deadline, which the pool setting does not override.

### Can one query get a longer limit?

Yes. `SET LOCAL statement_timeout` inside a transaction raises it for that transaction only; say which query in the prompt and the agent adds it in a hand-written transaction wrapper beside `TransferTx` rather than in the generated code.

## More sqlc jobs

- [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/)
- [Check sqlc Generated Code in CI With sqlc diff and sqlc vet](https://getrush.ai/run/add-codegen-check-to-ci/sqlc/)

## The same job on other stacks

- [Add Query Timeouts to a TypeORM + NestJS App](https://getrush.ai/run/add-query-timeouts/typeorm/)
- [Add Query Timeouts to a Drizzle and postgres.js Setup in Next.js](https://getrush.ai/run/add-query-timeouts/drizzle/)
- [Add Query Timeouts to a GORM App](https://getrush.ai/run/add-query-timeouts/gorm/)

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