Posted by Pete McBreen 12 Mar 2023 at 23:46
In the Ruby on Rails and Phoenix ecosystems, there is an explicit choice to use a test database for unit tests, along with fixtures to populate that test database with data for the tests. This all works seamlessly with the migrations for the schema and any seeds that are needed for the database. While both have slightly different strategies for isoloating the tests, the tests get decent coverage and will fail if the SQL sent to the database is invalid.
Looking at Java Spring codebases however, rather than having an explicit test database, the practice seems to be to completely mock out the interaction with the database. The end result is often that the tests set expectations as to the exact SQL that is emitted by the Data Access Object (Dao). Effectively the tests are asserting against the implementation of the method, not the results of the method, so query optimizations can end up breaking the unit tests.
This mocking to oblivion approach does allow the tests to run faster, but it does nothing to validate that the SQL (or HQL if using Hibernate) will return the correct dataset when run against a real database.
The case is often made that if the database is involved, then it is not a unit test, it is an integration test. My experience with Ruby on Rails and Phoenix was that even with touching the database, the tests ran fast enough, Phoenix easily achieving 100 tests/second on an average laptop.