At a glance
| Supported databases | PostgreSQL 9.1 to 18 (tested range for 1.3.4) |
| Data generation | None; tests insert their own rows |
| Masking | Not applicable |
| Referential integrity | Assertions can verify keys and constraints exist (has_pk, fk_ok) |
| CI/CD integration | TAP output read by TAP harnesses; pg_prove in CI scripts |
| Test automation | SQL unit tests for functions, queries, schema and privileges |
| Data volume or subsetting | Not applicable |
| Deployment | PostgreSQL extension (CREATE EXTENSION pgtap); pg_prove from CPAN |
| Licensing | Free, permissive PostgreSQL-style licence |
pgTAP is the main free option for writing database tests in SQL for PostgreSQL. Its large library of assertions covers schema objects (tables, columns, keys, indexes, functions, ownership and privileges) as well as query results and errors, and pg_prove runs suites in parallel. Install it on development and CI databases with CREATE EXTENSION pgtap; on a managed service, check whether the extension is offered.
Overview
pgTAP is a suite of database functions that emit TAP (Test Anything Protocol) output, so test results can be read by standard TAP harnesses. You can write tests as plain SQL scripts that call plan(), assertions and finish(), or as xUnit-style test functions that runtests() executes, rolling back changes after each test and supporting setup and teardown functions.
The project is maintained by David E. Wheeler on GitHub and distributed through PGXN. Version 1.3.4 (4 October 2025) fixed upgrade paths and was tested on PostgreSQL 9.1 through 18; a 1.3.5 is in development. Tests are run with pg_prove, a Perl tool from the TAP::Parser::SourceHandler::pgTAP CPAN distribution that calls psql and can run several test files in parallel.
Key features
- Basic TAP assertions such as ok, is, isnt, matches and cmp_ok
- Query and error tests: results_eq, set_eq, bag_eq, row_eq, throws_ok and lives_ok
- Schema assertions for tables, columns, keys, indexes, functions, schemas and other objects (has_table, columns_are, fk_ok and many more)
- Ownership and privilege checks such as table_owner_is, table_privs_are and policies_are
- xUnit-style test functions run by runtests(), with setup and teardown and automatic rollback
- pg_prove runner with parallel jobs (-j), xUnit mode (--runtests) and TAP formatters
Integrations and ecosystem
Pros and cons
Pros
- Free and permissively licensed
- Tests are written in SQL and run inside PostgreSQL
- Unusually broad schema and privilege assertions, useful for migration checks
- Standard TAP output works with existing harnesses
Cons
- PostgreSQL only
- Building from source needs make and pg_config, and CREATE EXTENSION needs superuser rights
- pg_prove depends on Perl and CPAN modules
- Availability on managed PostgreSQL services varies by provider
Pricing
pgTAP is free and open source. Its README grants permission to use, copy, modify and distribute it for any purpose without fee, on terms that read like the PostgreSQL licence. The pg_prove test runner is also free, from the TAP::Parser::SourceHandler::pgTAP distribution on CPAN.
Pricing checked on the vendor's official page on 7 November 2026. Prices change; confirm before buying.
Who it is best for
PostgreSQL teams that want automated tests for database functions, migrations and permissions written in SQL and run in CI.
Typical use cases
- Checking after every migration that expected tables, columns, indexes and foreign keys exist
- Unit testing PL/pgSQL functions with known inputs and expected outputs
- Verifying row-level security policies and grants for each role
- Running database test suites in parallel in a CI pipeline with pg_prove
Who should look elsewhere
- SQL Server teams: tSQLt is the equivalent framework there.
- Application teams that prefer to test the database through their own language test framework, for example with Testcontainers.
Verdict
pgTAP is the established way to unit test PostgreSQL in SQL, with assertions that reach well beyond query results into schema, ownership and privileges. It is free and current with PostgreSQL 18; the main practical hurdles are installing the extension where you lack superuser rights and setting up the Perl-based pg_prove runner.
Frequently asked questions
Is pgTAP free?
Yes. pgTAP is open source under permissive terms that allow use, copying, modification and distribution for any purpose without fee.
Which PostgreSQL versions does pgTAP support?
The README requires PostgreSQL 9.1 or later, and the 1.3.4 release notes (October 2025) say it was tested on PostgreSQL 9.1 through 18.
What is pg_prove?
pg_prove is the command-line runner for pgTAP tests. It comes from the TAP::Parser::SourceHandler::pgTAP distribution on CPAN, runs test files through psql, can run jobs in parallel and can execute xUnit-style test functions with --runtests.
How does pgTAP compare with tSQLt?
Both run unit tests inside the database. pgTAP targets PostgreSQL and emphasises TAP output and schema assertions; tSQLt targets SQL Server and emphasises test doubles such as FakeTable and SpyProcedure.
Sources
- pgTAP home
- pgTAP documentation
- pg_prove documentation
- pgTAP GitHub repository
- pgTAP Changes (release notes)
Checked November 2026.
Listed in: Database Testing & Test Data.
How we research reviews and what star ratings mean: our editorial method.