This month T-SQL Tuesday is hosted by Hamish Watson, quarantined down in New Zealand. Like me, he has a ranch, and kids, so he has space to move around a bit.
Hamish is a DevOps and testing advocate, just like me. His topic this month is to ask “So now that I have introduced unit testing – is it valuable for implementing with databases? “
I think it’s a great topic, and here is my take.
Database Unit Testing Documents
My big take is that database unit testing best documents what you, as a developer, think your part of the database code should do. You expect these things:
- The API a table will provide
- The way a CASE statement decides what to return
- The formulas you use for aggregation
- The manipulation a function uses to return a value
And more. Ultimately, we interpret a requirement, whether written down or told to us. We often may slightly mis-interpret what someone told us, or not think of certain cases. A test forces us to document the way we think our code behaves with certain data.
This isn’t perfect, as we still may not account for certain cases of data, we may have bugs, or we may interpret things incorrectly. However, the test documents what we think. When there is an issue, we can go look at the test and verify this behavior is what is expected. Or we can add a new test (or enhance ours) to handle the case we didn’t expect.
There are plenty of other reasons, but I believe that testing helps improve quality because it forces us to think, it documents our actions, and it helps ensure we don’t create regressions of code that already works.