pytest-pgtap

What is pytest-pgtap?

pytest-pgtap is a pytest plugin for running pgTAP tests against PostgreSQL. pgTAP is a mature and sadly underappreciated Postgres extension that enables running unit tests for database constructs in the database itself. You can use SQL to test your SQL.

You should read the documentation for pgTAP, which describes the functions and strategies for testing Postgres schemas, views, triggers, queries, etc.

Why?

I wanted a to run tests against my Alembic revisions and pgTAP is a great tool, but I have no idea how to install a CPAN package. Anyway, if you are testing a Flask or Django app (or whatever), it's easier to have a single test runner that fits easily into a Python toolchain. Hence a plugin for pytest.

How?

pytest-pgtap provides three entry points into the pgTAP test framework. It is designed to be a replacement for pg_prove, pgTAP's native test runner (which is written in Perl) that can...

1. Execute SQL Test Scripts directly (test_*.sql files collected by pytest)

2. Or run xUnit test functions already defined in your database. (i.e runtests())

   ...it can also be used...

3. As a pytest marker (@pytest.mark.pgtap) on pytest test cases to allow defining pgTAP tests in Python test files alongside application tests.

Requirements

• Python 3.11+
• pytest
• PostgreSQL
• pgTAP extension installed in the target database

pytest-pgtap does not automatically install pgTAP. Install it in your Postgres instance first.

Installation

Install from PyPI:

pip install pytest-pgtap

Note: To report PgTAP results, this plugin relies on the Pytest subtests feature introduced in Pytest v9. If you are using Pytest 8, you'll need to install the optional subtests dependency, that adds pytest-subtests as an extra plugin for backwards-compatibility.

pip install pytest-pgtap[subtests]

Usage

Connection

• Set a connection URI explicitly:

pytest --pgtap-uri postgresql://user:pass@host:5432/dbname

• Or rely on standard libpq environment variables (PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD).

• Or override the pgtap_connection fixture in your conftest.py to supply a connection directly — useful when your database is itself managed by a fixture (e.g. testcontainers):

import pytest
import psycopg


@pytest.fixture(scope='session')
def pgtap_connection(postgres):  # postgres is your own session-scoped fixture
    with psycopg.connect(postgres.get_connection_url()) as conn:
        yield conn

Mode 1: SQL file tests

Any file named test*.sql is collected as a pytest item.

Example tests/test_schema.sql:

BEGIN;
SELECT plan(2);
SELECT has_table('public', 'users', 'users table exists');
SELECT has_column('public', 'users', 'email', 'users.email exists');
SELECT * FROM finish();
ROLLBACK;

Run:

pytest --pgtap-uri postgresql://user:pass@host:5432/dbname

To apply pytest marks to a SQL file, write them as comments before the first line of SQL, using the same syntax as a decorator:

-- pytest.mark.slow
-- pytest.mark.xfail(reason="known bug", strict=True)
BEGIN;
...

Markers apply to the whole file.

Mode 2: xUnit runtests() mode

Run pgTAP xUnit functions from a schema using pgTAP runtests():

pytest --pgtap-uri postgresql://user:pass@host:5432/dbname --pgtap-schema mytests

Optional regex function filter:

pytest --pgtap-uri postgresql://user:pass@host:5432/dbname --pgtap-schema mytests --pgtap-match '^test_'

Note: These tests live in the database rather than in a file, so only fixtures defined in the root conftest.py are evaluated.

Mode 3: Inline Python marker mode

Use @pytest.mark.pgtap on a Python test function that returns SQL assertions:

import pytest


@pytest.mark.pgtap
def test_contacts_table(migrated_db):  # Pytest fixture for db setup
    return [
        "SELECT has_table('public', 'contacts', 'contacts table exists');",
        "SELECT has_column('public', 'contacts', 'name', 'contacts.name exists');",
    ]

Returning a single SQL string is also supported.

CLI options

• --pgtap-uri: Postgres connection URI (defaults to DATABASE_URL if set)
• --pgtap-schema: Enable xUnit mode by running runtests(<schema>)
• --pgtap-match: Regex filter used with --pgtap-schema

Development

This project suggests Hatch for managing your environment and automating development tasks:

hatch run dev:format
hatch run dev:lint
hatch run dev:test
hatch run docs:build

Similar to tox, Hatch supports multi-version matrix testing so we can verify this library works across different versions of Python, Pytest and Postgres.

hatch run test:run  # run all combinations
hatch run test.py3.13-pytest9-pg18:run  # single combination
hatch run test.py3.13-pytest9-pg18:run -x  # pass extra args to pytest
hatch env show test  # list combinations

The test suite uses testcontainers to spin up Postgres. You'll need Docker or some other OCI runtime.

Using Podman instead of Docker

If you use Podman, enable the compatibility socket and set two environment variables:

systemctl --user enable --now podman.socket
export DOCKER_HOST=unix://$XDG_RUNTIME_DIR/podman/podman.sock
export TESTCONTAINERS_RYUK_DISABLED=true