Database Testing for Self-Hosted Supabase: A Complete pgTAP Guide

Testing is a critical but often overlooked aspect of self-hosted Supabase deployments. While developers diligently write unit tests for their application code, database logic—especially Row Level Security (RLS) policies—frequently goes untested until something breaks in production.

If you're self-hosting Supabase, you have complete control over your testing infrastructure. This guide shows you how to leverage that control by implementing comprehensive database testing with pgTAP, PostgreSQL's native testing framework.

Why Database Testing Matters for Self-Hosted Supabase

When running Supabase on your own infrastructure, you're responsible for everything: schema changes, RLS policies, migrations, and ensuring they all work correctly together. Unlike managed Supabase Cloud, there's no safety net.

Consider these common failure scenarios:

• RLS policy bugs: A misconfigured policy might expose sensitive data or block legitimate access
• Migration regressions: Schema changes that inadvertently break existing functionality
• Performance degradation: Policies that work correctly but crush database performance at scale

The challenge is that RLS policies are notoriously difficult to debug. When something goes wrong, PostgreSQL logs offer little help—you'll see cryptic permission denied errors without context about which policy failed or why.

Automated testing catches these issues before they reach production.

Setting Up pgTAP for Self-Hosted Supabase

pgTAP is a PostgreSQL extension that implements the Test Anything Protocol (TAP) framework directly in your database. Since you're self-hosting, you have full control over enabling extensions.

Enabling the Extension

First, enable pgTAP in your Supabase instance:

-- Enable pgTAP extension
CREATE EXTENSION IF NOT EXISTS pgtap;

If you're using Supascale, you can enable extensions through the dashboard or include this in your initial migration files.

Creating Your Test Directory Structure

The Supabase CLI expects tests in a specific location:

supabase/
├── migrations/
│   └── 20260220000000_initial_schema.sql
├── tests/
│   └── database/
│       ├── 000-setup-tests.sql
│       ├── 001-rls-policies.sql
│       ├── 002-auth-functions.sql
│       └── 003-data-integrity.sql
└── seed.sql

Test files execute in alphabetical order, so prefix them with numbers to control execution sequence. The 000-setup-tests.sql file should initialize any test helpers or fixtures.

Installing Test Helpers

The Basejump test helpers simplify common testing patterns significantly:

-- 000-setup-tests.sql
-- Install test helpers for easier RLS testing

CREATE OR REPLACE FUNCTION tests.create_test_user(
  email text DEFAULT '[email protected]'
) RETURNS uuid AS $$
DECLARE
  user_id uuid;
BEGIN
  user_id := gen_random_uuid();
  
  INSERT INTO auth.users (
    id,
    email,
    encrypted_password,
    email_confirmed_at,
    raw_app_meta_data,
    raw_user_meta_data,
    created_at,
    updated_at
  ) VALUES (
    user_id,
    email,
    crypt('password123', gen_salt('bf')),
    now(),
    '{"provider":"email","providers":["email"]}',
    '{}',
    now(),
    now()
  );
  
  RETURN user_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tests.authenticate_as(user_id uuid)
RETURNS void AS $$
BEGIN
  PERFORM set_config('request.jwt.claim.sub', user_id::text, true);
  PERFORM set_config('request.jwt.claims', json_build_object(
    'sub', user_id::text,
    'role', 'authenticated'
  )::text, true);
  PERFORM set_config('role', 'authenticated', true);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tests.clear_authentication()
RETURNS void AS $$
BEGIN
  PERFORM set_config('request.jwt.claim.sub', '', true);
  PERFORM set_config('request.jwt.claims', '', true);
  PERFORM set_config('role', 'anon', true);
END;
$$ LANGUAGE plpgsql;

Testing RLS Policies

RLS policy testing is where database tests provide the most value. Let's walk through testing different policy types.

Testing SELECT Policies

SELECT policies filter which rows users can see. Test them by verifying users only see authorized data:

-- 001-rls-policies.sql
BEGIN;

SELECT plan(4);

-- Create test users
SELECT tests.create_test_user('[email protected]') AS alice_id \gset
SELECT tests.create_test_user('[email protected]') AS bob_id \gset

-- Insert test data
INSERT INTO public.posts (id, user_id, title, content)
VALUES 
  (gen_random_uuid(), :'alice_id', 'Alice Post', 'Alice content'),
  (gen_random_uuid(), :'bob_id', 'Bob Post', 'Bob content');

