Database

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 (that is, a setuid function).

Resources