Python SQLAlchemy - Mocking a model attribute's "desc" method

Question

In my application, there is a class for each model that holds commonly used queries (I guess it's somewhat of a "Repository" in DDD language). Each of these classes is passed the SQLAlchemy session object to create queries with upon construction. I'm having a little difficulty in figuring the best way to assert certain queries are being run in my unit tests. Using the ubiquitous blog example, let's say I have a "Post" model with columns and attributes "date" and "content". I also have a "PostRepository" with the method "find_latest" that is supposed to query for all posts in descending order by "date". It looks something like:

from myapp.models import Post

class PostRepository(object):
    def __init__(self, session):
        self._s = session

    def find_latest(self):
        return self._s.query(Post).order_by(Post.date.desc())

I'm having trouble mocking the Post.date.desc() call. Right now I'm monkey patching a mock in for Post.date.desc in my unit test, but I feel that there is likely a better approach.

Edit: I'm using mox for mock objects, my current unit test looks something like:

import unittest
import mox

class TestPostRepository(unittest.TestCase):

    def setUp(self):
        self._mox = mox.Mox()

    def _create_session_mock(self):
        from sqlalchemy.orm.session import Session
        return self._mox.CreateMock(Session)

    def _create_query_mock(self):
        from sqlalchemy.orm.query import Query
        return self._mox.CreateMock(Query)

    def _create_desc_mock(self):
        from myapp.models import Post
        return self._mox.CreateMock(Post.date.desc)

    def test_find_latest(self):
        from myapp.models.repositories import PostRepository
        from myapp.models import Post

        expected_result = 'test'

        session_mock = self._create_session_mock()
        query_mock = self._create_query_mock()
        desc_mock = self._create_desc_mock()

        # Monkey patch
        tmp = Post.date.desc
        Post.date.desc = desc_mock

        session_mock.query(Post).AndReturn(query_mock)
        query_mock.order_by(Post.date.desc().AndReturn('test')).AndReturn(query_mock)
        query_mock.offset(0).AndReturn(query_mock)
        query_mock.limit(10).AndReturn(expected_result)

        self._mox.ReplayAll()
        r = PostRepository(session_mock)

        result = r.find_latest()
        self._mox.VerifyAll()

        self.assertEquals(expected_result, result)

        Post.date.desc = tmp

This does work, though feels ugly and I'm not sure why it fails without the "AndReturn('test')" piece of "Post.date.desc().AndReturn('test')"

Answer 1

I don't think you're really gaining much benefit by using mocks for testing your queries. Testing should be testing the logic of the code, not the implementation. A better solution would be to create a fresh database, add some objects to it, run the query on that database, and determine if you're getting the correct results back. For example:

# Create the engine. This starts a fresh database
engine = create_engine('sqlite://')
# Fills the database with the tables needed.
# If you use declarative, then the metadata for your tables can be found using Base.metadata
metadata.create_all(engine)
# Create a session to this database
session = sessionmaker(bind=engine)()

# Create some posts using the session and commit them
...

# Test your repository object...
repo = PostRepository(session)
results = repo.find_latest()

# Run your assertions of results
...

Now, you're actually testing the logic of the code. This means that you can change the implementation of your method, but so long as the query works correctly, the tests should still pass. If you want, you could write this method as a query that gets all the objects, then slices the resulting list. The test would pass, as it should. Later on, you could change the implementation to run the query using the SA expression APIs, and the test would pass.

One thing to keep in mind is that you might have problems with sqlite behaving differently than another database type. Using sqlite in-memory gives you fast tests, but if you want to be serious about these tests, you'll probably want to run them against the same type of database you'll be using in production as well.