pgTAP

pgTAP is a suite of database functions that make it easy to write
TAP-emitting unit tests in
psql scripts or xUnit-style test functions. The TAP output
is suitable for harvesting, analysis, and reporting by a TAP harness,
such as those used in Perl applications.

Why would you want to unit test your database? Well, there are a
couple of scenarios in which it can be useful.

Application Development

So you write PostgreSQL-backed applications, maybe
in Rails,
or Django,
or Catalyst, and because
you’re an
agile developer, you
write lots of tests to make sure that your application works as you
practice iterative development. But, as one of the most important parts
of your application, should you not also test the database? Sure, you
write tests of your API, and the API covers the database, but that’s not
really unit testing the database itself, is it?

pgTAP allows you to really test the database, not only
verifying the structure of your schema, but also by exercising any
views, procedures, functions, rules, or triggers you write. Of course
you could use your application’s unit-testing framework to test the
database, but by writing your tests with pgTAP, you can keep your
database tests simple. Consider these simple tests written
with Test::More and the
Perl DBI to test a custom database
function:

The upshot is that you have to connect to the database, set up
transactions, execute the database functions, fetch back data into Perl
data structures, and then compare values. Now consider the equivalent
written with pgTAP:

Now isn’t that a lot easier to read? Unlike the Perl tests, the pgTAP
tests can just compare values directly in the database. There is no need
to do any extra work to get the database interface to talk to the
database, fetch data, convert it, etc. You just use SQL. And if you’re
working hard to keep SQL in the database and application code in the
application, why would you write database tests in Application code?
Just write them in SQL and be done with it!

Schema Validation

Even better is the scenario in which you need to test your database
schema objects, to make sure that everything is where it should be.
pgTAP provides a wealth of test functions that make schema testing a
snap!:

xUnit-Style Testing

In addition to the scripting-style of unit testing typical of TAP
test frameworks, pgTAP also supports xUnit-style testing of the
database, similar to the approach taken
by PGUnit and
Epic. It’s simple to use: Just write
your test functions in the schema of your choice and use the the pgTAP
assertion functions to do the tests:

Once you’ve created your test functions and installed them in your
database, you can run them at any time using the runtests()
function:

SELECT * FROM runtests('mytest'::name);

And that’s it. The runtests() function will handle all
the particulars, including rolling back any changes made to the schema
during the running of each test. It also supports setup and teardown
functions, as well as startup and shutdown. Consult
the complete documentation for
details.

Module Development

If you’re developing third-party modules for PostgreSQL, such
as custom data types
or foreign data wrappers, you can of course use
PostgreSQL’s standard regression test architecture. But if you’re doing
agile development, writing tests as you go, which test output would you
rather read? This: