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 to release and from there during its whole lifetime. Due to that constant change and its complexity every honest developer will have to accept someday, 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 (interesting blog post about correlation of release frequency and agile practices – especially including automated testing).

Unfortunately there is some kind of “holy war” about some programming techniques around testing (I will write a bit more on that in the last chapter of this blog post), but nonetheless the need for automated self-testing (be it system-, integration- or unit-testing) in todays software development is real and uncontroversial among the 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. In my impression still many of today’s database projects don’t even know about 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 to 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-commander gently asked you to create a new function in the deathstar control database. It should give information about the current energy level of several pillars and also allow to set the target energy level of every pillar.

As 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 float around by the force grabbing your throat, do you?). Everything works fine and you are very proud of yourself. Then, after a view months the sith-commander wants new information in the control panel. You had nightmares of rebels preparing a suicide-attack on the deathstar the night before, got headaches and are 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 much into Oracle databases: When you replace a view, all attached instead-of triggers are lost and you have to re-create them (hopefully you got your database version controlled, so you are able to easily solve such an issue if it happens). In fact this happened to me at least once in a production situation. Just a quick change in a view, run a create-or-replace script and some minutes later a customer on your phone, telling that he can’t save values anymore or gets an error when trying.

One can 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 myself accept the fact that I’m human and 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 with including self-tests into 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 re-reading the view
• inserting test-data by inserting into the view and checking the output by re-reading the view

create or replace procedure test_pillar_view
as
  v_energylevel integer;
begin
  -- Inserting test-data into the base table
  insert into pillar_table ( id, energylevel ) values ( -1, 100 );

  -- Test we can read the data via view
  select energylevel into v_energylevel from pillar_view where id = -1;
  if v_energylevel  100 then
    raise_application_error(-20000, 'expected energylevel to be 100, was ' || to_char(v_energylevel));
  end if;

  -- Update the test-data via view
  update pillar_view set energylevel = 150 where id = -1;
  select energylevel into v_energylevel from pillar_view where id = -1;
  if v_energylevel  150 then
    raise_application_error(-20000, 'expected energylevel to be 150, was ' || to_char(v_energylevel));
  end if;

  -- Insert new test-data via view
  insert into pillar_view ( id, energylevel ) values ( -2, 200 );
  select energylevel into v_energylevel from pillar_view where id = -2;
  if v_energylevel  200 then
    raise_application_error(-20000, 'expected energylevel to be 200, was ' || to_char(v_energylevel));
  end if;

  -- rollback all things we've done during the test
  rollback;
exception when others then
  rollback;
  raise;
end;
/
-- Run
call test_pillar_view();

Now we have just to make sure we run our tests every time we change something. Such a self-test 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 unit-tests: 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 and 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:

create or replace procedure check_all_valid as
  v_count integer;
begin
  select count(*) into v_count from user_objects where status  'VALID';
  if ( v_count  0 ) then
    raise_application_error(-20000, 'Check failed: not all database objects are valid!');
  end if;
end;

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 of Martin Fowler on self-testing and will 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 threat-scenario including sith wanting to kill you didn’t convince you entirely? You think developing tests around your database functionality is very costly and will ruin your project’s timeline? You think 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 corner 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 he/she is into databases or application development.

Here’s my own (by far not complete) 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 that a bug, once appeared and solved, 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 providing the output of a automated test-run to your customer (with some explanation what it is and what the purpose is of course)? You do 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 especially one thing which is 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 reliefing, motivating and gives room for the creativity you need.

Tools

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 and although this topic can (and maybe will) fill an own blog-article, I want to give you some possible entry-points, depending on your DBMS:

• Oracle: utPLSQL
• SQL Server: tSQLt
• Generic:
  • DbFit
  • DbUnit

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 functionality you change or add.

Your test-suite will slowly grow this way 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 take failed tests serious. 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):

-- Increment i
i := i+1;

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 the 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, although, serious arguments, empirically questioning the usefulness of Unit-testing in most situations and criticizing how heavily focusing on Unit-tests damages software design. Even the dead of TDD was announced several times.

To make that 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. As I said in the previous chapter: Self-tests must be meaningful. If they aren’t, they are useless codesmell hurting your project, no matter if 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 engaged 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, we can just create a new package like this:

create or replace package test_pillar_view
as
  --%suite(Pillar view)

  --%test(Read from view)
  procedure read_view;

  --%test(Update view)
  procedure update_view;

  --%test(Insert into view)
  procedure insert_view;

  --%beforeall
  procedure setup;
end;
/

create or replace package body test_pillar_view
as
  procedure read_view
  as
    v_energylevel int;
  begin
    select energylevel into v_energylevel from pillar_view where id = -1;
    ut.expect(v_energylevel).to_equal(100);
  end;

  procedure update_view
  as
    v_energylevel int;
  begin
    update pillar_view set energylevel = 150 where id = -1;
    select energylevel into v_energylevel from pillar_view where id = -1;
    ut.expect(v_energylevel).to_equal(150);
  end;

  procedure insert_view
  as
    v_energylevel int;
  begin
    insert into pillar_view ( id, energylevel ) values ( -2, 200 );
    select energylevel into v_energylevel from pillar_view where id = -2;
    ut.expect(v_energylevel).to_equal(200);
  end;

  procedure setup
  as
  begin
    insert into pillar_table ( id, energylevel ) values ( -1, 100 );
  end;
end;
/

-- Run
set serveroutput on
call ut.run();

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!

Update #1, 2018-03-31: I previously mentioned Visual Studio Testing as self-testing alternative for SQL Server. I removed it because it’s just too outdated and not maintained. I added the generic alternatives in return.
I also added the showcase for utPLSQL because it is awesome.