pgTAP

Visit Website

Introduction

pgTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or xUnit-style test functions. Its README describes it as written in PL/pgSQL and PL/SQL, with a comprehensive collection of TAP-emitting assertion functions and the ability to integrate with other TAP-emitting test frameworks.

The value for quality teams is that database behaviour gets tested inside the database. Application tests often treat the database as a black box, but constraints, triggers, functions, row-level security policies and migrations are code too, and they break. pgTAP lets you assert that tables, columns, indexes, primary and foreign keys exist as designed, that functions return the right types and values, and that queries return expected result sets.

The documentation covers a wide set of assertions. Basic ones include ok(), is(), isnt(), matches() and cmp_ok(). Schema assertions include has_table(), has_column(), has_index(), col_is_pk(), fk_ok(), tables_are(), has_function(), function_returns() and policies_are(). Result-set assertions such as results_eq(), set_eq(), bag_eq(), row_eq() and is_empty() compare query output, while throws_ok() and lives_ok() test errors and performs_ok() checks that a query runs within a time limit.

Tests are usually wrapped in a transaction that is rolled back, so they leave no trace. A script declares a plan(), runs assertions and calls finish(). The pg_prove command-line tool runs test scripts or xUnit-style test functions through runtests() and reports TAP results that CI systems understand.

pgTAP installs as an extension with CREATE EXTENSION pgtap. It must be installed on a host with a PostgreSQL server, and building from source needs pg_config.

In the CheckOps stage model pgTAP spans Unit and Integration testing, and runs in Release and CI pipelines to check migrations before deployment.

The source code is at theory/pgtap on GitHub under the PostgreSQL licence, with about 1,200 stars; the most recent commit was on 27 September 2026.

Organising tests by schema object keeps suites maintainable: one file for table structure, one for functions, one for security policies. Seed data should be created inside each test transaction so tests do not depend on each other. Running the suite against a database built from migrations, rather than a dump, also verifies that migrations themselves produce the intended schema. For larger teams, publishing pg_prove’s TAP output through a TAP-to-JUnit converter lets CI dashboards show database test results alongside application tests, which makes failures in schema changes as visible as any other broken build.

Finally, keep an eye on the cost of the suite: schema assertions are cheap, but result-set tests on large seed data can slow CI. Split slow tests into a separate job if needed so that quick structural checks still give fast feedback on every pull request.

Clear test descriptions in each assertion make TAP failures easy to read.

Treat failing database tests as release blockers, like any other test.

Features

1. TAP assertions: ok, is, isnt, matches, cmp_ok.
2. Schema assertions: Tables, columns, indexes and keys.
3. Function checks: has_function and function_returns.
4. Result-set comparison: results_eq, set_eq, bag_eq.
5. Error testing: throws_ok and lives_ok.
6. Performance checks: performs_ok time limits.
7. RLS policies: policies_are.
8. xUnit style: Test functions run with runtests().
9. pg_prove runner: CI-friendly TAP output.

Pros

1. Tests inside PostgreSQL: Exercises real constraints, triggers and functions.
2. Transaction isolation: Rollback keeps databases clean.
3. Free to use: Open source under the PostgreSQL licence, so the core tool costs nothing to use.
4. Actively maintained: The repository had commits as recent as 27 September 2026.

Cons

1. PostgreSQL only: Not for other databases.
2. SQL test code: Tests are written in SQL or PL/pgSQL.
3. Install requirements: Needs the extension on the server.
4. TAP output: Needs a TAP consumer for rich reports.

Usage and Links (Example)

BEGIN;
SELECT plan(4);

SELECT has_table('public', 'orders', 'orders table exists');
SELECT col_is_pk('public', 'orders', 'id', 'id is the primary key');
SELECT fk_ok('public', 'orders', 'customer_id', 'public', 'customers', 'id');
SELECT results_eq(
  $$ SELECT status FROM orders WHERE id = 1 $$,
  $$ VALUES ('paid'::text) $$,
  'order 1 is paid'
);

SELECT * FROM finish();
ROLLBACK;

pg_prove -d app_test tests/*.sql

Practical tips:

1. Run pgTAP after every migration in CI against a fresh database.
2. Use schema assertions to lock down the intended design.
3. Test row-level security as different roles.

Resources:

• pgTAP website
• GitHub repository

Pricing

pgTAP is free and open source under the PostgreSQL licence.

Recommended for?

1. PostgreSQL teams with logic in the database.
2. Supabase users testing schemas and policies.
3. Platform teams gating migrations in CI.
4. Not for non-PostgreSQL databases.

Conclusion

pgTAP is the standard way to unit test PostgreSQL schemas, functions and policies from inside the database.

Pair it with a migration linter such as Squawk and run it in CI against every schema change.

On CheckOps, pgTAP is filed under the Unit testing, Integration testing and Release & CI gates stages. Related tools listed here include utPLSQL, SQLFluff, Atlas, Squawk, Database Lab Engine and dbt tests. When comparing them, weigh the licence, the languages and platforms each supports, whether it is self-hosted or managed, and how cleanly it slots into your existing CI pipeline. Details on this page were checked against the official website, documentation and repository on 11 November 2026.