# Add Cursor Pagination to a Rust API With SQLx

Yes. An agent replaces OFFSET paging with keyset pagination in a SQLx and Axum API, adds the index and a next link, and opens a pull request.

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

## What the agent does

The agent reads the listing code and, in a codebase like launchbadge/realworld-axum-sqlx, finds the author already asked for this. `src/http/articles/listing.rs` takes `limit` and `offset` in `ListArticlesQuery`, and the comment above them says they are not the optimal way to paginate, suggests paging on `created_at`, and proposes a `next` field after `articles` holding the URL of the following page. The query in `list_articles` ends in `order by article.created_at desc limit $5 offset $6`. The one in `feed_articles` ends in `limit $2 offset $3` with no `order by` at all, so its pages have no defined order. In `migrations/4_article.sql`, `created_at` is a `timestamptz` defaulting to `now()`, the table has a GIN index on `tag_list` and a unique `slug`, and nothing indexes `created_at`.

Keyset pagination needs a total order, and a timestamp alone is not one: two articles can share a `created_at`. The agent pairs it with `slug`, which is unique and already selected, so `ArticleFromQuery` and the four other queries that share that struct stay untouched. The filter goes in beside the existing optional filters, in the style the file already uses: `$7::timestamptz is null or (article.created_at, slug) < ($7, $8)`, with `order by article.created_at desc, slug desc` on both queries. PostgreSQL infers the second parameter's type from the row comparison, and the clause compiles under `sqlx::query_as!` with an `Option<OffsetDateTime>` and an `Option<String>` on the SQLx 0.5.9 that `Cargo.lock` pins. A new migration adds an index on `(created_at desc, slug desc)`. `migrations/README.md` asks for integers for the bootstrap set and timestamps afterwards, so the agent creates the file with `sqlx migrate add`.

The cursor itself has a trap in this repository. Responses serialize `createdAt` through the `Timestamptz` wrapper in `src/http/types.rs`, which formats with `Format::Rfc3339` from the `time` 0.2 crate, and that format drops fractional seconds: a value with microseconds prints as a whole second and does not parse back to the same instant. A client that paged by the `createdAt` it was shown would skip or repeat every article written within the same second as the page boundary. So the agent builds the cursor from the timestamp's full precision and the slug, encodes the two into one opaque string without adding a crate, and returns it inside a `next` URL on `MultipleArticlesBody`, left out when there is no further page. It finds that out by asking for one row more than `limit`. `offset` keeps working when no cursor is sent, because the Realworld spec and the Postman collection that `.github/workflows/rust.yml` runs still use it; sending both is a 422 through the existing `Error::unprocessable_entity`.

The query macros check SQL against a live database at compile time, so nothing builds without one. The README starts PostgreSQL with `docker run`; the sandbox has no Docker, so the agent installs PostgreSQL from the system packages, sets `DATABASE_URL` and `HMAC_KEY` as `.env.sample` describes, installs `sqlx-cli` and runs `sqlx db setup`, which applies the four existing migrations and then the new one. It adds unit tests for encoding and decoding the cursor, including a timestamp with microseconds, runs `cargo build` and `cargo test`, then starts the server and drives it with curl: register a user, create a few dozen articles, walk the pages by following `next`, and insert an article between two fetches to show that no row repeats. It finishes with `EXPLAIN` on the new query to confirm the index is used.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add cursor (keyset) pagination to the article listing endpoints of this Rust API, which uses Axum and SQLx with compile-time checked queries. Read src/http/articles/listing.rs and its comments, src/http/articles/mod.rs, src/http/types.rs, src/http/error.rs, migrations/ and migrations/README.md, Cargo.toml, .env.sample and .github/workflows/rust.yml first. Order both list_articles and feed_articles by created_at desc with slug desc as the tie-breaker, and add an optional keyset filter in the same style as the existing optional filters, as a row comparison on (created_at, slug). Accept an opaque cursor query parameter and return a next URL in the response body when another page exists; fetch limit plus one rows to know. Build the cursor from the timestamp at full precision and the slug, not from the serialized createdAt string, and check whether that string keeps fractional seconds. Do not add a field to ArticleFromQuery and do not add a dependency. Keep limit and offset working when no cursor is sent; reject a request that sends both with a 422. Add a migration with sqlx migrate add for an index on (created_at desc, slug desc). There is no Docker here: install PostgreSQL from the system packages, set DATABASE_URL and HMAC_KEY, install sqlx-cli and run sqlx db setup. Add unit tests for the cursor, run cargo build and cargo test, then start the server and page through at least 25 articles with curl, inserting one between fetches. Include EXPLAIN for the new query. 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 with the two rewritten queries and the cursor type in `src/http/articles/listing.rs`, the optional `next` field on the response body, one timestamped file in `migrations/`, and the cursor's unit tests. The description shows the request and response shape, a curl transcript from the sandbox that walks three pages and inserts an article between the second and third, and the `EXPLAIN` output naming the new index. It states the finding about `createdAt` precision with a two-line example, since that is the reason the cursor is opaque.

