Testing database code is not that hard with correct tools like DbFit.
The fully functional example is available on GitHub. In this blog Digia's expert Jani Hurskainen shares his learnings about testing database code with DbFit.
DbFit is a database testing tool built on FitNesse acceptance testing framework so everything you know about FitNesse applies also to DbFit. DbFit brings in database features like queries, inserts, updates and running stored procedures in a (mostly) database agnostic manner.
FitNesse is a bit weird (but in a good way) as it is a wiki where most of the pages are also executable tests. The FitNesse mindset is that you have human readable specifications that can be used to verify the correctness of the system under test (SUT) – specifications and tests are the same.
FitNesse is a standalone Java web application. You run it in your local development host and connect to FitNesse with a browser. FitNesse provides a wiki interface where you write and execute tests. The server has also a REST interface that can be used to control the server from the command line e.g., for running tests automatically as part of continuous integration.
FitNesse tests (or wiki pages) are human readable and version control friendly text files. In fact, I most often modify the pages with my favorite text editor.
The DbFit tests are based on FitNesse table syntax that looks like this:
That executes the following SQL under the hood:
and saves the database auto-generated primary key (id column) to the variables that could be used later in the test.
Note that DbFit documentation covers only database features, so for everything else, one must refer to FitNesse documentation. That's awkward and confusing at first but is not a deal breaker, as the usage is rather simple. Anyway, here is the complete list of the relevant documentation:
Remember everything mentioned above about FitNesse also applies to DbFit. You can think of DbFit as a tailored version of FitNesse.
DbFit works great in all cases where you:
I have applied DbFit testing with great success in several cases that are essentially variations of the traditional ESB VETO pattern described in the diagram below. I'm not saying this should be your target architecture in the 2020s but you'll run into this when maintaining legacy (integration) systems.
In the diagram below:
Code and Configuration boxes comprise the private parts of the system under test and Execution trigger, Input data and Output data are considered the system's public interface.
There can be any number of input, configuration or output tables and other fancy database widgets like triggers, views, materialised views, and temporary tables depending on the system's implementation, but the pattern remains.
An example system with tests demonstrating how DbFit can be used to test a system described above can be found on GitHub. The example system has been implemented with Oracle 18.
DbFit has saved my day many times in the last 10 years in several database code maintenance and refactoring cases.
The nature of the DbFit tests make it easy to communicate the documented behavior of the system – the stakeholders usually immediately grasp the meaning of the test, and the reviews are fast.
DbFit delivers what it promises – executable documentation that verifies the database system's correctness.