Hamster Database Testing
Vitest or pgTAP? Pick the Right Tool
| Use Vitest (TypeScript) |
Use pgTAP (SQL) |
| RLS policy enforcement (SELECT/INSERT/UPDATE/DELETE) |
Schema assertions (table/column/index exists) |
| Multi-user access scenarios (owner/member/outsider) |
Trigger logic verification |
| Constraint violations via Supabase client |
PL/pgSQL function behavior |
| Integration with TypeScript types |
RPC function testing (security invoker/definer) |
Tests that benefit from typed @kit/supabase/database |
Complex SQL query result verification |
Rule of thumb: If the test impersonates different users to verify access control, use Vitest. If the test checks SQL internals (triggers, functions, schema shape, RPC), use pgTAP.
Quick Reference
| Task |
Tool |
Reference |
| Test RLS policies (SELECT/INSERT/UPDATE/DELETE) |
Vitest |
vitest-rls-testing.md |
| Test FK/CHECK/UNIQUE constraints |
Vitest |
vitest-rls-testing.md |
| Test triggers, functions, schema shape |
pgTAP |
pgtap-testing.md |
| Test RPC functions under user contexts |
pgTAP |
pgtap-testing.md |
| pgTAP assertion reference |
pgTAP |
pgtap-assertion-patterns.md |
| Basejump test helper API |
pgTAP |
pgtap-basejump-api.md |
| Makerkit project helpers |
pgTAP |
pgtap-project-helpers.md |
Commands
# Vitest database tests (RLS, constraints)
pnpm --filter web test:db
# pgTAP database tests (triggers, functions, schema)
pnpm --filter web supabase:test
Prerequisites
- Local Supabase running:
pnpm supabase:web:start
- Database reset with latest schema:
pnpm supabase:web:reset
File Organization
apps/web/
├── test/database/ # Vitest tests
│ ├── setup.ts # Loads env, validates vars
│ ├── helpers/
│ │ ├── exec-sql.ts # Raw SQL execution via psql
│ │ ├── client.ts # Admin & authenticated client factories
│ │ ├── seed.ts # User, team, data seeding
│ │ └── cleanup.ts # Best-effort cleanup
│ ├── rls/
│ │ └── *.test.ts # RLS policy tests
│ └── constraints/
│ └── *.test.ts # FK, CHECK, unique constraint tests
├── supabase/tests/database/ # pgTAP tests
│ ├── 00000-makerkit-helpers.sql # Shared helpers (auto-loaded first)
│ └── *.test.sql # Test files
└── vitest.config.db.ts # Vitest config for database tests
Vitest Patterns (RLS & Constraints)
Test Structure
import type { SupabaseClient } from '@supabase/supabase-js';
import { afterAll, beforeAll, describe, expect, it } from 'vitest';
import type { Database } from '@kit/supabase/database';
import { cleanupTestData } from '../helpers/cleanup';
import { createAdminClient, createAuthenticatedClient } from '../helpers/client';
import { DEFAULT_PASSWORD, createTestUser, createTestTeam, getAccountIdBySlug } from '../helpers/seed';
const EMAIL_PREFIX = 'xxx-yyy'; // unique per file
const OWNER_EMAIL = `${EMAIL_PREFIX}-owner@test.com`;
let adminClient: SupabaseClient<Database>;
let ownerClient: SupabaseClient<Database>;
beforeAll(async () => {
adminClient = createAdminClient();
await createTestUser(adminClient, { email: OWNER_EMAIL });
ownerClient = await createAuthenticatedClient(OWNER_EMAIL, DEFAULT_PASSWORD);
// Create teams, seed data...
});
afterAll(() => {
cleanupTestData({ userEmails: [OWNER_EMAIL] });
});
RLS Assertions
// SELECT: owner sees data
const { data, error } = await ownerClient.from('table').select('*').eq('id', ID);
expect(error).toBeNull();
expect(data).toHaveLength(1);
// SELECT: outsider sees nothing (RLS filters silently)
const { data, error } = await outsiderClient.from('table').select('*').eq('id', ID);
expect(error).toBeNull();
expect(data).toHaveLength(0);
// INSERT: outsider blocked
const { error } = await outsiderClient.from('table').insert({ ... });
expect(error).not.toBeNull();
expect(error!.code).toBe('42501'); // insufficient_privilege
Constraint Assertions
expect(error!.code).toBe('23503'); // foreign_key_violation
expect(error!.code).toBe('23505'); // unique_violation
expect(error!.code).toBe('23514'); // check_violation
pgTAP Patterns (Triggers, Functions, Schema)
Test Structure
BEGIN;
create extension "basejump-supabase_test_helpers" version '0.0.6';
select no_plan();
select tests.create_supabase_user('owner1', 'owner1@test.com');
select makerkit.authenticate_as('owner1');
select public.create_team_account('TestTeam');
-- assertions...
select * from finish();
ROLLBACK;
Common Assertions
-- Schema exists
select has_table('public', 'my_table', 'table should exist');
select has_column('public', 'my_table', 'status', 'should have status column');
-- RLS is enabled
select tests.rls_enabled('public', 'my_table');
-- Trigger fires correctly
set local role postgres;
insert into public.my_table (...) values (...);
select is(
(select updated_at from public.my_table where id = '...'),
now(),
'Trigger should set updated_at'
);
-- Function returns expected result
select row_eq(
$$ SELECT public.my_function('arg') $$,
row('expected'::text),
'Function should return expected value'
);
Naming Conventions
- Vitest email prefix: Unique per test file (e.g.,
rls-arj-owner@test.com)
- Vitest UUIDs: Deterministic per file with distinct hex prefix (
a1000000-..., a2000000-...)
- Vitest team names: Unique per file (e.g.,
RlsArjTestTeam)
- pgTAP user identifiers: Short descriptive names (
owner1, member1, outsider1)
Postgres Error Codes
| Code |
Name |
Use |
23503 |
foreign_key_violation |
FK constraint fails |
23505 |
unique_violation |
Unique/PK constraint fails |
23514 |
check_violation |
CHECK constraint fails |
42501 |
insufficient_privilege |
RLS policy blocks access |
P0001 |
raise_exception |
Custom RAISE from function |
Troubleshooting
| Issue |
Solution |
| "relation does not exist" |
Run pnpm supabase:web:reset |
| Leftover data from previous run |
Run pnpm supabase:web:reset or re-run tests |
| Auth errors on createAuthenticatedClient |
Ensure user was created with email_confirm: true |
| Member can't see team data |
Re-create auth client after addTeamMember (JWT needs refresh) |
| Timeout on beforeAll |
Increase hookTimeout in vitest config or check Supabase is running |
| Direct insert works but RPC fails |
Check security invoker vs definer — see pgtap-testing.md |
Key Files
| File |
Purpose |
apps/web/vitest.config.db.ts |
Vitest config for database tests |
apps/web/test/database/setup.ts |
Vitest setup (env loading) |
apps/web/test/database/helpers/ |
Vitest seed/cleanup/client helpers |
apps/web/supabase/tests/database/ |
pgTAP test files |
.github/workflows/02-test.yml |
CI (database job) |