Support Running pgTAP Tests with Custom Extension Schemas

[istarkov]

Problem Description

Currently, when creating the pgtap extension with a custom schema:

CREATE EXTENSION IF NOT EXISTS pgtap WITH SCHEMA pgtap; -- <== CUSTOM schema 

there is no straightforward way (or I have not found) to run supabase test db with a custom --search_path option (e.g., --search_path=pgtap,public).

Proposed Solution

Allow setting PGOPTIONS in the Supabase CLI, which would enable setting a custom search path. For example:

PGOPTIONS='--search_path=pgtap,public' supabase test db --db-url 'conn-string'

The following Docker command works seamlessly for running tests with a custom search_path

docker run --rm --network host \
-v ./supabase/tests:/tests \
-e PGOPTIONS='--search_path=pgtap,public' \
supabase/pg_prove:3.36 \
pg_prove -d "postgresql://postgres:pass@localhost/webstudio" /tests/latest-builds_test.sql

Alternatively, provide a mechanism to pass flags directly to the pg_prove CLI.

Alternative Solutions Considered

Currently, I am setting the search path within test files using:

SET LOCAL search_path = pgtap, public;

Anyway gonna move on

docker run --rm --network host \
-v ./supabase/tests:/tests \
-e PGOPTIONS='--search_path=pgtap,public' \
supabase/pg_prove:3.36 \
pg_prove -d "postgresql://postgres:pass@localhost/webstudio" /tests/latest-builds_test.sql

While this works, it's not ideal for managing multiple tests or maintaining clean code.

[istarkov]

The issue with the PGOPTIONS workaround is that it only works with direct connections.
Corresponding issue: supabase/supavisor#206