Setting up pgTAP for postgresql Unit Testing

Introduction:

Unit Testing is a term that is of no stranger to any software developer. Many popular framework has their own Unit Testing tools e.g. Qt. However, I wonder how many people out there actually knows that there exist unit testing for database too. And I only came to know about this yesterday. As I wanted to ensure the quality of my database schema and queries are correctly created, I went onto the web to look for a tool that I can do Unit Testing for postgresql, which is the database that I am using for all my web developments. It didn't take me more than 5 minutes to look for one. pgTAP is the first result from the google search. As it is the only postgresql Unit Testing tool that is still active(at this point of writing), I have therefore decided to give it a try. But it wasn't too long till I encountered some difficulties in following its installation instruction. Therefore this article is written to fill up the missing steps involved in setting up the the tool.

Installation:

First download the source file

wget http://api.pgxn.org/dist/pgtap/0.95.0/pgtap-0.95.0.zip
unzip pgtap-0.95.0.zip

Next, go into the pgtap-0.95.0 directory and run the following

make
sudo make install
sudo cpan TAP::Parser::SourceHandler::pgTAP

Next go to the sql directory and install the pgtap.sql into the database that you plan to use the unit testing tool.

cd sql
psql -d "database" -U "username" -f pgtap.sql

This will create all the necessary functions in the database in order for your assertions to work.

Lastly, lets check that our pgTAP is indeed properly installed

sudo make installcheck PGUSER="username"

After the last command has been executed, a series of prompts will appear for your password, you probably have to key it in many times. (I have not figure out what is a better way to do this)
Basically what the last line does is that it will run a series of Unit Testing on the pgTAP you have installed.

Running the Test

Lets make a sample test file named "test.sql".

\set QUIET 1
-- Turn off echo and keep things quiet.

-- Format the output for nice TAP.
\pset format unaligned
\pset tuples_only true
\pset pager

-- Revert all changes on failure.
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true
\set QUIET 1

-- Load the TAP functions.
BEGIN;
-- Plan the tests.
SELECT plan(1);

-- Run the tests.
SELECT pass( 'My test passed, w00t!' );

-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK;

To run the test, simply load the file using psql

psql -d "database" -U "username" -Xf test.sql

output:

1..1
ok 1 - My test passed, w00t!

Alternatively, you may choose to use pg_prove to run a series of files.
The advantage of using pg_prove is that you no longer need to set the variables before running the test.

-- Start transaction and plan the tests.
BEGIN;
SELECT plan(1);

-- Run the tests.
SELECT pass( 'My test passed, w00t!' );

-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK;

To run the test using pg_prove

pg_prove -d "database" -U "username" ./*.sql

To learn more about using pgTAP and its syntax and functions, please refer to its documentation here

You will realized that I have made a lot of modifications to the original installation instruction as many of the steps on the website did not work for me. =)