# Testing your database

> Test tables, functions and Row Level Security policies with pgTAP and the Supabase CLI, run the tests in CI, and check behaviour against a SupaHost branch before you ship a migration.

Source: https://supahost.app/docs/local-development/testing · Updated 2026-11-11

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](https://pgtap.org/), a testing framework that lives inside Postgres. Tests are plain SQL files, run against your local stack.

## Write a test

Create a test file:

```bash
npx supabase test new bookings_rls
```

This makes `supabase/tests/bookings_rls.test.sql`. The example below tests the `bookings` table from [Local development](/docs/local-development/overview): guests should only see their own bookings.

```sql
-- 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', 'ana@example.test'),
  ('22222222-2222-2222-2222-222222222222', 'ben@example.test');

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

```bash
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:

| Assertion | Use it to check |
|---|---|
| `has_table`, `has_column`, `col_not_null` | The schema matches what the app expects |
| `policies_are('public', 'bookings', array[...])` | No policy was added or dropped by accident |
| `results_eq`, `is_empty`, `isnt_empty` | What a given user can read |
| `lives_ok`, `throws_ok(sql, '42501')` | Which writes succeed and which RLS blocks |
| `function_returns`, `is` | Database functions return the right thing |

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

```sql
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:

```yaml
# .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](/docs/platform/branching) 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](/docs/platform/ssh) 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](/docs/database/row-level-security)
- [Seeding data](/docs/local-development/seeding)
- [RLS performance best practices](/kb/rls-performance)
- [Migrations workflow: local to SupaHost](/docs/local-development/migrations-workflow)