Testing Supabase Applications

// __mocks__/supabase.ts
export const mockSupabase = {
  from: jest.fn(() => mockSupabase),
  select: jest.fn(() => mockSupabase),
  insert: jest.fn(() => mockSupabase),
  update: jest.fn(() => mockSupabase),
  delete: jest.fn(() => mockSupabase),
  eq: jest.fn(() => mockSupabase),
  single: jest.fn(() => mockSupabase),
  order: jest.fn(() => mockSupabase),
  limit: jest.fn(() => mockSupabase),
  then: jest.fn(),
  auth: {
    getUser: jest.fn(),
    signInWithPassword: jest.fn(),
  },
}

// posts.test.ts
import { mockSupabase } from './__mocks__/supabase'
import { getPosts } from './posts'

jest.mock('@/lib/supabase', () => ({
  supabase: mockSupabase,
}))

describe('getPosts', () => {
  it('fetches published posts ordered by date', async () => {
    const mockPosts = [
      { id: '1', title: 'First Post', status: 'published' },
      { id: '2', title: 'Second Post', status: 'published' },
    ]

    mockSupabase.limit.mockResolvedValueOnce({
      data: mockPosts,
      error: null,
    })

    const result = await getPosts()

    expect(mockSupabase.from).toHaveBeenCalledWith('posts')
    expect(mockSupabase.eq).toHaveBeenCalledWith('status', 'published')
    expect(result).toEqual(mockPosts)
  })
})

// test/setup.ts — runs before all tests
import { createClient } from '@supabase/supabase-js'

// Use local Supabase instance
const supabase = createClient(
  'http://127.0.0.1:54321',
  'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...' // local anon key
)

// Use service role for test setup (bypasses RLS)
const adminClient = createClient(
  'http://127.0.0.1:54321',
  'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...' // local service_role key
)

beforeEach(async () => {
  // Clean up test data before each test
  await adminClient.from('posts').delete().neq('id', '')
  await adminClient.from('profiles').delete().neq('id', '')
})

describe('Posts with RLS', () => {
  it('users can only see their own draft posts', async () => {
    // Create a test user and sign in
    const { data: { user } } = await supabase.auth.signUp({
      email: 'test@example.com',
      password: 'testpassword123',
    })

    // Insert posts as admin (bypasses RLS)
    await adminClient.from('posts').insert([
      { title: 'My Draft', user_id: user.id, status: 'draft' },
      { title: 'Other Draft', user_id: 'other-user-id', status: 'draft' },
    ])

    // Query as the authenticated user
    const { data } = await supabase
      .from('posts')
      .select('*')
      .eq('status', 'draft')

    expect(data).toHaveLength(1)
    expect(data[0].title).toBe('My Draft')
  })
})

-- supabase/tests/rls_tests.sql
BEGIN;
SELECT plan(3);

-- Test 1: Anon users cannot access posts
SET role anon;
SELECT is_empty(
  $$ SELECT * FROM posts $$,
  'Anonymous users cannot see any posts'
);

-- Test 2: Authenticated users can see published posts
SET role authenticated;
SET request.jwt.claims = '{"sub": "user-1"}';
SELECT results_eq(
  $$ SELECT count(*)::int FROM posts WHERE status = 'published' $$,
  $$ VALUES (2) $$,
  'Authenticated users can see published posts'
);

-- Test 3: Users can only update their own posts
SET request.jwt.claims = '{"sub": "user-1"}';
SELECT throws_ok(
  $$ UPDATE posts SET title = 'Hacked' WHERE user_id = 'user-2' $$,
  'new row violates row-level security policy',
  'Users cannot update other users posts'
);

SELECT * FROM finish();
ROLLBACK;

# .github/workflows/test.yml
name: Test
on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: supabase/setup-cli@v1
        with:
          version: latest

      # Start local Supabase
      - run: supabase start

      # Run database tests (pgTAP)
      - run: supabase test db

      # Run application tests
      - uses: actions/setup-node@v4
        with:
          node-version: 20
      - run: npm ci
      - run: npm test

      # Stop Supabase
      - run: supabase stop