Should integration tests of a repository pattern use low-level ORM calls

Question

Problem summary:

In an application with wrapper methods over SQLAlchemy add() and query() methods, can integration tests that use the add() method wrapper use the query() method wrapper to validate that database table attributes and relationships are properly saved?

Detailed problem description:

We have a python application that manages a SQLite database via SQLAlchemy.

Besides the database model classes (e.g. Organization, Team, Member), the data layer implementation includes the following two classes:

• Database: wraps the SQLAlchemy session management methods
• Repository: wraps the SQLAlchemy CRUD operations and adds custom logic to before-insert, before-update, and before-delete events.

Given my lack of experience in testing applications like this, I would like to validate the following ideas:

• Unit tests would ensure that each feature (method) of the Database and Repository classes work as expected. One example of a unit test, here, would assert that the Repository class can write an entity (e.g. Organization) to the database correctly. In order to do that assertion, we're permitted to query the database directly using SQLAlchemy queries (or even the SQLite client, perhaps) and check the values of the different attributes to validate that the ORM properly saved the object and that the before-insert logic was applied successfully.

• Integration tests would ensure that after a sequence of actions using the Repository class, relationships between entities are correctly maintained. For example, we would test that a group of related entities (e.g. one Organization entity and two children Team entities) that have the right relationship() configurations are saved properly whether we save the object tree all at once or save the various objects separately over multiple calls to the add() method of the Repository class. In order to do that, we can just use the get() method of the Repository class and check the relationship attributes in the query results, since that method was already tested via unit tests. There's no need to keep using the SQLAlchemy query method directly to load the data (like what we'd do inside unit tests).

Please let me know if my understanding of how these tests is incorrect and how it should be implemented instead, in case it is.

Answer 1

These tests sounds like data mappings tests. I do this frequently, too, with NHiberate (an ORM for .NET). What you describe are perfectly valid tests. Beware that ORMs may have multiple layers of caching, which can affect your test results.

Typically ORMs cache data at the query and/or entity level. I've written tests that passed when inserting and then querying using the same database session, but failed when inserting from one session and then querying from another. This was due to subtle bugs in the code that loaded and saved relations between tables. For this reason I usually insert, update, or delete from one session and then query from another. This bypasses the first and second level cache of the ORM.

The only way to ensure your data mappings are correct is to go to the database, and then get data back from the database. ORM caching can cover up bugs in your mappings that become obvious when bypassing the cache. It doesn't matter if you use the repository or SQLAlchemy session directly in your tests. The important thing to do is preform CRUD operations from one session, then query-and-assert from another.