# Fix N+1 Queries in a Flask + SQLAlchemy App

Yes. An agent counts the statements each page runs, adds selectinload where a template lazy-loads a relationship per row, proves the drop in a test and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/fix-n-plus-one-queries/sqlalchemy/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent starts by counting queries rather than reading for suspects, because an N+1 is a runtime fact. In an app laid out like miguelgrinberg/microblog, `requirements.txt` pins `SQLAlchemy` 2.0.23, `Flask-SQLAlchemy` 3.1.1 and `Flask` 3.0.0, `config.py` sets `POSTS_PER_PAGE = 25`, and `tests.py` is a `unittest` suite with an in-memory SQLite `TestConfig`. `Post` in `app/models.py` has `author: so.Mapped[User] = so.relationship(back_populates='posts')` with the default lazy loading, and `_post.html` reads `post.author.username` twice and `post.author.avatar(70)` for every post it renders. `index` in `app/main/routes.py` paginates `current_user.following_posts()`, `explore` paginates `sa.select(Post).order_by(Post.timestamp.desc())`, `user` paginates `user.posts.select()`, `search` renders `Post.search`'s result, and `messages` renders `Message` rows through the same `_post.html`, where `Message.author` is also a lazy relationship.

It attaches a counter to the engine with `sa.event.listen(db.engine, 'before_cursor_execute', …)` inside a test, builds the four-user, four-post fixture that `test_follow_posts` already builds, and renders each page. What it finds is more precise than the usual picture: SQLAlchemy's many-to-one lazy load checks the identity map first, so a page of 25 posts costs one extra `SELECT` per distinct author on the page rather than one per post. On `explore` and `index` that is up to 25 extra statements, on `search` the same, on `user` none, because every post has the same author and it is already in the session, and on `messages` one per distinct sender. `following_posts()` already joins `User` twice through `Post.author.of_type(Author)` and `Author.followers`, but selects only `Post`, so the join brings the author rows to the database and throws them away.

The fix is the loader option, applied where the query is built so the template stays as it is. `explore` and `user` get `.options(so.selectinload(Post.author))`, `following_posts()` in `app/models.py` gets `.options(so.contains_eager(Post.author.of_type(Author)))`, which reuses the join it already has instead of adding a second query, `Post.search` in `SearchableMixin` gets `selectinload` on the select it builds from the ids, and `messages` gets `so.selectinload(Message.author)`. `db.paginate` in Flask-SQLAlchemy 3.1 takes a select, so the options ride along. The agent leaves `Post.author` lazy at the model level rather than making it eager everywhere, and says why in the pull request: `app/tasks.py`'s `export_posts` reads thousands of posts by one user and needs no author at all. It also notes the API's `to_dict` in `User`, which runs three `COUNT` queries per user for `post_count`, `follower_count` and `following_count`, so `GET /api/users` costs 1 + 3 × per_page statements; that is an aggregate problem, not a lazy load, and the agent describes the correlated-subquery rewrite as a follow-up rather than reshaping `to_dict` in this change.

`tests.py` gains a `QueryCountCase` that logs in through the test client with `WTF_CSRF_ENABLED = False` on the test config, renders `/explore` and `/index` with the fixture's posts, and asserts the statement count under the listener: two for `explore` (the page and the one `selectinload` batch), where before it was one plus the number of distinct authors. The agent runs `python -m unittest tests.py -v` as the README describes, then starts the app with `flask run` against SQLite and `SQLALCHEMY_ECHO` on, loads `/explore` and pastes the echoed statements, which now show a single `SELECT … FROM user WHERE user.id IN (?, ?, ?)` after the posts query.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Find and fix N+1 queries in this Flask + SQLAlchemy app. Read requirements.txt, config.py, app/__init__.py, app/models.py, app/main/routes.py, app/api/users.py, app/tasks.py, app/templates/_post.html, index.html, user.html, search.html and messages.html, and tests.py first. Measure before changing anything: in a test, attach a before_cursor_execute listener to db.engine, build the four-user four-post fixture test_follow_posts builds, render /explore, /index, /user/<name>, /search and /messages through the test client with WTF_CSRF_ENABLED off, and record the statement count for each. Fix the per-author lazy load with loader options on the selects rather than at the model: selectinload(Post.author) on explore, user and Post.search, contains_eager through the existing author join in following_posts, and selectinload(Message.author) on messages. Leave Post.author lazy at the model and say why, naming export_posts in app/tasks.py. Describe the three COUNT queries per user in User.to_dict as a follow-up with the correlated subquery it would take, without changing to_dict. Add a QueryCountCase to tests.py asserting the new counts for explore and index. Run python -m unittest tests.py -v, start the app with SQLALCHEMY_ECHO on, load /explore, and paste the before and after counts, the echoed statements and the test output into the pull request. Change no template.",
  "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 `app/main/routes.py`, `app/models.py` and `tests.py`. Its description has a small table of statement counts per page before and after, measured by the listener in the test with the four-post fixture, the `python -m unittest tests.py -v` output with the new `QueryCountCase`, and the `SQLALCHEMY_ECHO` output for one `/explore` load showing the posts query followed by one `IN` query for the authors.

