weighing tools/approaches for testing of database components

Question

Currently, the way we do this on LedgerSMB is to have SQL scripts which insert records into a test results table. This table is created in a transaction which runs the tests, and the tests always rollback. A wrapper program in Perl runs these scripts, checks the results of the test_result table, and reports errors back up to the test harness. There are some advantages here, including the fact that tests can be run on a live, production DB without contaminating it (and thus also move between QA and support as a tool), and we don't require any additional components to run the tests. In fact, you can run the tests passably using just psql.

One possible direction we are considering going is to move the test scripts to pgTAP and also centralize test data logic. This would seem to give us more options and might allow us to do exception testing and expand the reach of the test scripts, but it would mean additional dependencies for testing, and would likely rule out as a general support tool or one that could be run as part of the installation.

Which approach is best? The current one or pgTap? Or would it be better to adopt a hybrid approach?

Answer 1

It depends on the specific requirements and constraints of the project.

The current approach of using SQL scripts to insert records into a test results table has some advantages. One of these is that tests can be run on a live, production database without contaminating it. This allows the tests to move between QA and support as a tool, as well as being passably run using just psql. Another advantage is that no additional components are required to run the tests, as the test results table is created in a transaction that runs the tests and always rolls back after the tests are complete.

On the other hand, moving to pgTAP (PostgreSQL Unit Testing for TAP-Emitting Applications) could provide more options and allow for exception testing and expanded reach of the test scripts. This would also centralize test data logic, making it easier to maintain. However, it would also introduce additional dependencies for testing, and would likely rule out the possibility of using it as a general support tool or one that could be run as part of the installation.

In terms of deciding between the two approaches or considering a hybrid approach, it really depends on the specific needs and requirements of the database and the organization using it. If a more centralized test data logic and expanded test capabilities are necessary, pgTAP might be the better option. However, if maintaining the current setup with minimum dependencies is a higher priority, then the current approach might be better. A hybrid approach could also be considered, but it would require careful consideration and planning to determine the best combination of both approaches.