--- id: 'local-development-testing-overview' title: 'Testing Overview' description: 'Learn how to develop and test database schemas, tables, functions, and Row Level Security (RLS) policies.' --- 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](https://pgtap.org) 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 basic todo application: 1. Create a test table with RLS enabled: ```sql -- Create a todos table create 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 RLS alter table todos enable row level security; -- Create a policy create policy "Users can only access their own todos" on todos for all -- this policy applies to all operations to authenticated using ((select auth.uid()) = user_id); ``` 2. Set up your testing environment: ```bash # Create a new test for our policies using supabase cli supabase test new todos_rls.test ``` 3. Write your RLS tests: ```sql begin; -- install tests utilities -- install pgtap extension for testing create extension if not exists pgtap with schema extensions; -- Start declare we'll have 4 test cases in our test suite select plan(4); -- Setup our testing data -- Set up auth.users entries insert into auth.users (id, email) values ('123e4567-e89b-12d3-a456-426614174000', 'user1@test.com'), ('987fcdeb-51a2-43d7-9012-345678901234', 'user2@test.com'); -- Create test todos insert 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 1 set local role authenticated; set local request.jwt.claim.sub = '123e4567-e89b-12d3-a456-426614174000'; -- Test 1: User 1 should only see their own todos select 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 todo select 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 2 set local request.jwt.claim.sub = '987fcdeb-51a2-43d7-9012-345678901234'; -- Test 3: User 2 should only see their own todos select 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 todo SELECT 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: ```bash supabase test db psql:todos_rls.test.sql:4: NOTICE: extension "pgtap" already exists, skipping ./todos_rls.test.sql .. ok All 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. Application-level tests should not rely on a clean database state, as resetting the database before each test can be slow and makes tests difficult to parallelize. Instead, design your tests to be independent by using unique user IDs for each test case. Here's an example using TypeScript that mirrors the pgTAP tests above: ```typescript import crypto from 'crypto' import { createClient } from '@supabase/supabase-js' import { beforeAll, describe, expect, it } from 'vitest' 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_PUBLISHABLE_KEY!) beforeAll(async () => { // Setup test data specific to this test suite const adminSupabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_SECRET_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`: ```yaml name: Database Tests on: 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](https://github.com/usebasejump/basejump/tree/main/supabase/tests/database) - A production-grade example of testing RLS policies - [RLS Guide and Best Practices](https://github.com/orgs/supabase/discussions/14576) ## 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](https://pgtap.org) - [Supabase CLI Reference](/docs/reference/cli/supabase-test) - [pgTAP Supabase reference](/docs/guides/database/extensions/pgtap?queryGroups=database-method&database-method=sql#testing-rls-policies) - [Database testing reference](/docs/guides/database/testing)