A phrase I’ve often used when discussing database design is “insurance policy”, namely, when I talk about adding CHECK constraints, FOREIGN KEY constraints, UNIQUE constraints, stringent data types, the list goes on… there is sometimes push back from application development teams along the lines of “We already do these checks in the application code, so it is just duplication of effort to have them in the database”
Besides the numerous philosophical arguments about where data validation should or should not be taking place, perhaps the argument that most often sways the development team is the concept of validation in the database as an insurance policy, your “last line of defence” against data corruption. The reason this line of debate tends to work in my favour is that it aligns with the development principles behind unit testing. After all, unit testing also provides that important insurance policy against the all too common “This is such a simple change, what harm could it do?” question that comes from a project manager urging a developer to rush a quick fix through . I have lost track of the number of times I’ve been guilty of making what I think is the tiniest of changes in my code, only to have a Inbox full of “Automated Unit Test 1234 Has Failed” alerts a short time thereafter. Unit tests have saved me from disaster many times. It only takes one failed test to justify its existence. (this one courtesy of reddit although I tweaked the language for sensitive readers )
I know of very very few developers that don’t sing the praises of unit tests for their application code.
But when its comes to unit testing (or any other of the myriad of testing approaches) for the database layer, it is often overlooked or deemed “too hard”. After all, unlike private coding sandboxes, the database is often a shared resource and a constantly moving target in which you are trying to get consistent test results. Mocking up and destroying data in what is by definition a persistent data store can sound a difficult proposition. But without good testing at the database level, you are losing some of that insurance policy that gives you the confidence to promote your code to production.
If you have faced challenges with database testing, then this Office Hours session is for you. I have some special guests* who are experts in the field and I’m giving the entire session over to them to educate on effective and robust testing of database-centric applications!
(* – the blog post pic might be a clue!)
See you there!