# Add Cursor Pagination to a Drizzle API

Yes. An agent pages a Drizzle list query by keyset on a unique column, validates the cursor, updates the OpenAPI schema and tests a full walk through the rows.

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

## What the agent does

Cursor pagination stands or falls on the column the cursor is made from, so the agent reads the table before the handler. In an API like w3cj/hono-open-api-starter, `list` in `src/routes/tasks/tasks.handlers.ts` is one line, `db.query.tasks.findMany()`, and returns every row. The `tasks` table in `src/db/schema.ts` offers two candidates. `createdAt` is `integer({ mode: "timestamp" })`, which stores whole seconds, and it has no `notNull()`, so two tasks created in the same second tie and a row can have no value at all. `id` is an autoincrement primary key: unique, never null and already indexed. The agent pages on `id` and says why in the pull request, including what ordering by date would need in addition, a tiebreak on `id` and a composite index with its migration from `pnpm drizzle-kit generate`.

The query becomes a keyset query. With a cursor, the handler adds a `where` that compares `tasks.id` against the decoded value, orders by `id`, and asks for one row more than the page size; if that extra row comes back there is a next page, and the last row actually returned supplies the next cursor. The cursor is the id encoded as an opaque string, so callers do not start doing arithmetic on it. No offset appears anywhere, which is the point: a row inserted or deleted while a client is paging cannot shift the rows that follow.

This starter generates its API documentation from the route definitions, so the contract changes in `tasks.routes.ts` before the handler compiles. The agent adds a `request.query` schema to the `list` route with an optional `cursor` and a `limit` that has a default and an upper bound, and changes the 200 response from `z.array(selectTasksSchema)` to an object holding the items and an optional next cursor. A malformed cursor or an out-of-range limit is refused with the same 422 validation body the other routes produce. That response change breaks existing callers, and the codebase makes it visible: `src/app.ts` exports `AppType` for typed clients and the tests call the route through Hono's `testClient`, so every consumer of the old array shape fails to compile until it is updated. The agent says so at the top of the description and offers the alternative of keeping the array and returning the cursor in a `Link` header.

The tests run against a real SQLite file, as the existing ones do: `tasks.test.ts` pushes the schema with `pnpm drizzle-kit push` in `beforeAll` and removes `test.db` afterwards. The agent updates `get /tasks lists all tasks`, which asserts an array of length one, and adds cases that insert more rows than a page holds and walk the cursors to the end, asserting each id appears once and in order. It then inserts a row and deletes another between two page requests and checks nothing is repeated or skipped. Two more cases send a tampered cursor and an oversized limit and expect 422. It runs `pnpm typecheck`, `pnpm lint` and `pnpm test`, starts `pnpm dev`, follows the cursors with `curl` over seeded rows, and fetches `/doc` to show the new parameters in the generated OpenAPI document.

## Start it with one API call

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

