🛑 Stop Testing Your Code and Ignoring Your Database (Catching N+1 in Pytest)

#sqlalchemy #python #testing #tdd

Your green CI pipeline might be lying to you. 🚨

It tells you the code works, but it’s quietly hiding the N+1 database disaster that will bring down your production environment next week.

As Python & SQLAlchemy developers, we spend hours writing tests to assert our application’s final state, but we treat the database layer like a complete black box. We test what the application does, but completely ignore how it does it.

The business cost of this abstraction is expensive. 💸
Every inefficient query and silent lazy-load that slips into the main branch directly inflates your cloud bill and degrades the user experience.

I got tired of this, so I built and open-sourced pytest-capquery. 🛠️

🎯 What it does

pytest-capquery treats SQL queries as first-class citizens in your Pytest suite. By intercepting the SQLAlchemy engine at the driver level, it enforces a strict, chronological timeline of your execution footprint.

Instead of just checking if a function returns True, you can rigorously assert deterministic I/O. If an N+1 regression slips in, the build fails instantly. 💥

🐛 The N+1 Problem in Action

Let's say a developer forgets to use joinedload on a simple query:

def test_demonstrate_n_plus_one_problem(db_session, capquery):
    capquery.statements.clear()

    panels = db_session.query(AlarmPanel).all()

    for panel in panels:
        _ = panel.sensors

    capquery.assert_executed_queries(
        "SELECT ... FROM alarm_panels",
        ("SELECT ... FROM sensors WHERE ? = sensors.panel_id", (1,)),
        ("SELECT ... FROM sensors WHERE ? = sensors.panel_id", (2,)),
        ("SELECT ... FROM sensors WHERE ? = sensors.panel_id", (3,))
    )

If someone drops the joinedload optimization, pytest-capquery exposes the exact lazy-loading queries.

✅ The Fix

When you optimize the query, your test ensures the database behaves exactly as intended:

from sqlalchemy.orm import joinedload

def test_avoid_n_plus_one_queries(db_session, capquery):
    capquery.statements.clear()

    panels = db_session.query(AlarmPanel).options(joinedload(AlarmPanel.sensors)).all()

    for panel in panels:
        _ = panel.sensors

    capquery.assert_executed_queries(
        """
        SELECT ...
        FROM alarm_panels
        LEFT OUTER JOIN sensors AS sensors_1 ON alarm_panels.id = sensors_1.panel_id
        """
    )

Stop blaming the ORM for performance bottlenecks and start profiling your tests! 📈 Lock down your database performance, drastically increase your software resilience, and stop merging regressions.

👇 Check out the project and let me know what you think: