Pi

November 14, 2009

Unit Testing Postgres

Filed under: Uncategorized — rqmedes @ 11:55 am

I  have been using  Pg_Unit  to create unit tests for  my  latest postgres database application.
You can get PG_Unit from here  http://en.dklab.ru/lib/dklab_pgunit/

Pg_unit is pretty bare bones  allowing you to basically  assert values are the same and as the the  forums are in Russian it is hard to find any further information.

I find it useful to write tests for all my validation logic, checks and constraints. This is useful when you  have lots of tables and data validation checks.

A basic example:

insert into inventory(code) values (‘a”);
PERFORM   pgunit.assert_fail(‘insert into inventory(code) values (”a”);’);

which  inserts a record and then asserts that the same  record cannot be inserted again.

As pg_unit runs all the  tests within a transaction that is rolled back none of your test code will affect your data.

Here is the function to assert failures.

CREATE OR REPLACE FUNCTION pgunit.assert_fail(sql_statement text)
     RETURNS void AS
$BODY$
DECLARE ok boolean;
BEGIN
      BEGIN
            ok := false;
            execute  $1;
       EXCEPTION
       WHEN Others THEN
            ok := true;
       END;
       IF ok is false THEN
             PERFORM pgunit.fail('Should have Failed');
       END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Advertisements

1 Comment »

  1. That is very handy. I really like the idea of having validation on the db. You don’t have to worry about applications or even query tools inserting invalid data. Nice one!

    Comment by Glen — February 26, 2010 @ 3:20 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: