PGCon2009 Postscript: Unit Test Your Database!

I started watching some of the PGCon2009 videos that I didn't attend while at PGCon. Last night I watched, Unit Test Your Database!, by David Wheeler. I have had my come to Jesus moment on unit testing years ago, so I'm really happy that there is a solution for testing strictly at the database level without depending on the application layer.

Some developers make the mistake of treating the database as a glorified file system and therefore assume it doesn't need any testing. They are wrong! From the application's point of view, the database should be a black box and application level testing of the database should be limited to the interaction between the application and the interfaces the database exposes, like stored procedures and views. In this development paradigm the database is an independent entity and therefore needs its own set of tests to ensure that it's self consistent. This is where the speaker's own testing framework, pgTAP, excels. It's not limited to just testing the public interfaces the database exposes. It allows you to validate the database itself, i.e., verify the structure of the tables in the database and their relationships, verify triggers, verify the existence of indexes, verify the existence of functions, verify the behavior of functions, etc.

If you are still on the fence about whether unit testing your database is a worthwhile endeavor then watch the video, it may convince you. But if you've already got unit testing religion and are looking for a tool for testing your PostgreSQL database then pgTAP is going to be hard to beat.