A reviewer checks that the old contract still holds: a request with only `limit` and `offset` returns what it did before, apart from the feed now having a defined order. They look at the index against their data. It serves the unfiltered list and the feed's ordering; a list filtered by `tag` or `author` still has to find matching rows first, and the description says which filters it measured. They also confirm the workflow's Postman step passes on the pull request, as it is the only end-to-end check the repository has, and decide whether clients should be told to prefer `next` over `offset` from now on.

## Run it on every Linear issue labelled pagination

List endpoints get this treatment one at a time, usually when one grows large enough to hurt. In the console, add an automation on the Project that holds the API with a `linear` trigger on `linear.issue.labeled` and the label `pagination`, and save this prompt with the endpoint left to the issue. When a person in your Rush org labels an issue such as "comments list is slow past page 50", the issue is added after the prompt and the run opens a pull request for that endpoint with its own migration. The automation's runner needs a Linear connection, made in the console under Settings → Connections.

## Why run it on Rush?

With SQLx's checked queries, even reading the compiler's verdict needs a database with the schema loaded, and proving pagination needs a running server and enough rows to cross a page boundary. The agent sets all of that up in its own sandbox: PostgreSQL, the migrations, the build, the server, the seed data. The run keeps every command with its output, so the curl transcript and the query plan in the pull request are the ones the sandbox produced.

The precision bug is the kind of thing that work finds. It does not show in a code review of the query, and it does not show in a test that creates rows seconds apart. It shows when the agent creates many rows quickly and follows the links, which is cheap to do in a sandbox that exists only for this change and is thrown away after.

## Frequently asked questions

### Why not paginate on created_at alone?

Because it is not unique. With `created_at < $1` as the only condition, rows that share the boundary timestamp are all skipped on the next page. Adding a unique column as the second sort key and comparing the pair as a row makes the order total, so each row appears exactly once.

### Why is the cursor opaque instead of a plain timestamp?

Two reasons. The client should not need to know which columns the server pages on, which the comment in the source already argues. And in this codebase the timestamp the API prints has lost its fractional seconds, so a client could not send back a usable one even if it tried.

### Will it build without a database?

Not as the repository stands. The `query_as!` macros connect to `DATABASE_URL` during compilation, which is why the CI workflow starts a PostgreSQL service before `cargo build`. SQLx's offline mode, a checked-in query data file, removes that requirement and is a separate change.

## More SQLx jobs

- [Add Database Indexes to a sqlx Project](https://getrush.ai/run/add-database-indexes/sqlx/)
- [Set Up SQLx Offline Mode So a Rust App Builds Without a Database](https://getrush.ai/run/add-offline-mode/sqlx/)
- [Add an SQLx Offline Query Check to CI](https://getrush.ai/run/add-offline-query-check-to-ci/sqlx/)

## The same job on other stacks

- [Add Cursor Pagination to a tRPC Router](https://getrush.ai/run/add-cursor-pagination/trpc/)
- [Add Cursor Pagination to a TypeORM + NestJS API](https://getrush.ai/run/add-cursor-pagination/typeorm/)
- [Add Cursor Pagination to an Actix Web API](https://getrush.ai/run/add-cursor-pagination/actix/)

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