-- Test 1: Unauthenticated users see nothing
SELECT tests.clear_authentication();
SELECT is(
  (SELECT count(*) FROM public.posts),
  0::bigint,
  'Anonymous users cannot see any posts'
);

-- Test 2: Alice sees only her posts
SELECT tests.authenticate_as(:'alice_id'::uuid);
SELECT is(
  (SELECT count(*) FROM public.posts),
  1::bigint,
  'Alice sees only her own posts'
);

-- Test 3: Verify correct post is visible
SELECT is(
  (SELECT title FROM public.posts LIMIT 1),
  'Alice Post',
  'Alice sees her post specifically'
);

-- Test 4: Bob sees only his posts
SELECT tests.authenticate_as(:'bob_id'::uuid);
SELECT is(
  (SELECT title FROM public.posts LIMIT 1),
  'Bob Post',
  'Bob sees only his post'
);

SELECT * FROM finish();
ROLLBACK;

Testing INSERT Policies

INSERT policies throw errors when blocked, making them straightforward to test:

-- Test INSERT policy blocks unauthorized inserts
SELECT tests.authenticate_as(:'alice_id'::uuid);

SELECT throws_ok(
  $$
    INSERT INTO public.team_posts (team_id, title, content)
    VALUES ('other-team-id', 'Unauthorized Post', 'Should fail')
  $$,
  'new row violates row-level security policy for table "team_posts"',
  'Users cannot insert posts for teams they do not belong to'
);

Testing UPDATE Policies

UPDATE policies are tricky—blocked updates don't throw errors. Instead, they silently affect zero rows:

-- Test UPDATE policy prevents unauthorized modifications
SELECT tests.authenticate_as(:'alice_id'::uuid);

SELECT is_empty(
  $$
    UPDATE public.posts 
    SET title = 'Hacked Title'
    WHERE user_id = '$$|| :'bob_id' ||$$'
    RETURNING id
  $$,
  'Alice cannot update Bob posts'
);

Verifying RLS is Enabled on All Tables

A critical safety check ensures every table has RLS enabled:

-- Verify RLS enabled on all public tables
SELECT is(
  (SELECT count(*) 
   FROM pg_tables t
   LEFT JOIN pg_class c ON t.tablename = c.relname
   WHERE t.schemaname = 'public'
   AND NOT c.relrowsecurity),
  0::bigint,
  'All public tables have RLS enabled'
);

Testing Schema and Constraints

Beyond RLS, test your schema constraints and triggers:

-- 002-schema-tests.sql
BEGIN;

SELECT plan(3);

-- Test NOT NULL constraint
SELECT throws_ok(
  $$INSERT INTO public.users (email) VALUES (NULL)$$,
  23502,  -- PostgreSQL error code for NOT NULL violation
  'Email cannot be null'
);

-- Test unique constraint
INSERT INTO public.users (id, email) 
VALUES (gen_random_uuid(), '[email protected]');

SELECT throws_ok(
  $$INSERT INTO public.users (id, email) 
    VALUES (gen_random_uuid(), '[email protected]')$$,
  23505,  -- PostgreSQL error code for unique violation
  'Email must be unique'
);

-- Test foreign key constraint
SELECT throws_ok(
  $$INSERT INTO public.posts (user_id, title) 
    VALUES ('00000000-0000-0000-0000-000000000000', 'Orphan Post')$$,
  23503,  -- PostgreSQL error code for FK violation
  'Posts must reference existing users'
);

SELECT * FROM finish();
ROLLBACK;

Integrating Tests into Your CI/CD Pipeline

For self-hosted Supabase, you can run tests locally or integrate them into your deployment pipeline. If you're already using CI/CD pipelines for self-hosted Supabase, adding database tests is straightforward.

Running Tests Locally

With the Supabase CLI installed:

# Start local Supabase stack
supabase start

# Run all database tests
supabase test db

GitHub Actions Integration

Create .github/workflows/database-tests.yml:

name: Database Tests

on:
  pull_request:
    paths:
      - 'supabase/migrations/**'
      - 'supabase/tests/**'
  push:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - uses: supabase/setup-cli@v1
        with:
          version: latest
      
      - name: Start Supabase
        run: supabase start
      
      - name: Run migrations
        run: supabase db reset
      
      - name: Run tests
        run: supabase test db
      
      - name: Stop Supabase
        if: always()
        run: supabase stop

