The purpose of this repo is to show a quick demonstration on how to use pgtap to validate data integrity for Postgres databases. In this simple example, we show how to utilize and test indexes to prevent a room being double booked for a hotel suite. A summary of the steps are:
- Create a database that has a bookings table
- Create a series of tests for bookings table (including failing test for conflict booking)
- Run tests to see failure
- Update database with unique index to fix failing test
- Re-run tests to ensure previous failing test now passes
- Docker
- Docker Compose
- Basic understanding of unique indexes
- Start Postgres database server
docker-compose up -d db
- Initialize Database
Create awesome_hotel_booking database and bookings table.
Review init_db.sql to see details
docker-compose run db psql -h db -U test_pg_tap -f /seeds/init_db.sql
- Run pgtap tests
Review pgtap/bookings.sql and pgtap documentation to understand the test cases.
The 7th test should fail which tries to insert a double booking for the same room.
docker-compose run pgtap
## OUTPUT
Running tests: /test/*.sql
/test/bookings.sql .. 1/8
# Failed test 7: "do not allow two bookings for the same room on the same date"
# no exception thrown
# Looks like you failed 1 test of 8
/test/bookings.sql .. Failed 1/8 subtests
Test Summary Report
-------------------
/test/bookings.sql (Wstat: 0 Tests: 8 Failed: 1)
Failed test: 7
Files=1, Tests=8, 0 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU)
Result: FAIL
- Create unique index
See create_uq_index.sql for details.
docker-compose run db psql -h db -U test_pg_tap -d awesome_hotel_booking \
-f /seeds/create_uq_index.sql
- Re-run pg tap tests
All of the tests should pass now.
docker-compose run pgtap
## OUTPUT
Running tests: /test/*.sql
/test/bookings.sql .. ok
All tests successful.
Files=1, Tests=8, 0 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU)
Result: PASS
- Stop Docker
docker-compose down