Using pgTAP on GitHub Actions

07 May 2020 - François

pgTAP is a testing tool for the PostgreSQL database. I used it on indoor= and It remains open.

Using it with Github Actions is not easy, so here is an example:

File my_function.sql:

--- my_function.sql
CREATE OR REPLACE FUNCTION my_function() RETURNS INTEGER AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

File my_function_test.sql:

--- my_function_test.sql
BEGIN;
SELECT plan();

SELECT is(
    my_function(),
    1
);

SELECT * FROM finish();
ROLLBACK;

File .github/workflows/pgtest.yml:

# .github/workflows/pgtest.yml
name: Postgres tests

on: push

jobs:
  pgtap:

    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:14
        env:
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v3

      - name: install pg_prove
        run: sudo cpan TAP::Parser::SourceHandler::pgTAP
        env:
          SHELL: /bin/bash

      - name: install postgres dev
        run: sudo apt-get install -y --no-install-recommends postgresql-server-dev-14

      - name: Checkout pgtap
        uses: actions/checkout@v3
        with:
          repository: theory/pgtap
          path: pgtap
          ref: v1.2.0

      - name: install pgtap
        working-directory: pgtap
        run: make && psql --host localhost --username postgres --dbname postgres --file sql/pgtap.sql
        env:
          PGPASSWORD: postgres

      - name: load sql
        run: >-
          psql --host localhost --username postgres --dbname postgres \
               --file my_function.sql
        env:
          PGPASSWORD: postgres

      - name: run tests
        run: pg_prove --host localhost --dbname postgres --username postgres *_test.sql
        env:
          PGPASSWORD: postgres

A repository with a working code is available here: github.com/francois2metz/pgtap-github-actions

Have a comment? Contact me by email.