Samuel Pesse, an Oracle ACE, wrote in his blog about how to self-test your database. The content below is a republishing of his words.

Strongholds of Confidence: Self-Testing Your Database

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 the 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 today’s 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 How to Self-Test Your Database

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 the 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 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 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

1 create or replace procedure test_pillar_view
2 as
3   v_energylevel integer;
4 begin
5   -- Inserting test-data into the base table
6   insert into pillar_table ( id, energylevel ) values ( -1, 100 );
7
8   -- Test we can read the data via view
9   select energylevel into v_energylevel from pillar_view where id = -1;
10  if v_energylevel  100 then
11    raise_application_error(-20000, 'expected energylevel to be 100, was ' || to_char(v_energylevel));
12  end if;
13
14  -- Update the test-data via view
15  update pillar_view set energylevel = 150 where id = -1;
16  select energylevel into v_energylevel from pillar_view where id = -1;
17  if v_energylevel  150 then
18    raise_application_error(-20000, 'expected energylevel to be 150, was ' || to_char(v_energylevel));
19  end if;
20
21  -- Insert new test-data via view
22  insert into pillar_view ( id, energylevel ) values ( -2, 200 );
23  select energylevel into v_energylevel from pillar_view where id = -2;
24  if v_energylevel  200 then
25    raise_application_error(-20000, 'expected energylevel to be 200, was ' || to_char(v_energylevel));
26  end if;
27
28  -- rollback all things we've done during the test
29  rollback;
30 exception when others then
31  rollback;
32  raise;
33 end;
34 /
35 -- Run
36 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 the 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:

1 create or replace procedure check_all_valid as
2   v_count integer;
3 begin
4   select count(*) into v_count from user_objects where status  'VALID';
5   if ( v_count  0 ) then
6     raise_application_error(-20000, 'Check failed: not all database objects are valid!');
7   end if;
8 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 of what self-testing includes. It tests your project 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 of 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 customers that you care about your software’s quality. Why not provide the output of an automated test-run to your customer (with some explanation of what it is and what the purpose is of course)? You do invest in 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 relieving, motivating and gives room for the creativity you need.

Tools to Self-Test Your Database

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 python, 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

Holding Your Project vs. Writing Self-Tests

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 a powerful addition to your codebase which gives you the breathing room to quickly step up without hesitation.

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 a 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 behavior 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 behavior. 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):

1 -- Increment i
2 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 behavior, you pollute your source code and waste the time you could have invested in 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. 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:

1 create or replace package test_pillar_view
2 as
3   --%suite(Pillar view)
4
5   --%test(Read from view)
6   procedure read_view;
7
8   --%test(Update view)
9   procedure update_view;
10
11  --%test(Insert into view)
12  procedure insert_view;
13
14  --%beforeall
15  procedure setup;
16 end;
17 /
18
19 create or replace package body test_pillar_view
20 as
21  procedure read_view
22  as
23    v_energylevel int;
24  begin
25    select energylevel into v_energylevel from pillar_view where id = -1;
26    ut.expect(v_energylevel).to_equal(100);
27  end;
28
29  procedure update_view
30  as
31    v_energylevel int;
32  begin
33    update pillar_view set energylevel = 150 where id = -1;
34    select energylevel into v_energylevel from pillar_view where id = -1;
35    ut.expect(v_energylevel).to_equal(150);
36  end;
37
38  procedure insert_view
39  as
40    v_energylevel int;
41  begin
42    insert into pillar_view ( id, energylevel ) values ( -2, 200 );
43    select energylevel into v_energylevel from pillar_view where id = -2;
44    ut.expect(v_energylevel).to_equal(200);
45  end;
46
47  procedure setup
48  as
49  begin
50    insert into pillar_table ( id, energylevel ) values ( -1, 100 );
51  end;
52 end;
53 /
54
55 -- Run
56 set serveroutput on
57 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!

To learn more, check out Samuel’s blog for additional articles.

Additional Resources

COLLABORATE 20 will take place April 19-23, 2020 at the Mandalay Bay Resort and Casino in Las Vegas, Nevada! Don’t miss this chance to share inspiration, insights, and solutions with your peers, vendors, and the Oracle team! Register before March 6, 2020, to take advantage of Early Bird pricing.