Postgres - pgTAP

data/postgres/pgtap

pgTAP is a unit testing extension for Postgres.

On this page

Introduction

pgTAP is an open-source unit testing framework consisting of a suite of database functions written in PL/pgSQL. It provides a robust set of tools and utilities for writing and running tests against your database code. Inspired by the popular xUnit testing framework, pgTAP follows a similar convention and allows you to write comprehensive tests for database objects such as tables, functions, triggers, and more.

One of its standout features is its comprehensive set of built-in assertion functions that allow you to test for expected results and verify the integrity of your database objects.

pgTAP provides powerful features for PostgreSQL environments, streamlining the writing and execution of tests. With seamless integration into PostgreSQL, built-in assertions, and strong extensibility, the benefits of using pgTAP include comprehensive test coverage, native PostgreSQL integration, and automated validation of database components, making it an effective tool for ensuring database code reliability.

Getting started

Terminal window
docker run -d --name postgres --restart=always -p 5432:5432 -e POSTGRES_PASSWORD=password -e POSTGRES_USER=postgres postgres:18
docker exec postgres sh -c "apt update && apt install -y --no-install-recommends postgresql-18-pgtap"
docker exec postgres psql -U postgres -c "create database test;"

You can use the image registry.gitlab.com/xtec/data/postgres/pgtap:latest instead of postgres:18

Enable the extension

Connect to your PostgreSQL database and run the following command to enable the pgTAP extension:

init.sql
create schema if not exists extensions;
create extension if not exists pgtap with schema extensions;

Even though the SQL code is create extension, this is the equivalent of enabling the extension.

To disable an extension, you can call drop extension.

It’s good practice to create the extension within a separate schema (like extensions) to keep the public schema clean.

Using the pgTAP Extension

Once you have installed pgTAP, you can write tests for your PostgreSQL applications. Here is a simple example of how to use pgTAP:

add extensions to search_pat

test1.sql
set search_path = extensions, public;


begin;
select plan(2);


select ok(1 = 1, 'One equals one');
select is('PostgreSQL'::text, 'PostgreSQL'::text, 'PostgreSQL is PostgreSQL');


select * from finish();
rollback;

In this example, we first call the plan function to specify the number of tests we will run. Then, we use the ok and is functions to run our tests. Finally, we call the finish function to complete our test suite.

In PostgreSQL, search_path is the ordered list of schemas the server searches when you refer to an object without qualifying it with a schema.

pgtap.is() is polymorphic (anyelement). When you pass two string literals like 'PostgreSQL', PostgreSQL initially treats them as type unknown. With both args unknown, it can’t infer the polymorphic type. The solution is to cast at least one (I recommend both) to text.

Testing

👉 Create a unit testing framework for PostgreSQL using the pgTAP extension

Pending