Database testing – Unit Testing

As I was saying…

One of my unresolved concerns about unit testing is that there is a gulf between where unit testing has come from and the best way to write performant database code. How well does the tradition unit testing and TDD approach sit with set-based operations in code.

What I am going on about?

Well, it seems to be that unit testing sits very nicely with refactored code, where little routines can be packaged into a function with a defined responsibility that can be reused by many other components.

Where this gives me a problem, is that this encourages you to write SQL with lots of row-by-row functional lookups which are tried and tested, trusted and known to work. But this can cripple your SQL performance.

The flipside can require you to repeatedly write the same lookups inline in various bits of SQL. Better performing but you’re having to test many of the same things for each piece of SQL. Needs some thought that one.

For now, for me, performance wins every time.


There are a number of tools out there to help database testing of code modules, many of which have benefited from Steven Feuerstein’s involvement. Some of the options are GUI led, some require knowledge of Java or some other language.

The core technology is utPLSQL – “developed by Steven Feuerstein … it allows the automated testing of packages, functions and procedures.”

Extending utPLSQL, we have Ounit. “Ounit offers a basic graphical interface to utPLSQL”. Ounit was designed by Steven Feuerstein who has gone on to move the concept on further with what is now known as Quest Code Tester for Oracle, which is now a commercial product from Quest but which also offers trial and freeware versions.

Another tool out there is DBunit– ” JUnit extension targeted for database-driven projects that, among other things, puts your database into a known state between test runs”.

Also, another option that has been highlighted to me recently is DBFit, a version 0.1 utility.

In these early stages of my understanding, I’m not convinced that any one of these options ticks all the boxes. It sounds like utPLSQL is going to be involved. A GUI front end might be involved but it needs to be integrated with other unit testing and build results – so it might be as simple as collating the results and sending them in some format to a web page somewhere.

Definitely, a significant feature of any solution should be some sort of integration with build technologies such as Ant, Maven and Cruise Control – we want to automate testing as much as possible, after all. But if the underlying technology for the tests is PLSQL then calling that is not going to be much of a problem.

Also, not having played with these yet, I have questions over whether these tend to best suited to just testing procedures which do calculations, etc on inputs rather than results sets from complex SQL which may or may not be joining the right tables and returning the right values. And how well can we adapt them to test batch jobs.

I expect that we can adapt some of them pretty well to do most things that we want. I’m going to have a decent play with all of them and I wouldn’t be surprised if out of the big mix came a little bit of utPLSQL along with some DBUNIT to put up and tear down the data required.


5 Responses to Database testing – Unit Testing

  1. Dominic,

    I was very glad to see your post regarding code testing (aka, unit testing) in the world of Oracle and PL/SQL. My feeling is that deficient testing is generally an ongoing worldwide crisis in the software industry, and the more attention paid, the better.

    Just a few comments regarding the tools you mentioned:

    utPLSQL – the PL/SQL equivalent of JUnit, utPLSQL is used by at least hundreds and hopefully thousands of PL/SQL developers. It has been, in essence, the only semi-automated testing framework available for many years. The main problem that I have with utPLSQL (and the rest of the xUnit frameworks) is that you still have to write most of the test code yourself. The result is that only the most dedicated and sophisticated PL/SQL shops will ever take advantage of utPLSQL.

    Ounit – a very simple UI on top of utPLSQL. I built it several years ago, and it is a “mature” product – it is not maintained, supported or enhanced. It does what it does reasonably well, but I want to make sure expectations are set properly.

    Quest Code Tester for Oracle – this is my latest effort at building a unit testing tool that LOTS of developers might actually use. The key differentiator between utPLSQL and Code Tester is that with Code Tester you describe the expected behavior of your program through a graphical interface, and it will then generate the test code for you. It certainly does help you test the contents of datasets, as well. We don’t yet make the setup and teardown as automatic and elegant as I would like, but we are getting there!

    I firmly believe that testing will never take hold in a big way in the world of PL/SQL and Oracle unless much of it, especially the writing of the test code, can be automated.

    Regards, Steven

  2. dombrooks says:

    Thanks, Steven,
    Couldn’t agree more. Automation is key, including in the setup and teardown. And current levels of database testing are very poor. And that’s coming from me, fairly ignorant on the matter and complicit in the current deficient status quo.

  3. laxman says:

    good Blog

  4. Pingback: Unit testing good « OraStory

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: