pgTAP: Unit Testing

pgTAP is a unit testing extension for PostgreSQL.

Overview#

Let's cover some basic concepts:

• Unit tests: allow you to test small parts of a system (like a database table!).
• TAP: stands for Test Anything Protocol. It is an framework which aims to simplify the error reporting during testing.

Usage#

Enable the extension#

Testing tables#

1begin;
2select plan( 1 );
3
4select has_table( 'profiles' );
5
6select * from finish();
7rollback;

API:

• has_table(): Tests whether or not a table exists in the database
• has_index(): Checks for the existence of a named index associated with the named table.
• has_relation(): Tests whether or not a relation exists in the database.

Testing columns#

1begin;
2select plan( 2 );
3
4select has_column( 'profiles', 'id' );  # test that the "id" column exists in the "profiles" table
5select col_is_pk( 'profiles', 'id' );   # test that the "id" column is a primary key
6
7select * from finish();
8rollback;

API:

• has_column(): Tests whether or not a column exists in a given table, view, materialized view or composite type.
• col_is_pk(): Tests whether the specified column or columns in a table is/are the primary key for that table.

Testing RLS Policies#

1begin;
2select plan( 1 );
3
4select policies_are(
5  'public',
6  'profiles',
7  ARRAY [
8    'Profiles are public', # Test that there is a policy called  "Profiles are public" on the "profiles" table.
9    'Profiles can only be updated by the owner'  # Test that there is a policy called  "Profiles can only be updated by the owner" on the "profiles" table.
10  ]
11);
12
13select * from finish();
14rollback;

API:

• policies_are(): Tests that all of the policies on the named table are only the policies that should be on that table.
• policy_roles_are(): Tests whether the roles to which policy applies are only the roles that should be on that policy.
• policy_cmd_is(): Tests whether the command to which policy applies is same as command that is given in function arguments.

You can also use the results_eq() method to test that a Policy returns the correct data:

1begin;
2select plan( 1 );
3
4select results_eq(
5    'select * from profiles()',
6    $$VALUES ( 1, 'Anna'), (2, 'Bruce'), (3, 'Caryn')$$
7    'profiles() should return all users'
8);
9
10
11select * from finish();
12rollback;

API:

• results_eq()
• results_ne()

Testing Functions#

1begin;
2select plan( 1 );
3
4select function_returns( 'hello_world', 'text' );                   # test if the function "hello_world" returns text
5select function_returns( 'is_even', ARRAY['integer'], 'boolean' );  # test if the function "is_even" returns a boolean
6select results_eq('select * from hello_world()', 'hello');          # test if the function "hello_world" returns "hello"
7
8select * from finish();
9rollback;

API:

• function_returns(): Tests that a particular function returns a particular data type
• is_definer(): Tests that a function is a security definer (i.e., a “setuid” function).

Resources#

• Official pgTAP documentation