This workflow runs tests on every pull request that modifies database migrations or tests.

Testing Against Your Self-Hosted Instance

For staging environment testing, you can run tests against your actual self-hosted Supabase instance:

# Connect to remote database
export DATABASE_URL="postgres://postgres:your-password@your-server:5432/postgres"

# Run tests
psql $DATABASE_URL -f supabase/tests/database/001-rls-policies.sql

Be cautious with this approach—use a dedicated staging instance, not production.

RLS Performance Testing

A policy that works correctly but slowly is still a problem. Test performance alongside correctness:

-- Performance test for RLS policy
SELECT is(
  (SELECT count(*) FROM (
    EXPLAIN ANALYZE
    SELECT * FROM public.posts
    WHERE (SELECT auth.uid()) = user_id
  ) AS explain_output
  WHERE explain_output::text LIKE '%Seq Scan%'),
  0::bigint,
  'Posts query uses index, not sequential scan'
);

For optimal RLS performance, remember these patterns:

-- Slow: function called per row
CREATE POLICY "users_own_data" ON posts
  FOR SELECT USING (auth.uid() = user_id);

-- Fast: function result cached via subquery
CREATE POLICY "users_own_data" ON posts
  FOR SELECT USING ((SELECT auth.uid()) = user_id);

The subquery wrapping creates an "initPlan" that PostgreSQL's optimizer can cache, avoiding repeated function calls.

Managing Test Data

Using Seed Files

Keep test fixtures in your seed file for consistent test environments:

-- supabase/seed.sql
-- Test users
INSERT INTO auth.users (id, email, encrypted_password, email_confirmed_at)
VALUES 
  ('11111111-1111-1111-1111-111111111111', '[email protected]', 
   crypt('password', gen_salt('bf')), now()),
  ('22222222-2222-2222-2222-222222222222', '[email protected]',
   crypt('password', gen_salt('bf')), now());

-- Test data
INSERT INTO public.organizations (id, name, owner_id)
VALUES ('org-1', 'Test Org', '11111111-1111-1111-1111-111111111111');

Transaction Rollbacks

Each test file should wrap tests in a transaction and rollback:

BEGIN;
-- All test operations here
SELECT * FROM finish();
ROLLBACK;

This ensures tests don't pollute the database state, making them repeatable and parallelizable.

Debugging Failed Tests

When tests fail, debugging RLS issues can be challenging. Here are some strategies:

Check Current User Context

-- Debug current authentication state
SELECT 
  current_user,
  current_setting('role', true) as role,
  current_setting('request.jwt.claim.sub', true) as jwt_sub;

Examine Policy Definitions

-- List all RLS policies on a table
SELECT 
  polname as policy_name,
  polcmd as command,
  pg_get_expr(polqual, polrelid) as using_expression,
  pg_get_expr(polwithcheck, polrelid) as with_check
FROM pg_policy
WHERE polrelid = 'public.posts'::regclass;

Use EXPLAIN ANALYZE

-- See how RLS affects query execution
EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM public.posts;

Simplifying Test Management with Supascale

Writing and maintaining database tests is essential, but managing the infrastructure for running them shouldn't be a burden. Supascale simplifies self-hosted Supabase operations, letting you focus on building and testing your application.

With Supascale's one-click backup and restore, you can easily create test environments from production snapshots. Need to test migrations against real data? Restore a backup to a staging instance, run your tests, and tear it down—all through the dashboard or REST API.

Conclusion

Database testing for self-hosted Supabase isn't optional—it's essential for maintaining security and reliability. By implementing pgTAP tests for your RLS policies, schema constraints, and database functions, you catch bugs before they reach production.

Start with RLS policy tests, as they provide the highest value for security-critical applications. Then expand to schema validation and performance testing as your test suite matures.

The investment in testing pays dividends every time you confidently deploy a migration, knowing your RLS policies still protect your users' data.

Ready to simplify your self-hosted Supabase operations? Check out Supascale's features or get started today.

Further Reading

• CI/CD Pipelines for Self-Hosted Supabase
• Securing Self-Hosted Supabase: A Production Hardening Guide
• Supabase Backups Documentation
• Environment Variables Guide