PGUnit - unit test framework for Postgresql

The purpose of this suite of stored procedures is to allow a user to run unit tests as stored procedures.

The testing is based on a specific naming convention that allows automatic grouping of tests, setup, tear-downs, pre- and post- conditions.

Each unit test procedure name should have "test_case_" prefix in order to be identified as an unit test. Here is the comprehensive list of prefixes for all types:

• "test_case_": identifies an unit test procedure
• "test_precondition_": identifies a test precondition function
• "test_postcondition_": identifies a test postcondition function
• "test_setup_": identifies a test setup procedure
• "test_teardown_": identifies a test tear down procedure.

For each test case the following 3 transactions are executed:

1. setup transaction: the setup procedure is searched based on the test name. If one is found it is executed in an autonomous transaction
2. unit test transaction: the pre and post condition functions are searched based on the test name; if they are found the autonomous transaction will be: if the precondition is true (default if one is not found) the unit test is ran, then the postcondition function is evaluated (true if one is not found). If any condition returns false the test is failed
3. tear down transaction: if a tear down procedure is found it is executed in an autonomous transaction indepedent of the unit test result.

A unit test execution can have 3 results: successful if the condition functions are true and the unit test procedure doesn't throw an exception, failed if there is an action exception triggered by a condition function or an assertion, and finally erroneous if any other exeception is triggered by any of the code above.

Tests logical grouping

The tests can be grouped based on their name structure. The underscore character is used to separate the test name into a hierarchical structure so that you can share the setup, tear down, or the preconditions across several unit tests. For instance the tests 'test_case_finance_audit_x' and 'test_case_finance_accounting_y' can share the setup procedure called 'test_setup_finance', the teardown procedure 'test_teardown_finance' as well as the precondition 'test_precondition_finance' function as they share a common prefix.

If, let's say, the audit tests have a specific precondition function, you can define a new 'test_precondition_finance_audit' function and that will be shared accross all unit test procedures with prefix 'test_case_finance_audit_' and override the common 'test_precondition_finance' function.

Using the built-in grouping mechanism you can re-use supporting code such as the data setup and tear down across unit tests . There is little if any gain if the save data setup is shared across multiple tests and they will have to be present in test-specific setup function while having the exact same content.

Running one or more tests

To run the entire test suite the 'test_run_all' stored procedure needs to be used:

select * from test_run_all();

You can pick one or an entire group of tests based on their prefix using the 'test_run_suite' stored procedure:

select * from test_run_suite('finance');

The statement above will pick up all unit tests starting with the 'test_case_finance' prefix together with the associated support functions and procedures.

The select statement returns the type 'test_results', which is:

create type test_results as (
  test_name varchar, 
  successful boolean, 
  failed boolean, 
  erroneous boolean, 
  error_message varchar,
  duration interval);

Setting up PGUnit

The plpgsql code depends on the dblink extension being present in the database you run the tests on, so you need to ensure the statement below has been run before loading the test code:

CREATE EXTENSION DBLINK;

If you want to set up PGUnit in a dedicated schema like 'pgunit', run these two lines of SQL:

CREATE SCHEMA pgunit;
CREATE EXTENSION DBLINK SCHEMA pgunit;

You should run the PGUnit.sql code using either the psql command line tool or a tool like PGAdmin 4's query tool and deploy it in the public schema of the selected database or a dedicated schema, such as pgunit. The code should be deployed as superuser, but can be used by ordinary users.

A convenient way to install the PGUnit suite in a dedicated schema is to temporarily change the search path like this:

ALTER DATABASE my_db SET search_path TO pgunit;

run PGUnit.sql, and then reset the search_path. There is a good tip for this on Stack Exchange: https://dba.stackexchange.com/questions/145280/reset-search-path-to-the-global-cluster-default

ALTER DATABASE my_db RESET search_path;

Removal

The PGUnitDrop.sql has the code you can use to remove all PGUnit code from the database.

Assertion procedures

Examples

Test case that checks if an application user is created by a stored procedure

• the user id is returned if user id 1 is a parent
• the id is larger than a thresold

create or replace function test_case_user_create_1() returns void as $$
declare
  id BIGINT;
begin
  SELECT customer.createUser(1, 100) INTO id;
  perform test_assertNotNull('user not created', id);
  perform test_assertTrue('user id range improper', id >= 10000);
end;
$$ language plpgsql;

A precondition function for this test may be one checking for user id 1 being present in the database

create or replace function test_precondition_user() returns boolean as $$
declare
   id BIGINT;
begin
  SELECT user_id INTO id FROM customer.user WHERE user_id=1;
  RETURN id IS NOT NULL AND (id = 1);
end;
$$ language plpgsql;

The precondition above will be shared on all 'user' tests unless one with a more specific name is created.

Troubleshooting

Lock issues

Although the unit tests are run in autonomous transactions it is possible to run into lock issues and have the select statements above hanging. In this case have a new connection on the same database and issue the statement below to stop all locking sessions: select * from test_terminate('my_db_name');

In order to find out which test is at issue you should run the suite one test at the time. The procedure above is not specific to PGUnit and can be used in general as well; it will terminate all locking sessions.

Install the code in public schema and switching to a different schema

You can add the 'public' schema to the search path using the statement below:

SELECT set_config(
    'search_path',
    current_setting('search_path') || ',public',
    false
) WHERE current_setting('search_path') !~ '(^|,)public(,|$)';

Installing the code in dedicated schema

The framework can be installed in a dedicated schema, even if it is not present in the search_path, for example pgunit. In that case all calls to pgunit functions should be qualified with its installation schema name:

create or replace function test_case_user_create_1() returns void as $$
declare
  id BIGINT;
begin
  SELECT customer.createUser(1, 100) INTO id;
  perform pgunit.test_assertNotNull('user not created', id);
  perform pgunit.test_assertTrue('user id range improper', id >= 10000);
end;
$$ language plpgsql;

Dealing with 'could not establish connection' errors

On a local server running in Windows 10, the only way I could find of removing these errors was to pass the database owner's name as user to the connection string used to establish a connection through db_link. The commit entitled 'Added pgunit.dblink_conn_extra setting for extra connection settings' checks a current_setting called 'pgunit.dblink_conn_extra'. If it exists, it adds the string in that setting to the connection. So, just before running test_run_all(), you need to set a configuration setting. In my environment, I only needed to specify the owner of the database. The password is supplied from the pgpass.conf file. I passed false as the 3rd parameter to add the setting to the current session. If you pass true, you get 'could not establish connection' errors again.

select set_config('pgunit.dblink_conn_extra', 'user=myuser", false)

Copyright and License

Copyright (c) 2016 Adrian Andrei. Some rights reserved.

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL ADRIAN ANDREI BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF ADRIAN ANDREI HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

ADRIAN ANDREI SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND ADRIAN ANDREI HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.