PostgreSQL Testing with pgsql-test: Does It Fit a CI Pipeline?

Database changes can be easy to test manually but harder to validate consistently in a CI pipeline. A developer may run a few SQL commands locally, while the same change can behave differently when executed against a clean database.

pgsql-test provides a way to organize and run PostgreSQL tests automatically. It can be useful when database logic, schema changes, functions, procedures, or queries are important parts of an application.

The main question is not whether database testing is useful. It is whether the testing approach is simple enough to run reliably as part of CI.

Why Test PostgreSQL Changes?

A database contains more than tables.

A typical PostgreSQL application may include:

Database
 |
 +-- Tables
 +-- Indexes
 +-- Constraints
 +-- Views
 +-- Functions
 +-- Procedures
 +-- Triggers
 +-- Permissions

A change to one part can affect another.

For example, adding a constraint may cause an existing application test to fail. Changing a function can also change the result returned by an API.

Automated database tests make these problems easier to detect before deployment.

What Is pgsql-test?

pgsql-test is a PostgreSQL testing framework designed to execute database tests and verify expected results.

A database test can validate behavior directly inside PostgreSQL rather than testing only through an application's API.

Conceptually:

Test Case
   |
   v
PostgreSQL
   |
   +-- SQL
   +-- Function
   +-- Procedure
   +-- Trigger
   |
   v
Expected Result

This makes database-level testing useful for logic that belongs inside PostgreSQL.

What Should You Test?

Not every SQL statement needs a dedicated test.

Focus on database behavior that can break an application.

Good candidates include:

Stored functions
Stored procedures
Complex queries
Constraints
Triggers
Data validation
Permission rules
Important business calculations
Migration behavior

For example, if an application calculates an order total inside PostgreSQL, that calculation is a good candidate for an automated test.

A Simple Database Test

A basic test can create known input data and verify the result.

For example, suppose the database contains an order calculation function:

CREATE OR REPLACE FUNCTION calculate_total(
    price numeric,
    quantity integer
)
RETURNS numeric
LANGUAGE sql
AS $$
    SELECT price * quantity;
$$;

A test can check the expected result:

SELECT calculate_total(25, 4);

The expected value is:

The important idea is that the test verifies database behavior rather than relying on a developer to manually inspect the result.

Test Positive and Negative Cases

A useful test suite should not test only valid input.

For example:

Valid quantity
Invalid quantity
Zero quantity
Large quantity
Null value

For a business function, these cases can expose problems that a simple happy-path test will miss.

The test strategy can look like this:

Function
   |
   +-- Valid Input
   |
   +-- Invalid Input
   |
   +-- Boundary Value
   |
   +-- Null Input

Test Database Constraints

Constraints are another good candidate.

For example:

CREATE TABLE products
(
    id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric CHECK (price >= 0)
);

Tests should verify that invalid data is rejected.

For example:

INSERT INTO products
    (id, name, price)
VALUES
    (1, 'Keyboard', -10);

The test should expect this operation to fail because the price violates the constraint.

Testing failure behavior is important because database constraints often provide the final protection against invalid data.

Test Functions and Procedures Separately

If business logic is implemented inside PostgreSQL, test it directly.

For example:

CREATE OR REPLACE FUNCTION get_order_status(
    order_id integer
)
RETURNS text
LANGUAGE sql
AS $$
    SELECT status
    FROM orders
    WHERE id = order_id;
$$;

A test can prepare a known order:

INSERT INTO orders
    (id, status)
VALUES
    (1001, 'Completed');

Then verify:

SELECT get_order_status(1001);

Expected:

Completed

This isolates the database logic from the application layer.

Keep Test Data Predictable

Database tests become unreliable when they depend on existing data.

Avoid tests that assume:

Customer 101 already exists
Order 500 already exists
Product 10 already exists

Instead, create the required test data as part of the test setup.

A predictable test follows this pattern:

Setup
  |
  v
Insert Test Data
  |
  v
Execute Operation
  |
  v
Check Result
  |
  v
Clean Up

This makes tests repeatable.

Use Transactions Where Appropriate

Transactions can help isolate database tests.

A typical approach is:

BEGIN;

-- Test setup
-- Execute test
-- Validate result

ROLLBACK;

This allows temporary test data to be removed automatically when the test finishes.

However, not every test can be safely wrapped in a transaction. Tests involving transaction behavior itself, commits, or certain database operations may require a different setup.

Running Tests in CI

The real value of database testing appears when tests run automatically.

A typical pipeline can look like this:

Developer Push
      |
      v
CI Pipeline
      |
      v
Start PostgreSQL
      |
      v
Apply Migrations
      |
      v
Run Database Tests
      |
      v
Application Tests
      |
      v
Build / Deploy

If the database tests fail, the pipeline should stop before deployment.

