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 Zuvo 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 basic todo application:

1. Create a test table with RLS enabled:

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

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

3. Write your RLS tests:

   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:

   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.

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

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 adminZuvo = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_SECRET_KEY!)

    // Create test users with unique IDs
    await adminZuvo.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 adminZuvo.auth.admin.createUser({
      id: USER_2_ID,
      email: `user2-${USER_2_ID}@test.com`,
      password: 'password123',
      email_confirm: true,
    })

    // Create initial todos
    await adminZuvo.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 Zuvo CLI
        uses: supabase/setup-cli@v1

      - name: Start Zuvo
        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 Zuvo CLI is properly installed
   • Ensure database migrations are run before tests
   • Check for proper test isolation using transactions

Additional resources

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