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. It tests database structure, Row Level Security (RLS) policies, functions and procedures, and data integrity.

For more information about writing and running pgTAP tests for an RLS policy, see Test Row Level Security policies with pgTAP.

Application-Level testing#

Application-level tests check your policies end to end, through the same client your app uses.

For more information about testing RLS policies from TypeScript, see Test the policy from your application.

Continuous integration testing#

Set up automated database testing in your CI pipeline:

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

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

Best practices#

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

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
CI Pipeline Issues
- Verify Supabase CLI is properly installed
- Ensure database migrations are run before tests
- Check for proper test isolation using transactions