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?

  • I think you're going to drive yourself nuts barking up that tree. Apr 30, 2018 at 21:53
  • @EvanCarroll So do you suggest I just test my procedures in client software? Apr 30, 2018 at 21:55
  • Just a thought - you could use your search engine of choice to look into D. Richard Hipp's thoughts on testing SQLite - interesting reading and I, for one, am firmly convinced that his approach is the major reason why SQLite is the most successful SQL/RDBMS system out there.
    – Vérace
    Apr 30, 2018 at 22:00
  • Sorry, might be being a bit stupid here, do you mean how SQLite core is tested, how it is used to test models or how the DDL used to implement an SQLite DB instance is tested? May 1, 2018 at 9:36
  • This is a really outstanding question. As a data scientist who also tries to be a good software engineer, I absolutely want to test any complex SQL queries. I wonder how we can approximate mocks given that they are not really available. Jan 16, 2020 at 9:43

1 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.

  • OK, thanks for your comments on what to test. I do specifically say it is the stored procedures I want to apply this approach to, not the 'framework'. I would not test a variable assignment in middle tier unit testing or a dependency. As to unit testing the models, I will do that too, what I want here it to have unit tests for stored procedures that break if I change the wrong thing, remove a dependency or update the DDL May 1, 2018 at 9:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.