Strongholds of Confidence: Self-Testing Your Database

Comments 0

Share to social media

This article was originally published on Clean Database Development.

Software testing has always been a very important topic in professional development because software – unlike, for example, buildings – changes a lot from the first line of code, through to release, and from there across its whole lifetime. Given that constant change and its complexity, every honest developer will have to accept that there is no software without bugs.

The rise of agile methods, ideas like continuous integration and the urge to have more frequent releases led to an even stronger focus on modern testing strategies. Here’s an interesting blog post about correlation of release frequency and agile practices – especially including automated testing.

Unfortunately there is a kind of “holy war” around some testing programming techniques, but nonetheless the need for automated self-testing (be it system, integration, or unit-testing) in todays software development is real and uncontroversial among experts. Therefore I will use the general term of “self-testing” in this article, concentrating on the very basic idea and trying to give an introduction on how it can be done in database projects. I get the impression that many of today’s database projects are implemented without the basics of self-testing, so let me try to show you the benefits without nit-picking about specific techniques.

Basics of self-testing

Computers are very formal beings and at the moment it’s not possible to tell them what to do in a natural language. This might change in future, but until then developers are stuck with programming languages to tell a machine what they want. Humans on the other hand are not very formal beings and programming is not natural to us. This results in software which has flaws and that again results in testing being a very substantial part of software development, especially if the acceptance for errors in production is limited (this depends on the customer, the specific project and the purpose of the software – a bug in the employee database of an intranet site might not be as impactful as an error in a flight control software).

Let’s look at a real life example:

Your Sith Lord commander gently asks you to create a new function in the Death Star control database. It should give information about the current energy level of several pillars and also allow the user to set the target energy level of every pillar.

As a skilled database developer you decide to create a new database view and an instead-of trigger to control which data may be updated. You work hard and are confident in what you do, implement the view and test it thoroughly (you don’t want to end up floating around while your boss Force-grabs your throat, do you?). Everything works fine and you are very proud of yourself. Then, after a few months the commander wants new information in the control panel. You had nightmares of Rebels preparing a suicide attack on the Death Star the night before, you’ve got a headache and you’re pretty tired. Although you add a new column to your view and issue a “CREATE OR REPLACE VIEW…” command (everyone knows the Dark Side uses Oracle, why else would administering these databases cause so much pain?). You quickly check the control panel, see the new information appearing and go to your rest area. It will be your last rest.

For those who are not so deep into Oracle databases: when you replace a view, all attached instead-of triggers are lost and you have to recreate them (hopefully you got your database version controlled, so you are able to easily solve this issue if it happens). This happened to me at least once in a production situation. I made a quick change in a view, ran a create-or-replace script and some minutes later had a customer on the phone, yelling that he can’t save values any more or gets an error when he tries.

You could argue that I was just not professional and that I should’ve known about the impacts – and that might even be true. But in reality there will be situations where pressure is high, your mind is not as clear as it should be or you just don’t remember everything you should (actually we should be glad about the ability to forget – if only we could control it a bit better…).

I accept the fact that I’m human and that I’m likely to forget things I shouldn’t forget. I even accept the possibility of making mistakes. I think it’s professional to accept the risk of making mistakes and to act accordingly – for example by including self-tests in your project.

In our example, a simple test program written in PL/SQL would have saved us. Such a self-test could contain the following steps:

  • Reading expected test data from the view after inserting it into the base tables
  • Updating test data by updating the view and checking the output by rereading the view
  • Inserting test data by inserting into the view and checking the output by rereading the view

Now we just have to make sure we run our tests every time we change something. This would have immediately revealed the missing instead-of trigger and we could’ve acted before updating production system.

This is a pretty low-level self-test directly attached to a specific function or module. It’s what some of you might already know as a unit test: directly testing the unit, in this case the view (one could argue that it’s not a “unit”-test because it tests three units in combination: the view, the table behind and the instead-of trigger).

Self-testing can and should contain more than such low-level tests. I’ll give you a very basic example I include in every new database project right from the start: assure that there are no invalid objects in your database:

In application development we have similar functionality included in our compiler most of the time. You normally can’t build an application with parts being (compiler-)invalid. But you can have invalid parts in databases and still use the rest, which is a very important feature but also puts some responsibility on the developer’s shoulders (I don’t think we have to argue about whether or not invalid database objects are clean and professional development, do we?).

