Database Testing

pgTAP: PostgreSQL Unit Testing Framework — Getting Started Guide

HelpMeTest

10 Aug 2026 — 5 min read

Database logic doesn't live only in application code. Stored procedures, triggers, views, and constraints encode business rules directly in the database — and those rules need tests too. pgTAP is the standard unit testing framework for PostgreSQL, letting you write tests as SQL functions using the TAP (Test Anything Protocol) format.

This guide walks through installing pgTAP, writing your first tests, and integrating them into a CI pipeline.

What Is pgTAP?

pgTAP is a PostgreSQL extension that provides a suite of test functions following the TAP protocol. TAP is a text-based format for test output that many CI tools and test harnesses understand natively. With pgTAP, you write test functions that call assertions like ok(), is(), has_table(), has_column(), and throws_ok().

Tests run inside the database itself, which means they have full access to schema introspection functions, transactional rollbacks, and all PostgreSQL features.

Installing pgTAP

On most systems, pgTAP is available via your package manager:

# Ubuntu / Debian
sudo apt-get install postgresql-16-pgtap

# macOS with Homebrew
brew install pgtap

# From source
git clone https://github.com/theory/pgtap.git
cd pgtap
make
make install

Once installed, enable the extension in your database:

CREATE EXTENSION IF NOT EXISTS pgtap;

Verify it's working:

SELECT pgtap_version();

Writing Your First pgTAP Test

Tests are written as SQL functions. Here's a minimal example:

BEGIN;
SELECT plan(3);

-- Test 1: table exists
SELECT has_table('public', 'users', 'users table should exist');

-- Test 2: column exists with correct type
SELECT has_column('public', 'users', 'email', 'users should have email column');

SELECT col_type_is('public', 'users', 'email', 'text', 'email should be text type');

SELECT * FROM finish();
ROLLBACK;

The plan(N) call declares how many tests you expect. finish() reports any discrepancy between expected and actual test counts. The ROLLBACK at the end ensures tests don't permanently modify the database.

Core Assertion Functions

pgTAP provides over 200 assertion functions. The most commonly used ones:

Schema Assertions

-- Check object existence
SELECT has_table('orders');
SELECT has_view('active_orders');
SELECT has_function('calculate_tax', ARRAY['numeric', 'text']);
SELECT has_index('orders', 'orders_customer_id_idx');
SELECT has_trigger('orders', 'audit_trigger');

-- Check columns
SELECT has_column('orders', 'total_amount');
SELECT col_not_null('orders', 'created_at');
SELECT col_has_default('orders', 'status');
SELECT col_default_is('orders', 'status', 'pending');

Data and Logic Assertions

-- Basic assertions
SELECT ok(1 = 1, 'basic equality');
SELECT is(2 + 2, 4, 'arithmetic works');
SELECT isnt(NULL, 'value', 'should not be equal');

-- Test return values from functions
SELECT is(
  calculate_discount(100.00, 'PREMIUM'),
  10.00,
  'PREMIUM discount should be 10%'
);

-- Test that a function raises an exception
SELECT throws_ok(
  'SELECT divide_by_zero(5, 0)',
  '22012',
  'division by zero',
  'should throw division by zero error'
);

-- Row count assertions
SELECT results_eq(
  'SELECT count(*) FROM orders WHERE status = ''pending''',
  $$VALUES (3::bigint)$$,
  'should have 3 pending orders'
);

Constraint Assertions

SELECT col_is_pk('users', 'id', 'id should be primary key');
SELECT fk_ok('orders', 'customer_id', 'customers', 'id', 'orders.customer_id references customers.id');
SELECT col_is_unique('users', ARRAY['email'], 'email must be unique');

Organizing Tests with Test Functions

For larger test suites, organize tests into dedicated functions:

CREATE OR REPLACE FUNCTION test_user_registration()
RETURNS SETOF TEXT AS $$
BEGIN
  -- Setup
  INSERT INTO users (email, name, status)
  VALUES ('test@example.com', 'Test User', 'active');

  RETURN NEXT ok(
    EXISTS(SELECT 1 FROM users WHERE email = 'test@example.com'),
    'user should be created'
  );

  RETURN NEXT is(
    (SELECT status FROM users WHERE email = 'test@example.com'),
    'active',
    'new user should have active status'
  );

  RETURN NEXT isnt(
    (SELECT created_at FROM users WHERE email = 'test@example.com'),
    NULL,
    'created_at should be set automatically'
  );
END;
$$ LANGUAGE plpgsql;

Run specific test functions:

