Database

Testing Supabase: Local Dev, RLS Policies, and Edge Functions

HelpMeTest

HelpMeTest

— 6 min read
Testing Supabase: Local Dev, RLS Policies, and Edge Functions

Supabase testing starts with supabase start for a fully local Postgres stack, pgTAP for Row Level Security policy validation, and Deno's built-in test runner for Edge Functions — all running locally without any remote API calls.

Key Takeaways

RLS policies are database logic — test them in the database. pgTAP lets you write SQL tests that set request.jwt.claims, impersonate roles, and assert that SELECT/INSERT/UPDATE/DELETE either succeeds or raises an exception.

Edge Functions run in Deno — test them with Deno. The Deno.test API plus fetch against supabase functions serve gives you full integration coverage without deploying to Supabase's infrastructure.

supabase db reset is your test lifecycle hook. It replays all migrations and seed files, giving you a clean schema state in seconds. Run it before your test suite, not after.

The Supabase Local Development Stack

supabase start spins up a complete local Supabase environment: Postgres, PostgREST, GoTrue (auth), Storage, and the Supabase Studio UI. Everything that runs in production runs locally.

# Install the CLI
npm install -g supabase

<span class="hljs-comment"># Start local stack (first run pulls Docker images)
supabase start

<span class="hljs-comment"># Output:
<span class="hljs-comment"># API URL: http://localhost:54321
<span class="hljs-comment"># DB URL: postgresql://postgres:postgres@localhost:54322/postgres
<span class="hljs-comment"># Studio: http://localhost:54323
<span class="hljs-comment"># Anon key: eyJhbGc...
<span class="hljs-comment"># Service role key: eyJhbGc...

To reset the database to a clean state with all migrations applied:

supabase db reset

This replays every file in supabase/migrations/ and then runs supabase/seed.sql. Use it in your CI setup script.

Project Structure

supabase/
  migrations/
    20260101000000_create_tables.sql
    20260201000000_add_rls.sql
  seed.sql
  tests/
    rls_policies.test.sql
  functions/
    process-webhook/
      index.ts
      index.test.ts

Testing Row Level Security with pgTAP

pgTAP is a PostgreSQL testing framework that lets you write unit tests in SQL. Supabase ships with pgTAP available via pg_tle. Install it in your local environment:

-- supabase/migrations/20260101000001_enable_pgtap.sql
create extension if not exists pgtap;

Here is a real schema to test:

-- supabase/migrations/20260101000000_create_tables.sql
create table public.documents (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references auth.users(id),
  org_id uuid not null,
  content text not null,
  visibility text not null default 'private' check (visibility in ('private', 'org', 'public'))
);

alter table public.documents enable row level security;

-- Owners can do anything with their documents
create policy "owner_full_access" on public.documents
  for all
  using (auth.uid() = owner_id)
  with check (auth.uid() = owner_id);

