LegitimateSounding.com

PostgreSQL is an amazing and extensible database, providing a ton of functionality. One of the best parts, in my opinion, is the ability to add additional programming languages to create stored procedures. This allows developers to move business logic deeper into the database itself. Unfortunately, this is often very hard to test in isolation.

The current state of testing in PostgreSQL is to run make installcheck, which runs a myriad of SQL commands living in files in the sql directory, with the results checked against a bunch more files living in the expected directory. This works, but is often-times difficult to add into existing test suites, which ultimately leaves holes in testing.

This is where Equinox comes in. Equinox is a testing framework built on top of PLV8 and Node.js; which comes in handy if you're already in the Javascript ecosystem. All tests are written in Javascript, executed in the PLV8 environment, and the results of the tests are available in a few different formats: dot, spec, and tap.

Installing

Installation requires Node.js to be installed:

$ npm install equinox

From there, it's just a matter of creating tests and running them.

Your First Test

Tests are written in Javascript, which get executed in PLV8 - this gives you query access, as well as logging where needed.

For more information about querying the database and executing stored procedures, see Database Access.

You define an array of tests to run, which get executed by the test runner:

var tests = [
  {
    'should equal 1': function ( ) {
      var result = plv8.execute('SELECT 1 AS num');
      assert.equal(result.length, 1, "length should be 1");
      assert.equal(result[0].num, 1, "num should equal 1");
    }
  },
  {
    'another test': [...]
  }
];

In addition, there are setup and teardown functions that can be executed at specific times. See testing to learn more.

Tests can be divided up to into multiple files. Each file will be treated in isolation, so each one should encapsulate all logic needed in order to run the tests.

Assert

There is currently only one type of assertion: assert.equal(a, b, message). This assertion only handles simple values, and does not yet do deep object equality checks.

Running Tests

By default, the equinox command-line script can be used to execute the tests:

$ equinox -d mydatabase --files tests/*.js

There are options available to help with configuration:

Under the Hood

Equinox creates a schema named equinox in your database when it runs. It then loads all of your tests into that schema by name as Postgres functions. These functions include all of the information needed to setup, execute, and teardown your tests, there is no need for you to add anything but the test code.

When the tests are complete, the schema is destroyed.

Node.js is used to instrument the tests. It creates the equinix schema, adds the tests by name, executes them in PostgreSQL, and returns the results in the format requested.

The tests are run using the pg module, and running SELECT against each function. The results are tallied, and reported.