Use a Clean PostgreSQL Environment

CI tests should preferably run against a predictable database environment.

For example:

CI Runner
   |
   +-- PostgreSQL Test Database
   |
   +-- Database Schema
   |
   +-- Test Data
   |
   +-- pgsql-test

This avoids accidental dependency on a developer's local database.

A containerized PostgreSQL instance can be useful because the CI environment can create a fresh database for each pipeline run.

Test Migrations Too

A database test suite should not focus only on stored functions.

Migration testing is also important.

A migration pipeline can look like:

Empty Database
      |
      v
Migration 001
      |
      v
Migration 002
      |
      v
Migration 003
      |
      v
Database Tests

This helps detect problems such as:

Missing columns
Incorrect data types
Invalid constraints
Broken indexes
Failed migrations
Incorrect default values

Keep CI Tests Fast

Database tests should provide useful feedback without making every pipeline unnecessarily slow.

A practical strategy is to separate tests:

Test Type	Example	Frequency
Unit database tests	Functions and queries	Every change
Migration tests	Schema changes	Every database change
Integration tests	Application + PostgreSQL	CI
Full environment tests	Complete system	Selected pipelines

Not every test needs to run at every stage.

Common CI Failure

One common problem is assuming that the database environment already exists.

For example:

Developer Machine
    |
    +-- PostgreSQL
    +-- Required Database
    +-- Test Data

The CI runner may have none of these.

Instead, the pipeline should explicitly create the required environment:

CI Runner
   |
   v
Start PostgreSQL
   |
   v
Create Database
   |
   v
Apply Schema
   |
   v
Run Tests

This makes the pipeline reproducible.

Test Database Permissions

CI tests should use a dedicated database account.

Avoid using a production database account.

For example:

Application Production User
        X
        |
        X
CI Database Tests

Instead:

CI Test User
     |
     v
Test Database

The test account should have only the permissions required for the test environment.

What pgsql-test Is Good At

pgsql-test fits well when you need to validate PostgreSQL behavior directly.

It is particularly useful when your application relies heavily on:

PostgreSQL functions
Procedures
Constraints
Triggers
Database-side calculations
Complex SQL

Testing these components directly can catch errors earlier than waiting for an application integration test.

What It Does Not Replace

Database testing should not replace application-level tests.

Consider:

Database Tests
      +
Application Unit Tests
      +
Integration Tests
      +
End-to-End Tests

Each layer checks a different part of the system.

For example, a database test can confirm that a function returns the expected value, but it may not detect an incorrect API mapping or UI behavior.

Common Mistakes

Testing Only Successful Queries

Test invalid input and failure conditions as well.

Depending on Existing Data

Create predictable test data.

Using Production Credentials

Use a dedicated CI database and account.

Skipping Migration Tests

A function test cannot help if the schema migration fails first.

Making Every Test End-to-End

Not every database behavior needs to be tested through the entire application.

Ignoring Test Isolation

Tests that modify shared data can produce inconsistent results.

Best Practices

Keep database tests close to the database logic.
Use clean databases in CI.
Create test data explicitly.
Test both successful and failing operations.
Test important migrations.
Use transactions where appropriate.
Keep CI credentials separate from production.
Keep database tests focused and fast.
Run integration tests in addition to database tests.
Remove dependencies on manually prepared environments.

Advantages

Tests PostgreSQL behavior directly.
Useful for database-side business logic.
Can run automatically in CI.
Helps detect migration and schema problems early.
Reduces dependence on manual database testing.
Makes database behavior easier to validate repeatedly.

Disadvantages

Adds another test layer to maintain.
Database tests can become slow if poorly designed.
Test isolation requires careful planning.
Some database operations are difficult to test inside transactions.
It does not replace application or end-to-end testing.

Does It Fit a CI Pipeline?

For projects where PostgreSQL contains meaningful application logic, automated database testing can fit well into CI.

A practical pipeline is:

Code Change
    |
    v
Build
    |
    v
Start Clean PostgreSQL
    |
    v
Run Migrations
    |
    v
Run pgsql-test
    |
    v
Run Application Tests
    |
    v
Build Artifact

The key is keeping database tests focused. They should validate important PostgreSQL behavior without becoming a second copy of the entire application test suite.

Conclusion

pgsql-test can be useful in a CI pipeline when PostgreSQL is responsible for more than simple data storage.

Functions, procedures, constraints, triggers, complex queries, and migrations are strong candidates for automated testing.

The most practical approach is to use a clean PostgreSQL environment, create predictable test data, validate both success and failure cases, and run the tests before application deployment.

Database tests work best as one layer of a broader testing strategy:

Database Tests
      +
Application Tests
      +
Integration Tests
      +
End-to-End Tests

This gives development teams faster feedback and helps catch database problems before they reach production.