When business logic lives inside PostgreSQL, application-level tests are not enough. Functions, triggers, views, and constraints can encode rules that directly affect data integrity, scoring, state transitions, and downstream workflows. For teams building data-intensive platforms, this creates a familiar tension: the database is critical production code, but it often lacks the same fast feedback loop as application code.

This article explains how we applied test-driven development to PostgreSQL using pgTAP. The goal was not to adopt a testing tool for its own sake. The goal was to make database changes safer, reviews sharper, deployments less risky, and SQL business rules easier to understand over time.

The Problem: SQL Had Become Production Logic

Our team builds data infrastructure behind learning platforms. A significant part of that work happens inside PostgreSQL. The database does not merely store rows. It processes assignment results, tracks learning outcomes, validates incoming events, computes scores, and enforces state transitions.

That means a small SQL change can have large consequences. A function update can violate a business rule. A trigger can write incorrect derived data. A view can silently change the meaning of a downstream report. A state transition can become valid when it should be rejected.

Manual testing, code review, and integration tests were already part of the workflow. They helped, but they did not solve the core problem.

Integration tests were often too broad. They required larger setup, were slower to run, and made failures harder to isolate. Manual testing was too easy to perform against the happy path while missing edge cases. Code review improved design quality, but reviewers still needed a concrete way to verify that every business rule had been covered.

We needed a feedback loop that ran every time SQL changed. It had to work locally during development, run in CI for every pull request, and fail loudly when a database rule regressed.

Why Test SQL Inside PostgreSQL

A common instinct is to keep database testing outside the database. For many systems, that is reasonable. If the database is mostly CRUD storage and the application layer owns the business logic, application-level tests are usually the right place to focus.

That was not our case.

Our PostgreSQL schema contained meaningful behavior. The right test boundary was therefore inside PostgreSQL itself. We needed tests that could call database functions directly, assert on table structures, verify exceptions, and inspect state after real database operations.

pgTAP fit that requirement because it runs tests inside PostgreSQL and speaks the Test Anything Protocol, commonly known as TAP. Instead of treating the database as a black box, pgTAP lets SQL tests exercise database behavior directly.

That distinction matters. Testing a database-backed application through an API can confirm that one workflow works. Testing the SQL function itself can confirm the rules encoded in that function, including failure modes that may be difficult to reach through application flows.

Choosing the Right pgTAP Style

pgTAP supports several styles of testing. We experimented with three patterns: direct SQL checks, plan-based test scripts, and function-based tests.

Direct SQL checks are useful for quick sanity checks. For example, a developer can ask whether a function exists or whether a table has a specific column. This is convenient during exploration, but it does not scale well into a reusable team workflow.

-- Quick assertion without a test plan
SELECT ok(
    EXISTS(
        SELECT 1
        FROM public.learning_outcomes
        WHERE status = 'RetryGranted'
    ),
    'Retry granted for the assignment'
);

Plan-based tests are more structured. A test file declares the number of assertions it expects to run, executes them, then finishes. This works for simple linear test scripts. The drawback appears when tests evolve. Adding an assertion means updating the plan count. With many assertions and frequent changes, that becomes unnecessary friction.

SELECT plan(3);
SELECT is(
    some_function(), expected_result, 'Test description'
);
SELECT ok(some_condition, 'Another test');
SELECT throws_ok(
    'SELECT invalid_function()', 'Expected error message'
);
SELECT * FROM finish();

Function-based tests are the best fit for our workflow. Each test is implemented as a callable PostgreSQL function. The test can declare variables, arrange state, execute real database behavior, and assert on the result. These tests can be run individually during development or as part of a full suite in CI.

-- Each test is a function returning test results
-- ============================================================================
-- Test: Function signature and access
-- ============================================================================
CREATE OR REPLACE FUNCTION
    test.test_function_validate_array_param_access_and_signature()
RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT has_function(
        'results', 'validate_array_param',
        ARRAY['text', 'anyarray', 'integer', 'boolean'],
        'Function validate_array_param exists'
    );

    RETURN NEXT function_returns(
        'results', 'validate_array_param',
        ARRAY['text', 'anyarray', 'integer', 'boolean'],
        'void',
        'Function returns void'
    );
