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.
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
_10begin;_10select plan( 1 );_10_10select has_table( 'profiles' );_10_10select * from finish();_10rollback;
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
_10begin;_10select plan( 2 );_10_10select has_column( 'profiles', 'id' ); -- test that the "id" column exists in the "profiles" table_10select col_is_pk( 'profiles', 'id' ); -- test that the "id" column is a primary key_10_10select * from finish();_10rollback;
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
_14begin;_14select plan( 1 );_14_14select policies_are(_14 'public',_14 'profiles',_14 ARRAY [_14 'Profiles are public', -- Test that there is a policy called "Profiles are public" on the "profiles" table._14 '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._14 ]_14);_14_14select * 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:
_12begin;_12select plan( 1 );_12_12select results_eq(_12 'select * from profiles()',_12 $$VALUES ( 1, 'Anna'), (2, 'Bruce'), (3, 'Caryn')$$,_12 'profiles() should return all users'_12);_12_12_12select * from finish();_12rollback;
API:
Testing functions
_11prepare hello_expr as select 'hello'_11_11begin;_11select plan(3);_11-- You'll need to create a hello_world and is_even function_11select function_returns( 'hello_world', 'text' ); -- test if the function "hello_world" returns text_11select function_returns( 'is_even', ARRAY['integer'], 'boolean' ); -- test if the function "is_even" returns a boolean_11select results_eq('select * from hello_world()', 'hello_expr'); -- test if the function "hello_world" returns "hello"_11_11select * from finish();_11rollback;
API:
function_returns()
: Tests that a particular function returns a particular data typeis_definer()
: Tests that a function is a security definer (that is, asetuid
function).
Resources
- Official
pgTAP
documentation