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
as an example value, with a “traditional” non property based test:
smallint_test() -> ?assertEqual(-1, pbe("select $1::smallint", [-1])).
pbe is a function that performs the actions of parse, bind
and execute. PostgreSQL
select $1::smallint and bind the parameter $1 with
-1. When we execute this statement we assert that the database
-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
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
Result to be identical to
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
double precision which are inexact,
variable-precision numeric types. The property test generator
float(inf, inf) will generate floating point numbers between -∞ and
real has a precision of just 6 decimal
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
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
F. The call to
precision(F, 6) ensure that
the appropriate number of decimal digits for the
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.