This simple function is a great example for what self-testing includes. It tests your project from a from a higher abstraction. And that’s not all: It’s absolutely valid – and useful – to have several “high-level” self-tests checking business logic in your data which can’t be enforced by simple database constraints. This has nothing to do with unit-testing, but is as important and beneficial for the safety of your project.

I encourage you to read the excellent article by Martin Fowler on self-testing, and I’ll try to frame a very short definition of what self-testing is myself:

Self-testing provides an automated and easy-to-use check that a specific problem or feature is solved in the project.

Why should I care?

I guess the scenario with Sith Lords wanting to kill you didn’t convince you entirely? You think that developing tests around your database functionality is very costly and will ruin your project’s timeline? You think that including more code will introduce even more bugs? You think self-testing is for people who don’t know how to develop correctly in the first place?

The topic of self-testing has been around in the application development space for decades and there are many good and comprehensive arguments for self-testing, for example the Top 12 Reasons to write Unit Tests (note that this answer is from 2003 and is still valid, so we can guess we found a pretty elemental topic in software development). The pragmatic programmer contains several chapters about self-testing – it’s a book I’d recommend to every developer, no matter if they’re involved with databases or application development.

Here’s my own (incomplete) list of reasons why you should have (automated) self-tests in your project:

  • Self-tests can give you a different view on a problem. You might have to think about a use-case or a feature in a different way, which will lead to a deeper understanding of the business case and therefore enable you to deliver a better solution.
  • Self-tests are a great way to document the purpose of a function or use-case. It can provide a “real life” example on how a function can work.
  • Self-tests can assure you that a bug, once  found and fixed, will never ever come up again. They can increase quality and reduce bugs in your project.
  • Self-tests give you the opportunity to show your customer that you care about your software’s quality. Why not provide the output of a automated test-run to your customer (with some explanation what it is and what the purpose is of course)? You invest into quality and reliability – your customer will be glad to have such kind of partner.
  • Self-tests can help to write better code. Having a clean and simple API makes it easier to self-test, so self-testing encourages you to develop a cleaner and simpler API.

There are many more reasons why self-tests are a great benefit (and to be honest a necessity) to your project, but there is one thing which is the most important for me:

Self-testing creates a stronghold of confidence for change.

Software changes, sometimes faster than anyone involved in a project can imagine. Requirements change, use-cases change, expectations change and so should software. To make your project and your software great, you have to embrace and welcome change – but that’s only possible if you can be confident. You have to be confident, that adding a new feature won’t break another. You have to be confident that changing an API won’t make the software stop working. You have to be confident to improve your codebase via refactoring without blowing it up. You have to be confident, that your customer will have a great experience after you ship your next update. You have to be confident that you won’t destroy a project when helping out in a collaborative project situation.

Having a solid suite of self-tests creates that confidence. Developing inside such a stronghold of confidence is relieving, motivating and gives room for the creativity you need.


It is totally possible to create self-tests without the help of any frameworks or tools. Just create a bunch of T-SQL or PL/SQL scripts and a simple pyhton, perl or Powershell file to run them. You can even just create a main-SQL-file which calls all the others (hard-coded). It is far better than having no tests, no question.

Though in the long run it might not be enough and you might want some kind of help to reduce boilerplate code.

There are several tools (at least for the big DBMS’ Oracle and SQL Server) which make self-testing significantly easier. Although this topic could fill its own article, I want to give you some possible entry-points, depending on your DBMS:

But I can’t set my project on hold for three months to write self-tests for everything

You know what? That’s okay. You don’t need to. We don’t work in a perfect environment, we don’t create perfect software and we don’t live in a perfect world. It’s no reason and no excuse, though, not to start improving all of those things.

The most important thing to get started with self-testing is a change of mind. Don’t consider writing self-tests as burden and duty, increasing the cost of your project without providing (visible) benefit. Instead, make the quality of your software a key goal, welcome change in your project and treat self-testing as powerful addition to your code base which gives you the breathing room to quickly step up without hestitation.

For the practical approach two simple rules might be enough to get started with including self-testing in your current project:

  • Every time a software bug occurs, write a self-test revealing this bug. You can use the exact data set which led to the problem in the first place. Make sure your self-test fails. Then fix the bug.
  • Write self-tests for every piece of functionality you change or add.

