Today we have a guest editorial from Phil Factor.

There has been much excitement recently about Database Unit Testing. Much in the same way as the sixties hippies felt they’d invented sex, a few writers have implied that database developers were completely innocent of any unit testing or regression testing methodologies before they came upon the scene with their splendid ideas, products, and insights.

Those of us who’ve worked in the industry a long time were trained in the arts of using structured and automated test harnesses for our code, even before it was delivered to the test teams. This practice was carried over to relational databases, when they appeared, just as it been intrinsic in mainframe practice, particularly in financial and trading systems.

Even automation, by itself, is hardly new: To develop and maintain database code and schemas at any speed or reliability, your regression testing has always had to be pretty seriously automated. Edge cases, soak tests and limit tests are best left to professional testers, but nothing else. I still wince at the reproach I used to get, as a cub programmer, if a simple error slipped through to them. I can also remember, thirty years ago, being instructed to build the test harness before writing the code. It concentrates the mind on what you’re designing. In developing serious financial systems, unit testing was always done before the code was delivered, as the code was built.

There is no mystery to how to test a deterministic routine. You feed it a variety of inputs and check that the output is exactly what you expect. If possible, you also run tests with randomly generated input, and verify the results by hand. You run the batches automatically every time you make a change or make a build. With a database, you need a range of test data, and to run a session with a typical query workload on it. It is a constant surprise to some developers to find out just how many bugs get flushed out by such a simple ritual, ‘How could such a simple change have had that effect?’

Although Database Unit Testing has a long and honorable history, there have been recent, significant developments. Recent work, by Alex Kuznetsov, on replicating deadlocks comes to mind. There is much to gain, for example, from developing more sophisticated client-based test harnesses that can simulate real usage. I share with the proponents of test-driven development a fascination for the whole subject of database test harnesses and test data, and their automatic generation: but to claim that any of this is new is all phooey.