pytest fixture to help debugging (and asserting) database queries on django tests.

get_queries.py

"""
Based on this answer https://stackoverflow.com/a/59125267

Requirements:

- django
- pytest
- pytest-django

Include this fixture in your `conftest.py`

There's actually a fixture from `pytest-django` that can also 
be used: https://pytest-django.readthedocs.io/en/latest/helpers.html#django-assert-num-queries

The main problems I try to tackle with this new fixture, based on my personal 
use case and preference when debugging, are:

1. In the case that you have multiple databases configured, this one separates
   those queries and let you assert and debug per database. 
   Django one throws everything in a single list without distinction, which in 
   some cases is not what you need.

2. When debugging the test on a Pdb session with a  breakpoint, if you use the 
   django one, you'll have to decide where to use the context manager before 
   entering the pdb session in order to see the captured queries in your pdb 
   session. With the `get_queries` you can simply add a breakpoint and use it 
   whenever you need it during the pdb session.

3. Also, `get_queries` removes the (sometimes) undesired queries performed during 
   middlewares, like the `django_sessions` ones and some `SAVEPOINTS` that happen
   on an http request.
"""


@pytest.fixture
def get_queries(settings) -> Callable:
    """
    Helper fixture to debug the queries made to the database
    during the run of a test.

    After any operation to the database (saving a model, or a
    factory, or api call) the quieries will be reseted, therefore,
    you will always get the queries performed in the last
    connection to the database.

    In your tests, you can simply add this as a parameter and
    call it (and assert it) anywhere in the tests, so you can
    check the amount of calls to the database during the whole
    cicle of the test. Or check if the resulting queries are the
    ones you are expecting, looking for possible optimizations.
    It removes some of the (sometimes) un-wanted queries that 
    comes from middlewares, like the django_session ones, so 
    we can focus on the queries that actually matter.

    IMPORTANT: As we only get the SQL as string (no extra infor
    regarding where that querie comes from or when was executed)
    it was difficult to exclude the querie that is made by the
    middleware to include the user in the request, therefore,
    after every http request you'll find an extra querie to get
    user info from 'auth_user'.

    Example:
    ```
        from some_app import SomeModel

        @pytest.mark.django_db
        def some_test(client, get_queries):

            resp = client.get("some-url/")
            
            # You expect that the endpoint only perform two queries
            # to the database (plus the one from the user in the request)
            assert len(get_queries()["default"]) == 3

            # This will reset the queries_log in the connection
            SomeModel.objects.create(name="some name")

            # You also expect that when saving a model there is no side
            # effect and you only are performing a query to INSERT the
            # data.
            assert len(get_queries()["default"]) == 1
    ```

    Or you can simply add a breakpoint, and while in the pdb
    session start debugging the resulting queries after each
    line of the test.

    ```
        (Pdb) len(get_queries()["default"])
        1
        (Pdb) from pprint import pprint
        (Pdb) pprint(get_queries()["default"])
        [{'sql': 'SELECT "auth_user"."id", "auth_user"."password", '
                 '"auth_user"."last_login", "auth_user"."is_superuser", '
                 '"auth_user"."username", "auth_user"."first_name", '
                 '"auth_user"."last_name", "auth_user"."email", '
                 '"auth_user"."is_staff", "auth_user"."is_active", '
                 '"auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = '
                 '2 LIMIT 21',
           'time': '0.000'}]
    ```
    """
    settings.DEBUG = True
    from django.db import connections

    def _queries() -> Dict[str, str]:
        databases = dict()
        # Some of the queries comes from middlewares and
        # are not desired for debugging purposes.
        EXCLUDE_QUERIES = ["django_session", "SAVEPOINT"]

        for conn in connections.all():
            queries = list(
                filter(
                    lambda x: not any(
                        [
                            exclude_query in x.get("sql")
                            for exclude_query in EXCLUDE_QUERIES
                        ]
                    ),
                    conn.queries_log,
                )
            )
            databases[conn.alias] = queries
        return databases

    return _queries