END;
$$ LANGUAGE plpgsql;

That modularity changes the development loop. A developer can work on one database function, reload the relevant test function, execute it repeatedly, and get immediate feedback without running the entire system.

Structuring SQL Tests Like Application Tests

The test layout intentionally resembles a conventional application codebase. We use a src/tests structure, initialize the pgTAP setup required by all tests, and load test files through a shared setup script.

Tests are organized by database unit: function by function, table by table, and rule by rule. Naming conventions make failures easier to diagnose. A function test follows a pattern such as:

test_function_<function_name>_<case_or_rule>

For example, tests around learning outcomes covered validation rules, state transitions, normalized score calculation, and retry-granted flows. The name of the test described the behavior being checked, not merely the implementation detail being exercised.

That naming discipline matters in CI. A failing database test should tell the developer which business rule broke. “Learning outcome transition from initialized to retry granted is invalid” is far more useful than “test 17 failed.”

-- Actual function name
<schema>.is_valid_learning_outcome_transition

-- Test function name
test.test_function_is_valid_learning_outcome_transition()

Arrange, Act, Assert Works for SQL Too

The most useful mental model is the same one used in application unit testing: arrange, act, assert.

First, the test arranges a realistic database state. For a learning outcome flow, that might mean inserting a synthetic event with the same shape as production data, creating the initial learning outcome record, or preparing assignment metadata.

Then the test acts by calling the real PostgreSQL function under test.

Finally, the test asserts on the result. That might mean checking a returned value, verifying that a record is valid, confirming a state transition, or ensuring that derived fields were written correctly.

The same approach works for failure paths. pgTAP can assert that a function raises an exception. That allows us to test not only valid flows, but also invalid transitions, malformed input, and rule violations.

This is important because many of the highest-risk database bugs are not happy-path failures. They come from accepting something that should have been rejected.

-- ============================================================================
-- Test: Error flow — invalid arrays raise exceptions
-- ============================================================================
CREATE OR REPLACE FUNCTION
    test.test_function_validate_array_param_flow_error()
RETURNS SETOF TEXT AS $$
BEGIN
    -- ACT + ASSERT: Array exceeding max size raises exception
    RETURN NEXT throws_ok(
        $test$SELECT results.validate_array_param(
            p_name     => 'p_ids'::TEXT,
            p_array    => ARRAY[
                '11111111-1111-1111-1111-111111111111'::UUID,
                '22222222-2222-2222-2222-222222222222'::UUID,
                '33333333-3333-3333-3333-333333333333'::UUID
            ],
            p_max_size => 2::INTEGER
        )$test$,
        '22023',
        'p_ids has 3 elements, maximum allowed is 2',
        'Array exceeding max size raises invalid_parameter_value'
    );
END;
$$ LANGUAGE plpgsql;

Tests Must Encode Business Rules, Not Implementation Details

The most important testing principle is to test business behavior, not the current shape of the implementation.

A brittle SQL test checks that a function performs a specific internal step. A useful SQL test checks that a learning outcome cannot move into an invalid state, that a normalized score is calculated according to the rule, or that an event with missing required data is rejected.

That distinction keeps the test suite valuable during refactoring. If the implementation changes but the business rule remains the same, the test should still pass. If the implementation accidentally changes the rule, the test should fail.

We also avoid unrealistic fixtures. The data is synthetic, but the patterns match production. That means test events, identifiers, scoring data, and state transitions resemble the actual data the system processes.

Each test is self-contained. Test data created for one function is not reused by another. That isolation prevents hidden coupling between tests and makes failures easier to understand. A test should not pass because another test happened to create the right row earlier in the suite.

Tooling Made TDD Practical

pgTAP provides the database testing foundation, but developer experience requires additional tooling.

We built a CLI tool, Odin, to wrap the local workflow. It can create a PostgreSQL schema in a local container, load the test infrastructure, add or modify tests, run the full test suite, run individual test functions, and clean everything up.

That tooling matters more than expected. Without it, developers would need to remember a sequence of container, schema, extension, and SQL-loading commands. With it, the development loop is repeatable:

1. Load the schema and test infrastructure.
2. Write or update a test.
3. Run the target test function.
4. Watch it fail.
5. Implement the database change.
6. Reload and rerun until the test passes.
7. Run the full suite before opening the pull request.

The ability to run a single function-based test is especially useful. During development, full-suite feedback is less important than fast, targeted feedback. CI can run everything. The local loop should stay narrow.

# Test: test.test_function_validate_array_param_flow_normal()
    ok 1 - Valid array within size limit does not raise
    ok 2 - Array exactly at max size does not raise
    ok 3 - NULL array with allow_null=true (default) does not raise
    ok 4 - NULL array with explicit allow_null=true does not raise
    1..4
ok - test.test_function_validate_array_param_flow_normal

CI Turned Tests into Deployment Confidence

The test suite is part of continuous integration. Every pull request runs the schema setup, loads the tests, executes the suite, and reports the result.

This changes code review. Reviewers no longer have to infer whether a database change has been tested. They can inspect the tests alongside the SQL and ask sharper questions:

Does this cover the invalid transition?

What happens when the learning outcome already exists?

Is the retry-granted flow tested separately from the initial creation flow?

Are we asserting the business rule or only the current implementation?

The test output also improves failure diagnosis. pgTAP reports assertions in TAP format and includes descriptive messages. When a test fails, the developer can see which assertion failed and why. That avoids the familiar database-debugging problem of searching through staging data to reconstruct what went wrong.

The Trade-Off: Retrofitting Tests Costs More Than Starting With Them

We did not begin with pgTAP from day one. The need became clear after meaningful database logic already existed. That meant some tests had to be retrofitted onto existing behavior.

Retrofitting still delivered value, but it was more expensive than writing tests first. Existing functionality already contained assumptions that had to be rediscovered. Some edge cases were implicit. Performance baselines also arrived later than they should have.

Once the team adopted the test-first workflow, new SQL work became cleaner. Writing the test first forces the developer to clarify the rule before encoding it. If the rule cannot be expressed clearly in a test, the implementation is probably not ready either.

The lesson is direct: database testing should not be treated as a hardening phase. If SQL contains business logic, tests belong in the first version of the work.

When pgTAP Is Worth It

pgTAP is a good fit when the database contains meaningful behavior.

It is especially useful when:

• PostgreSQL functions, triggers, or views enforce business rules.
• Multiple developers change database logic.
• Data integrity failures are expensive.
• Deployments are frequent.
• Reviewers need clear evidence that edge cases are covered.
• Refactoring SQL safely matters.

It may be unnecessary if the database is mostly CRUD storage, the application layer owns nearly all logic, or the architecture is heavily ORM-driven. In those systems, application tests may provide better coverage with less overhead.

The boundary is not ideological. Test logic where the logic lives. If the rule lives in Java, TypeScript, or C#, test it there. If the rule lives in PostgreSQL, the database needs its own fast test suite.

Start Simple, Then Invest in Workflow

The useful path was incremental.

Start with one high-value function. Write a pgTAP test for a business rule that would hurt if it regressed. Keep the first tests small. Prove that the team can run them locally and understand the output.

Once the value is visible, invest in structure: naming conventions, setup scripts, isolated fixtures, CI integration, and local tooling. The tooling does not need to be elaborate, but it must make the desired behavior easy. If running a database test requires too many manual steps, developers will avoid the loop.

The strongest tests come from understanding the requirement before writing SQL. pgTAP does not remove the need for clear business rules. It exposes the absence of them.

Database Code Deserves the Same Discipline as Application Code

Testing PostgreSQL code has changed how we approach database development. SQL functions have become units with explicit contracts. Edge cases have become executable examples. Pull requests are easier to review. Refactoring is safer. Deployments are less dependent on crossed fingers.

The larger lesson is not “use pgTAP.” The lesson is that database logic is production logic. If a PostgreSQL function can corrupt data, violate a workflow, or silently change learner outcomes, it deserves the same engineering discipline as any application service.

For our case, pgTAP gives PostgreSQL the missing feedback loop: write the rule as a test, watch it fail, implement the SQL, run it locally, verify it in CI, and deploy with evidence instead of hope.

Written by