Constructive Testing

All database testing frameworks for Constructive. Each framework builds on pgsql-test underneath — they all create isolated test databases with proper teardown.

When to Apply

Use this skill when:

• Writing PostgreSQL integration tests
• Testing RLS policies, permissions, multi-tenant security
• Seeding test data (fixtures, JSON, SQL, CSV)
• Testing with Drizzle ORM or Supabase
• Working in the pgsql-parser repository
• Choosing which test framework to use

Which Framework to Use

Scenario	Framework	Reference
Raw SQL, RLS policies, database functions	pgsql-test	pgsql-test.md
PostGraphile schema, basic GraphQL queries	graphile-test	(part of constructive monorepo)
GraphQL with Constructive plugins (search, pgvector, etc.)	@constructive-io/graphql-test	(part of constructive monorepo)
Dynamic codegen + ORM in tests	@constructive-io/graphql-test (runCodegenAndLoad)	(part of constructive monorepo)
HTTP endpoints, auth headers, middleware	@constructive-io/graphql-server-test	(part of constructive monorepo)
Type-safe Drizzle ORM tests	drizzle-orm-test	drizzle-orm-test.md
Supabase applications, auth.users	supabase-test	supabase-test.md
pgsql-parser repo specifically	pgsql-parser workflow	pgsql-parser-testing.md

Quick Start (pgsql-test)

import { getConnections } from 'pgsql-test';

let db, teardown;
beforeAll(async () => ({ db, teardown } = await getConnections()));
afterAll(() => teardown());
beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());

test('example', async () => {
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': '123' });
  const result = await db.query('SELECT current_user_id()');
  expect(result.rows[0].current_user_id).toBe('123');
});

The Testing Framework Hierarchy

Choose the highest-level framework that fits your test scenario:

┌─────────────────────────────────────────────────────┐
│  @constructive-io/graphql-server-test               │  HTTP-level
│  SuperTest against real Express + PostGraphile       │  (full stack)
├─────────────────────────────────────────────────────┤
│  @constructive-io/graphql-test                      │  GraphQL + Constructive
│  GraphQL queries with all Constructive plugins      │  plugins loaded
├─────────────────────────────────────────────────────┤
│  graphile-test                                      │  GraphQL schema-level
│  GraphQL queries against PostGraphile schema        │  (no HTTP)
├─────────────────────────────────────────────────────┤
│  pgsql-test                                         │  SQL-level
│  Raw SQL queries, RLS, seeding, snapshots           │  (database only)
└─────────────────────────────────────────────────────┘

Critical Rules

1. Always include beforeEach/afterEach hooks — savepoint-based isolation prevents test state leakage
2. Never create new pg.Pool() or new pg.Client() in tests — use getConnections()
3. Never manually create/drop databases — the framework handles this
4. Never skip hooks — tests will leak state

Reference Guide

Core Test Framework (pgsql-test)

Reference	Topic	Consult When
pgsql-test.md	pgsql-test overview and setup	Getting started with PostgreSQL testing
pgsql-test-rls.md	RLS policy testing	Testing row-level security, user isolation
pgsql-test-seeding.md	Test data seeding	loadJson, loadSql, loadCsv fixtures
pgsql-test-exceptions.md	Exception handling	Testing operations that should fail
pgsql-test-snapshot.md	Snapshot testing	pruneIds, pruneDates, deterministic assertions
pgsql-test-helpers.md	Helper utilities	Common test helper functions
pgsql-test-jwt-context.md	JWT context testing	Setting JWT claims, testing authenticated queries
pgsql-test-scenario-setup.md	Complex scenario setup	Multi-client scenarios, complex test arrangements

Additional Frameworks

Reference	Topic	Consult When
drizzle-orm-test.md	Drizzle ORM testing	Type-safe database tests with Drizzle
drizzle-orm.md	Drizzle ORM schema patterns	Schema design, query building with Drizzle
supabase-test.md	Supabase testing	Testing Supabase apps, auth.users, anon/authenticated roles
pgsql-parser-testing.md	pgsql-parser repo testing	SQL parser/deparser tests, round-trip validation

Cross-References

• pgpm — Database migrations (deploy before testing)
• constructive — Platform core, environment configuration