Home

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#

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for "pgtap" and enable the extension.

Testing tables#


_10
begin;
_10
select plan( 1 );
_10
_10
select has_table( 'profiles' );
_10
_10
select * from finish();
_10
rollback;

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#


_10
begin;
_10
select plan( 2 );
_10
_10
select has_column( 'profiles', 'id' ); -- test that the "id" column exists in the "profiles" table
_10
select col_is_pk( 'profiles', 'id' ); -- test that the "id" column is a primary key
_10
_10
select * from finish();
_10
rollback;

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#


_14
begin;
_14
select plan( 1 );
_14
_14
select 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
_14
select * from finish();
_14
rollback;

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:


_12
begin;
_12
select plan( 1 );
_12
_12
select results_eq(
_12
'select * from profiles()',
_12
$$VALUES ( 1, 'Anna'), (2, 'Bruce'), (3, 'Caryn')$$
_12
'profiles() should return all users'
_12
);
_12
_12
_12
select * from finish();
_12
rollback;

API:

Testing Functions#


_11
prepare hello_expr as select 'hello'
_11
_11
begin;
_11
select plan(3);
_11
-- You'll need to create a hello_world and is_even function
_11
select function_returns( 'hello_world', 'text' ); -- test if the function "hello_world" returns text
_11
select function_returns( 'is_even', ARRAY['integer'], 'boolean' ); -- test if the function "is_even" returns a boolean
_11
select results_eq('select * from hello_world()', 'hello_expr'); -- test if the function "hello_world" returns "hello"
_11
_11
select * from finish();
_11
rollback;

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#

© Supabase Inc—ContributingAuthor StyleguideOpen SourceSupaSquad