# Fix N+1 Queries in a Flask App

Yes. An agent counts the SQL each Flask page runs, finds the loops that query once per row, fixes them with eager loads and opens a PR with a query-count test.

[Run this on Rush](https://cloud.getrush.ai/run/fix-n-plus-one-queries/flask/), in a Project that has your repository.

## What the agent does

The agent starts by learning how the app loads data, because an N+1 fix in the wrong place adds a join nobody needed. In a Flask forum shaped like flaskbb/flaskbb, `pyproject.toml` requires Python 3.12 or newer and pins Flask at 3.1.3 or later, Flask-SQLAlchemy 3.1.1 or later and SQLAlchemy 2.1.4 or later, with a `tests` dependency group of pytest, pytest-cov, pytest-xdist and freezegun and a `uv.lock` beside it. The `Makefile` runs the suite with `uv run pytest`, and `flaskbb/configs/testing.py` points the tests at an in-memory `sqlite://` database with `SQLALCHEMY_ECHO = False`. The views in `flaskbb/forum/views.py` are `MethodView` classes: `ViewTopic.get` calls `Topic.get_posts`, `ViewForum.get` calls `Forum.get_topics`, `ForumIndex.get` calls `Category.get_categories`, and `TopicTracker.get` builds its own `sa.select` inline.

Then it reads the queries behind those views in `flaskbb/forum/models.py` rather than assuming the worst. `Topic.get_posts` selects `(Post, User)` with an `outerjoin` on `Post.user_id == User.id` and `selectinload(Post.attachments)`, so the topic page already gets authors and attachments in a fixed number of statements. `Forum.get_topics` outerjoins `Post` on `Topic.last_post_id` and adds `selectinload(Post.user)`. `User.primary_group` is declared `lazy="joined"`, which is why `topic.html` can print `user.primary_group.name` per post without a query each. The agent writes this map down before changing anything, because a codebase that already batches its hot pages needs a measurement, not a rewrite.

The measurement comes from the repository's own test helpers. `tests/fixtures/helpers.py` defines a `selects` fixture that attaches a `before_cursor_execute` listener to the engine and records every `SELECT`, and `tests/unit/forum/test_topic_page_permissions.py` already uses it to assert that the topic page loads all authors' group memberships in one `groups_users.user_id IN (` query and zero per-user ones. The agent copies that pattern: for each page it seeds N rows through the fixtures in `tests/fixtures/forum.py` (`category`, `forum`, `topic`, `user`), loads the page with N and then 2N rows, and compares the counts. A count that grows with N is an N+1; a flat count is left alone. One candidate it checks is the topic tracker: `TopicTracker.get` selects `Topic, Post, TopicsRead, ForumsRead` with outerjoins but, unlike `Forum.get_topics`, adds no `selectinload(Post.user)`, while `templates/forum/topictracker.html` prints `last_post.user.username` for every row.

Each confirmed N+1 gets the narrowest fix SQLAlchemy 2 offers: a `selectinload` on the relationship the template reads, a `contains_eager` where the join is already in the statement, or a `lazy="joined"` on a relationship that every page reads, in that order of preference. The agent keeps the fix in the model or view, never in the template, and adds one test per fix in the style of the existing one, asserting the exact number of statements for the relationship in question. It runs `uv run pytest tests/unit/forum` for the pages it touched and then the whole suite the way CI does, `uv sync --group dev` then `uv run tox run -e py3.12`, and opens the pull request with the before and after statement counts from its tests.

## Start it with one API call

`POST https://api.prix.dev/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Find and fix N+1 query patterns in this Flask app. Read pyproject.toml, the Makefile, flaskbb/configs/testing.py, flaskbb/forum/views.py, flaskbb/forum/models.py and the templates under flaskbb/templates/forum first, and read tests/fixtures/helpers.py for the selects fixture and tests/unit/forum/test_topic_page_permissions.py for how it is used. For the forum index, forum, topic, member list and topic tracker pages, write a test with the selects fixture that loads the page with N and 2N seeded rows and compares SELECT counts. Fix only the pages whose count grows with N, using selectinload, contains_eager or lazy=\"joined\" in the models or views, never in templates. Keep one query-count test per fix. Run uv run pytest, then the tox environment CI uses. Open a pull request listing each page, its statement count before and after, and the relationship that caused it.",
  "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.prix.dev/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 on its own branch that touches `flaskbb/forum/models.py` or `flaskbb/forum/views.py` where a relationship needed an eager load, and adds query-count tests under `tests/unit/forum/` built on the existing `selects` fixture. Templates are unchanged. The description lists each page the agent measured, whether its SELECT count grew with the number of rows, and for each fix the relationship, the loader strategy it chose and the statement count its test now asserts. Pages that were already flat are listed as checked and left alone.

A reviewer reads the loader choices against the pages that render them: a `selectinload` adds one `IN` query per page, a `lazy="joined"` changes every query that loads that model, so the second deserves a closer look. They check that each new test seeds rows through the fixtures in `tests/fixtures/forum.py` rather than raw SQL, and that it clears `cache` the way `test_topic_page_permissions.py` does, since `ForumIndex` caches its board statistics. The session's transcript holds the `uv run pytest` and `tox` output, so the reviewer can see the suite that ran rather than take the description's word for it.

## Run it on every slow-page alert

When your monitoring flags a page whose database time rose, a handler on your server receives that alert and sends `POST /o/{org}/sessions` with this prompt, the page's route written into `input`, and the repository as the `resources` URL. Send a `request_id` for each alert so a retried delivery returns the run it already started instead of a second one.

Set a `webhook_url` on the API key and Rush POSTs `run.completed` when the turn finishes, with `session_url` to read the pull request from, or `run.failed` when the agent could not reproduce a growing count and said what it measured.

## Why run it on Rush?

Hunting N+1 queries by hand means running the app with `SQLALCHEMY_ECHO` on, scrolling logs, and guessing which loop caused which burst. The agent does the same work with a listener on the engine, in its own sandbox with the app's own test database, and turns every guess into a test that fails before the fix and passes after it. Your laptop runs nothing.

The result is a pull request whose claims are tests, not a paragraph about performance. Several pages or several services can be measured at once, each in its own sandbox and branch, and the transcript keeps every pytest run so the numbers in the description can be checked against the output that produced them.

## Frequently asked questions

### What if the pages are already optimized?

Then the pull request contains tests and no model changes. In flaskbb the topic and forum pages already use `outerjoin` and `selectinload`, so the agent's measurement confirms them flat and adds the tests that keep them that way. The prompt tells it to fix only what grows with N, so a well-tuned app gets a regression guard, not churn.

### Will it switch relationships to lazy="joined" everywhere?

No. The prompt orders the strategies: a `selectinload` on the one page that reads the relationship first, `contains_eager` when the join is already in the statement, and `lazy="joined"` only for a relationship every page reads, as `User.primary_group` already is in flaskbb. Each choice is named in the pull request so a reviewer can disagree with one without rejecting the rest.

### Does it need my production database?

No. The tests run against the in-memory SQLite database that `flaskbb/configs/testing.py` configures, with rows seeded through the pytest fixtures. An N+1 is a shape, not a volume problem, so loading a page with N and 2N rows is enough to see the count grow. Nothing connects to a real database.

### How does it count queries without changing the app?

It uses the `selects` fixture already in `tests/fixtures/helpers.py`, which listens to SQLAlchemy's `before_cursor_execute` event on the test engine and collects every `SELECT`. The app code gets no instrumentation; the counting lives in the tests, and the fixture is removed when each test ends.

### Which Python and test command does it use?

The ones the repository declares: Python 3.12 or newer from `pyproject.toml`, `uv run pytest` from the `Makefile`, and the `tox` environments that `.github/workflows` runs after `uv sync --group dev`. The agent runs the forum tests first for speed and the full matrix entry for the Python version it has before opening the pull request.