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 Postgres 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:

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   -- Create a simple todos tablecreate table todos (id uuid primary key default gen_random_uuid(),task text not null,user_id uuid references auth.users not null,completed boolean default false);-- Enable RLSalter table todos enable row level security;-- Create a policycreate policy "Users can only access their own todos"on todos for all -- this policy applies to all operationsto authenticatedusing ((select auth.uid()) = user_id);

2. Set up your testing environment:

   1
   2
   # Create a new test for our policies using supabase clisupabase test new todos_rls.test

3. Write your RLS tests:

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36
   37
   38
   39
   40
   41
   42
   43
   44
   45
   46
   47
   48
   49
   50
   51
   52
   53
   54
   55
   begin;-- install tests utilities-- install pgtap extension for testingcreate extension if not exists pgtap with schema extensions;-- Start declare we'll have 4 test cases in our test suiteselect plan(4);-- Setup our testing data-- Set up auth.users entriesinsert into auth.users (id, email) values	('123e4567-e89b-12d3-a456-426614174000', 'user1@test.com'),	('987fcdeb-51a2-43d7-9012-345678901234', 'user2@test.com');-- Create test todosinsert into public.todos (task, user_id) values	('User 1 Task 1', '123e4567-e89b-12d3-a456-426614174000'),	('User 1 Task 2', '123e4567-e89b-12d3-a456-426614174000'),	('User 2 Task 1', '987fcdeb-51a2-43d7-9012-345678901234');-- as User 1set local role authenticated;set local request.jwt.claim.sub = '123e4567-e89b-12d3-a456-426614174000';-- Test 1: User 1 should only see their own todosselect results_eq(	'select count(*) from todos',	ARRAY[2::bigint],	'User 1 should only see their 2 todos');-- Test 2: User 1 can create their own todoselect lives_ok(	$$insert into todos (task, user_id) values ('New Task', '123e4567-e89b-12d3-a456-426614174000'::uuid)$$,	'User 1 can create their own todo');-- as User 2set local request.jwt.claim.sub = '987fcdeb-51a2-43d7-9012-345678901234';-- Test 3: User 2 should only see their own todosselect results_eq(	'select count(*) from todos',	ARRAY[1::bigint],	'User 2 should only see their 1 todo');-- Test 4: User 2 cannot modify User 1's todoSELECT results_ne(	$$ update todos set task = 'Hacked!' where user_id = '123e4567-e89b-12d3-a456-426614174000'::uuid returning 1 $$,	$$ values(1) $$,	'User 2 cannot modify User 1 todos');select * from finish();rollback;

4. Run the tests:

   1
   2
   3
   4
   5
   6
   supabase test dbpsql:todos_rls.test.sql:4: NOTICE:  extension "pgtap" already exists, skipping./todos_rls.test.sql .. okAll tests successful.Files=1, Tests=6,  0 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
import { createClient } from '@supabase/supabase-js'import { beforeAll, describe, expect, it } from 'vitest'import crypto from 'crypto'describe('Todos RLS', () => {  // Generate unique IDs for this test suite to avoid conflicts with other tests  const USER_1_ID = crypto.randomUUID()  const USER_2_ID = crypto.randomUUID()  const supabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_ANON_KEY!)  beforeAll(async () => {    // Setup test data specific to this test suite    const adminSupabase = createClient(process.env.SUPABASE_URL!, process.env.SERVICE_ROLE_KEY!)    // Create test users with unique IDs    await adminSupabase.auth.admin.createUser({      id: USER_1_ID,      email: `user1-${USER_1_ID}@test.com`,      password: 'password123',      // We want the user to be usable right away without email confirmation      email_confirm: true,    })    await adminSupabase.auth.admin.createUser({      id: USER_2_ID,      email: `user2-${USER_2_ID}@test.com`,      password: 'password123',      email_confirm: true,    })    // Create initial todos    await adminSupabase.from('todos').insert([      { task: 'User 1 Task 1', user_id: USER_1_ID },      { task: 'User 1 Task 2', user_id: USER_1_ID },      { task: 'User 2 Task 1', user_id: USER_2_ID },    ])  })  it('should allow User 1 to only see their own todos', async () => {    // Sign in as User 1    await supabase.auth.signInWithPassword({      email: `user1-${USER_1_ID}@test.com`,      password: 'password123',    })    const { data: todos } = await supabase.from('todos').select('*')    expect(todos).toHaveLength(2)    todos?.forEach((todo) => {      expect(todo.user_id).toBe(USER_1_ID)    })  })  it('should allow User 1 to create their own todo', async () => {    await supabase.auth.signInWithPassword({      email: `user1-${USER_1_ID}@test.com`,      password: 'password123',    })    const { error } = await supabase.from('todos').insert({ task: 'New Task', user_id: USER_1_ID })    expect(error).toBeNull()  })  it('should allow User 2 to only see their own todos', async () => {    // Sign in as User 2    await supabase.auth.signInWithPassword({      email: `user2-${USER_2_ID}@test.com`,      password: 'password123',    })    const { data: todos } = await supabase.from('todos').select('*')    expect(todos).toHaveLength(1)    todos?.forEach((todo) => {      expect(todo.user_id).toBe(USER_2_ID)    })  })  it('should prevent User 2 from modifying User 1 todos', async () => {    await supabase.auth.signInWithPassword({      email: `user2-${USER_2_ID}@test.com`,      password: 'password123',    })    // Attempt to update the todos we shouldn't have access to    // result will be a no-op    await supabase.from('todos').update({ task: 'Hacked!' }).eq('user_id', USER_1_ID)    // Log back in as User 1 to verify their todos weren't changed    await supabase.auth.signInWithPassword({      email: `user1-${USER_1_ID}@test.com`,      password: 'password123',    })    // Fetch User 1's todos    const { data: todos } = await supabase.from('todos').select('*')    // Verify that none of the todos were changed to "Hacked!"    expect(todos).toBeDefined()    todos?.forEach((todo) => {      expect(todo.task).not.toBe('Hacked!')    })  })})

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
name: Database Testson:  push:    branches: [main]  pull_request:    branches: [main]jobs:  test:    runs-on: ubuntu-latest    steps:      - uses: actions/checkout@v4      - name: Setup Supabase CLI        uses: supabase/setup-cli@v1      - name: Start Supabase        run: supabase start      - name: Run Tests        run: supabase test db

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