pgTAP’s plan() function doesn’t just declare how many tests you’re running; it actually enforces it.
Let’s see pgTAP in action. Imagine you’ve got a users table and a create_user function that handles some basic validation.
-- migrations/0001_create_users_table.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- migrations/0002_create_user_function.sql
CREATE OR REPLACE FUNCTION create_user(user_email VARCHAR)
RETURNS users AS $$
BEGIN
IF user_email IS NULL OR user_email = '' THEN
RAISE EXCEPTION 'Email cannot be null or empty';
END IF;
IF EXISTS (SELECT 1 FROM users WHERE email = user_email) THEN
RAISE EXCEPTION 'Email address "%" already exists', user_email;
END IF;
INSERT INTO users (email) VALUES (user_email);
RETURN (SELECT * FROM users WHERE email = user_email);
END;
$$ LANGUAGE plpgsql;
Now, we want to test create_user using pgTAP. We’ll create a test file, say tests/user_tests.sql.
-- tests/user_tests.sql
\set ON_ERROR_STOP true
\i tests/pgtap_setup.sql -- This file sets up pgtap and connection details
-- Test case 1: Successfully create a user
SELECT plan(1); -- We expect 1 test to pass
SELECT is(
(SELECT email FROM create_user('test@example.com')),
'test@example.com',
'Should successfully create a new user with a valid email'
);
SELECT * FROM finish(); -- Reports results
-- Test case 2: Prevent duplicate email
SELECT plan(1);
-- First, create a user to ensure the duplicate exists
SELECT create_user('duplicate@example.com');
-- Now, try to create another user with the same email and expect an exception
SELECT throws_ok(
$$SELECT create_user('duplicate@example.com')$$::TEXT,
'Email address "duplicate@example.com" already exists',
'Should throw an exception when attempting to create a user with a duplicate email'
);
SELECT * FROM finish();
-- Test case 3: Prevent null email
SELECT plan(1);
SELECT throws_ok(
$$SELECT create_user(NULL)$$::TEXT,
'Email cannot be null or empty',
'Should throw an exception when attempting to create a user with a NULL email'
);
SELECT * FROM finish();
To run these tests, you’d typically use the psql command-line tool. Assuming you have your Supabase project running locally or can connect to your remote project via psql:
psql "postgresql://postgres:your_password@your_supabase_host:5432/postgres?schema=public" -f tests/user_tests.sql
Here’s what the output might look like if everything passes:
TAP version 13
1..1
ok 1 Should successfully create a new user with a valid email
1..1
ok 1 Should throw an exception when attempting to create a user with a duplicate email
1..1
ok 1 Should throw an exception when attempting to create a user with a NULL email
All tests successful.
The plan(N) function is crucial. If your test script declares plan(3) but only runs two tests, finish() will fail, reporting that you promised 3 tests but only executed 2. Conversely, if you run 4 tests but only declared plan(3), it will also fail. This ensures you don’t accidentally skip tests or run more than you intended.
Internally, pgTAP uses a set of special functions and a global state to track test execution. When you call plan(N), it sets an internal counter for the expected number of tests in the current test suite. Functions like is(), ok(), throws_ok(), etc., increment a "passed" or "failed" counter and decrement the expected test count. finish() then compares the actual number of tests run against the planned number and reports the results, including any mismatches. The \i command is a psql meta-command that includes the content of another file, allowing you to organize your pgTAP setup and tests modularly.
A common pitfall is forgetting to SELECT * FROM finish(); at the end of each test suite. Without it, pgTAP can’t finalize the test run and report the results correctly, often leading to confusing output or apparent test failures.
The next step is often integrating these tests into your CI/CD pipeline, ensuring code changes don’t break your SQL logic.