-- Org members can read org-visible documents
create policy "org_read_access" on public.documents
  for select
  using (
    visibility = 'org'
    and org_id = (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid
  );

-- Anyone can read public documents
create policy "public_read_access" on public.documents
  for select
  using (visibility = 'public');

Now write pgTAP tests for every policy:

-- supabase/tests/rls_policies.test.sql
begin;
select plan(12);

-- Seed test data
insert into auth.users (id, email) values
  ('00000000-0000-0000-0000-000000000001', 'alice@example.com'),
  ('00000000-0000-0000-0000-000000000002', 'bob@example.com');

insert into public.documents (id, owner_id, org_id, content, visibility) values
  ('doc-0001-0000-0000-0000-000000000001',
   '00000000-0000-0000-0000-000000000001',
   'org-0001-0000-0000-0000-000000000001',
   'Alice private doc', 'private'),
  ('doc-0002-0000-0000-0000-000000000001',
   '00000000-0000-0000-0000-000000000001',
   'org-0001-0000-0000-0000-000000000001',
   'Alice org doc', 'org'),
  ('doc-0003-0000-0000-0000-000000000001',
   '00000000-0000-0000-0000-000000000001',
   'org-0001-0000-0000-0000-000000000001',
   'Alice public doc', 'public');

-- Helper: set the JWT claims to simulate a logged-in user
create or replace function set_auth(user_id uuid, org_id uuid default null)
returns void language plpgsql as $$
begin
  perform set_config('request.jwt.claims', json_build_object(
    'sub', user_id::text,
    'app_metadata', json_build_object('org_id', coalesce(org_id::text, ''))
  )::text, true);
  perform set_config('role', 'authenticated', true);
end;
$$;

-- Test 1: Owner can read their own private document
select set_auth(
  '00000000-0000-0000-0000-000000000001'::uuid,
  'org-0001-0000-0000-0000-000000000001'::uuid
);
select is(
  (select count(*) from public.documents where id = 'doc-0001-0000-0000-0000-000000000001'),
  1::bigint,
  'owner can read their own private document'
);

-- Test 2: Non-owner cannot read private document
select set_auth('00000000-0000-0000-0000-000000000002'::uuid);
select is(
  (select count(*) from public.documents where id = 'doc-0001-0000-0000-0000-000000000001'),
  0::bigint,
  'non-owner cannot read private document'
);

-- Test 3: Org member can read org-visible document
select set_auth(
  '00000000-0000-0000-0000-000000000002'::uuid,
  'org-0001-0000-0000-0000-000000000001'::uuid
);
select is(
  (select count(*) from public.documents where id = 'doc-0002-0000-0000-0000-000000000001'),
  1::bigint,
  'org member can read org-visible document'
);

-- Test 4: User from different org cannot read org document
select set_auth(
  '00000000-0000-0000-0000-000000000002'::uuid,
  'org-9999-0000-0000-0000-000000000001'::uuid
);
select is(
  (select count(*) from public.documents where id = 'doc-0002-0000-0000-0000-000000000001'),
  0::bigint,
  'user from different org cannot read org document'
);

-- Test 5: Anyone can read public document
select set_auth('00000000-0000-0000-0000-000000000002'::uuid);
select is(
  (select count(*) from public.documents where id = 'doc-0003-0000-0000-0000-000000000001'),
  1::bigint,
  'any authenticated user can read public document'
);

-- Test 6: Non-owner cannot insert a document claiming someone else as owner
select set_auth('00000000-0000-0000-0000-000000000002'::uuid);
select throws_ok(
  $$insert into public.documents (owner_id, org_id, content, visibility)
    values ('00000000-0000-0000-0000-000000000001', gen_random_uuid(), 'forged', 'private')$$,
  'new row violates row-level security policy for table "documents"',
  'cannot insert document with another user as owner'
);

select * from finish();
rollback;

Run pgTAP tests:

supabase test db

This command runs all .sql files in supabase/tests/ against the local Postgres instance.

Testing with Different Roles in JavaScript

For integration tests that go through the PostgREST API (as your frontend would), use the Supabase JS client with different JWT tokens:

// test/rls-integration.test.ts
import { createClient } from "@supabase/supabase-js";
import { sign } from "jsonwebtoken";

const SUPABASE_URL = "http://localhost:54321";
const ANON_KEY = process.env.SUPABASE_ANON_KEY!;
const JWT_SECRET = process.env.SUPABASE_JWT_SECRET!; // from `supabase status`

function makeUserToken(userId: string, orgId: string) {
  return sign(
    {
      sub: userId,
      role: "authenticated",
      app_metadata: { org_id: orgId },
      exp: Math.floor(Date.now() / 1000) + 3600,
    },
    JWT_SECRET
  );
}

test("RLS prevents cross-org document access via REST", async () => {
  const aliceToken = makeUserToken("alice-uid", "org-1");
  const bobToken = makeUserToken("bob-uid", "org-2");

  const aliceClient = createClient(SUPABASE_URL, ANON_KEY, {
    global: { headers: { Authorization: `Bearer ${aliceToken}` } },
  });

  // Alice creates a private document
  const { data: created } = await aliceClient
    .from("documents")
    .insert({ content: "Alice's secret", visibility: "private", org_id: "org-1" })
    .select()
    .single();

  const bobClient = createClient(SUPABASE_URL, ANON_KEY, {
    global: { headers: { Authorization: `Bearer ${bobToken}` } },
  });

  // Bob should not be able to see it
  const { data: found } = await bobClient
    .from("documents")
    .select()
    .eq("id", created!.id);

  expect(found).toHaveLength(0);
});

Testing Edge Functions with Deno

Supabase Edge Functions run in Deno. Write tests using Deno.test and the built-in assert module:

// supabase/functions/process-webhook/index.ts
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

serve(async (req: Request) => {
  const body = await req.json();

  if (!body.event || !body.payload) {
    return new Response(JSON.stringify({ error: "Missing event or payload" }), {
      status: 400,
      headers: { "Content-Type": "application/json" },
    });
  }

  // Process webhook...
  return new Response(JSON.stringify({ received: true }), {
    headers: { "Content-Type": "application/json" },
  });
});
// supabase/functions/process-webhook/index.test.ts
import { assertEquals } from "https://deno.land/std@0.177.0/testing/asserts.ts";

const FUNCTIONS_URL = "http://localhost:54321/functions/v1";
const ANON_KEY = Deno.env.get("SUPABASE_ANON_KEY") ?? "";

Deno.test("returns 400 for missing event field", async () => {
  const response = await fetch(`${FUNCTIONS_URL}/process-webhook`, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      "Authorization": `Bearer ${ANON_KEY}`,
    },
    body: JSON.stringify({ payload: { foo: "bar" } }),
  });

  assertEquals(response.status, 400);
  const body = await response.json();
  assertEquals(body.error, "Missing event or payload");
});

