All jobs

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 RushOpens 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Hand off a long task.

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

The same job on other stacks

Keep reading