Posted by on September 27, 2017

I had a realisation that I’m looking forward to blogging about soon.  However, when I outlined my post I couldn’t find references for several things that I know to be true.  So I started outlining those posts, and ran into the same lack of references.  Which brings us to the beginning:

How to test a database view

The textbook approach

I advocate Test Driven Design for databases. For procedural code, the first step is to write an acceptance test. once that test is written, and obviously failing, then the next step would be to write some unit tests. However, SQL is a set based, declarative language, so by the end of this post I hope to convince you to use a simpler approach than is standard for testing procedural code.

This post uses the following schema, which is greatly simplified to make the examples shorter:

I’m using the pgtap test framework. In all of the SQL test frameworks that I know acceptance tests look like this:

In this example we’re defining a small, static data set that we load into the view’s driving table and a single row that we expect as output. Like most acceptance tests this first test is a simple, happy path.

This test is failing, the view hasn’t even been defined yet. So we now write a whole series of unit tests, similar to this one.

The first thing that you should notice is that the “unit test” looks just like the “acceptance test”. Keen observers will also note that the foreign key dependencies haven’t been satisfied. I use the same setup and teardown for the acceptance test and all of the unit tests.

Which brings us to the point where we can safely write the view being tested.

The set approach

When I test stored procedures, the approach that I’ve described above is what I do. However, it has a few downsides:

  1. There is a lot of repeated code
  2. All the parts of the test framework, and the view are called repeatedly, which makes testing slow and verbose.

SQL is a language that operates on sets. After preparing a series of little sets each of which tests a single execution path, I recommend running them all together rather than separately.

The result is that each view ends up with a single test function. The downside to this approach is when the test fails you’ll need to open the test code to learn what the paths that failed were asserting. Which I think is a bargain for the reduction in testing complexity.


Be the first to comment.

Leave a Reply