Skip to content
A demonstration repo showing how to test your database constraints with pgtap
PLpgSQL
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
pgtap
seeds
.gitignore
License.md
README.md
docker-compose.yml

README.md

PgTap Test Constraints

Introduction

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:

  1. Create a database that has a bookings table
  2. Create a series of tests for bookings table (including failing test for conflict booking)
  3. Run tests to see failure
  4. Update database with unique index to fix failing test
  5. Re-run tests to ensure previous failing test now passes

Requirements

Steps

  1. Start Postgres database server
docker-compose up -d db
  1. 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
  1. 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
  1. 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
  1. 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
  1. Stop Docker
docker-compose down
You can’t perform that action at this time.