The whole point of the mocking the database is to not test the database! If you need to test the database then test the database! Just like mocking an API can hide hidden issues with the API… which is again the exact point of mocking the API. This article should really be named “mocking your database isn’t testing your database” which seems like it should be obvious.
The point of mocking the database is to avoid the hassle of spinning up a database to test against. If we ignore that, why not test our use of the database? Are we disinterested in the schema and query errors we might catch?
Unit testing is useful because (a) it's easier to test all the code paths in a unit when that unit is isolated, and (b) unit tests don't need to be modified in response to changes in unrelated modules. But (a) is irrelevant since we can easily put dummy data in the DB, and (b) isn't a concern since the relevant DB schemas and queries are tightly coupled to the module in question anyway.
Primarily slowness and brittleness. Tests with side effects are much more likely to be flaky, and flaky tests are the worst. Especially if they're slow and hence hard to debug.
Of course, you do test your use of the database, but in a much smaller test suite. For the business logic tests you just spin up a copy of your app with fake adapters, and you're done. Quick, deterministic tests.
I agree that integrated tests tend to be brittle, but if we need database tests either way, the database harness can't be flaky. So any flakiness there is something that has to be fixed regardless. Slowness, I agree, but unless you're spinning up new containers for every test, the overhead of each query being real is going to be small.
If the database interactions are trivial, I agree—just use stubs. But if you've got non-trivial joins, then you'll need a separate database test suite anyway. And if there's stateful logic involving the database and you want to use fakes in unit tests, you need a whole extra set of tests to validate that the fakes behave the same way that the real database does.
I do actually prefer to avoid relying on the database for unit tests—it's cleaner, it's faster—but often testing with the database is simpler & reliable, and I can't justify the extra lines of code required for fully isolated unit tests. That's extra surface area for tech debt that could lead to stuff like fakes behaving differently from their production equivalent.
The point of mocking the DB isn’t to avoid hassle, it’s to not test your dependencies. Technically the calling code might not even know it’s a DB, and it might not even care, or it might be a DB sometimes or an API other times, or even a command line once in awhile. They are only tightly coupled if you write it that way. And yes, we would be disinterested in the schema and query errors we might catch because that’s not the point of the test.
