Database mocks are not worth it

> Then you've never seen a test, I guess. That [,not to prove correctness,] is the only goal they can serve, fundamentally.

I cannot wrap my head around this statement. It's literally in the name: "test" as in to prove something works... hopefully as designed.

> Mistakes are guaranteed to happen given enough output/time. No matter how hard you try, you are going to make a mistake at some point.

Yep, and they do. Its really easy to figure out which one is right: if the docs say that something happens, it happens. If the code doesn't do what the docs say, the code (and the tests) are wrong; and not the other way around.

> Having to read code documentation in English is a horrible user experience.

It's the difference between intention and action! I worked with a guy who opened PRs with totally empty descriptions. It was annoying. When I was reviewing his code, I had to first figure out his intention before I could understand why there was a PR in the first place. Was he fixing a bug, adding a new feature, or just writing code for the hell of it? ... nobody knew. Then, when you spotted a bug, you had to ask if it was a bug or on purpose, because you didn't know why the code was there in the first place.

Documentation is that living PR description. It doesn't just tell you WHAT exists, but WHY it exists, what purpose it serves, why that weird little line is the way it is, etc., etc.

I’ve always felt this was partly database vendors fault for not having tooling that integrates well. I think there is some theoretical world where pgTAP and other database specific test harnesses work fairly seamlessly with language runtimes via some well designed SDK. Tracing would need to walk into the db call stack from the application code.

Clearly this is harder to get right than expected since there aren’t many places doing stuff like that, that I’m aware of at least. Maybe if some of these database vendors built out better API’s for things like that with the expectation other language runtimes would be calling them the situation would be better. But it seems a lot of the old school db world is perfectly happy to build their products as if they were a black box from the application’s perspective

> Unit tests don’t care about integration.

NOBODY HAS DEFINED WHAT A UNIT IS! This needs to be shouted because all your arguments fall apart when you realize this. Until you get down to atoms (or something in quantum mechanics) you can go a level deeper. String.length is a integration test if you are the hardware engineer building the microcode on the CPU (do CPUs use microcode anymore?). If you are laying out the traces tests for the microcode are an integration tests for you. When I write a simple function that happens to use string.length I'm an integration test for that. Note that I skipped many many levels in both directions.

What people care about is that things work. The term unit test and integration test needs to die as they are not useful. Test at the right level. If you can write all your tests as full system tests (that is via UI input) that runs fast and are reliable then do so. We generally say don't test via UI input because painful experience is UI changes often in ways that break 1000s of tests all the time, which is good reason to inject tests below the UI. There are often other reasons to test break up tests into smaller areas, but that is trade offs.

Odds are you are not writing basic containers (ie string.length) and so your API is something that should be easy to refactor (you have no reason to think it won't change in the future as requirements change) and thus you should not test that API. Tests are an assertion that in the future this will not change, so if you are testing something that should change you are doing it wrong! (of course you will never get this 100% right, but that should be your aim!)

---

If your mocks are hard to write you may have your fanout too wide and would do better with squaring up your dependency tree a bit.

Mocks are not hard to write. They are the wrong solution to most testing problems though. Mocks assert that you call a specific function as expected - it is really easy (in most languages) to automate writing with a framework and so they are easy to write. The problem is calling a function is not what you want to test in most cases. I used write as an example because file IO APIs generally have many different forms of write. You have WriteWithBufferAndLength you have WriteAVector (or whatever your language calls the basic list container), then WriteWithSomeWeirdOptionsWeForgetToPutInOriginalAPIAndDidNotWantToRefactorEverythingSoWeAddedANewAPI... Some languages these are all named different, some allow overloading - it doesn't matter, the important part is mocks don't let you switch which one you use as 100 tests will break if you try even though switching might be better it isn't enough better to be worth trying to fix all the tests that happen to use that mock. Making the above worse, write often can be called with one large buffer or many smaller buffers and the end result is the same yet mocks assert how many times write is called which is wrong.

> What I see with Fakes too often is that they are difficult to write so people reuse them, end up writing too many tests that only test the fake

When I write a fake I write one fake that close for everyone to use that fake in their tests - I know how the thing it is faking works in production and model that behavior except for whatever it is that makes it unsuitable to use in real tests. I often do add verification helpers - you can ask what data was written at any point (including concatenating several writes) for example.

> And now we are getting back to having to deploy those 50 stored procedures to a database to test your code when you branch. Are you going to use a shared dev database? What happens when other developers are also making changes to other branches?

Valid questions, but there are also valid answers, the primary one is of course "it depends".

Sometimes a shared DB works fine, as long as you manage other work being done to avoid conflicts.

Sometimes an isolated DB is needed for either just this work, or this work plus related project work isolated from other projects.

We do all of the above, and yes it takes effort, but that effort exists regardless of sprocs or not due to the nature of most of our projects and changes, rarely do we have small isolated work.

But I'm guessing our environment is not the same as your based on your statements, this is our environment:

1-Medium sized enterprise which means we run many apps from vendors, some on prem, some in cloud, as well as internally developed apps - in other words it's not a homogeneous environment and we don't control all aspects.

2-Functionality that frequently either sits on the side of operational systems, or in between the operational systems or as a layer on top that is unifying the business activity across systems and realizing a higher level of abstraction and activity.

3-Focus on end to end testing due to higher bug detection rate (i.e. around 70% bug detection) vs unit testing (i.e. around 30% bug detection), and due to work flowing through multiple apps frequently.

> even if you don’t have stored procs, you would keep your schema change sql files in another repo

Our schema changes are typically in the branch being merged and deployed. Our deployment process has pre and post DB actions sections.

But some changes are too big for that type of thing and require multiple day deployments with various business and technical actions happening in a specific sequence across multiple different apps (e.g. hold new activity for set of skus, stores and web site complete final transactions, clear DCs, switch to new process where those skus have a new unique flow through the systems and the enterprise).