Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    Pytest + Async SQLAlchemy: Tests That Don't Lie (2026)

    1

    pytest suites for async SQLAlchemy + FastAPI that catch what they currently miss — updated for the 2026 releases that broke old testing patterns.

    $9.99

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned
    Pytest + Async SQLAlchemy: Tests That Don't Lie (2026)

    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)
    
    CheckWhy
    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.dependencieseffective deps, so router-level require_admin counts
    assert checkedthe 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_admin is 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.routes a tree. The classic isinstance(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 parametrize is 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 installs greenlet by 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:

    TaskWith skillWithout skill
    Admin-route architecture testcatches the unprotected routefinds 0 routes, misses it
    Post-upgrade suite review4/4 issues1/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. 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. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 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

    Permissions

    Read Files
    Write Files
    Listed1 month ago

    What's inside

    Frequently Asked Questions

    This skill addresses silent test failures caused by breaking changes in the 2026 releases of FastAPI, SQLAlchemy, and pytest, ensuring your agent writes tests that actually catch bugs rather than reporting 'false greens.'

    The skill includes a comprehensive SKILL.md with 20 'symptom-cause-fix' rules, a reference conftest.py, verified patterns for complex scenarios like N+1 detection and race conditions, and evaluation prompts.

    Yes, all patterns and fixes have been rigorously verified using PostgreSQL 17, SQLAlchemy 2.1.3, pytest 9.1, and FastAPI 0.142 to ensure they work with the latest library ecosystems.

    This skill is delivered as a set of structured rules and reference code that can be imported into any major LLM or AI coding assistant (such as Cursor, GitHub Copilot, or Windsurf) to update its internal testing logic.

    It specifically addresses modern pitfalls like the new FastAPI route tree architecture, pytest 9.1's generator parametrization changes, and SQLAlchemy 2.1's new autoflush behaviors that cause older testing patterns to fail silently.

    New in Testing & Debugging