So what is database unit testing anyway?
Unit testing is exercising very small, individual pieces of code and validating the results. In a database, a unit may be a stored procedure, view, function, constraint, trigger, or any other bit of code that you have to write.
We prefer to use automation for unit testing as it greatly reduces the amount of work and decreases the chance of mistakes. Besides repetition is boring.
There are other forms of testing which focus on exercising more than one piece of code at a time. This general area is referred to as integration testing. Integration is a very large topic area of its own. For this discussion, you just need to know that integration testing is not unit testing.
We write code units that depend on other code units. So the question often arises of how to unit test these pieces of code interact with each other. In object-oriented languages, we often use test doubles (e.g. mocks, stubs and fakes) to isolate the unit which we want to test.
We can also use test doubles in the database. For example, if we are testing a view which accesses a table, we can replace the table with a “fake table”. This makes testing the view easier as we don’t have to worry about the constraints on the table. The same holds for a stored procedure which calls another stored procedure – we can use a “spy procedure” to check what parameter values were passed to the called stored procedure.
There are many advantages to using a framework when unit testing. The first is that we don’t need to keep repeating boiler-plate code to write a test. A database unit testing framework should have the following features:
- Executing tests in a transaction
- Reporting test results, preferably in a way which is consumable by continuous integration frameworks
- Allows writing unit tests in the same language as the database code
- Support for test doubles
- Ability to group test cases
Database unit testing is testing the individual pieces of database code. In the database, we can borrow techniques from object-oriented unit testing to isolate the dependencies from what we want to test. Using appropriate unit test frameworks can facilitate success with database unit testing.