- Home
- Skills
- Testing & Debugging
- Pytest + Async SQLAlchemy: Tests That Don't Lie (2026)
Works with the AI Agents you already use
Pytest + Async SQLAlchemy: Tests That Don't Lie (2026)
pytest suites for async SQLAlchemy + FastAPI that catch what they currently miss — updated for the 2026 releases that broke old testing patterns.
$9.99
Pytest + Async SQLAlchemy: Tests That Don't Lie (2026)
Example session with this skill installed
Write an architecture test for our FastAPI 0.142 app that fails if any endpoint under /api/admin is missing the require_admin dependency (on the route or on the router). The app is built from routers: app.include_router(api), where api includes the users, admin and admin_reports routers.
- Read your context and instructions
- Compiled the pytest + async
Summary
Since FastAPI 0.137, app.routes is a tree: included routers appear as _IncludedRouter nodes, and the classic isinstance(route, APIRoute) loop returns an empty list. A test written that way passes forever, even with an unprotected endpoint. This one walks the tree with iter_route_contexts.
from fastapi.routing import APIRoute, iter_route_contexts
from app.auth.deps import require_admin
from app.main import app
ADMIN_PREFIX = "/api/admin"
def _is_admin_path(path: str) -> bool:
# Segment boundary: /api/admin and /api/admin/..., but not /api/administrators
return path == ADMIN_PREFIX or path.startswith(ADMIN_PREFIX + "/")
def test_admin_routes_require_admin():
checked = 0
missing = []
# app.routes is a tree since FastAPI 0.137: a plain loop finds no APIRoute.
for ctx in iter_route_contexts(app.routes):
if not isinstance(ctx.route, APIRoute) or not _is_admin_path(ctx.path):
continue
checked += 1
# ctx.dependencies = effective deps (router-level and route-level);
# ctx.path = full path (ctx.route.path is relative to its router).
deps = {d.dependency for d in ctx.dependencies}
if require_admin not in deps:
missing.append(f"{sorted(ctx.methods)} {ctx.path}")
assert checked, "no admin routes inspected: the route walk is broken"
assert not missing, "admin endpoints missing require_admin:\n" + "\n".join(missing)
| Check | Why |
|---|---|
iter_route_contexts(app.routes) | walks included routers; the old loop finds nothing on FastAPI ≥ 0.137 |
ctx.path (full) | ctx.route.path is relative to its router (/admin/...) |
ctx.dependencies | effective deps, so router-level require_admin counts |
assert checked | the test can never pass by inspecting zero routes again |
Run against an app with one unprotected route:
AssertionError: admin endpoints missing require_admin:
['GET'] /api/admin/reports/leak
Next steps
- Apply the same walk to any other architecture test (tenant dependency, response models).
- If
require_adminis wrapped in another dependency, extend the check to walk sub-dependencies.
Connects securely to your tools. The creator never sees your data.
About this skill
Your assistant writes tests the way code looked when it was trained. Since then, several standard testing patterns quietly stopped working:
- FastAPI 0.137 made
app.routesa tree. The classicisinstance(r, APIRoute)loop now finds zero routes, so "every admin route requires auth" tests pass without checking anything. - pytest 9.1: a generator in a class-level
parametrizeis used up by the first method. The rest are skipped, with a line in the summary nobody reads. - SQLAlchemy 2.1 autoflushes before
text()and no longer installsgreenletby default.
This skill teaches your agent those changes, plus the classic ways an async SQLAlchemy suite stays green while production breaks: fixtures that leak committed data, a shared session that hides MissingGreenlet, now() frozen in the test transaction, deferred constraints never checked, sequences not rolled back, a commit after yield that runs after the 201 is sent, fire-and-forget tasks, AsyncMock sessions taking the wrong branch, race tests that pass without the fix, event-loop scope, lifespan and leaked overrides.
Every rule was reproduced on PostgreSQL 17 with SQLAlchemy 2.1.3, pytest 9.1, pytest-asyncio 1.4 and FastAPI 0.142.
Measured by running the code, not reading the answers. We ran the agents' tests against an app with a planted bug:
| Task | With skill | Without skill |
|---|---|---|
| Admin-route architecture test | catches the unprotected route | finds 0 routes, misses it |
| Post-upgrade suite review | 4/4 issues | 1/4 |
What you get: SKILL.md with 20 rules (symptom → cause → fix) and a review checklist, a complete reference conftest.py, verified patterns for N+1 counting, races and row locks, and the eval prompts.
Assumes PostgreSQL and SQLAlchemy's async ORM; not written for SQLite or sync SQLAlchemy. Not a pytest tutorial or TDD methodology. The SQLAlchemy 2.1 greenlet change is taken from the official migration notes; all other rules were reproduced. The 2026 section reflects releases up to October 2026.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 1 month ago