Software testing is the process of analyzing program components, programs, and systems with the intention of finding errors in them, and for determining or checking their technical limits and requirements.
The database is a specific system that requires special approaches for testing. This is because the behavior of database software components (views, stored procedures, or functions) depends not only on their code but also on the data; in many cases, it is not possible to just repeat the same function calls to get the same results.
That's why one should use specific techniques for testing database modules. PostgreSQL provides some features for helping developers and testers to do that.
In software architecture, the database usually lies at the lowest level. Business software processes the data, and the data is modeled and stored in the database. This is the reason why, in most cases, changes in the database schema affect many other software components.
This topic is closely related to database development and refactoring. Database objects are often accessed by several different systems, and when one of them is changed, the others can also be affected. Moreover, the database structure can be changed without changing the code of the software using the database, and the developer should be sure that the application would still work with the new data structure.
In this chapter, some methods of testing database objects are discussed. They can be applied when implementing changes in the data structure of complex systems, and when developing database interfaces.
Unit testing is a process in software development that makes it possible to check for errors in the various components or modules of software. In databases, those components are stored procedures, functions, triggers, and so on. A view definition, or even a code of queries that applications use, can be an object for unit testing.
The idea behind unit testing is that for every module of the software system, like class or function, there is a set of tests that invokes that module with a certain input data, and checks if the outcome of the invocation matches the expected result. When a module being tested needs to interact with other systems, those systems can be emulated by the test framework so that the interaction can also be tested.
The set of tests should be big enough to cover as much of the source code of the tested module as possible. This can be achieved when the tests imply invocation of the tested code with all possible logical combinations of values of input parameters. If the tested module is supposed to be able to react on invalid data, the tests should include that as well. The execution of those tests should be automated, which makes it possible to run the tests each time a new release of the tested module is developed.
All this makes it possible to change the software, and then quickly check whether the new version still satisfies the old requirements. This approach is called regression testing. Additionally, there is a software development technique called test-driven development. It implies writing tests that reflect the requirements of a software component first, and then developing code which satisfies the tests.
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:
BEGIN-EXCEPTION-END
block. It isolates the test from the transaction that has executed the test, and makes it possible to run another test afterwards, which could use the same data structures.BEGIN-EXCEPTION-END
block. This makes it possible to continue testing even if one of the tests fails.increment_counter()
function. The test is considered successful if the function is called without any error. The test is considered unsuccessful if an exception of any kind occurs.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.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/.
18.117.105.74