The particularity of database unit tests is that not only the parameters of a function but also the data, which is stored in database tables, can be both the input and the outcome of the module being tested. Moreover, the execution of one test could influence the following tests due to the changes it makes to the data, so it might be necessary to execute the tests in a specific order.

Therefore, the testing framework should be able to insert data into the database, run tests, and then analyze the new data. Furthermore, the testing framework could also be required to manage the transactions in which the tests are executed. The easiest way to do all of that is by writing the tests as SQL scripts. In many cases, it is convenient to wrap them into stored procedures (functions in case of PostgreSQL). These procedures can be put in the same database where the components being tested were created.

Testing functions can take test cases from a table iterating through its records. There could be many testing functions, and one separate function that executes them one by one and then formats the result protocol.

Let's create a simple example. Suppose there is a table in the database and a function performing an action on the data in the table:

CREATE TABLE counter_table(counter int);
CREATE FUNCTION increment_counter() RETURNS void AS
$$
BEGIN
  INSERT INTO counter_table SELECT count(*) FROM counter_table;
END;
$$
LANGUAGE plpgsql;

The table contains only one integer field. The function counts the number of records in the table, and inserts that number in the same table. So, subsequent calls of the function will cause insertion of the numbers 0, 1, 2, and so on into the table. This functionality is an object for testing. So, the test function can be the following:

CREATE FUNCTION test_increment() RETURNS boolean AS
$$
DECLARE
  c int; m int;
BEGIN
  RAISE NOTICE '1..2';
  -- Separate test scenario from testing environment
  BEGIN
    -- Test 1. Call increment function
    BEGIN
      PERFORM increment_counter();
      RAISE NOTICE 'ok 1 - Call increment function';
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'not ok 1 - Call increment function';
    END;
    -- Test 2. Test results
    BEGIN
      SELECT COUNT(*), MAX(counter) INTO c, m
        FROM counter_table;
      IF NOT (c = 1 AND m = 0) THEN
        RAISE EXCEPTION 'Test 2: wrong values in output data';
      END IF;
      RAISE NOTICE 'ok 2 - Check first record';
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'not ok 2 - Check first record';
    END;
    -- Rollback changes made by the test
    RAISE EXCEPTION 'Rollback test data';
  EXCEPTION
    WHEN raise_exception THEN RETURN true;
    WHEN OTHERS THEN RETURN false;
  END;
END;
$$
LANGUAGE plpgsql;

The preceding test function works in the following way:

If we run the preceding test function, we will get the following protocol:

username=# SELECT test_increment();
NOTICE:  1..2
NOTICE:  ok 1 - Call increment function
NOTICE:  ok 2 - Check first record
 test_increment
----------------
 t
(1 row)

The test is successful!

Suppose the requirements have been changed, and now it is necessary to add another field in the table to record the time when a value was inserted:

username=# ALTER TABLE counter_table ADD insert_time timestamp with time zone NOT NULL;

After the change of the data structure, one should run the test again to see if the function still works:

username=# SELECT test_increment();
NOTICE:  1..2
NOTICE:  not ok 1 - Call increment function
NOTICE:  not ok 2 - Check first record
 test_increment
----------------
 t
(1 row)

The test fails. That happens because the increment_counter() function does not know about the other field, and it should also be changed:

CREATE OR REPLACE FUNCTION increment_counter() RETURNS void AS
$$
BEGIN
  INSERT INTO counter_table SELECT count(*), now() FROM counter_table;
END;
$$
LANGUAGE plpgsql;

And now, if the testing function is called again, it will succeed:

 username=# SELECT test_increment();
NOTICE:  1..2
NOTICE:  ok 1 - Call increment function
NOTICE:  ok 2 - Check first record
 test_increment
----------------
 t
(1 row)

The preceding test function is not perfect. First, it does not check if the function increment_counter() actually counts the records. The test will succeed even if the function increment_counter() just inserts the constant value of zero in the database. To fix that, the test function should run the test twice, and check the new data.

