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

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

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. Suppose we want to test this functionality. So, the test function can be as follows:

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:

• The whole test scenario is executed in its own BEGIN-EXCEPTION-END block. It isolates the test from the transaction that executes the tests, and makes it possible to run another test afterward, which could use the same data structures.
• Each test in the test scenario is also executed in its own BEGIN-EXCEPTION-END block. This makes it possible to continue testing even if one of the tests fails.
• The first test runs the function, increment_counter(). The test is considered successful if the function is executed without any error. The test is considered unsuccessful if an exception of any kind occurs.
• The second test selects the data from the table and checks whether it matches the expected values. If the data is wrong, or if the select statement fails for any reason, the test fails.
• The result of testing is reported to the console by the RAISE NOTICE commands. The output format follows the Test Anything Protocol (TAP) specification, and can be processed by a test harness (external testing framework), like Jenkins.
• The function returns true when the execution of the tests succeeded, regardless of the results of the tests. Otherwise, it returns false.

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

car_portal=> 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 to the table to record the time when a value was inserted:

car_portal=> ALTER TABLE counter_table ADD insert_time timestamp with time zone NOT NULL;
ALTER TABLE

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

car_portal=> 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. This happens because the increment_counter() function does not know about the new field. The function should also be changed:

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

Now the tests are successful again:

car_portal=> 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 whether the increment_counter() function actually counts the records. The test will succeed even if the increment_counter() function just inserts the constant value of zero in the
database. To fix this, 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 could get this information from PostgreSQL using the GET STACKED DIAGNOSTICS command, and show it with RAISE NOTICE.
The improved version of the test function code is available in the attached media in the file examples.sql. It is too big to put it here.

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 by several services or modules. Any development in the database on behalf of one of those services can cause others to break. In many cases, it is not clear which service uses which object in the database and how they are used. That's why it is essential to have unit tests that emulate the usage of the database by each of the external services. And when developers work on changes to the data structure, those tests should be executed to check whether the whole system 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 to create testing data. Alternatively, the tests could be executed in a copy of the production database. The test script could also contain some cleanup code.