Testing Overview

Testing is a critical part of database development, especially when working with features like Row Level Security (RLS) policies. This guide provides a comprehensive approach to testing your Supabase database.

Testing approaches#

Database unit testing with pgTAP#

pgTAP is a unit testing framework for PostgreSQL that allows testing:

• Database structure: tables, columns, constraints
• Row Level Security (RLS) policies
• Functions and procedures
• Data integrity

This example demonstrates setting up and testing RLS policies for a simple todo application:

1. Create a test table with RLS enabled:

   
   

   _16

   -- Create a simple todos table

   _16

   create table todos (

   _16

   id uuid primary key default gen_random_uuid(),

   _16

   task text not null,

   _16

   user_id uuid references auth.users not null,

   _16

   completed boolean default false

   _16

   );

   _16

   _16

   -- Enable RLS

   _16

   alter table todos enable row level security;

   _16

   _16

   -- Create a policy

   _16

   create policy "Users can only access their own todos"

   _16

   on todos for all -- this policy applies to all operations

   _16

   to authenticated

   _16

   using ((select auth.uid()) = user_id);

   
   

2. Set up your testing environment:

   
   

   _10

   # Create a new test for our policies using supabase cli

   _10

   supabase test new todos_rls.test

   
   

3. Write your RLS tests:

   
   

   _55

   begin;

   _55

   -- install tests utilities

   _55

   -- install pgtap extension for testing

   _55

   create extension if not exists pgtap with schema extensions;

   _55

   -- Start declare we'll have 6 test cases in our test suite

   _55

   select plan(4);

   _55

   _55

   -- Setup our testing data

   _55

   -- Set up auth.users entries

   _55

   insert into auth.users (id, email) values

   _55

   ('123e4567-e89b-12d3-a456-426614174000', 'user1@test.com'),

   _55

   ('987fcdeb-51a2-43d7-9012-345678901234', 'user2@test.com');

   _55

   _55

   -- Create test todos

   _55

   insert into public.todos (task, user_id) values

   _55

   ('User 1 Task 1', '123e4567-e89b-12d3-a456-426614174000'),

   _55

   ('User 1 Task 2', '123e4567-e89b-12d3-a456-426614174000'),

   _55

   ('User 2 Task 1', '987fcdeb-51a2-43d7-9012-345678901234');

   _55

   _55

   -- as User 1

   _55

   set local role authenticated;

   _55

   set local request.jwt.claim.sub = '123e4567-e89b-12d3-a456-426614174000';

   _55

   _55

   -- Test 1: User 1 should only see their own todos

   _55

   select results_eq(

   _55

   'select count(*) from todos',

   _55

   ARRAY[2::bigint],

   _55

   'User 1 should only see their 2 todos'

   _55

   );

   _55

   _55

   -- Test 2: User 1 can create their own todo

   _55

   select lives_ok(

   _55

   $$insert into todos (task, user_id) values ('New Task', '123e4567-e89b-12d3-a456-426614174000'::uuid)$$,

   _55

   'User 1 can create their own todo'

   _55

   );

   _55

   _55

   -- as User 2

   _55

   set local request.jwt.claim.sub = '987fcdeb-51a2-43d7-9012-345678901234';

   _55

   _55

   -- Test 3: User 2 should only see their own todos

   _55

   select results_eq(

   _55

   'select count(*) from todos',

   _55

   ARRAY[1::bigint],

   _55

   'User 2 should only see their 1 todo'

   _55

   );

   _55

   _55

   -- Test 4: User 2 cannot modify User 1's todo

   _55

   SELECT results_ne(

   _55

   $$ update todos set task = 'Hacked!' where user_id = '123e4567-e89b-12d3-a456-426614174000'::uuid returning 1 $$,

   _55

   $$ values(1) $$,

   _55

   'User 2 cannot modify User 1 todos'

   _55

   );

   _55

   _55

   select * from finish();

   _55

   rollback;

   
   

4. Run the tests:

   
   

   _10

   supabase test db

   _10

   psql:todos_rls.test.sql:4: NOTICE: extension "pgtap" already exists, skipping

   _10

   ./todos_rls.test.sql .. ok

   _10

   All tests successful.

   _10

   Files=1, Tests=6, 0 wallclock secs ( 0.01 usr + 0.00 sys = 0.01 CPU)

   _10

   Result: PASS

   
   

Application-Level testing#

Testing through application code provides end-to-end verification. Unlike database-level testing with pgTAP, application-level tests cannot use transactions for isolation.

Here's an example using TypeScript that mirrors the pgTAP tests above:

Test isolation strategies#

For application-level testing, consider these approaches for test isolation:

1. Unique Identifiers: Generate unique IDs for each test suite to prevent data conflicts
2. Cleanup After Tests: If necessary, clean up created data in an afterAll or afterEach hook
3. Isolated Data Sets: Use prefixes or namespaces in data to separate test cases

Continuous integration testing#

Set up automated database testing in your CI pipeline:

1. Create a GitHub Actions workflow .github/workflows/db-tests.yml:

Best practices#

1. Test Data Setup

   • Use begin and rollback to ensure test isolation
   • Create realistic test data that covers edge cases
   • Use different user roles and permissions in tests

2. RLS Policy Testing

   • Test Create, Read, Update, Delete operations
   • Test with different user roles: anonymous and authenticated
   • Test edge cases and potential security bypasses
   • Always test negative cases: what users should not be able to do

3. CI/CD Integration

   • Run tests automatically on every pull request
   • Include database tests in deployment pipeline
   • Keep test runs fast using transactions

Real-World examples#

For more complex, real-world examples of database testing, check out:

• Database Tests Example Repository - A production-grade example of testing RLS policies
• RLS Guide and Best Practices

Troubleshooting#

Common issues and solutions:

1. Test Failures Due to RLS

   • Ensure you've set the correct role set local role authenticated;
   • Verify JWT claims are set set local "request.jwt.claims"
   • Check policy definitions match your test assumptions

2. CI Pipeline Issues

   • Verify Supabase CLI is properly installed
   • Ensure database migrations are run before tests
   • Check for proper test isolation using transactions

Additional resources#

• pgTAP Documentation
• Supabase CLI Reference
• pgTAP Supabase reference
• Database testing reference