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#
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pgtap" and 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 databasehas_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:
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 typeis_definer()
: Tests that a function is a security definer (i.e., a “setuid” function).
Resources#
- Official
pgTAP
documentation