SQAlchemy .filter does not work when Session is in MagicMock()

Question

I have a pytest.fixture for a mock database session that is used in a dependencies_overrides whenever a database session is triggered

app.dependency_overrides[dependencies.get_db] = lambda: mock_db_session

@pytest.fixture
def mock_db_session():
    """Create a mock database session"""
    session = MagicMock(spec=Session)
    users = [
        User(
            id="1",
            username="user1",
            user_first_name="fname1",
            user_last_name="lname1",
            deleted=False,
        ),
        User(
            id="2",
            username="user2",
            user_first_name="fname2",
            user_last_name="lname2",
            deleted=True,
        )
    ]
    # Mock the query method
    query_mock = session.query.return_value

    # Mock the filter method
    filter_mock = query_mock.filter.return_value

    # Mock the all method with the desired return value
    filter_mock.all.return_value = users

    yield session

    # Clean up resources, if necessary
    session.close()

Whenever the dependencies.get_db is called within my test, the mock_db_session is called instead example in here:

def get_users(db: Session, user: UserResponseModel):

    try:
        users =  db.query(User).filter(User.deleted == False).all()
    except:
        raise HTTPException(
            status_code=500, detail="Internal Server Error"
        )

I was able to get all the users (user1 and user2) but it is expected to return user1 only as user2 deleted=True. I dag deeper and found out that .filter does not.

Answer 1

When you do this:

# Mock the all method with the desired return value
filter_mock.all.return_value = users

you, basically, replace the functions filter, all with a mock object that returns users no matter what - it ignores any input arguments, any external state, you name it. Eventually, no filtering happens, because your database is never actually called.

What it does, however, it records all input arguments and the fact that it has been called.

If you want to see only valid users here, you have to provide already filtered return value:

users = [
    User(
        id="1",
        username="user1",
        user_first_name="fname1",
        user_last_name="lname1",
        deleted=False,
    )
]

And my final 50 cents on that - in this particular case mocking seems to be pointless - there is no point to test this logic if you already know all inputs and outputs. I would recommend mocking a little bit more high-level functionality, which actually processes DB output somehow. In this case you could mock DB outputs and check that the rest of the code is consistent with DB outputs and meets your expectations.