+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}

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

We used to use pgtap extensively, but ended up removing it in favour of testing queries as part our regular nodejs application tests, via a db client - the ergomonics of the test tooling is far better, and I don't think we really lost anything.

Unfortunately, I’m not surprised people test queries in their applications’ unit tests. What they’re actually testing is the ORM/query builder. Instead, with pgTAP, you can test specifically your queries.

I was going to mention pgtap as i had used it in a previous role and it works but its cumbersome. I was hoping for a better solution by reading the comments