Property Testing A Database Driver

A database driver is responsible for orchestrating the protocol between an application programming language and a database. Using Erlang/OTP and PostgreSQL, this article features writing property tests with PropEr to efficiently verify a driver implementation.

For example, PostgreSQL has smallint which is represented by a 16 bit signed integer. Using -1 as an example value, with a “traditional” non property based test:

smallint_test() ->
  ?assertEqual(-1, pbe("select $1::smallint", [-1])).

Where pbe is a function that performs the actions of parse, bind and execute. PostgreSQL will parse select $1::smallint and bind the parameter $1 with -1. When we execute this statement we assert that the database responds with -1. This mechanism enables us to test the serialisation and deserialisation of each type.

As a signed 16 bit integer, the range of values for smallint are between -32,768 and 32,767. Using non property based testing methods, we might write further tests that check that -32,768, …, -1, 0, 1, …, 32,767 are correctly represented:

smallint_test() ->
  case for -32,768,
  ...
  case for -1,
  case for 0,
  case for 1,
  ...
  case for 32,767

There are dozens of other PostgreSQL types that also need testing. Maintaining the test data and verifying its correctness rapidly becomes a maintenance overhead.

Instead, with property based testing we define a “generator” for our test inputs. For smallint such a generator would be integer(-32_768, 32_767). The tool uses this generator to create valid test input, resulting in a property based test:

smallint_test() ->
  ?FORALL(
    I,
    integer(-32_768, 32_767),
    begin
      Result = pbe("select $1::smallint", [I]),
      Result == I
    end).

The above says that ?FORALL integers (called I) in the range of -32,768..32,767, when executing select $1::smallint with I we expect the Result to be identical to I.

We let the property based testing tool generate our test values using the model that we have supplied. The tool can be configured to generate as many test cases as we want using this model.

In PostgreSQL floating point numbers can be represented by the real or double precision which are inexact, variable-precision numeric types. The property test generator float(inf, inf) will generate floating point numbers between -∞ and ∞. However, real has a precision of just 6 decimal digits, whereas double precision has 15. Rounding might take place if the precision of an input number is too high. We must ensure that the returned value is correct to the right level of precision for the type that we are testing.

We can refine the generator using ?LET for real with 6 decimal digits of precision:

?LET(F, float(inf, inf), precision(F, 6))

A value from the generator of float(inf, inf) will be represented by the variable F. The call to precision(F, 6) ensure that F has the appropriate number of decimal digits for the type.

Our test for the real now looks like:

real_test() ->
  ?FORALL(
    Real,
    ?LET(F, float(inf, inf), precision(F, 6)),
    begin
      Result = pbe("select $1::real", [Real]),
      Result == Real
    end).

Further more complex examples can be found here, including PostgreSQL types of point, polygon, path, line segment, circle, box, line.