```json
{
  "agent": "codex",
  "input": "A GitHub issue labelled api says GET /tasks in this Hono and Drizzle API returns every row and needs cursor pagination. Read package.json, src/db/schema.ts, src/db/index.ts, src/db/migrations, src/app.ts, src/lib/create-app.ts, src/lib/constants.ts and every file under src/routes/tasks first. Choose the cursor column from the schema and explain the choice in the pull request: for each candidate say whether it is unique, nullable and indexed, and what precision a timestamp is stored at. Implement keyset pagination with no offset: an optional cursor and a limit with a default and a maximum in the route's query schema, a where clause on the cursor column, an explicit order, and one extra row fetched to detect the next page. Encode the cursor as an opaque string. Change the 200 response in tasks.routes.ts to an object with the items and an optional next cursor, and refuse a malformed cursor or an out-of-range limit with the same 422 body other validation errors use. State at the top of the pull request that the response shape is a breaking change, list what in the repository consumes it, and describe the Link header alternative. Add a migration only if the chosen order needs a new index. Update the existing list test and add tests against the real test database: a full walk over more rows than one page, an insert and a delete between pages, a tampered cursor and an oversized limit. Run pnpm typecheck, pnpm lint and pnpm test, start the server, follow the cursors with curl over seeded rows, and fetch /doc to show the new parameters. Open a pull request that closes the issue.",
  "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

The pull request changes `tasks.routes.ts`, `tasks.handlers.ts` and `tasks.test.ts`, and adds a migration only when the chosen order needs an index. Its first line is the breaking change: `GET /tasks` now answers an object, with the old and new shapes shown next to each other. Then comes the column table the agent built from the schema, giving uniqueness, nullability, index and stored precision for each candidate, which is the reasoning behind the cursor.

The evidence section has the typecheck, lint and vitest output, and the `curl` walk from the running server: each request with its cursor, the ids on each page and the final response that carries no next cursor. An excerpt of `/doc` shows the `cursor` and `limit` parameters and the new response schema as the documentation page at `/reference` will render them.

A reviewer decides the two things the code cannot: whether clients can absorb the new response shape now or need the header variant first, and whether the page-size bounds fit real usage. They also read the walk test, since it is the one that fails if someone later swaps the keyset query back to an offset.

## Run it on every GitHub issue labelled api

List endpoints get paginated one at a time, usually after one of them times out. Add an automation to the Project in the console with a `github` trigger on `issues.labeled` and `api` in its `labels`. The saved prompt keeps the method and drops the specifics: choose the cursor column from the schema with reasons, page by keyset, validate the inputs, update the generated contract, and test a full walk. The issue is placed after the prompt and names the endpoint.

A `github` trigger fires only for work by an owner, member or collaborator of the repository, and never for a bot sender, so an outside account cannot start runs by labelling issues. If two endpoints are labelled in the same minute, each gets its own run on its own branch, so the two pull requests can be reviewed and merged independently.

## Why run it on Rush?

The claim a pagination change makes is that a client walking the pages sees every row once. Proving it takes a database with more rows than a page, writes happening between requests, and a running server to walk. The agent does all three in its sandbox against the project's own SQLite setup and keeps the requests and responses in the run's record. The pull request shows the walk; a reviewer does not have to seed a database to believe it.

It also surfaces the breaking change before a customer does. Because the agent runs the type checker and the typed test client in the sandbox, the places that depended on the array shape appear as compile errors in its transcript, and the description lists them. On a laptop this job competes with whatever else is open in the editor. As a run it is a branch, a set of outputs and a pull request, produced while the person who filed the issue works on something else.

## Frequently asked questions

### Why not limit and offset? It is less code.

An offset counts rows from the start on every request, so deep pages get slower, and any insert or delete before the current position shifts what the next page contains. A keyset query seeks straight to the cursor through the index and returns the same rows however the table changed behind it.

### Can it sort newest first, or by another column?

Yes. Ask in the issue. Descending order on the id reverses the comparison and the order clause. Sorting by a column that is not unique needs the id as a tiebreak inside the cursor and a composite index, which the agent adds with a generated migration and names in the description.

### Does this work with Drizzle on PostgreSQL or MySQL?

Yes. The query builder calls are the same across dialects, and the agent reads which one your schema imports. On PostgreSQL the tests need a server, which the agent installs in its sandbox for the run.

### Will it include a total count?

Not by default. A count is a second query over the whole table on every page, which undoes much of the benefit. If the issue asks for one, the agent adds it as a separate optional field and says what it costs.

## More Drizzle jobs

- [Add Database Indexes to a Drizzle Schema from a Linear Issue](https://getrush.ai/run/add-database-indexes/drizzle/)
- [Add Database Migrations to a Drizzle App](https://getrush.ai/run/add-database-migrations/drizzle/)
- [Add Full-Text Search to a Drizzle and PostgreSQL App](https://getrush.ai/run/add-full-text-search/drizzle/)

## The same job on other stacks

- [Add Cursor Pagination to an Echo API](https://getrush.ai/run/add-cursor-pagination/echo/)
- [Switch a Fastify API From Offset to Cursor Pagination](https://getrush.ai/run/add-cursor-pagination/fastify/)
- [Add Cursor Pagination to a Gin API from a Linear Issue](https://getrush.ai/run/add-cursor-pagination/gin/)

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