Unit testing PostgreSQL schemas

During the development of a complex PostgreSQL database for my employer, the need arose to test the correctness of complicated (trigger) functions. Although we were using Rails for the project infrastructure, we never wanted to depend on an application framework for work that so clearly belongs in the DB layer. Also, when trying to do unit tests the Rails way, we noticed that ActiveRecord is only an annoying get-in-the-way if you're doing things which really relate only to SQL.

Test organization and conventions

The basic test unit which I use are simple functions written in one of PostgreSQL's procedural languages. So far, PL/pgSQL has proven to be particular well-suited to this task (although, sadly, setting explicit savepoints within PL/pgSQL isn't possible).

Example trigger function

The following is a trigger function which, when used in a trigger, will set a created_at timestamp

CREATE OR REPLACE FUNCTION common.set_and_protect_created_at_timestamp()
  RETURNS "trigger" AS
$BODY$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- Is saved_at set and is is set to something else than CURRENT_TIMESTAMP
    -- (This works properly if the saved_at COLUMN has CURRENT_TIMESTAMP as its default value)
    IF NEW.created_at IS NOT NULL AND NEW.created_at != CURRENT_TIMESTAMP THEN
      RAISE EXCEPTION 'Giving created_at any value other than CURRENT_TIMESTAMP or NULL is illegal.';
    END IF;
 
    NEW.created_at := CURRENT_TIMESTAMP;
 
  ELSIF TG_OP = 'UPDATE' THEN
    IF NEW.created_at != OLD.created_at THEN
      RAISE EXCEPTION 'Updating a created_at timestamp is illegal.';
    END IF;
  END IF;
 
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Example test function

CREATE OR REPLACE FUNCTION test.test_common_set_and_protect_created_at_timestamp() RETURNS VOID AS $$
DECLARE
  v_exception_raised BOOLEAN;
BEGIN
  CREATE TABLE test.test (created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, dummy TEXT);
  CREATE TRIGGER auto1 BEFORE INSERT OR UPDATE ON test.test
    FOR EACH ROW EXECUTE PROCEDURE common.set_and_protect_created_at_timestamp();
 
  INSERT INTO test.test (dummy) VALUES ('bla');
 
  IF (SELECT created_at != CURRENT_TIMESTAMP FROM test.test) THEN
    RAISE EXCEPTION 'created_at timestamp does not equal CURRENT_TIMESTAMP.';
  END IF;
 
  
  
  <<test_inserting_of_wrong_timestamp>>
  BEGIN
    v_exception_raised := false;
    INSERT INTO test.test (created_at) VALUES ('2000-01-01');    
  EXCEPTION WHEN raise_exception THEN v_exception_raised := true; END;
  
  PERFORM test.assert(v_exception_raised, 'Inserting a wrong created_at timestamp should not be possible.');
  
  
 
  <<test_updating_timestamp>>
  BEGIN
    v_exception_raised := false;
    UPDATE test.test SET created_at = '2000-01-01';
  EXCEPTION WHEN raise_exception THEN v_exception_raised := true; END;
  
  PERFORM test.assert(v_exception_raised, 'Changing a created_at timstamp during an update should not be possible.');
END;
$$ LANGUAGE plpgsql;

Like all our test functions, this function return nothing and does nothing except raise an exception when an assertion fails.

Example function for help_global.pgsql

-- Functions that are available in each test.
 
CREATE FUNCTION test.assert(p_assertion BOOLEAN, p_message_on_error VARCHAR(255)) RETURNS VOID AS $$
BEGIN
  IF p_assertion IS NULL THEN
    RAISE EXCEPTION 'Assertion test is null, that is not supported.';
  END IF;
  
  IF NOT p_assertion THEN
    RAISE EXCEPTION '%', p_message_on_error;
  END IF;
END;
$$ LANGUAGE plpgsql;

Test isolation

Isolation is provided by calling the test functions each from within a separate transaction that is rolled back after the call to test function. Later, we'll see a Rakefile which already does that for us. Of course, when a test assertion raises an exception, a rollback will also occur.

It would be nice if, within a PL/pgSQL function, explicit savepoints could be used to more cleanly separate individual assertions, but apparently I'm the only one in the PostgreSQL community who thinks so…

The test schema

I create all the test functions in the test schema. When I create mock tables, triggers and views, I tend to also create those there. Sticking everything in a separate schema gives me another level of isolation.

I've decidedly chosen not to depend on a separate test database to run our tests in. (Rails uses three environments with separate databases: testing, development and production.) I wanted to remain flexible and be able to run tests in my development environment during the development of schema changes without having to clone the whole development DB to my test DB all the time. (Different from Rails' default, I also regularly clone our complete production DB to my development DB periodically which makes my schema changes more likely not to fail during deployment.)

I've also decided against sticking the test functions in the schemas where they apply, because, when you want to access your test functions directly from your database (think pgAdmin or psql) instead of from your Framework's command-line, this way it's much easier to get a summary of which tests there are.

Naming individual tests

The test functions are named according to test_[schema]_[what_to_test](), where [schema] is the schema where the tested entity lives, and [what_to_test] is the entity (a name of a relation, function or constraint) to test. When necessary, [what_to_test] can also be the name of a behaviour that crosses, for example, multiple tables, triggers and views.

Test filenames

Each of these test functions is then put into a separate file following the function name. The above test lives in a file called test_common_set_and_protect_created_at_timestamp.pgsql.

Helper functions

Sometimes, you might have a chunk of functionality (such as setting up some test data) which you don't want to repeat for each test function. You can define helper functions for this functionality. These functions should go in files named help_*.pgsql, where * should match some part of the filenames of the tests for which you want the helpers to be loaded.

Running the tests

