PostgreSQL Unit Testing with pgTAP

Learn how to implement comprehensive database testing in Supabase using pgTAP for robust PostgreSQL unit testing.

What is pgTAP?

pgTAP is a unit testing framework for PostgreSQL that provides a comprehensive set of functions for testing database objects, including tables, functions, triggers, views, and more. It follows the TAP (Test Anything Protocol) standard, making it compatible with various test runners and CI/CD systems.

Why Do We Need Database Testing?

Database testing is often overlooked in modern application development, but it's crucial for ensuring data integrity, business logic correctness, and system reliability. While application-level tests are important in classic software development, they're not applicable in the context of Supabase.

Benefits of Using pgTAP

  • Database-level testing ensures data integrity and constraint validation
  • Comprehensive coverage of PostgreSQL objects and functionality
  • Seamless integration with existing CI/CD pipelines
  • Performance testing capabilities for database operations

Writing Tests with pgTAP

pgTAP provides a wide range of testing functions. Here are some common patterns for testing different database objects:

Testing Database Functions

Functions are one of the most common objects to test. Here's how to test function existence, return values, and error handling:

-- Example: Testing a user creation function
BEGIN;
SELECT plan(3);

-- Test function exists
SELECT has_function('create_user', ARRAY['text', 'text', 'text']);

-- Test function returns expected result
SELECT results_eq(
  'SELECT create_user(''john@example.com'', ''John'', ''Doe'')',
  'SELECT 1',
  'create_user should return 1 on success'
);

-- Test function handles invalid input
SELECT throws_ok(
  'SELECT create_user('''', ''John'', ''Doe'')',
  'Invalid email address',
  'create_user should throw error for empty email'
);

SELECT * FROM finish();
ROLLBACK;

Testing Tables and Constraints

Ensure your tables have the correct structure and constraints:

-- Example: Testing table structure and constraints
BEGIN;
SELECT plan(6);

-- Test table exists
SELECT has_table('users');

-- Test required columns exist
SELECT has_column('users', 'id');
SELECT has_column('users', 'email');
SELECT has_column('users', 'created_at');

-- Test if the table has a primary key constraint
SELECT has_pk('users');

-- Test unique constraint on email
SELECT has_unique('users', ARRAY['email']);

SELECT * FROM finish();
ROLLBACK;

Testing Triggers

Verify that triggers are properly attached and functioning:

-- Example: Testing triggers
BEGIN;
SELECT plan(2);

-- Test trigger exists
SELECT trigger_is('users', 'update_updated_at', 'users', 'update_updated_at()');

-- Test trigger function
SELECT has_function('update_updated_at');

SELECT * FROM finish();
ROLLBACK;

Best Practices

  • Use transactions to ensure test isolation and rollback changes
  • Organize tests by database object type and functionality
  • Use descriptive test names that clearly indicate what is being tested
  • Aim for comprehensive coverage of all database objects and edge cases
  • Include performance tests for critical database operations

CI/CD Integration

Integrate pgTAP tests into your CI/CD pipeline to ensure database quality. With Supabase, your tests should be located in the `supabase/tests` directory:

Note: With Supabase, your database tests should be located in the supabase/tests directory.
# .github/workflows/test-database.yml
name: Database Tests and Production Deployment

on:
  push:
    branches:
      - main
  workflow_dispatch:

jobs:
  test:
    name: Run Database Tests
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
        with:
          version: latest
      - run: supabase db start
      - run: supabase test db

Troubleshooting

Common issues and solutions when working with pgTAP:

Common Issues

  • Permission errors - ensure proper database permissions for test execution
  • Transaction management - always wrap tests in transactions for isolation

Conclusion

pgTAP provides a powerful and flexible way to test PostgreSQL databases in Supabase. By implementing comprehensive database testing, you can ensure data integrity, catch issues early, and maintain high-quality applications.

Next Steps

  • Explore the full pgTAP documentation and available testing functions
  • Integrate pgTAP tests into your existing Supabase projects
  • Share your testing patterns and best practices with the community
  • Continuously improve your test coverage and testing strategies
llms.txt