BEGIN;
SELECT plan(3);
SELECT * FROM test_user_registration();
SELECT * FROM finish();
ROLLBACK;

Testing Triggers

Triggers are often untested because they're invisible during normal application tests. pgTAP makes them straightforward to test:

CREATE OR REPLACE FUNCTION test_audit_trigger()
RETURNS SETOF TEXT AS $$
DECLARE
  v_audit_count INT;
BEGIN
  -- Insert a record that should trigger the audit log
  INSERT INTO products (name, price) VALUES ('Widget', 9.99);

  SELECT count(*) INTO v_audit_count
  FROM audit_log
  WHERE table_name = 'products' AND action = 'INSERT';

  RETURN NEXT ok(v_audit_count > 0, 'audit trigger should log INSERT');

  -- Update the record
  UPDATE products SET price = 12.99 WHERE name = 'Widget';

  SELECT count(*) INTO v_audit_count
  FROM audit_log
  WHERE table_name = 'products' AND action = 'UPDATE';

  RETURN NEXT ok(v_audit_count > 0, 'audit trigger should log UPDATE');
END;
$$ LANGUAGE plpgsql;

Running Tests with pg_prove

The pg_prove command-line tool (part of the TAP::Parser::SourceHandler::pgTAP Perl module) runs pgTAP tests and formats the output:

# Install pg_prove
cpan TAP::Parser::SourceHandler::pgTAP

# Run all test files in a directory
pg_prove -d mydb tests/*.sql

# Run with verbose output
pg_prove -v -d mydb tests/

# Run with a specific user
pg_prove -d mydb -U testuser tests/

Example output:

tests/schema.sql .. ok
tests/functions.sql .. ok
tests/triggers.sql .. 1/3
not ok 2 - audit trigger should log UPDATE
# Failed test 'audit trigger should log UPDATE'
tests/triggers.sql .. Failed 1/3 subtests

Test Summary Report
-------------------
tests/triggers.sql (Wstat: 0 Tests: 3 Failed: 1)
  Failed test:  2
Files=3, Tests=8, 0 wallclock secs
Result: FAIL

CI Integration

GitHub Actions

name: Database Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Install pgTAP
        run: sudo apt-get install -y postgresql-16-pgtap

      - name: Install pg_prove
        run: sudo cpan TAP::Parser::SourceHandler::pgTAP

      - name: Run migrations
        run: psql -h localhost -U postgres -d testdb -f schema.sql
        env:
          PGPASSWORD: postgres

      - name: Enable pgTAP
        run: psql -h localhost -U postgres -d testdb -c "CREATE EXTENSION pgtap;"
        env:
          PGPASSWORD: postgres

      - name: Run pgTAP tests
        run: pg_prove -h localhost -U postgres -d testdb tests/*.sql
        env:
          PGPASSWORD: postgres

Docker Compose Setup

For local development, a docker-compose.yml with pgTAP pre-installed:

version: '3.8'
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp_test
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/01-init.sql
      - ./tests:/tests

  test:
    image: postgres:16
    depends_on:
      - db
    command: >
      sh -c "apt-get update && apt-get install -y postgresql-client perl cpanminus &&
             cpanm TAP::Parser::SourceHandler::pgTAP &&
             pg_prove -h db -U postgres -d myapp_test /tests/*.sql"
    environment:
      PGPASSWORD: postgres

Best Practices

Use transactions for isolation. Always wrap tests in BEGIN/ROLLBACK so test data doesn't leak between test files.

Separate schema tests from logic tests. Keep tests/schema/ for structural assertions (tables, columns, indexes) and tests/functions/ for behavioral tests. Schema tests catch unintended migrations; logic tests catch broken business rules.

Test negative cases. Use throws_ok() and lives_ok() to verify that invalid inputs are properly rejected. A function that should error on null input needs a test proving it errors.

Use pg_temp for test fixtures. Create temporary tables in pg_temp for test data that needs to exist across multiple statements within a single test function, without polluting the main schema.

Assert constraint violations explicitly. If a NOT NULL or UNIQUE constraint matters to your business logic, write a test that tries to violate it and confirms the error is raised.

What pgTAP Tests Catch

Stored procedure regressions after schema changes
Trigger logic bugs that only surface at the database level
Constraint violations in edge cases
View definition drift after column renames or type changes
Index existence for query performance guarantees
Permission and row-level security policy correctness

For teams with significant database logic, pgTAP provides the same confidence for SQL code that unit tests provide for application code. The investment is low — a few hundred lines of test SQL — and the payoff is catching database regressions before they reach production.