Nothing changes in what any page shows: the templates are untouched and `post.author` resolves to the same object, now already loaded. The one behavior the description points out is that `following_posts()` now returns posts with their author attached through the join it already made, so any caller that relied on the author being lazy, and there is none in this codebase, would see it populated.

Before merging, a reviewer decides whether the `to_dict` follow-up should be taken now, since the API's user list is the heavier case, and whether they want `selectinload` on the query or `lazy='selectin'` on the model, which the pull request explains would also change `export_posts`.

## Run it on every page that lazy-loads per row

An engineer who noticed the repeated `SELECT … FROM user WHERE user.id = ?` in a log starts this from the terminal with this body and goes back to the bug. One `POST /o/{org}/sessions` per Flask app, each with its own `resources` URL; the run measures each app's real pages, so the fix lands only where the count shows a problem.

Send a `request_id`, a UUID, with each body so a retried call returns the run it already started. A key runs at most `max_concurrent` sessions at once, 4 by default, and a request past that answers `202` with `status: "queued"` and starts when a slot frees. A `webhook_url` on the key brings `run.completed` for each run the key started, with `data.session_url` to read for the pull request link.

## Why run it on Rush?

An N+1 is invisible in a diff and obvious in a query count, and guessing at loader options adds joins to pages that did not need them. The agent measures each page against the real app in its own sandbox with a statement listener, changes only the queries whose count shows the pattern, and proves the drop with a test the suite keeps, so the number in the pull request is one it observed, not one it expects.

The transcript keeps every command and its output, so the reviewer reads the counts and the echoed SQL without reproducing the setup. Several Flask services can be measured at once, each in its own sandbox, and nothing merges until a person reads the diff.

## Frequently asked questions

### Why is it one query per author and not one per post?

SQLAlchemy's lazy loader for a many-to-one relationship looks the target up in the session's identity map by primary key before emitting SQL. On a page where several posts share an author, the second and later posts find the author already loaded. So the cost is one `SELECT` per distinct author on the page, which is why the user page, where every post has one author, has no N+1 at all.

### Why not set lazy='selectin' on Post.author and be done?

That would load authors for every `Post` query in the app, including `export_posts` in `app/tasks.py`, which walks all of a user's posts to write a JSON file and never reads the author. Putting `selectinload` on the page queries fixes the pages and leaves the task alone. The pull request shows the model-level option as the one-line alternative if you prefer it.

### Why contains_eager for the index page?

`following_posts()` already joins `User` as `Author` to filter by followers. `contains_eager` tells SQLAlchemy to populate `Post.author` from columns that join already returns, so the index page needs no second query at all. `selectinload` would work too, at the cost of one more `SELECT`.

### What about the API user list?

`User.to_dict` runs three `COUNT` queries per user, so `GET /api/users?per_page=10` runs 31 statements. That is not a lazy relationship, it is an aggregate per row, and the fix is a select with three correlated scalar subqueries that `to_collection_dict` passes through. The pull request describes it and leaves it out of this change so the diff stays one kind of fix.

### How is the count measured in the test?

With `sa.event.listen(db.engine, 'before_cursor_execute', …)` incrementing a counter, registered in `setUp` and removed in `tearDown`. It counts statements SQLAlchemy sends, which is what the database sees, and the test asserts exact numbers for `explore` and `index` with the fixture's known number of distinct authors.

## More SQLAlchemy jobs

- [Fix "Textual SQL expression should be explicitly declared as text()" in SQLAlchemy 2](https://getrush.ai/run/fix-textual-sql-expression-argument-error/sqlalchemy/)
- [Migrate a SQLAlchemy App From MySQL to PostgreSQL](https://getrush.ai/run/migrate-mysql-to-postgres/sqlalchemy/)
- [Review Pull Requests in a SQLAlchemy App](https://getrush.ai/run/review-pull-requests/sqlalchemy/)

## The same job on other stacks

- [Fix N+1 Queries in a Symfony App](https://getrush.ai/run/fix-n-plus-one-queries/symfony/)
- [Fix N+1 Queries in a Wagtail Site](https://getrush.ai/run/fix-n-plus-one-queries/wagtail/)
- [Fix N+1 Queries in a Django App](https://getrush.ai/run/fix-n-plus-one-queries/django/)

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