Programmatically measure database query complexity in Python SQLAlchemy

Is this possible in python/sqlalchemy?

When I write an endpoint which retrieves a list of records, I might accidentally make my query very inefficient without realizing.

Is there a way to measure the complexity of database queries in a method/unit test and throw an error if too many transactions take place?

In my example, I am using strawberry for providing a graphql router. On more than one occasion, I've made the following mistake, which involves an additional database query being made for each ParentModel in the list to retrieve the ChildModel. To get around this, I can make the ChildModel be loaded eagerly in the initial query. I would like to be able to make it very obvious to myself if my method will result in a large number of database queries.

import strawberry


@strawberry.type
class ChildGQLSchema:
    id: int

    @classmethod
    def from_model(cls, model: ChildModel):
        return cls(
            id=model.id,
        )


@strawberry.type
class ParentGQLSchema:
    id: int

    @strawberry.field
    def children(
        self, info, page: int = 1, limit: int = 20
    ) -> list[ChildGQLSchema]:
        # Unless explicitly loading the children, this will result in a
        # query to the database for each parent.
        models = (
            session.query(ChildModel)
            .filter(ChildModel.parent_id == self.id)
            .all()
        )


@strawberry.type
class Query:
    @strawberry.field
    def parent(self, info, id: int) -> ParentGQLSchema | None:
        model = session.query(ParentModel).filter(ParentModel.id == id).first()
        if not model:
            return None
        return ParentGQLSchema.from_model(model)

Solution

You're describing an example of a N+1 query problem. You'll find a lot of resources/examples online using that as a search term.

Libraries like https://github.com/jmcarp/nplusone can help detect them for you.

It's still a good idea to learn more about N+1's b/c libraries like that usually can only catch the easy/obvious instances.