[vogtb]

+1 for pgtap. Surprised it's not been mentioned more in this thread.

I'm using it w/ supabase, and it works really well.

They have a small doc on it that's a better primer than the pgtap docs: <https://supabase.com/docs/guides/database/extensions/pgtap>.

Pretty easy to get started, I'm doing something like this in a Makefile.

  supabase_tests := $(call rfind, supabase/tests/*_tests.sql)
  DB_URL := "postgresql://postgres:postgres@localhost:54322/postgres"
  test-supabase: $(supabase_tests)
    @echo "Testing supabase..." && \
        echo "${supabase_tests}" && \
        psql -f supabase/tests/setup.sql ${DB_URL} && \
        $(foreach t,$(supabase_tests),\
            echo "Test: $(t)..." && psql -f $(t) ${DB_URL} $(__EXEC)) && \
        psql -f supabase/tests/teardown.sql ${DB_URL}

[kiwicopple]

the test runner is now built into the supabase CLI: https://supabase.com/docs/reference/cli/supabase-test

Guide is here: https://supabase.com/docs/guides/database/testing