In my previous post I explained why we should write more unit tests for our database code. Databases contain crucial business logic that is often developed and maintained in tandem with the code that depends on it. If we want to validate complex SQL statements in isolation and in detail, that means writing much more automatic tests than we normally do. Testing against a real database server (not an in-memory emulation) is by nature slower and more cumbersome to set up and run than basic unit tests. Here I will explain some practical strategies to make that process faster and more manageable.
The tips and tricks fall into two categories. First, I’ll look at ways to arrange your code under test so that the database is only spun up and accessed when it is actually needed. Secondly there are tips on how to prepare your database container with schemas and data, so setup time is kept to a minimum.
The test pyramid is a well-known visualization aid to classify software tests. As you climb the steps towards greater integration of components, you proceed from many, detailed, fast and isolated tests towards fewer, slower and more global tests that validate the system as a whole. It makes good sense in principle but it’s harder to explain how the stratification between unit-, integration and end-to-end tests is supposed to work. Opinions differ on what parts to integrate at which layer in the pyramid. You would think the database belongs in the upper strata, because it is expensive to set up and run. Yet it also makes sense to integrate it at the lower, detailed stage in the pyramid when it contains business-critical logic that requires detailed validation. It often needs the kind of rigorous validation that you cannot leave to a few slow, global integration tests. But let’s start with a recap of the definition of a unit test.