bag.sqlalchemy.testing module

Fake objects for unit testing code that uses SQLAlchemy.

Problem: SQLAlchemy is the main thing making our automated tests slow. In larger systems, hitting the database (even if SQLite in memory) leads to multiple-minute test suite runs, making TDD (Test First) impossible.

Mocking SQLAlchemy is impossibly hard to keep doing in numerous tests because the SQLAlchemy API is made of many objects and methods (session, query, filter, order_by, all, first, one etc.). It is bad to need to change the mocks every time you change an implementation detail!

Is there really no easy way to unit-test code that uses SQLAlchemy?

Come on, we are programmers! We can do this!

Solution 1: Create a fake session which can be populated with entities in the Arrange phase of the unit test, and then provides these entities to the code being tested. FakeSessionByType is a fake that does this – it only pays attention to the model class being queried and ignores all filters and order_bys and whatever else.

This solution was moderately successful, but what is annoying in it is that, unlike the real session, it does not populate entities with their IDs when it is flushed – neither does it take care of foreign keys.

Solution 2: The ambitious FakeSession is an implementation of the session that also stores entities in memory but tries to behave like a real session and actually interpret queries and filters and orders and so on and so forth. Currently it only works for very simple queries, but with your help it could become the perfect solution in the future.

Solution 3: As of 2016-05, I am sidestepping this as I try to implement Robert C. Martin’s Clean Architecture in Python, which forbids I/O in the center layers of the system. The only place in the system that can import and use the session is the Repository, which is dependency-injected into the service layer. This means the repository will contain one function per operation or query – thus it must be easy to mock. We’ll see.

class bag.sqlalchemy.testing.BaseFakeQuery(sas, typs)[source]

Bases: object

Base class for Query objects. Look at the subclasses.


Return a matching entity, or None.


Ensure there is only one result and returns i, or raise.

class bag.sqlalchemy.testing.BaseFakeSession[source]

Bases: object

Base class for fake SQLAlchemy sessions. Look at the subclasses.

no_autoflush = <bag.sqlalchemy.testing.FakeNoAutoFlush object>
class bag.sqlalchemy.testing.FakeNoAutoFlush[source]

Bases: object

class bag.sqlalchemy.testing.FakeQuery(sas, typs)[source]

Bases: bag.sqlalchemy.testing.BaseFakeQuery

class bag.sqlalchemy.testing.FakeQueryByType(sas, typs)[source]

Bases: bag.sqlalchemy.testing.BaseFakeQuery

filter(*a, **kw)[source]
filter_by(*a, **kw)
join(*a, **kw)
order_by(*a, **kw)
class bag.sqlalchemy.testing.FakeSession(query_cls=None)[source]

Bases: bag.sqlalchemy.testing.BaseFakeSession

SQLALchemy session mock intended for use in quick unit tests. Because even SQLite in memory is far too slow for real unit tests.

Uses lists as an in-memory “database” which can be inspected at the end of a unit test. Tries to behave like autoflush mode. You can actually make queries on this session, but only simple queries work right now.

Use it like a real SQLAlchemy session:

sas = FakeSession()
user = User(name="Johann Gambolputty")
assert user in sas.db[User]
sas.add_all((Address(address="221b Baker Street"),
             Address(address="185 North Gower Street")))
sas.flush()  # optional because next line does autoflush
q = sas.query(User)  # returns a FakeQuery instance
q1 = q.filter_by(name="Johann Gambolputty")  # a new FakeQuery
assert user == q1.first()
assert user ==
assert [user] == q1.all()
assert [] == sas.query(User).filter_by(
    name="Johann Gambolputty... de von Ausfern-schplenden").all()
class bag.sqlalchemy.testing.FakeSessionByType(*a, query_cls=None, **kw)[source]

Bases: bag.sqlalchemy.testing.BaseFakeSession

Mock session that returns query results based on the model type.

This mock session can be configured to return the results you want based on the model type being queried.

add_query_results(typs, results)[source]