Testing your database

Row Level Security policies, triggers and database functions are code, and they fail quietly: a policy that is too loose returns other people's rows without an error. Tests catch that before your users do.

The Supabase CLI runs database tests written in pgTAP, a testing framework that lives inside Postgres. Tests are plain SQL files, run against your local stack.

Write a test

Create a test file:

npx supabase test new bookings_rls

This makes supabase/tests/bookings_rls.test.sql. The example below tests the bookings table from Local development: guests should only see their own bookings.

-- supabase/tests/bookings_rls.test.sql
begin;
create extension if not exists pgtap with schema extensions;

select plan(4);

-- Two guests
insert into auth.users (id, email) values
  ('11111111-1111-1111-1111-111111111111', '[email protected]'),
  ('22222222-2222-2222-2222-222222222222', '[email protected]');

insert into public.bookings (guest_id, room, starts_at, nights) values
  ('11111111-1111-1111-1111-111111111111', 'A1', now(), 2),
  ('22222222-2222-2222-2222-222222222222', 'B1', now(), 3);

-- The table has RLS turned on
select ok(
  (select relrowsecurity from pg_class where oid = 'public.bookings'::regclass),
  'RLS is enabled on bookings'
);

-- Act as Ana
set local role authenticated;
set local request.jwt.claims to '{"sub": "11111111-1111-1111-1111-111111111111", "role": "authenticated"}';

select results_eq(
  'select room from public.bookings',
  array['A1'],
  'Ana only sees her own booking'
);

select is_empty(
  $$ select 1 from public.bookings where guest_id = '22222222-2222-2222-2222-222222222222' $$,
  'Ana cannot see Ben''s booking'
);

-- Act as a signed-out visitor
set local role anon;
set local request.jwt.claims to '{"role": "anon"}';

select is_empty('select 1 from public.bookings', 'Visitors see no bookings');

select * from finish();
rollback;

How it works:

• Everything runs inside begin ... rollback, so the test leaves no data behind.
• set local role authenticated switches to the role the Data API uses for signed-in users.
• request.jwt.claims is where auth.uid() reads the user id from, so setting it makes the policies see Ana.
• plan(4) declares how many assertions to expect, so a test that stops early fails instead of passing silently.

Run the tests

npx supabase start      # if it isn't running
npx supabase test db

The CLI runs every file in supabase/tests/ and prints a TAP report. A failing assertion shows what was expected and what came back.

Useful pgTAP assertions for Supabase work:

For write policies, throws_ok with error code 42501 confirms RLS rejected an insert or update:

select throws_ok(
  $$ insert into public.bookings (guest_id, room, starts_at, nights)
     values ('22222222-2222-2222-2222-222222222222', 'A2', now(), 1) $$,
  '42501',
  null,
  'Ana cannot create a booking for Ben'
);

Run tests in CI

Tests run against a fresh local stack, so CI only needs Docker and the CLI. A GitHub Actions job:

# .github/workflows/db-tests.yml
name: Database tests
on: [pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v3
        with:
          version: latest
      - run: supabase db start
      - run: supabase test db

supabase db start starts only Postgres and applies your migrations, which is quicker than the full stack.

Testing against SupaHost

Run pgTAP files locally and in CI; there's no need to run them on your production project. To check a migration against real data before shipping it, create a branch with data (Pro and above), apply the migration there, and exercise it:

• from your app, by pointing a preview build at the branch's URL and keys;
• from Studio's SQL editor on the branch, by pasting a test file. First check pgTAP is available with select * from pg_available_extensions where name = 'pgtap';;
• with psql over the SSH tunnel on Business, using the branch's own connection string.

Then delete the branch. MCP's get_advisors also flags tables without RLS, tables with RLS but no policies, and functions with a mutable search path, which is a useful quick check after any migration.

Testing from your app

For end-to-end tests, point your test runner at the local stack (http://127.0.0.1:54321 and the local keys), create users through the Auth admin API in a setup step, and sign in with supabase-js as each one. This tests the same path your real clients use: Auth, the Data API and your policies together.

Related

• Row Level Security
• Seeding data
• RLS performance best practices
• Migrations workflow: local to SupaHost