Local Development

Advanced pgTAP Testing

While basic pgTAP provides excellent testing capabilities, you can enhance the testing workflow using database development tools and helper packages. This guide covers advanced testing techniques using database.dev and community-maintained test helpers.

Using database.dev

Database.dev is a package manager for PostgreSQL that allows installation and use of community-maintained packages, including testing utilities.

Setting up dbdev

To use database development tools and packages, install some prerequisites:


_36
create extension if not exists http with schema extensions;
_36
create extension if not exists pg_tle;
_36
drop extension if exists "supabase-dbdev";
_36
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
_36
select
_36
pgtle.install_extension(
_36
'supabase-dbdev',
_36
resp.contents ->> 'version',
_36
'PostgreSQL package manager',
_36
resp.contents ->> 'sql'
_36
)
_36
from http(
_36
(
_36
'GET',
_36
'https://api.database.dev/rest/v1/'
_36
|| 'package_versions?select=sql,version'
_36
|| '&package_name=eq.supabase-dbdev'
_36
|| '&order=version.desc'
_36
|| '&limit=1',
_36
array[
_36
('apiKey', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyYndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzIsImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJrzM0AQKsu_5k134s')::http_header
_36
],
_36
null,
_36
null
_36
)
_36
) x,
_36
lateral (
_36
select
_36
((row_to_json(x) -> 'content') #>> '{}')::json -> 0
_36
) resp(contents);
_36
create extension "supabase-dbdev";
_36
select dbdev.install('supabase-dbdev');
_36
_36
-- Drop and recreate the extension to ensure a clean installation
_36
drop extension if exists "supabase-dbdev";
_36
create extension "supabase-dbdev";

Installing test helpers

The Test Helpers package provides utilities that simplify testing Supabase-specific features:


_10
select dbdev.install('basejump-supabase_test_helpers');
_10
create extension if not exists "basejump-supabase_test_helpers" version '0.0.6';

Test helper benefits

The test helpers package provides several advantages over writing raw pgTAP tests:

  1. Simplified User Management

    • Create test users with tests.create_supabase_user()
    • Switch contexts with tests.authenticate_as()
    • Retrieve user IDs using tests.get_supabase_uid()

  2. Row Level Security (RLS) Testing Utilities

    • Verify RLS status with tests.rls_enabled()
    • Test policy enforcement
    • Simulate different user contexts

  3. Reduced Boilerplate

    • No need to manually insert auth.users
    • Simplified JWT claim management
    • Clean test setup and cleanup

Schema-wide Row Level Security testing

When working with Row Level Security, it's crucial to ensure that RLS is enabled on all tables that need it. Create a simple test to verify RLS is enabled across an entire schema:


_10
begin;
_10
select plan(1);
_10
_10
-- Verify RLS is enabled on all tables in the public schema
_10
select tests.rls_enabled('public');
_10
_10
select * from finish();
_10
rollback;

Test file organization

When working with multiple test files that share common setup requirements, it's beneficial to create a single "pre-test" file that handles the global environment setup. This approach reduces duplication and ensures consistent test environments.

Creating a pre-test hook

Since pgTAP test files are executed in alphabetical order, create a setup file that runs first by using a naming convention like 000-setup-tests-hooks.sql:


_10
supabase test new 000-setup-tests-hooks

This setup file should contain:

  1. All shared extensions and dependencies
  2. Common test utilities
  3. A simple always green test to verify the setup

Here's an example setup file:


_55
-- install tests utilities
_55
-- install pgtap extension for testing
_55
create extension if not exists pgtap with schema extensions;
_55
/*
_55
---------------------
_55
---- install dbdev ----
_55
----------------------
_55
Requires:
_55
- pg_tle: https://github.com/aws/pg_tle
_55
- pgsql-http: https://github.com/pramsey/pgsql-http
_55
*/
_55
create extension if not exists http with schema extensions;
_55
create extension if not exists pg_tle;
_55
drop extension if exists "supabase-dbdev";
_55
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
_55
select
_55
pgtle.install_extension(
_55
'supabase-dbdev',
_55
resp.contents ->> 'version',
_55
'PostgreSQL package manager',
_55
resp.contents ->> 'sql'
_55
)
_55
from http(
_55
(
_55
'GET',
_55
'https://api.database.dev/rest/v1/'
_55
|| 'package_versions?select=sql,version'
_55
|| '&package_name=eq.supabase-dbdev'
_55
|| '&order=version.desc'
_55
|| '&limit=1',
_55
array[
_55
('apiKey', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyYndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzIsImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJrzM0AQKsu_5k134s')::http_header
_55
],
_55
null,
_55
null
_55
)
_55
) x,
_55
lateral (
_55
select
_55
((row_to_json(x) -> 'content') #>> '{}')::json -> 0
_55
) resp(contents);
_55
create extension "supabase-dbdev";
_55
select dbdev.install('supabase-dbdev');
_55
drop extension if exists "supabase-dbdev";
_55
create extension "supabase-dbdev";
_55
-- Install test helpers
_55
select dbdev.install('basejump-supabase_test_helpers');
_55
create extension if not exists "basejump-supabase_test_helpers" version '0.0.6';
_55
_55
-- Verify setup with a no-op test
_55
begin;
_55
select plan(1);
_55
select ok(true, 'Pre-test hook completed successfully');
_55
select * from finish();
_55
rollback;

Benefits

This approach provides several advantages:

  • Reduces code duplication across test files
  • Ensures consistent test environment setup
  • Makes it easier to maintain and update shared dependencies
  • Provides immediate feedback if the setup process fails

Your subsequent test files (001-auth-tests.sql, 002-rls-tests.sql) can focus solely on their specific test cases, knowing that the environment is properly configured.

Example: Advanced RLS testing

Here's a complete example using test helpers to verify RLS policies putting it all together:


_51
begin;
_51
-- Assuming 000-setup-tests-hooks.sql file is present to use tests helpers
_51
select plan(4);
_51
_51
-- Set up test data
_51
_51
-- Create test supabase users
_51
select tests.create_supabase_user('user1@test.com');
_51
select tests.create_supabase_user('user2@test.com');
_51
_51
-- Create test data
_51
insert into public.todos (task, user_id) values
_51
('User 1 Task 1', tests.get_supabase_uid('user1@test.com')),
_51
('User 1 Task 2', tests.get_supabase_uid('user1@test.com')),
_51
('User 2 Task 1', tests.get_supabase_uid('user2@test.com'));
_51
_51
-- Test as User 1
_51
select tests.authenticate_as('user1@test.com');
_51
_51
-- Test 1: User 1 should only see their own todos
_51
select results_eq(
_51
'select count(*) from todos',
_51
ARRAY[2::bigint],
_51
'User 1 should only see their 2 todos'
_51
);
_51
_51
-- Test 2: User 1 can create their own todo
_51
select lives_ok(
_51
$$insert into todos (task, user_id) values ('New Task', tests.get_supabase_uid('user1@test.com'))$$,
_51
'User 1 can create their own todo'
_51
);
_51
_51
-- Test as User 2
_51
select tests.authenticate_as('user2@test.com');
_51
_51
-- Test 3: User 2 should only see their own todos
_51
select results_eq(
_51
'select count(*) from todos',
_51
ARRAY[1::bigint],
_51
'User 2 should only see their 1 todo'
_51
);
_51
_51
-- Test 4: User 2 cannot modify User 1's todo
_51
SELECT results_ne(
_51
$$ update todos set task = 'Hacked!' where user_id = tests.get_supabase_uid('user1@test.com') returning 1 $$,
_51
$$ values(1) $$,
_51
'User 2 cannot modify User 1 todos'
_51
);
_51
_51
select * from finish();
_51
rollback;

Not another todo app: Testing complex organizations

Todo apps are great for learning, but this section explores testing a more realistic scenario: a multi-tenant content publishing platform. This example demonstrates testing complex permissions, plan restrictions, and content management.

System overview

This demo app implements:

  • Organizations with tiered plans (free/pro/enterprise)
  • Role-based access (owner/admin/editor/viewer)
  • Content management (posts/comments)
  • Premium content restrictions
  • Plan-based limitations

What makes this complex?

  1. Layered Permissions

    • Role hierarchies affect access rights
    • Plan types influence user capabilities
    • Content state (draft/published) affects permissions

  2. Business Rules

    • Free plan post limits
    • Premium content visibility
    • Cross-organization security

Testing focus areas

When writing tests, verify:

  • Organization member access control
  • Content visibility across roles
  • Plan limitation enforcement
  • Cross-organization data isolation

1. App schema definitions

The app schema tables are defined like this:


_51
create table public.profiles (
_51
id uuid references auth.users(id) primary key,
_51
username text unique not null,
_51
full_name text,
_51
bio text,
_51
created_at timestamptz default now(),
_51
updated_at timestamptz default now()
_51
);
_51
_51
create table public.organizations (
_51
id bigint primary key generated always as identity,
_51
name text not null,
_51
slug text unique not null,
_51
plan_type text not null check (plan_type in ('free', 'pro', 'enterprise')),
_51
max_posts int not null default 5,
_51
created_at timestamptz default now()
_51
);
_51
_51
create table public.org_members (
_51
org_id bigint references public.organizations(id) on delete cascade,
_51
user_id uuid references auth.users(id) on delete cascade,
_51
role text not null check (role in ('owner', 'admin', 'editor', 'viewer')),
_51
created_at timestamptz default now(),
_51
primary key (org_id, user_id)
_51
);
_51
_51
create table public.posts (
_51
id bigint primary key generated always as identity,
_51
title text not null,
_51
content text not null,
_51
author_id uuid references public.profiles(id) not null,
_51
org_id bigint references public.organizations(id),
_51
status text not null check (status in ('draft', 'published', 'archived')),
_51
is_premium boolean default false,
_51
scheduled_for timestamptz,
_51
category text,
_51
view_count int default 0,
_51
published_at timestamptz,
_51
created_at timestamptz default now(),
_51
updated_at timestamptz default now()
_51
);
_51
_51
create table public.comments (
_51
id bigint primary key generated always as identity,
_51
post_id bigint references public.posts(id) on delete cascade,
_51
author_id uuid references public.profiles(id),
_51
content text not null,
_51
is_deleted boolean default false,
_51
created_at timestamptz default now(),
_51
updated_at timestamptz default now()
_51
);

2. RLS policies declaration

Now to setup the RLS policies for each tables:


_122
-- Create a private schema to store all security definer functions utils
_122
-- As such functions should never be in a API exposed schema
_122
create schema if not exists private;
_122
-- Helper function for role checks
_122
create or replace function private.get_user_org_role(org_id bigint, user_id uuid)
_122
returns text
_122
set search_path = ''
_122
as $$
_122
select role from public.org_members
_122
where org_id = $1 and user_id = $2;
_122
-- Note the use of security definer to avoid RLS checking recursion issue
_122
-- see: https://supabase.com/docs/guides/database/postgres/row-level-security#use-security-definer-functions
_122
$$ language sql security definer;
_122
-- Helper utils to check if an org is below the max post limit
_122
create or replace function private.can_add_post(org_id bigint)
_122
returns boolean
_122
set search_path = ''
_122
as $$
_122
select (select count(*)
_122
from public.posts p
_122
where p.org_id = $1) < o.max_posts
_122
from public.organizations o
_122
where o.id = $1
_122
$$ language sql security definer;
_122
_122
_122
-- Enable RLS for all tables
_122
alter table public.profiles enable row level security;
_122
alter table public.organizations enable row level security;
_122
alter table public.org_members enable row level security;
_122
alter table public.posts enable row level security;
_122
alter table public.comments enable row level security;
_122
_122
-- Profiles policies
_122
create policy "Public profiles are viewable by everyone"
_122
on public.profiles for select using (true);
_122
_122
create policy "Users can insert their own profile"
_122
on public.profiles for insert with check ((select auth.uid()) = id);
_122
_122
create policy "Users can update their own profile"
_122
on public.profiles for update using ((select auth.uid()) = id)
_122
with check ((select auth.uid()) = id);
_122
_122
-- Organizations policies
_122
create policy "Public org info visible to all"
_122
on public.organizations for select using (true);
_122
_122
create policy "Org management restricted to owners"
_122
on public.organizations for all using (
_122
private.get_user_org_role(id, (select auth.uid())) = 'owner'
_122
);
_122
_122
-- Org Members policies
_122
create policy "Members visible to org members"
_122
on public.org_members for select using (
_122
private.get_user_org_role(org_id, (select auth.uid())) is not null
_122
);
_122
_122
create policy "Member management restricted to admins and owners"
_122
on public.org_members for all using (
_122
private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin')
_122
);
_122
_122
-- Posts policies
_122
create policy "Complex post visibility"
_122
on public.posts for select using (
_122
-- Published non-premium posts are visible to all
_122
(status = 'published' and not is_premium)
_122
or
_122
-- Premium posts visible to org members only
_122
(status = 'published' and is_premium and
_122
private.get_user_org_role(org_id, (select auth.uid())) is not null)
_122
or
_122
-- All posts visible to editors and above
_122
private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin', 'editor')
_122
);
_122
_122
create policy "Post creation rules"
_122
on public.posts for insert with check (
_122
-- Must be org member with appropriate role
_122
private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin', 'editor')
_122
and
_122
-- Check org post limits for free plans
_122
(
_122
(select o.plan_type != 'free'
_122
from organizations o
_122
where o.id = org_id)
_122
or
_122
(select private.can_add_post(org_id))
_122
)
_122
);
_122
_122
create policy "Post update rules"
_122
on public.posts for update using (
_122
exists (
_122
select 1
_122
where
_122
-- Editors can update non-published posts
_122
(private.get_user_org_role(org_id, (select auth.uid())) = 'editor' and status != 'published')
_122
or
_122
-- Admins and owners can update any post
_122
private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin')
_122
)
_122
);
_122
_122
-- Comments policies
_122
create policy "Comments on published posts are viewable by everyone"
_122
on public.comments for select using (
_122
exists (
_122
select 1 from public.posts
_122
where id = post_id
_122
and status = 'published'
_122
)
_122
and not is_deleted
_122
);
_122
_122
create policy "Authenticated users can create comments"
_122
on public.comments for insert with check ((select auth.uid()) = author_id);
_122
_122
create policy "Users can update their own comments"
_122
on public.comments for update using (author_id = (select auth.uid()));

3. Test cases:

Now everything is setup, let's write RLS test cases, note that each section could be in its own test:


_181
-- Assuming we already have: 000-setup-tests-hooks.sql file we can use tests helpers
_181
begin;
_181
-- Declare total number of tests
_181
select plan(10);
_181
_181
-- Create test users
_181
select tests.create_supabase_user('org_owner', 'owner@test.com');
_181
select tests.create_supabase_user('org_admin', 'admin@test.com');
_181
select tests.create_supabase_user('org_editor', 'editor@test.com');
_181
select tests.create_supabase_user('premium_user', 'premium@test.com');
_181
select tests.create_supabase_user('free_user', 'free@test.com');
_181
select tests.create_supabase_user('scheduler', 'scheduler@test.com');
_181
select tests.create_supabase_user('free_author', 'free_author@test.com');
_181
_181
-- Create profiles for test users
_181
insert into profiles (id, username, full_name)
_181
values
_181
(tests.get_supabase_uid('org_owner'), 'org_owner', 'Organization Owner'),
_181
(tests.get_supabase_uid('org_admin'), 'org_admin', 'Organization Admin'),
_181
(tests.get_supabase_uid('org_editor'), 'org_editor', 'Organization Editor'),
_181
(tests.get_supabase_uid('premium_user'), 'premium_user', 'Premium User'),
_181
(tests.get_supabase_uid('free_user'), 'free_user', 'Free User'),
_181
(tests.get_supabase_uid('scheduler'), 'scheduler', 'Scheduler User'),
_181
(tests.get_supabase_uid('free_author'), 'free_author', 'Free Author');
_181
_181
-- First authenticate as service role to bypass RLS for initial setup
_181
select tests.authenticate_as_service_role();
_181
_181
-- Create test organizations and setup data
_181
with new_org as (
_181
insert into organizations (name, slug, plan_type, max_posts)
_181
values
_181
('Test Org', 'test-org', 'pro', 100),
_181
('Premium Org', 'premium-org', 'enterprise', 1000),
_181
('Schedule Org', 'schedule-org', 'pro', 100),
_181
('Free Org', 'free-org', 'free', 2)
_181
returning id, slug
_181
),
_181
-- Setup members and posts
_181
member_setup as (
_181
insert into org_members (org_id, user_id, role)
_181
select
_181
org.id,
_181
user_id,
_181
role
_181
from new_org org cross join (
_181
values
_181
(tests.get_supabase_uid('org_owner'), 'owner'),
_181
(tests.get_supabase_uid('org_admin'), 'admin'),
_181
(tests.get_supabase_uid('org_editor'), 'editor'),
_181
(tests.get_supabase_uid('premium_user'), 'viewer'),
_181
(tests.get_supabase_uid('scheduler'), 'editor'),
_181
(tests.get_supabase_uid('free_author'), 'editor')
_181
) as members(user_id, role)
_181
where org.slug = 'test-org'
_181
or (org.slug = 'premium-org' and role = 'viewer')
_181
or (org.slug = 'schedule-org' and role = 'editor')
_181
or (org.slug = 'free-org' and role = 'editor')
_181
)
_181
-- Setup initial posts
_181
insert into posts (title, content, org_id, author_id, status, is_premium, scheduled_for)
_181
select
_181
title,
_181
content,
_181
org.id,
_181
author_id,
_181
status,
_181
is_premium,
_181
scheduled_for
_181
from new_org org cross join (
_181
values
_181
('Premium Post', 'Premium content', tests.get_supabase_uid('premium_user'), 'published', true, null),
_181
('Free Post', 'Free content', tests.get_supabase_uid('premium_user'), 'published', false, null),
_181
('Future Post', 'Future content', tests.get_supabase_uid('scheduler'), 'published', false, '2024-01-02 12:00:00+00'::timestamptz)
_181
) as posts(title, content, author_id, status, is_premium, scheduled_for)
_181
where org.slug in ('premium-org', 'schedule-org');
_181
_181
-- Test owner privileges
_181
select tests.authenticate_as('org_owner');
_181
select lives_ok(
_181
$$
_181
update organizations
_181
set name = 'Updated Org'
_181
where id = (select id from organizations limit 1)
_181
$$,
_181
'Owner can update organization'
_181
);
_181
_181
-- Test admin privileges
_181
select tests.authenticate_as('org_admin');
_181
select results_eq(
_181
$$select count(*) from org_members$$,
_181
ARRAY[6::bigint],
_181
'Admin can view all members'
_181
);
_181
_181
-- Test editor restrictions
_181
select tests.authenticate_as('org_editor');
_181
select throws_ok(
_181
$$
_181
insert into org_members (org_id, user_id, role)
_181
values (
_181
(select id from organizations limit 1),
_181
(select tests.get_supabase_uid('org_editor')),
_181
'viewer'
_181
)
_181
$$,
_181
'42501',
_181
'new row violates row-level security policy for table "org_members"',
_181
'Editor cannot manage members'
_181
);
_181
_181
-- Premium Content Access Tests
_181
select tests.authenticate_as('premium_user');
_181
select results_eq(
_181
$$select count(*) from posts where org_id = (select id from organizations where slug = 'premium-org')$$,
_181
ARRAY[3::bigint],
_181
'Premium user can see all posts'
_181
);
_181
_181
select tests.clear_authentication();
_181
select results_eq(
_181
$$select count(*) from posts where org_id = (select id from organizations where slug = 'premium-org')$$,
_181
ARRAY[2::bigint],
_181
'Anonymous users can only see free posts'
_181
);
_181
_181
-- Time-Based Publishing Tests
_181
select tests.authenticate_as('scheduler');
_181
select tests.freeze_time('2024-01-01 12:00:00+00'::timestamptz);
_181
_181
select results_eq(
_181
$$select count(*) from posts where scheduled_for > now() and org_id = (select id from organizations where slug = 'schedule-org')$$,
_181
ARRAY[1::bigint],
_181
'Can see scheduled posts'
_181
);
_181
_181
select tests.freeze_time('2024-01-02 13:00:00+00'::timestamptz);
_181
_181
select results_eq(
_181
$$select count(*) from posts where scheduled_for < now() and org_id = (select id from organizations where slug = 'schedule-org')$$,
_181
ARRAY[1::bigint],
_181
'Can see posts after schedule time'
_181
);
_181
_181
select tests.unfreeze_time();
_181
_181
-- Plan Limit Tests
_181
select tests.authenticate_as('free_author');
_181
_181
select lives_ok(
_181
$$
_181
insert into posts (title, content, org_id, author_id, status)
_181
select 'Post 1', 'Content 1', id, auth.uid(), 'draft'
_181
from organizations where slug = 'free-org' limit 1
_181
$$,
_181
'First post creates successfully'
_181
);
_181
_181
select lives_ok(
_181
$$
_181
insert into posts (title, content, org_id, author_id, status)
_181
select 'Post 2', 'Content 2', id, auth.uid(), 'draft'
_181
from organizations where slug = 'free-org' limit 1
_181
$$,
_181
'Second post creates successfully'
_181
);
_181
_181
select throws_ok(
_181
$$
_181
insert into posts (title, content, org_id, author_id, status)
_181
select 'Post 3', 'Content 3', id, auth.uid(), 'draft'
_181
from organizations where slug = 'free-org' limit 1
_181
$$,
_181
'42501',
_181
'new row violates row-level security policy for table "posts"',
_181
'Cannot exceed free plan post limit'
_181
);
_181
_181
select * from finish();
_181
rollback;

Additional resources