The advantage of the simple test_[schema]_[entity] naming convention becomes clear once you start running tests. The following simple Rakefile, for example, lets you run a batch of tests very easily by letting your run rake followed by the filenames of the tests. Then, thanks to your shell's completion and wildcard expansion, you can easily identify and group tests. Simply issuing rake test_common_* in my test directory will (re)load and run the tests for everything in the schema called common.

Example Rakefile

Rake is a very powerful Ruby alternative to Make. I've made this Rakefile to integrate in a Rails application environment, but by modifying the db_env task, it can easilly be made to work with any environment. Its only dependency that's not already distributed with Ruby's standard library is ruby-postgres.

$yellow_begin = "\033[01;33m"
$green_begin = "\033[01;32m"
$red_begin = "\033[01;31m"
$color_end = "\033[00m"
$succeeded = "#{$green_begin}succeeded#{$color_end}."
$failed = "#{$red_begin}failed#{$color_end}."
 
$search_path = "SET SEARCH_PATH TO test, public"
 
 
FileList['help_*.pgsql'].each do |help_fname|
  file help_fname => [:pg_conn, :clear_test_schema, :load_global_helper] do    
    print "#{$yellow_begin}Loading helper #{help_fname}: #{$color_end}"
 
    begin
      $db_conn.exec $search_path
      $db_conn.exec File.open(help_fname).read
    rescue PGError
      puts $failed
      raise
    else
      puts $succeeded
    end
  end 
end
 
 
# Traverse all filenames prefixed with 'test_'
FileList['test_*.pgsql'].each do |test_fname|
 
  # Find files with helper functions
  test_basename = test_fname.gsub(/..*sql$/, '').gsub(/^test_/, '')
  test_basename.count('_').times do
 
    test_basename = test_basename.gsub(/_[^_]+$/, '')
 
    help_fname = "help_#{test_basename}.pgsql"
 
    if File.exists? help_fname
      file test_fname => help_fname do
      end
    end
  end
  
  file test_fname => [:pg_conn, :clear_test_schema, :load_global_helper] do
    file_basename = File.basename(test_fname, '.pgsql')
    print "#{$yellow_begin}Loading test #{test_fname}: #{$color_end}"
 
    # Only show me bad things. I don't want to drown in NOTICEs
    $db_conn.exec "SET client_min_messages TO 'WARNING'"
    
    begin
      # (Re)load test function
      $db_conn.exec $search_path
      $db_conn.exec File.open(test_fname).read
    rescue PGError
      puts $failed
      raise
    else
      puts $succeeded
    end
    
    print "#{$yellow_begin}Running test #{file_basename}(): #{$color_end}"
 
    begin
      # Execute the test function, and rollback any changes it might have made
      $db_conn.exec "BEGIN"
      $db_conn.exec "SELECT test.#{test_fname.gsub(/\..*sql$/, '')}()"
      $db_conn.exec "ROLLBACK"
    rescue PGError
      puts $failed
      raise
    else
      puts $succeeded
    end
  end
end
 
task :load_global_helper => :clear_test_schema do
  print "#{$yellow_begin}Loading global helper: #{$color_end}"
  
  begin            
    $db_conn.exec $search_path
    $db_conn.exec File.open("help_global.pgsql").read      
  rescue PGError
    puts $failed
    raise
  else
    puts $succeeded
  end
end
 
task :clear_test_schema => :pg_conn do
  print "#{$yellow_begin}Clearing test schema: #{$color_end}"
 
  begin
    $db_conn.exec "SET client_min_messages TO 'WARNING'"
    $db_conn.exec "BEGIN"
    $db_conn.exec "DROP SCHEMA test CASCADE"
    $db_conn.exec "CREATE SCHEMA test"
    $db_conn.exec "COMMIT"
  rescue PGError
    puts $failed
    raise
  else
    puts $succeeded
  end  
 
end
 
task :pg_conn => :pg_env do
  require 'postgres'
 
  puts "Connecting to #{ENV['PGDATABASE']} on #{ENV['PGHOST']}:#{ENV['PGPORT']}."
 
  $db_conn = PGconn.open(
    ENV['PGHOST'], ENV['PGPORT'].to_i, "", "", ENV['PGDATABASE'],
    ENV['PGUSER'], ENV['PGPASSWORD']
  )
end
 
# Change this task to fit your particular environment
task :pg_env do
  require 'yaml'
  require 'erb'
 
  RAILS_ROOT = File.dirname(__FILE__) + '/../../'
  
  db_cfg = YAML.load(
    ERB.new(
      File.open(File.dirname(__FILE__) + '/../../config/database.yml').read
    ).result
  )[ ENV['RAILS_ENV'] || 'development' ]
 
  ENV['PGHOST'] = db_cfg['host']
  ENV['PGPORT'] = db_cfg['port'].to_s
  ENV['PGUSER'] = db_cfg['username']
  ENV['PGPASSWORD'] = db_cfg['password']
  ENV['PGDATABASE'] = db_cfg['database']
end
 
# vim: set filetype=ruby shiftwidth=2 tabstop=2 expandtab: 

Correction

30 okt. 2007 – Too long a time ago already, Halfgaar fixed a glaring bug in the test function which I use as an example in this text. I was catching an error generated by a trigger (raise_exception). If such an error was caught, I'd raise an error from within the BEGIN...END block, which, of course, would cause a loop of errors being raised by the block which was catching them. Because there's only one type of fatal exception which you can raise yourself, the solution was as simple as setting a boolean which lasts untill after the BEGIN...END block. Today, I finally updated the text with his fixes (and some other of his improvements), hoping that not too many people have blindly copied the previous verion (although it would work fine when handling most other types of exception).

Let me know if anything is unclear or missing.