How to write tests in Postgres

Thomas Dickson

4 minute read



Postgresql is my database of choice when hacking on hobby projects. If a particular project goes on a tangent there’s usually a way to develop functionality in Postgresql before relying on a specific system such as Redis, for example. However I need to confirm that whatever I’ve developed works before being confident enough to move on. pgTAP is an existing Postgresql test framework, but I want to see if I can implement some simple tests using existing postgresql functionality without introducing new dependencies1.

Postgresql has the assert keyword in it’s PL/pgSQL language, docs here, that allows errors to be raised if particular conditions aren’t met. See the example below for an illustration of how assert can be used. This snippet shows how assert can be used to check an addition query. I’ve put the dbfiddle here2.

1
2
3
4
5
6
7
8
9

do $$
declare 
   sum_total integer;
begin
   select 1 + 1
   into sum_total;
  
   assert sum_total = 2, 'Sum total is incorrect';
end$$;

Great stuff, now to develop this approach. I’ve now written a PL/pgSQL function named increment and I want to make sure that it does what I think it does, namely add an increment of 1 to an integer value. I’ve put the dbfiddle for this example here.

Here’s my function for adding $1$ to an integer:

1
2
3
4
5

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

And here’s my (broken) test:

1
2
3
4
5
6
7
8
9
10

-- 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:

1
2

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

And this is the working test:

1
2
3
4
5
6
7
8
9
10

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

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

At the moment I have a series of these assert statements that confirm various things about the functionality I’m implementing. The postgresql code for a given project of mine might be broken into files like3:

I run all of these against a local postgresql database using a makefile. I run this manually, but I could turn this into an automated build step in a container by tee-ing output into a file, greping for any test failures and if there are none mark the build as successful.

🡹

What about these footnotes? First of all this is my website so I can do what I want, but I’m also a massive fan of Montaigne. Montaigne is arguably the father of the modern essay as a form of expression and was unafraid of writing long rambling essays on multiple topics, peppered with retrospective often lengthy footnotes. If I’ve not sold him already I encourage you to read him.

  1. “Why do you hate dependencies?” I hear you ask. Well, I don’t necessarily hate dependencies. Using more software tools and frameworks can be really useful and save a lot of time and effort. However new dependencies often open up new vulnerabilities in products and also require a bit of time investment to learn how they work. After I’ve understood how a particular widget works it may still even not exactly do what I want, so I might end up having to do more specific development anyway. It’s an “it depends” sort of situation. ↩

  2. dbfiddle is a pretty cool website allowing one to run code on a variety of databases. It’s quite useful for demonstrating things for stackoverflow answers or for blog posts like this one, as it turns out. ↩

  3. I base my hobby code projects off a similar setup to that used by James Powell in his. Here is an example. ↩