Your test suite will slowly grow without having huge up-front impact on timelines and costs of your project. And to be honest, functionality which is neither changed nor produces mistakes in the behaviour of the software doesn’t need to be self-tested anyway, does it?

Meaningful testing

Once you decided to improve your software with self-tests, make sure your self-testing is meaningful.

It might be obvious, but you should take failed tests seriously. Test as often as possible (for example after every change on your local environment, after every commit in a separate, shared environment, after every delivery into a consolidation environment). If a test fails, don’t proceed until it is fixed. I’ve seen people ignore self-tests with the comment “Ah, I know the problem which is causing this, I don’t need to fix that immediately” and even felt the urge to behave like that myself. Don’t be that guy. Failing tests mean bad quality software – either in the software itself or in the tests. Don’t accept bad quality (not even if it is a very nasty topic like charset problems).

You included a high-level test for invalid objects and it failed? Fix the invalid objects or remove them. If they are not fixable, they are waste in your database, get rid of them.

Don’t write tests for simple or obvious behaviour. Testing a function which returns the next value of a sequence is as beneficial as the following comment (I like good commenting of source code, don’t get me wrong):

Test your public API. Test functionality that matters or test very complicated algorithms. Test things that are critical for the success of your software.

If you test trivial behaviour, you pollute your source code and waste the time you could have invested into creating a more meaningful test on a higher level.

Don’t write tests just to increase the number of self-tests your project has. They will provide you a false illusion of confidence and reduce maintainability. Writing self-tests doesn’t dispense you from using your brain. It is a tool to increase the quality of your software. If your self-tests lead to lower code quality, you are doing it wrong.

Stay as critical with your tests as with your other code, otherwise your tests will become meaningless. And meaningless tests are worse than no tests, because they don’t provide strongholds of confidence. They provide stronghold-facade made of pasteboard, hiding a giant, deep hole.

Test-Driven Development (TDD)

One of the most famous agile programming techniques of the last years, Test-Driven Development (TDD), created a design principle upon test-first, focusing the whole development and design process heavily around (unit-)tests.

While TDD definitely brought the importance and necessity of automated self-tests back into people’s minds, parts of the community practicing TDD got more and more aggressive about TDD being the only valid and “professional” way to develop software. There are serious arguments empirically questioning the usefulness of Unit-testing in most situations and criticizing how heavily focusing on Unit-tests damages software design. The death of TDD has even been announced several times.

To be clear: the critics of heavy unit-testing and TDD don’t question the usefulness of automated self-testing. They criticize the heavy focus on a special kind of self-tests and the design principle created around it.

In my opinion, the TDD approach and especially the arguments of many TDD practitioners today include an increasing amount of dogma. In my experience dogmatic approaches and arguments rarely lead to pragmatic solutions (this is a general problem of dogma, not only in questions of software development techniques).

I don’t say TDD or unit-testing is dead, it can be a great tool and suitable approach for some situations. But I encourage everyone to stay critical towards any kind of dogmatic, religious ruleset. Self-tests must be meaningful. If they aren’t, they are useless code smell hurting your project, no matter whether they were included as part of TDD or not.

So let’s get started with writing meaningful, automated, easy to run self-tests to give us the freedom and creativity we need in a fast-changing environment.

Showcase: The example in utPLSQL

For I am involved in the Open Source Unit-Testing framework utPLSQL I want to quickly show you how straightforward you can achieve the previous example with the use of this powerful framework.

Let’s assume we installed utPLSQL 3.0.4 (latest release at the time I write this) in our database, so we can just create a new package like this:

I hope you agree with me that this approach is just awesome and beautiful.

If you work with Oracle and your database contains logic, there is no excuse not to check out how utPLSQL can help you building your stronghold!

About the author

Samuel Nitsche

See Profile

Samuel Nitsche is a curiosity-driven software developer who programs, learns and collaborates in the software trade since the early 2000s. He works as Senior Software Developer and trainer at Smart Enterprise Solutions. His main interest is on modern database development, automated testing and code quality, topics he writes regularly about on different platforms, mainly on his blog at He is an Oracle ACE, one of the main contributors and maintainers of utPLSQL and loves to share his experience in an entertaining way - gladly in Sith robes - at meetups and conferences.

Samuel's contributions