postgres pgTAP unit test pg_prove pgunit testgres - ghdrako/doc_snipets GitHub Wiki

unit tests are coded in SQL too.

The relation-testing functions for implementing unit tests based on result sets. From the documentation, let’s pick a couple examples, Testing against static result sets as VALUES:

SELECT results_eq( 
 'SELECT * FROM active_users()', 
 $$ 
 VALUES (42, 'Anna'), 
        (19, 'Strongrrl'), 
        (39, 'Theory') 
 $$, 
 'active_users() should return active users' 
);

and ARRAYS:

SELECT results_eq( 
 'SELECT * FROM active_user_ids()', 
  ARRAY[ 2, 3, 4, 5] 
);

Straight from the pg_prove command-line tool for running and harnessing pg-TAP tests, we can see how it looks:

1 % pg_prove -U postgres tests/ 
 tests/coltap.....ok 
 tests/hastap.....ok 
 tests/moretap....ok 
 tests/pg73.......ok 
 tests/pktap......ok 
 All tests successful.
 Files=5, Tests=216, 1 wallclock secs 9 ( 0.06 usr 0.02 sys + 0.08 cusr 0.07 csys = 0.23 CPU)
 Result: PASS You

integration with ci/cd

pg_virtualenv is a tool that creates a temporary PostgreSQL installation that will exist only while you’re running your tests.

Regression Tests

The RegreSQL tool implements that idea. It ??nds SQL ??les in your code repos-itory and allows registering plan tests against them, and then it compares the results with what’s expected.

Instrumentation sql

PostgreSQL implements the application_name parameter, which you can set in the connection string and with the SET command within your session. IT is then possible to have it reported in the server’s logs, and it’s also part of the system activity view pg_stat_activity.

pgTAP

CREATE SCHEMA IF NOT EXISTS pgTAP;
CREATE EXTENSION IF NOT EXISTS pgTAP WITH SCHEMA pgTAP;
  1. Testing plan The preferred way to do this is to declare a plan by calling the plan () function1. for tests; if you only have one test, then you will call SELECT plan(1), but if you intend to implement n tests, you will declare SELECT plan(n):
SET search_path TO pgTAP;
SELECT plan(1);
SELECT is_empty('select "ID" from public."ATM locations" where "ZipCode" NOT IN (select "zip" from public."Zip coordinates") Limit 1;', 'Check if there are any Bank ZipCodes not included in Zip coordinates table');
SELECT * FROM finish();

2.The preceding SQL code is a test to see whether there are any bank ZIP codes2. inside the ATM locations table that are not found in the ZIP coordinates table, hence we execute that code from pgAdmin. 3. The output shows that the test fails because the first ATM has ZipCode = 10003. but this value cannot be found in the ZIP coordinates table; the failure is # Looks like you failed 1 test of 1. 4. Now we test the second error, because the ATM database is used to store ATM4. locations of New York City, hence the ATM locations table must not be empty, so we have the following code for the next test:

SET search_path TO pgTAP;
SELECT plan(1); 
SELECT isnt_empty('select distinct "ID" FROM public."ATM locations";', 'Check if the ATM locations inside the ATM database are not empty');
SELECT * FROM finish();
  1. We now execute the second test with pgAdmin
  2. to combine the two preceding tests together into one plan. So,7. our code will be designed for two tests with SELECT plan(2), as follows:
SET search_path TO pgTAP;
SELECT plan(2);
SELECT is_empty('select "ID" from public."ATM locations" where "ZipCode" NOT IN (select "zip" from public."Zip coordinates") LIMIT 1;', 'Check if there are any Bank ZipCodes not included in Zip coordinates table');
SELECT isnt_empty('select distinct "ID" FROM public."ATM locations";', 'Check if the ATM locations inside the ATM database are not empty');
SELECT * FROM finish();
  1. Hence, when we execute the two-test plan with pgAdmin, the result will show
Looks like you failed 1 test of 2:

Uninstalling pgTAP

DROP EXTENSION IF EXISTS pgTAP;
DROP SCHEMA IF EXISTS pgTAP;

PG_Unit

Setting up PGUnit

  1. The SQL script to set up PGUnit is found here:

  2. The reinstall.sql installation script calls to execute another install.sql file and in turn, the install.sql script calls to many other scrips, such as assert_array_equals.sp.sql, assert_equals.sp.sql, and assert_false.sp.sql,

  3. The script is prepared to execute in psql. To execte in pgAdmin need to modify script

  4. The library is based on stored procedures that compare variables between themselves or compare variables with various constants:

    • pgunit.assert_equals (_expected anyelement, actual anyelement, custom_message varchar): Compares two elements. If they are not equal, then the #assert_equalsn {custom_message} exception is thrown.
    • pgunit.assert_not_equals (_expected anyelement, actual anyelement, custom_message varchar): Compares two elements. If they are equal, an exception is thrown: #assert_not_equalsn {custom_message}.
    • pgunit.assert_array_equals (_expected anyelement [ ], actual [] anyelement, custom_message varchar): Compares two arrays. Arrays are considered equal if these arrays have the same elements and the sizes of the arrays are equal. If the arrays are not equal, an exception is thrown with the text #assert_array_equalsn {custom_message}.
    • pgunit.assert_true (_value boolean, _custom_message varchar): Compares _value to True. If they are not equal, a #assert_truen {custom_message} exception is thrown.
    • pgunit.assert_false (_value boolean, _custom_message varchar): Compares _value to False. If they are not equal, an exception is thrown: #assert_falsen {custom_message}.
    • pgunit.assert_null (_value boolean, _custom_message varchar): Compares _value to NULL. If they are not equal, a #assert_nulln {custom_message} exception is thrown.
    • pgunit.assert_not_null (_value boolean, _custom_message varchar): Compares _value to NULL. If they are equal, an exception is thrown: #assert_not_nulln {custom_message}.
    • `pgunit.fail(_custom_message varchar)``: Throws an exception with the text #assert_failn {custom_message}.
    • `pgunit.run_test(_sp varchar)``: Runs the specified stored procedure inside the test infrastructure. After starting the test procedure, data is rolled back.

PGUnit – same name but a different approach

https://github.com/adrianandrei-ca/pgunit

  1. The plpgsql code depends on the dblink extension being present in the database you run the tests on. We set up a simple pgunit in a dedicated schema such as pgunit and run these two lines of SQL:
CREATE SCHEMA pgunit;
CREATE EXTENSION DBLINK SCHEMA pgunit;
  1. You should run the PGUnit.sql code using pgAdmin:
  1. We will copy the code from GitHub
  2. A convenient way to install the simple pgunit suite in our dedicated pgunit schema is to temporarily change the search path like this:SET search_path TO pgunit; We then revert search_path back to the public schema after the GitHub script.
  3. execute the whole of the script together in pgAdmin.
  4. Here is a list of prefixes for all tests:
  • "test_case_": It is a unit test procedure.
  • "test_precondition_": It is a test precondition function.
  • "test_postcondition_": It is a test postcondition function.
  • "test_setup_": It is a test setup procedure.
  • "test_teardown_": It is a test tear-down procedure.