Secondly, if the test fails, it would be good to know the exact reason for the failure. The testing function can get that information from PostgreSQL using the GET STACKED DIAGNOSTICS command, and show it with RAISE NOTICE.

The current and the improved versions of the code are available in the addendum, in the files unit_test_short.sql and unit_test_full.sql.

It is a very good practice to have unit tests for the database components in complicated software systems. This is because, in many cases, the database is a component that is shared among many software modules. And any development in the database on behalf of one of those modules can cause the other modules to break. In many cases, the system by which the database object is being used and the way in which it is being used is not clear. That's why it is essential to have unit tests that emulate the usage of the database by each of the external systems. And when developers work on changes to the data structure, those tests should be executed to check if the systems can work with the new structure.

The tests could be run in a newly created testing environment. In that case, the install script should include some code for creating testing data and some necessary data like the commonly used lookup tables. Additionally, the test environment could be based on a clone of the production database. The test script should also contain some cleanup code.

The preceding example has some more drawbacks. For example, if the function being tested raises warnings or notices, they will spoil the test protocol. Moreover, it implies that the testing function is not written very well. For example: the test ID and description are repeated, the BEGIN-END blocks are bulky, and the developer of the test function did not take care about formatting the result protocol. All these tasks could be automated by using any of the unit test frameworks.

There is no unit test framework that comes out of the box with PostgreSQL, but there are several of them available from the community.

The most commonly used one is pgTAP (http://pgtap.org/). One can download it from GitHub, and install in the test database. The installation is quite easy and described well in the documentation.

The tests are written as SQL scripts, and they can be run in batches by the utility called pg_prove, which is provided with pgTAP. There is also a way to write tests as stored functions using plpgsql.

The pgTAP framework provides the user with a set of helper functions that are used to wrap the testing code. They also write the results into a temporary table, which is used later to generate the testing protocol. For example, the ok() function reports a successful test if its argument is true, and a failed test, if not. The has_relation() function checks the database if the specified relation exists. There are about a hundred of those functions.

The test scenario that was described in the preceding section can be implemented in the following script using pgTAP:

-- Isolate test scenario in its own transaction
BEGIN;
-- report 2 tests will be run
SELECT plan(2);
-- Test 1. Call increment function
SELECT lives_ok('SELECT increment_counter()','Call increment function');
-- Test 2. Test results
SELECT is( (SELECT ARRAY [COUNT(*), MAX(counter)]::text FROM counter_table), ARRAY [1, 0]::text,'Check first record');
-- Report finish
SELECT finish();
-- Rollback changes made by the test
ROLLBACK;

And the result of execution of the script:

1..2
ok 1 - Call increment function
ok 2 - Check first record

There are several other unit test frameworks that are not as popular:

plpgunit: The tests are written as functions in plpgsql. They use the provided helper functions to perform tests like assert.is_equal(), which checks if two arguments are equal. The helper functions format the results of testing and display them on the console. A managing function, unit_tests.begin(), runs all the testing functions, logs their output into a table, and formats the results protocol.

The advantage of plpgunit is its simplicity—it is very lightweight and easy to install, there is only one SQL script that you needs to run to get the framework in their database.

The plpgunit framework is available in GitHub at https://github.com/mixerp/plpgunit.

Another unit test framework that is available is the dklab_pgunit. The general idea is similar to plpgunit; the tests are plpgsql functions, which are executed by another managing function. The main difference is that this framework can optimize the process of running the tests when several of them use the same set up procedure. That procedure will be executed only once, and then several tests will use the same results. The biggest advantage of this framework is that it can be used not only on an empty, just-created test database but also on an image of the production database, which can be quite heavy.

Unfortunately, the project seems to be abandoned since 2008. The dklab_pgunit framework can be found at http://en.dklab.ru/lib/dklab_pgunit/.