Testing

Lesson 17 · about 15 minutes · three test frameworks, one rule for picking, and why CI runs only a third of them.

Every ticket you ship here needs a guard, and the repo has three places to put one. Pick wrong and the test either cannot run in CI, or duplicates a rule Postgres already enforces. This lesson is the decision table plus the mechanics of each framework, read from the actual files.

Which framework

What changed	Framework	Where	Runs in CI?
Migration, RLS policy, DB function, trigger, constraint	pgTAP	`src/supabase/tests/database/*.test.sql` (155 files)	No
Pure logic inside a function (a normaliser, a parser, a core with injected deps)	Deno test, hermetic	`src/supabase/tests/functions/*-unit-test.ts` and any file that stubs the client	Yes
HTTP behaviour of a served function (status, auth gate, response shape)	Deno test, integration	`src/supabase/tests/functions/*-test.ts` importing `_testUtils.ts`	No
Legacy multi-system flows (Quartr webhook + DB + cache)	Node.js	`test/`, orchestrated by `run-tests.js`	No; maintenance only

Why pgTAP first? A DB rule tested through an edge function is tested once per caller. Tested in SQL it is tested once, in the same transaction the migration runs in, with rollback cleanup for free. CLAUDE.md fixes this priority order; the Boy Scout rule says a Node test you touch gets migrated down this table, not patched. CLAUDE.md "Test Strategy — Priority Order"

pgTAP: a test is a transaction

1begin; then select plan(25); declares how many assertions will run. A miscount fails the file, which catches a test that silently stopped early. tests/database/account-deletion.test.sql:16-17

2Assertions are SQL functions: has_function('public','soft_delete_user',array['uuid'],…), is(a, b, msg), isnt(a, b, msg). Each returns one row of TAP output. account-deletion.test.sql:23-56

3Setup is plain insert; the test then calls the function under test and asserts on auth.users, profiles, sessions. Because the file ends with rollback;, nothing persists and tests cannot poison each other. CLAUDE.md pgTAP example

4cd src && npx supabase test db discovers every *.test.sql; no registration anywhere. It needs the Docker stack, so CI does not run it.

Naming convention seen across the folder: the file is named after the migration it guards (add-avatar-support.test.sql for …_add_avatar_support.sql), and the header comment lists what it verifies. Copy that.

Deno tests: two species in one folder

Hermetic tests import a "core" function and inject its dependencies. account-change-bio-test.ts builds processBioChange(req, deps) with a fake moderate, getUserId and callRpc, then asserts on the returned Response status. No stack, no served function, no OpenAI key. tests/functions/account-change-bio-test.ts:19-30

Integration tests import _testUtils.ts, seed rows through adminClient, hit http://localhost:54321/functions/v1/…, then delete what they made. stocks-details-test.ts seeds a company with a unique ticker built from Date.now() so parallel runs do not collide. tests/functions/stocks-details-test.ts:22-25

`_testUtils.ts` export	Why it exists
`TEST_ORIGIN`	Deno's `fetch` sends no Origin, and every function 403s without one. Every client sets it. _testUtils.ts:12-15
`SERVICE_ROLE_KEY`, `ANON_KEY`	The legacy JWT-shaped keys the local runtime injects. `validateServiceRole` compares against these, so the new `sb_secret_` key does not* pass locally. _testUtils.ts:16-19
`PUBLISHABLE_KEY`, `SECRET_KEY`	The modern keys. `verifyPublicAuth` in stocks accepts only the publishable key, rejecting the legacy anon JWT. Env-first with a fallback to the fixed local value so a forgotten `--env-file` does not 401 everything. _testUtils.ts:25-33
`createTestUser()`	Creates a user via the admin API, signs in, returns a client that carries the anon apikey plus the user's bearer. Carrying the service apikey would make `validateServiceRole` wrongly authorise a user request. _testUtils.ts:44-88

Why does CI run only some Deno tests? deno-tests.yml selects files with grep -LE "localhost:54321|_testUtils": anything that does not mention the local stack. It fails the job if the selection is empty, so a bad filter cannot pass silently. Type-checking is on, which is why CLAUDE.md warns to import SupabaseClient from the same specifier as the module under test: the npm: and jsr: builds have different types. .github/workflows/deno-tests.yml:27-35

Integration tests pass { sanitizeResources: false, sanitizeOps: false } to Deno.test. Deno by default fails a test that leaks an open connection or pending timer; supabase-js keeps a fetch keep-alive open, so the sanitizers are switched off. stocks-details-test.ts:19

Legacy Node suite

test/run-tests.js spawns each test-*.js as a child process and infers pass/fail from the exit code plus regexes over stdout (success rate: N%, N passed … N failed, a literal ✅). Results are sorted into criticalIssues, errors and warnings. Config comes from test/.env.test, parsed by hand. Touch it only to fix a failure, and when you do, ask which row of the first table it belongs in. test/run-tests.js:296-370, 679-706

Check yourself

Do this in the repo

From src/, with the stack up, run npx supabase test db and find the line reporting the plan count for account-deletion.test.sql.
Run deno test --allow-all supabase/tests/functions/account-change-bio-test.ts with the stack stopped. It passes: that is what hermetic means.
Run the CI filter locally: grep -LE "localhost:54321|_testUtils" supabase/tests/functions/*.ts | wc -l and compare with ls supabase/tests/functions/*.ts | wc -l.

Primary source

Supabase: Testing Edge Functions, the pattern CLAUDE.md's Deno section is derived from. For SQL, Supabase: pgTAP.

Next: Lesson 18, local dev and deploy: the stack these tests run against and how code reaches prod. Unclear on why a test 401s locally? Ask the teacher: "which key does verifyPublicAuth accept?"