Deno.test("returns 200 for valid webhook", async () => {
  const response = await fetch(`${FUNCTIONS_URL}/process-webhook`, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      "Authorization": `Bearer ${ANON_KEY}`,
    },
    body: JSON.stringify({ event: "user.created", payload: { id: "123" } }),
  });

  assertEquals(response.status, 200);
  const body = await response.json();
  assertEquals(body.received, true);
});

Serve the function locally before running tests:

supabase functions serve process-webhook --env-file .env.local
deno <span class="hljs-built_in">test --allow-net --allow-env supabase/functions/process-webhook/index.test.ts

Testing Storage Buckets

Supabase Storage is available locally and respects storage policies:

test("authenticated user can upload to their own folder", async () => {
  const userToken = makeUserToken("user-123", "org-1");
  const client = createClient(SUPABASE_URL, ANON_KEY, {
    global: { headers: { Authorization: `Bearer ${userToken}` } },
  });

  const file = new Blob(["hello world"], { type: "text/plain" });
  const { error } = await client.storage
    .from("documents")
    .upload("user-123/test-file.txt", file);

  expect(error).toBeNull();

  const { error: crossError } = await client.storage
    .from("documents")
    .upload("other-user/test-file.txt", file);

  expect(crossError).not.toBeNull();
  expect(crossError?.message).toContain("Unauthorized");
});

CI Integration

In your GitHub Actions workflow:

- name: Start Supabase local stack
  run: supabase start

- name: Run database tests (pgTAP)
  run: supabase test db

- name: Run integration tests
  run: jest --testPathPattern="test/"
  env:
    SUPABASE_URL: http://localhost:54321
    SUPABASE_ANON_KEY: ${{ steps.supabase.outputs.anon-key }}
    SUPABASE_JWT_SECRET: ${{ steps.supabase.outputs.jwt-secret }}

- name: Stop Supabase
  run: supabase stop
  if: always()

HelpMeTest can run your Supabase integration tests automatically on every pull request — sign up free.

Read more