How to write tests in Postgres

PostgreSQL is my database of choice for hobby projects. If a project diverts, I can usually develop functionality in PostgreSQL before turning to specialised systems like Redis. But I need to verify that my code works before moving on. pgTAP is an existing PostgreSQL test framework, but I wanted to see if simple tests were possible using only built-in functionality, without adding dependencies¹.

PostgreSQL has the assert keyword in its PL/pgSQL language (see docs), which raises errors when conditions fail. The example below shows how to use assert to test an addition calculation. I’ve provided a working dbfiddle here².

do $$
declare
   sum_total integer;
begin
   select 1 + 1
   into sum_total;

   assert sum_total = 2, 'Sum total is incorrect';
end$$;

Now I’ll develop this further. I wrote a PL/pgSQL function called increment that adds 1 to an integer. I wanted to verify it works correctly. Here’s the dbfiddle for this example.

Here’s my function for adding to an integer:

create or replace function increment(i integer) returns integer AS $$
        begin
                return i + 1;
        end;
$$ language plpgsql;

And here’s my (broken) test:

-- example to fail
do $$
declare
  test_result integer;
begin
  select increment(1)
  into test_result;

  assert test_result = 1, 'Wrong increment added';
end$$;

That gives the result:

ERROR:  Wrong increment added
CONTEXT:  PL/pgSQL function inline_code_block line 8 at ASSERT

And this is the working test:

-- test to pass
do $$
declare
  test_result integer;
begin
  select increment(1)
  into test_result;

  assert test_result = 2, 'Wrong increment added';
end$$;

I now use multiple assert statements to test various aspects of my code. My PostgreSQL projects typically have files like³:

• tables.sql
• views.sql
• functions.sql
• data.sql
• tests.sql

I run all of these against a local PostgreSQL database using a makefile, which I execute manually. To automate this, I could pipe output through tee into a file, grep for test failures, and mark the build successful only if none exist.

🡹

Why so many footnotes? It’s my website, so I can indulge myself. But I’m also captivated by Montaigne, the father of the modern essay. He had no qualms writing lengthy essays that ramble across topics, punctuated by lengthy retrospective footnotes. If I haven’t convinced you already, do read him.

Footnotes

1. “Why do you hate dependencies?” I don’t—dependencies save time. But each one introduces vulnerabilities and requires learning. After mastering a tool, I might find it doesn’t fit my needs exactly, forcing custom development anyway. It depends on the situation. ↩

2. dbfiddle lets you run code on various databases. It’s useful for Stack Overflow answers and blog posts. ↩

3. I model my hobby projects on James Powell’s setup. See his example here. ↩