Testing the database with pgTAP

Most developers are aware of unit tests or integration tests/end-to-end tests. Just to drop a few tool names here: Jasmine, Cypress, Selenium – you name it. Often, these test tools will then implicitly test the database by setting up a fresh database for each test run, and then checking the effect/implications of the code on the database (are the correct rows inserted? Is rejecting row deletion working when I don’t have permissions? And so on…).

However, in Postgres, within Supabase, you can run explicit database tests. For this, you need to enable the pgtap extension first.

Let’s assume I am the database architect and I want to ensure that whoever will finally implement the database does so in the way I expect it. For example, I want to enforce that the tickets and comments tables are created and that they are related to each other. With pgtap, we can run a test like this:

BEGIN;
SELECT plan( 3 ); -- number of tests...