Setting up pgTAP for postgresql Unit Testing
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.
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
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. =)