Test Driven Design for postgres procedures

Question

I am looking to introduce a test driven design style to writing my stored procedures for a postgresql database implementation.

I have seen that pgTap is a popular unit testing tool for postgres and correctly allows for the tests to be written in SQL and not built and run externally.

In order to go the full distance I would also like to be able to use test doubles (stub, mock, fake, dummy) and maybe even some IDE support for running and refactoring (something like tsql)

Are there currently tools and resources available specifically for supporting TDD in postgres or do people roll their own by building fake tables and data and wrapping everything in transactions that roll back on completion?

What are currently the best practices for approaching TDD in postgres?

Answer 1

Test-Driven-Development (TDD) is an ideology. You can usually poke holes in implementations; and generally you sacrifice convenience and efficiency in any implementation of TDD -- which is why you have to sell it, and why it's not intuitive. You're running up against a problem which is one of the terminus ad quem of testing. You're likely trying to be a purist and often people get lost in the woods in that pursuit.

When you create a website, generally you don't test whether or not the framework, is producing a valid HTTP response. Such a test resides in a different layer, and you should only use frameworks and libraries that have a rigorous test suite that test their own domain. Anything you do to test a valid HTTP response should come up in end-to-end testing (with something like Protractor).

Think of PostgreSQL as a "framework" at a lower level that manages your data. You need to test how you get things in and get things out. Most of what PgTAP does is absolutely overkill. A ton of it is validating the schema -- how would that even work with TDD? Take..

CREATE TABLE foo ( a int PRIMARY KEY, b text, c uuid );

You're going to test

1. foo exits and is a table
2. a is an interger
3. b is a text field
4. c is a uuid

All of that is well tested to be the result of,

CREATE TABLE foo ( a int PRIMARY KEY, b text, c uuid );

You're going to drive yourself nuts testing that. If you need to validate two schemas are the same, you can simply pg_dump --schema and diff the two. You can actually generate, with great ease, a full schema test suite that tests everything.

As far as what you need to test: any procedural code in the database. Generally we do this with test-data as PostgreSQL does, or by simply having a test-database which your framework can connect to and test the procedural code like everything else.

Shy of that, you want Fat Model, Skinny Controller (put as much as your data-manipulation logic in the model) so you can use it outside of your web app, and testing that should be done like anything else in whatever language you prefer.