How To Tame Postgres Unit Testing
Do you have a complex stored procedure defined in a Postgres database in which there are countless different scenarios that need to be tested? One option is to use a specific DEV/QA Postgres instance where the stored procedure is run with different sets of input data and verify the results are as expected through queries. This approach has some disadavantages:
- the tests are not easily repeatable
- there is a high chance that the testing will conflict with other work on the same instance
- running different tests concurrently on the same stored procedure is not advisable
To combat these disadvantages, we devised a simple framework where:
- tests can be written in pure SQL and (mostly) avoid conflicts on the instance
- testers can stand up a new local Postgres instance (or multiple instances for concurrency) to avoid conflicts and execute easily repeatable tests
Pure SQL testing with plpgunit
If unit testing in pure SQL is desired (maybe the team is most comfortable with SQL), there is a very simple open source framework that allows one to write easily repeatable tests as Postgres functions – plpgunit on Github. There is no complex installation necessary for this framework, simply run the installation SQL script located in the install directory. That’s it! Once that is done, tests can be written as functions under the “unit_tests” schema that do not accept any arguments and return the data type “test_result.”
For example, if you have a table with two columns—id and value—and a stored procedure that adds one to each value in the table, you could write the following two functions to test this procedure:
CREATE FUNCTION unit_tests.add_one_positive() RETURNS test_result AS $$
DECLARE message test_result;
DECLARE result boolean;
DECLARE actual integer;
DECLARE expected integer;
BEGIN
INSERT INTO test_values VALUES
(1, 25);
SELECT * FROM add_one() INTO actual;
expected := 26;
SELECT value FROM test_values WHERE id = 1 INTO actual;
SELECT * FROM assert.is_equal(actual, expected) INTO message, result;
TRUNCATE TABLE test_values;
-- Check if test failed
IF result = false THEN
RETURN message;
END IF;
-- Otherwise test passed
SELECT assert.ok('Test passed.') INTO message;
RETURN message;
END
$$
LANGUAGE plpgsql;
CREATE FUNCTION unit_tests.add_one_negative() RETURNS test_result AS $$
DECLARE message test_result;
DECLARE result boolean;
DECLARE actual integer;
DECLARE expected integer;
BEGIN
INSERT INTO test_values VALUES
(1, -25);
SELECT * FROM add_one() INTO actual;
expected := -24;
SELECT value FROM test_values WHERE id = 1 INTO actual;
SELECT * FROM assert.is_equal(actual, expected) INTO message, result;
TRUNCATE TABLE test_values;
-- Check if test failed
IF result = false THEN
RETURN message;
END IF;
-- Otherwise test passed
SELECT assert.ok('Test passed.') INTO message;
RETURN message;
END
$$
LANGUAGE plpgsql;
Now all that needs done is to execute the tests with the following code:
BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
SELECT * FROM unit_tests.test_details;
ROLLBACK TRANSACTION;
This gives the following result:
message result
Test completed on : 2017-11-23
16:45:57.693974 UTC. Y
Total test runtime: 8 ms.
Total tests run : 2.
Passed tests : 2.
Failed tests : 0.
Skipped tests : 0.
List of failed tests:
----------------------
NULL
List of skipped tests:
----------------------
NULL
End of plpgunit test.
id test_id function_name message ts status executed
7 6 unit_tests.add_one_positive() 11/23/17 11:45 TRUE TRUE
8 6 unit_tests.add_one_negative() 11/23/17 11:45 TRUE TRUE
The unit_tests.begin() will execute all functions in the unit_tests schema with a test_result return type. The unit_tests.test_details table contains a list of the tests and the statuses. The reason you execute this inside of a transaction is so that the data does not persist in the database.
Let’s say that there is a bug introduced in the stored procedure in which 2 is added to the values instead of 1. Running the same tests gives the following results:
message result
Test completed on : 2017-11-23
16:57:01.875077 UTC. N
Total test runtime: 9 ms.
Total tests run : 2.
Passed tests : 0.
Failed tests : 2.
Skipped tests : 0.
List of failed tests:
----------------------
1. unit_tests.add_one_positive() -->
ASSERT IS_EQUAL FAILED.
Have -> 27
Want -> 26
2. unit_tests.add_one_negative() -->
ASSERT IS_EQUAL FAILED.
Have -> -23
Want -> -24
.
List of skipped tests:
----------------------
End of plpgunit test.
id test_id function_name message ts status executed
9 7 unit_tests.add_one_positive() ASSERT IS_EQUAL FAILED. 11/23/17 11:57 FALSE TRUE
Have -> 27
Want -> 26
10 7 unit_tests.add_one_negative() ASSERT IS_EQUAL FAILED. 11/23/17 11:57 FALSE TRUE
Have -> -23
Want -> -24
Now you have easily repeatable unit tests for your stored procedure that are written purely in SQL. This method of execution also reduces the chances that multiple testers on the same QA instance will conflict with each other (though for longer running procedures this could still be an issue).
Embedded Postgres - Automatically stand up a new local instance of Postgres
Now let’s turn our attention to the approach that will solve the remaining problems of test isolation and execution concurrency. It requires test case developers to be fluent in Java. There is an open source library for Java (Postgresql-embedded on Github) in which with just a few lines of code a new Postgres instance can be spun up locally - no additional installations of any kind required. It will download the binaries for any Postgres version you specify for the correct operating system and spin up the instance automatically. Using this library in combination with JUnit is a very effective way to unit test Postgres stored procedures.
The following code is all that is needed to start a local Postgres instance (under most system configurations):
private static EmbeddedPostgres postgres;
private static String url = null;
public static void startPostgres() throws IOException {
postgres = new EmbeddedPostgres();
url = postgres.start("localhost", 5433, "dbname", "username", "password");
}
Use the URL string to connect to the instance using a Postgres JDBC driver:
Connection conn = DriverManager.getConnection(url);
To specify a specific version of Postgres (maybe your production database is not on the latest version and you would like to test with the same version), then pass in an instance of IVersion interface to the EmbeddedPostgres() constructor:
postgres = new EmbeddedPostgres(() -> "9.6.3-1");
There are a lot of different settings and options that can be configured. For more control over configuration, the objects hidden by EmbeddedPostgres can be instantiated. The PostgresConfig class handles the settings of the actual Postgres instance, whereas the IRuntimeConfig interface handles the settings for downloading/extraction of the Postgres binaries.
First, we need a PostgresStarter
PostgresStarter runtime = PostgresStarter.getInstance(runtimeConfig);
,>
An instance of IRuntimeConfig can be obtained from the build method of RuntimeConfigBuilder. The defaults method should be called first passing the Command.Postgres (for starting Postgres), followed by any other named parameters to override default settings, and then finally the build method:
IRuntimeConfig runtimeConfig = new RuntimeConfigBuilder()
.defaults(Command.Postgres)
.artifactStore(artifactStore)
.build();
ArtifactStore is part of the runtime config and contains settings for the downloading of Postgres binaries; it works just like the runtime config:
IArtifactStore artifactStore = new PostgresArtifactStoreBuilder()
.defaults(Command.Postgres)
.download(downloadConfig)
.build();
The same goes for DownloadConfig. Say if you need to specify an HTTP proxy to be able to download the Postgres binaries over the network:
IDownloadConfig downloadConfig = new PostgresDownloadConfigBuilder()
.defaultsForCommand(Command.Postgres)
.proxyFactory(new HttpProxyFactory("proxy.host", 1111))
.build();
There are other interfaces that control the runtime configurations which you may want to explore. Once PostgresStarter is instantiated, then we need to create an instance of PostgresConfig where we configure the actual instance of Postgres:
PostgresConfig config = new PostgresConfig(
() -> "9.6.3-1",
new Net(),
new Storage("dbname"),
new Timeout(),
new Credentials("username", "password"));
Finally, start the local instance of Postgres:
PostgresExecutable exec = runtime.prepare(config);
PostgresProcess process = exec.start();
The URL for the connection will need to be constructed manually (see the EmbeddedPostgres class source code for an example). Once testing is complete, the instance can be stopped:
process.stop();
Running your tests in JUnit, you will start by defining a @BeforeClass JHnit hook where you start the embedded Postgres instance and a @AfterClass where you stop the instance. You can also take advantage of the startPostgres() method you defined earlier as well as define a new stopPostgres() method:
public static void stopPostgres() throws IOException {
process.stop();
}
@BeforeClass
public static void start() throws IOException {
startPostgres();
}
@AfterClass
public static void stop() throws IOException {
stopPostgres();
}
You also want to define @Before and @After hooks to set up the database and tear it down after:
@Before
public void setUp() throws SQLException {
try (Connection conn = DriverManager.getConnection(url)) {
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(
"CREATE TABLE test_values\n"
+ "(\n"
+ "\tid integer,\n"
+ "\tvalue integer\n"
+ ");");
}
}
}
@After
public void tearDown() throws SQLException {
try (Connection conn = DriverManager.getConnection(url)) {
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(
"DROP TABLE IF EXISTS test_values;");
}
}
}
Finally, you can create your test method(s):
@Test
public void test() throws IOException, SQLException {
try (Connection conn = DriverManager.getConnection(url)) {
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(
"INSERT INTO test_values VALUES\n"
+ "(1, 25),\n"
+ "(2, 41);"
);
stmt.executeQuery(
"SELECT * FROM add_one();");
try (ResultSet rs = stmt.executeQuery(
"SELECT value FROM test_values\n"
+ "WHERE id = 1")) {
while (rs.next()) {
Assert.assertEquals(26, rs.getInt(1));
}
}
}
}
}
If the tests themselves run for a long time, you may want to run them concurrently. This library can handle that as well.
To achieve this, you need to change the start() and stop() methods to instance methods, change the JUnit hook labels to @Before and @After respectively, remove the JUnit hook labels from the setUp() and tearDown() methods, and call setUp() and tearDown() in the start() and stop() methods respectively:
@Before
public void start() throws IOException, SQLException {
startPostgres();
setUp();
}
@After
public void stop() throws IOException, SQLException {
tearDown();
stopPostgres();
}
Also, make sure that your PostgresProcess and URL String variables are not static.
To execute JUnit tests concurrently, you can use an open source library such as the JUnit-toolbox:
@RunWith(ParallelRunner.class)
public class PostgresUnitTestBase {
// Start/stop, setup/teardown, and @Test methods
}
To get this to work nicely with the Embedded Postgres library, you will want to first download/extract the Postgres binaries in an @BeforeClass JUnit hook method:
private static PostgresExecutable exec;
@BeforeClass
public static void getPostgres() throws IOException {
PostgresStarter runtime = PostgresStarter.getInstance(
new RuntimeConfigBuilder().defaults(Command.Postgres).build());
PostgresConfig config = new PostgresConfig(
() -> "9.6.3-1",
new Net(),
new Storage("dbname"),
new Timeout(),
new Credentials("user", "pass"));
exec = runtime.prepare(config);
}
,>
This prevents multiple downloads/extractions due to concurrent start() method executions. In case you do not want to leave the extracted binaries in a temporary director (since a new one is created on each execution of the class), then you should add the following to clean up after all the tests have finished:
@AfterClass
public static void removePostgres() {
exec.stop();
Files.forceDelete(SubdirTempDir.defaultInstance().asFile());
}
Now you have a way to easily start a new instance of Postgres resulting in full isolation of tests that are easily repeatable, as well as a way to execute the tests concurrently, but what if the team is not comfortable with coding in Java?
Pure SQL testing with Embedded Postgres
Using the non-concurrent Embedded Postgres code from the previous section, you can use JUnit’s Parameterized class to dynamically generate JUnit tests for each of your plpgunit tests:
@Parameterized.Parameters(name = "{0}")
public static Collection data() throws IOException, SQLException {
start();
List data = new ArrayList<>();
try (Connection conn = DriverManager.getConnection(url)) {
try (Statement stmt = conn.createStatement()) {
stmt.executeQuery("SELECT * FROM unit_tests.begin();");
try (ResultSet rs = stmt.executeQuery(
"SELECT function_name, message, status, executed FROM unit_tests.test_details;")) {
while (rs.next()) {
data.add(new String[]
{
rs.getString(1),
rs.getString(2),
rs.getString(3),
rs.getString(4)
}
);
}
}
}
}
catch (SQLException e) {
Assert.assertNull(e);
}
return data;
}
private String functionName;
private String message;
private String status;
private String executed;
public PostgresUnitTestSql(String functionName, String message, String status,
String executed) {
this.functionName = functionName;
this.message = message;
this.status = status;
this.executed = executed;
}
[]> []>
You will also need to add the following line before your class definition:
@RunWith(Parameterized.class)
This command will:
- start up a local instance of Postgres (with the start() method you defined earlier
- run the plpgunit tests with unit_tests.begin()
- select the results of the tests from unit_tests.test_details
- run a JUnit test for each row in test_details. asserting that the test was executed and passed
Next, you'll need to:
- remove the @BeforeClass and @Before annotations from start() and setUp(), making the setUp() method static
- call setUp() from start()
- run the plpgunit install script as well as any unit test function definitions in setUp()
This has to be done because the @Parameters class actually runs before the @BeforeClass and depends on the local Postgres instance to both exist and contain the unit tests. Unfortunately, this method of execution will not allow for concurrent test execution, so if this is a requirement then test case developers will need to code in Java.
The last step to SQL-izing this test framework is to remove any hardcoded unit test functions. One solution is to keep a list of unit test function scripts in a text file on the resource path, then read and execute the script files listed in the text file. This is not the only solution; it really depends on the circumstances of how these tests get executed. With this last step completed, the Java code should not need any modifications and you now have a fully isolated unit testing framework in which tests are written in pure SQL code.
