Unit testing Postgres with pgTAP

Like most professional developers, we like to ensure we have a solid suite of tests across our code. This gives us a base level of confidence that our software is doing what we expect, it also protects us from introducing bugs when integrating new features.

Both Gurn and Kintra rely heavily on their databases to provide much of their functionality, so it’s important to us that we have the same confidence in those databases as we do our source code. PostgreSQL is our database of choice so we were keen to scope out options available for testing in Postgres.

We took to the PostgreSQL official wiki to see what solutions were available and the documentation pointed us towards two frameworks, pgTAP and PGUnit.

After researching PGUnit it was clear that it had not been updated since Postgres 8.3+ (2008). Given how much Postgres has moved on we decided that this was probably not the right technology for the job. With PGUnit off of the table that left us with pgTAP.

What is TAP?

The Test Anything Protocol (TAP) has been around since 1987 when it was originally developed for Perl. It provides a mechanism for communicating test results to a test harness while staying agnostic to the language. This is done using the TAP protocol itself alongside producers and consumers.

A TAP producer is any system that can output TAP protocol, and a TAP consumer is any system that can process TAP protocol and do something useful with it. This is pretty straightforward, and is also pretty powerful. Because the protocol is language agnostic, TAP has been implemented across a large range of modern languages. pgTAP is simply the implementation of TAP producers written in pgsql.

Installing pgTAP

Now that we understand a little bit about TAP and pgTAP, I’ll show you how to go about getting started using it to test your own database.

The steps I’ll show will use Bash, if you’re using another shell or operating system then you may need to follow the pgTAP tutorials directly.

If you use OSX I have created a gist of pgTAP here that will enable you to skip past these initial steps.

You can grab the latest version of pgTAP from here

pgTAP: Unit testing for PostgreSQL / PostgreSQL Extension Network

Once you have downloaded the zip file you need to extract it and compile it.

$ unzip pgtap-0.97.0.zip    #Your version may differ
$ cd pgtap-0.97.0/          #And here too
$ make
$ make installcheck
$ make install
$ cd sql                    # Once compiled cd into sql directory

There will be lots of generated files, the one we care about beingpgtap.sql . This file provides all the necessary methods for Postgres and you should run this file against your test database, doing so will set up all the functions required to test using pgTAP.

pg_prove

pg_prove is a command line utility function that we can use to run our tests. It can be installed on Mac OS / OSX by running the following commands.

cpan App::cpanminus
sudo cpan TAP::Parser::SourceHandler::pgTAP

This uses cpan , the Perl package manager to install pg_prove. If you do not have Perl installed you can install it using homebrew or by following these instructions

Anatomy of a test

pgTAP provides a number of functions that allow us to test any aspect of a schema or function. In fact, there are so many functions for conducting tests it can result in there being a number of ways of testing if something is true. In our experience once you have dug around a little in the documentation it is straightforward enough to identify the basics to get your tests written.

Transactions

Every test is written within a transaction. This allows pgTAP to perform actions on the database and test results without making any changes to the database itself.

A simple test for a table

Below is a test that outlines the basics of testing a table called agents . The purpose of this table is to store web browser user agent strings.

-- Start transaction and plan the tests.
BEGIN; -- Start the transaction

-- Plan count should match the number of tests. If it does
-- not then pg_prove will fail the test
SELECT plan(6);

-- Run the tests.

-- Columns
SELECT columns_are('agents', ARRAY[ 'id', 'name' ]);

SELECT col_type_is('agents', 'name', 'text', 'name column type is -- text' );

-- Keys
SELECT has_pk('agents', 'Has a Primary Key' );
SELECT col_is_pk( 'agents', 'id', 'Column is Primary Key -- id' );

-- Indexes
SELECT has_index( 'agents', 'idx_agents_name', 'name', 'Column has index -- name' );

-- Constraints
SELECT col_is_unique( 'agents', 'name', 'Name columns has unique constraint' );

-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK; -- We don’t commit the transaction, this means tests don’t change the database in anyway

We are testing a number of aspects of this table, including the columns it contains, their types, keys, indexes, and constraints. To finish the test we invoke the `finish()` function and then rollback the transaction.

The documentation for each function explains how it can be invoked. For example the docs for has_index() show a number of signatures. In our case we chose theSELECT has_index( :table, :index, :columns, :description ); signature. The description field provides a friendly description that appears next to the test. This is particularly useful when running a lot of tests so you can understand what is failing.

Running a test

To run tests we usepg_prove, the command line utility that we installed earlier.

At Gradient, we run our test database in a Docker container and spin this up on localhost:3100. All our tests are located in a tests directory with subdirectories for tables, functions, types, etc. We do this as it makes it easy to run the tests in complete isolation and also integrates them incredibly well with our build service CircleCI 2.0.

pg_prove --host localhost --dbname postgres --username postgres --port 3100 tests/**/*.sql

When the tests are run, pg_prove provides an output in the command line showing which tests have succeeded and which have failed. In the example below the tags.sql tests have passed and the team-invites.sql tests have failed.

Conclusion

In this article we have explored the options available for unit testing PostgreSQL. We have focussed on pgTAP as a testing framework and looked at what the Test Anything Protocol is and how it can be applied to Postgres. We have covered how to unit test a table, giving an example test suite, and shown how you can use the pgTAP documentation to learn about and implement a wide array of tests. Finally we looked at how to set up and run pg_prove, the command line task runner for pgTAP to run your test suite against a Postgres database.

We hope you have found this useful and are able to implement your own unit tests in Postgres. If you have any corrections, questions or comments, please share them in the comments section below and we will get back to you.