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:

   sqlCopy

   -- Create a simple 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:

   bashCopy

   # Create a new test for our policies using supabase cli
   supabase test new todos_rls.test
   

3. Write your RLS tests:

   sqlCopy

   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', '[email protected]'),
   	('987fcdeb-51a2-43d7-9012-345678901234', '[email protected]');
   
   -- 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:

   bashCopy

   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:

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

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