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

begin;
select plan( 1 );

select has_table( 'profiles' );

select * from finish();
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

begin;
select plan( 2 );

select has_column( 'profiles', 'id' ); -- test that the "id" column exists in the "profiles" table
select col_is_pk( 'profiles', 'id' ); -- test that the "id" column is a primary key

select * from finish();
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

begin;
select plan( 1 );

select policies_are(
  'public',
  'profiles',
  ARRAY [
    'Profiles are public', -- Test that there is a policy called  "Profiles are public" on the "profiles" table.
    '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.
  ]
);

select * from finish();
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:

begin;
select plan( 1 );

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


select * from finish();
rollback;

API:

Testing functions

prepare hello_expr as select 'hello'

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

select * from finish();
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

Official pgTAP documentation

Edit this page on GitHub

pgTAP: Unit Testing

Overview#

Enable the extension#

Testing tables#

Testing columns#

Testing RLS policies#

Testing functions#

Resources#

Overview

Enable the extension

Testing tables

Testing columns

Testing RLS policies

Testing functions

Resources