How to unit test async SQLAlchemy/SQLModel code with pytest: compare compiled SQL, not expression objects

Learn how to unit test async SQLAlchemy/SQLModel code with pytest and mocks by comparing compiled SQL, not expression objects. Use the right dialect for fast tests.

When unit testing async database code with pytest and mocks, it’s tempting to compare raw SQLAlchemy/SQLModel expressions directly. That’s where many tests mysteriously fail: two queries that look identical won’t be equal by instance identity. The right approach is to compare the actual SQL that will be executed, not the in-memory Python objects.

Problem setup

Consider an async service method that performs a simple lookup by login. The logic is straightforward: build a select, execute it via the session, and return the first row.

from typing import Optional
from sqlalchemy import select
class ProfileService:
    @classmethod
    async def fetch_by_handle(cls, db_session: "SessionStub", handle: str) -> Optional["Person"]:
        query_stmt = select(Person).where(Person.login == handle)
        outcome = await db_session.exec(query_stmt)
        return outcome.first()

A naive test tries to assert that the session was called with exactly the same SQL expression instance:

import pytest
from unittest.mock import AsyncMock
from sqlalchemy import select
USER_HANDLE = "jdoe"
@pytest.mark.asyncio
async def test_fetch_by_handle(mocker):
    result_mock = AsyncMock(return_value=None)
    session_mock = mocker.AsyncMock()
    session_mock.exec.return_value = result_mock
    expected_query = select(Person).where(Person.login == USER_HANDLE)
    await ProfileService.fetch_by_handle(session_mock, USER_HANDLE)
    session_mock.exec.assert_called_once_with(expected_query)
    session_mock.exec.assert_called_once()
    result_mock.first.assert_called_once_with()

This fails because two independently constructed SQL expressions are distinct objects, even if they represent the same SQL. The failure output shows different memory addresses for the expected and actual arguments.

Why the assertion fails

SQL expression objects in SQLAlchemy/SQLModel are Python structures. Building a select twice produces two different instances. Using assert_called_once_with on those instances compares object identity and structure, not the compiled SQL text bound for the database. In other words, you’re comparing the wrappers, not the query. That’s why the call assertion doesn’t match, even though the executed statement is logically equivalent.

Solution: compare the compiled SQL

Instead of comparing object instances, compare the SQL generated by those expressions. The argument passed to the mock is available via await_args on the coroutine mock. Compile both the actual and expected expressions using the appropriate SQLAlchemy dialect and compare their string forms.

import pytest
from unittest.mock import AsyncMock
from sqlalchemy import select
from sqlalchemy.dialects import postgresql
USER_HANDLE = "jdoe"
@pytest.mark.asyncio
async def test_fetch_by_handle_compares_sql(mocker):
    result_mock = AsyncMock(return_value=None)
    session_mock = mocker.AsyncMock()
    session_mock.exec.return_value = result_mock
    expected_stmt = select(Person).where(Person.login == USER_HANDLE)
    await ProfileService.fetch_by_handle(session_mock, USER_HANDLE)
    sent_stmt = session_mock.exec.await_args[0][0]
    assert str(sent_stmt.compile(dialect=postgresql.dialect())) == str(
        expected_stmt.compile(dialect=postgresql.dialect())
    )
    session_mock.exec.assert_called_once()
    result_mock.first.assert_called_once_with()

This focuses the test on the actual SQL, not on Python object identity. If the SQL expression changes in the future, the test will fail as intended. Ensure you compile with the correct dialect for your database so the resulting SQL text matches expectations.

Alternative matching approach

There is also a general technique for call argument matching: create objects that implement __eq__ and pass them into assert_called_once_with, letting your equality logic decide whether the value is acceptable. This applies broadly to mock argument verification and is not specific to SQL. See the Python data model for object.__eq__ and an example approach here: object.__eq__ and this example.

Why this matters

Tests that compare SQL expression instances are brittle and misleading. As your code evolves, you might reconstruct equivalent statements in slightly different ways, causing instance mismatches and false negatives. Verifying the compiled SQL ensures your unit test validates what the database will actually receive, capturing meaningful regressions while avoiding noise from internal Python object differences.

Takeaway

When unit testing SQLAlchemy/SQLModel code with pytest, treat SQL expressions as builders, not as values to compare directly. Retrieve the argument passed to the mocked executor, compile both actual and expected statements with the correct dialect, and compare the resulting SQL strings. This keeps tests focused, deterministic and aligned with the real behavior of your persistence layer.