Testing database logic has always been the hard part. You need real Postgres—not mocks, not simulators—because you're testing constraints, triggers, Row-Level Security policies, and complex SQL logic that only works in an actual database.
But here's the problem: the tools don't match how developers actually work today.
The Flow State Problem
TypeScript has become the lingua franca of modern development. You write your application logic, hit save, and see test results in under a second. You're in flow state—that magical zone where ideas turn into working code effortlessly.
Then you need to test your database. The flow breaks.
pgTap requires learning plpgsql and writing stored procedures. Test databases need manual setup and cleanup. Shared state between tests means unpredictable failures. Slow feedback loops kill momentum. The distance between idea → execution stretches from seconds to minutes—or worse, hours of configuration.
Most developers just skip database testing entirely. They deploy to production and hope for the best, or click through their UI like QA testers instead of writing automated tests. That's not testing. That's crossing your fingers.
Introducing pgsql-test
pgsql-test is a TypeScript-native testing framework for Postgres that makes database testing feel joyful again.
It creates ephemeral Postgres databases for your tests, provides transaction-based isolation between test cases, and gives you the fast feedback loops you expect from modern development tools. Write tests in the same language as your application, get instant feedback with watch mode, and have each test run in complete isolation without manual cleanup.
Write real SQL. Get real coverage. Stay fast. No mocks, no simulators—just real Postgres running in a local-first development environment that's also perfect for CI/CD.
What Makes It Powerful
Zero Configuration - Every test suite gets its own temporary Postgres database that's automatically created and destroyed. No shared state. No manual cleanup. No conflicts between test runs. Call getConnections() and you're ready to test.
Transaction Isolation - Each test runs in its own transaction with automatic rollback. Your schema persists, but data changes roll back automatically. Tests are completely isolated with zero manual cleanup—and dramatically faster than recreating databases.
Powerful RLS Testing - The dual-client pattern gives you a superuser client for setup and a user client for testing with authentication roles. Switch between user contexts with setContext(), verify multi-tenant scenarios, and ensure your security policies actually protect your data.
Instant Feedback Loops - Watch mode re-runs tests automatically when you save changes. Response time from code change to test result is typically under 1 second. This tight feedback loop transforms how you develop database features—you stay in flow instead of context-switching to manual QA.
Multiple Seeding Strategies - Seed your database the way you want. Use SQL fixtures, TypeScript/JavaScript seed scripts, CSV for large datasets, JSON for inline data, or full database modules powered by our TypeScript-native SQL migration engine, pgpm (pure SQL, no ORM — inspired by Sqitch). Mix and match to build rich, realistic end-to-end test scenarios.
Test-Framework Agnostic - Works with Jest, Mocha, Vitest, or any other test runner. No lock-in, no special configuration.
CI/CD Optimized - Dramatically reduced test suite run times with optimized deployment. Lightweight, deterministic, and perfect for GitHub Actions or any CI pipeline.
Real-World Impact
When testing RLS policies is as simple as writing a TypeScript test, teams actually test their security policies. When feedback is instant, developers iterate faster and catch bugs earlier. When tests are isolated and automatic, test suites become reliable and maintainable.
This is especially powerful for teams building multi-tenant applications with Supabase or Postgres. Row-Level Security is critical for data isolation, but it's notoriously difficult to test. pgsql-test makes RLS testing straightforward.
The combination of these features creates a development environment where secure patterns naturally emerge and best practices become easier to adopt. When the right thing is the easy thing, developers build better software.
Built for Extensibility
pgsql-test is low-level enough to adapt to many Postgres projects and workflows. We've already built specialized integrations for Supabase and Drizzle ORM, making it easy to test with your preferred stack.
Interested in other integrations? We'd love to hear about your use case. Please file an issue on GitHub and let us know what you're building.
The Vision: Database Testing as a First-Class Citizen
Postgres is the most expressive, most powerful, most under-leveraged platform in engineering. But the testing tools haven't kept pace with how developers actually build applications today. pgsql-test bridges that gap, bringing the fast feedback loops and developer joy of modern TypeScript development to database testing.
When database testing feels as natural as testing your frontend code, you write more tests. When you write more tests, you catch bugs earlier. When you catch bugs earlier, you ship with confidence.
That's not just better tooling. That's making building modern software feel joyful again.
Get Started
Start building more reliable Postgres applications:
-
📦 pgsql-test on npm — Install and start testing your database logic
-
📖 End-to-End Postgres Testing Course — Comprehensive guides covering ephemeral databases, RLS testing, multi-user scenarios, and data seeding
-
🚀 pgpm-modules Test Suite — Complete working example with CI/CD, showing real-world testing patterns across multiple modules
-
🔧 Supabase Testing Course — Specialized guides for testing Supabase applications with
supabase-test
The future of database testing is here—and it speaks TypeScript.