Can We Test Postgres at the Database Level? From Functions to Indexes, Here’s How!

When working with complex database features like triggers, functions, and indexes, testing at the database level can boost reliability and confidence. Learn how to achieve this in Postgres!

What you are going to read in this article:

• Why using Database Functions, Triggers and Other Capabilities of Database?
• Do we need a Postgres Extension to Implement Tests?
• Implement Tests Using PGTap
• Conclusion

Unit Tests for Postgres

Why Using Database Functions, Triggers and Other Capabilities of Database in the First Place?

Before knowing how to test Database, I would like to explain, why it is useful to use such features in the first place!

I really love to move some of the complicated logic that revolves around Data, to the Database itself(in example of a Web App). On the other hand some other people argue that when you move the logic to the Database and you have a bug, you do not know if the bug is happened on the backend side or the Database itself.

There is this spectrum in my opinion and it is important to find the optimal point, you would like to stay, based on your team, their skills and the problems you are trying to solve.

Spectrum of Logic existing on the Backend or Database

The reasons I personally would like to take advantage of the Database but there are always advantages and disadvantages based on the use cases we have. the advantages could be faster execution, reduced network overhead, automatic actions(triggers) and disadvantages could be complex debugging, more load on the Database. Also in my opinion data integrity and consistency is something that should be achieved by the Database itself. And for keeping the data integrity sometimes you have to use Functions or complicated Triggers to make sure data is correct.

So, in some cases we might have to use these capabilities. Let’s see how we can test the Database.

Do we need a Postgres Extension to Implement Tests?

The answer is no. We can use some simple scripts to verify what we are looking for, a very basic example would be:

-- Setup test data
CREATE TEMP TABLE test_users (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_users (name) VALUES ('Alice'), ('Bob');-- Test: Check row count
DO $$
BEGIN
    IF (SELECT COUNT(*) FROM test_users) != 2 THEN
        RAISE EXCEPTION 'Row count does not match';
    END IF;
END $$;
-- Cleanup
DROP TABLE test_users;

We simply check if there 2 users in the table or not and we raise an exception. but we are going to take a look at PGTap.

Implement Tests Using PGTap

We are going to use PGTap because it implements some functions which are familiar to us in terms of testing something. TAP stands for Test Anything Protocol :

“TAP, the Test Anything Protocol, is a simple text-based interface between testing modules in a test harness. It decouples the reporting of errors from the presentation of the reports.”

It has functions like: is , ok() ,is() , isnt() .

Installation

We have to install this extension because this is not included by default. On paper, after using this commands and then enabling the extension, you should be fine.

make
make install
make installcheck

Enabling the extension:

CREATE EXTENSION pgtap;

But most of the time, it is not the case you need to install some dependencies to make it work. so I did not want to give trouble to people who read this and I created a working docker container that has this extension and works!(hopefully :) ).

The container is in my personal repository: pshaddel/postgres-pgtap:latest

You can run the container by passing proper env variables:

docker run -d --name my_postgres_pgtap \
       -e POSTGRES_USER=myuser \
       -e POSTGRES_PASSWORD=mypassword \
       -e POSTGRES_DB=mydb \
       -p 5432:5432 \
       pshaddel/postgres-pgtap:latest

After running it you can either directly get a shell in the docker container by something like this:

docker exec -it my_postgres_pgtap sh  

and then you have psql for accessing the Database, or you can directly connect to your Database based on the env variables and the exposed port.

Tests

For running a test, we need a plan! A plan is simply number of tests that you are running.

In first example we are going to check if a table named users exist or not.

SELECT plan(1);
SELECT has_table('users');
SELECT * FROM finish();

The result would be:

# Looks like you failed 1 test of 1

Test Tables and Schemas

Now let’s create the users table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  -- is first_name + last_name: use always generated column
  full_name VARCHAR(255) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
  email VARCHAR(255),
  username VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

If we run the same test once more, we get this result:

ok 4 - Table users should exist

If we have some schemas, let’s say users table is under iam_service, we can test it by this command:

SELECT has_schema('iam_service');

Or if the table we want to test is under a schema we can test it like this:

SELECT has_table('iam_service','users', 'has table iam_service.users');

You have to pass 3 arguments to this one, because otherwise it assumes that first one is the table name and second one is the description of the test.

Test a Generated Field

As you have seen, we have created the full_name field, using generated column . let’s implement a test to make sure that it works fine. We can use is function for equality check of two strings. First we need to create a user and then by we can pass the full_name of this record as first argument and the expected value as the second one.

INSERT INTO users (first_name, last_name, email, username) VALUES ('John', 'Doe', 'john_doe@gmail.com', 'johndoe');
SELECT is((SELECT full_name FROM users WHERE username = 'johndoe'), 'John Doe', 'full_name is correct');
DELETE FROM users WHERE username = 'johndoe';

ok 12 — full_name is correct

Test a Trigger

Let’s implement a trigger for having the updated_at field correctly and then test the functionality:

First the function that updates the field

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

Now the trigger which uses this function:

CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Let’s test it out!

-- first user
INSERT INTO users (first_name, last_name, email, username) VALUES ('John', 'Doe', 'john_doe@gmail.com', 'johndoe');
-- second user
INSERT INTO users (first_name, last_name, email, username) VALUES ('Bruce', 'Wayne', 'bruce_wayne@gmail.com', 'brucewayne');
-- update the first user
UPDATE users SET first_name = 'Jane' WHERE username = 'johndoe';
-- compare the two updated at in these two users
SELECT isnt((SELECT updated_at FROM users WHERE username = 'johndoe'), (SELECT updated_at FROM users WHERE username = 'brucewayne'), 'updated_at is not the same');

We can do it better by finding out which one has the newer updated_at:

We can sort the two records based on update_at and pick the first one.

SELECT is(
    (SELECT username FROM users WHERE username = 'johndoe' OR username = 'brucewayne' ORDER BY updated_at DESC LIMIT 1), 
    'johndoe', 
    'updated_at of the updated row is updated'
    );

ok 17 - updated_at of the updated row is updated

Testing If Indexes are Present

Something else which is important is that, if we have a field that we are searching based on it, we always want to have an index on it. Let’s see if we can test it either. Let’s create a unique index on username field:

CREATE UNIQUE INDEX users_username_idx ON users (username);

We can check if there is an index on field username:

one of the ways you can use has_index function is this pattern:

SELECT has_index( 'table', 'index', 'column', 'description');

SELECT has_index('users', 'users_username_idx', 'username', 'username is indexed');

ok 22 - username is indexed

If we want to check the uniqueness of the index:

SELECT index_is_unique('users_username_idx');

The Whole Flow for Testing Something in the Pipeline!

If we have an application and we want to test the Database as part of it in the CI CD, there are some steps that we need to take:

• Spin up a container that has the extension(in case you want to implement the tests using an extension)
• Create the Tables and Functions using a SQL Script
• Run the Tests

We have already the container to run the tests. For the second part, we need to have some scripts for creating a Database similar to our Production environment. For example to create our users table and the triggers on it.(Some ORM’s can produce such scripts, some can’t. but you can put a dump of the DB for this use case)

And the final step is to run the tests. For running the scripts before your testsm, you can use /i like this:

\i path/to/script.sql

and you can have a more step in your CI CD pipeline for running these tests.

Conclusion

Running tests for the Database functionalities is not that complicated, and for some of the Database functions and triggers, it would be necessary to have tests. For checking the schema, structure and indexes, it is also possible to implement tests.

GitHub - theory/pgtap: PostgreSQL Unit Testing Suite

pgTAP

Test Anything Protocol