Testing System Boundaries with Real PostgreSQL

A function can return the expected object while the database contains the wrong result. A customer query may return the right records because its filter is correct, even though the database role can read every customer’s data. A payment operation may return success while updating the wrong balance account.

In the platform I maintain, I use database integration tests to check these boundaries against real PostgreSQL. The tests exercise the application’s data functions, SQL, constraints and permissions together. Unit tests still cover calculations and branching; the database tests answer questions that depend on the database actually enforcing something.

The examples below use generic names and invented values. They describe implemented test patterns without exposing customer data or deployment details.

Build the test database from migrations

The integration harness starts a disposable PostgreSQL 16 container with Testcontainers, opens a connection pool and applies the repository’s Drizzle migrations. A suite creates its fixtures, executes its cases, then closes its connections and stops the container. The RLS suite also applies the database-role scripts before connecting as the restricted roles.

flowchart TD
  A[Start disposable PostgreSQL] --> B[Apply migrations]
  B --> C[Apply role grants where required]
  C --> D[Create small synthetic fixtures]
  D --> E[Run data functions and SQL]
  E --> F[Assert stored state and rejected access]
  F --> G[Close connections and remove container]

This makes migration execution part of test setup. An omitted function or invalid constraint can fail before a business test begins. A separate hand-written test schema would risk hiding those differences. The container lifecycle follows the Testcontainers PostgreSQL integration.

Most fixtures insert only the rows a scenario needs, using generated IDs. That makes the setup easier to understand and avoids depending on a developer’s existing database. It is not the same as clearing the database before every test: cases within a suite can share a container, so assertions must use their own fixture IDs rather than count unrelated rows globally.

A permission test must use the restricted role

Database setup needs elevated access. Permission assertions need the role whose restrictions are being tested.

PostgreSQL superusers and roles with BYPASSRLS bypass row-level security; table owners normally do too. An ownership test executed only through the setup connection therefore cannot establish the application’s RLS boundary. PostgreSQL documents these exceptions explicitly.

The tests create two synthetic customers and use separate connection pools for the customer, staff and unrelated application roles. The customer helper begins a transaction, sets the customer identity locally and executes the query through that same connection.

The useful test matrix includes successful access and attempted escape:

The first case matters because it removes the application predicate that could conceal a missing policy. The update and insert cases also have different expected outcomes: rejection does not always mean an exception.

This abbreviated pseudocode illustrates the read test; the helper supplies the transaction-local identity:

await asCustomer(customerA, async (connection) => {
  // Intentionally omit WHERE owner_id = ... to test the policy itself.
  const result = await connection.query(
    'SELECT id FROM protected_records',
  );

  expect(result.rows.map((row) => row.id)).toEqual([recordA.id]);
});

The actual suite also verifies that a staff role can append an access-log entry but cannot update or delete it. These assertions test the granted capability, not whether a particular button is hidden. The application-side design is covered in Shared Accounts, Separate Data Access.

Read back the state that the operation changed

For balance operations, checking the returned amount is only one assertion. The existing tests read the balance row and the movement record, checking the stored amount, movement type and status. They also cover insufficient funds, missing accounts and distinct error types that callers can handle differently.

A simplified credit scenario might begin with 240.00, add 60.00, and expect:

• a stored balance of 300.00;
• a returned before/after pair of 240.00 and 300.00;
• a completed movement for 60.00 associated with the intended owner.

All values here are illustrative. The engineering point is that a correct response and a correct stored record are separate things to verify.

Another test creates two balance accounts for one customer, applies an operation to the selected unit, and checks both accounts afterward. Verifying the untouched account catches a query that selects by customer alone and accidentally changes a different account. A mock that always returns the intended row can miss that error.

Test the query that the next request will read

Some results are derived rather than stored. Product availability, for example, is computed from physical stock and pending reservations. The stock tests execute the real availability query after changing order state.

Using invented quantities, the expected progression is:

Cancellation is a separate branch: cancelling the unpaid order releases its reservation without deducting physical stock. Existing tests check both branches. This catches mismatches between a write path and the SQL that supplies the next page, even when each operation appears reasonable in isolation.

Checkout Concurrency explains the locking design. These sequential state-transition tests establish a narrower result: the tested writes and availability query agree after each operation.

Keep the claim proportional to the test

A real database gives these tests meaningful enforcement, but it does not make them complete system tests.

• Replaying migrations on an empty database checks fresh installation, not every upgrade path over existing data.
• Sequential cases do not establish correctness under overlapping transactions. That requires explicit competing connections and controlled scheduling.
• SQL tests do not validate browser sessions, external payment delivery or production configuration.
• This integration suite has its own local command and container requirement; it is separate from the existing unit-test CI job.

The value is a specific, repeatable claim: under these roles, fixtures and operations, PostgreSQL stores the expected state and refuses the access that should be refused. That is a useful basis for changing data code, because a regression has a concrete boundary to break.