Enhancement: 'assumeThat()'-friendly methods in JdbcDatabaseContainer

[beargiles]

(This is based on experience and experiments.)

Context

Database tests are notorious for a simple typo causing hundreds of tests to fail without a clear cause. A "table doesn't exist" or "column doesn't exist" message doesn't help you unless you're the person who made the breaking changes.

AssumeThat()

Test frameworks usually have a assumeThat()_ methods that mirror the assertThat() methods. When triggered they cause a test to be skipped. You'll see ton of 'skipped test' messages but only a handful of failing tests.

There is one small twist with this approach - you may need to explicitly add assertThat() somewhere. If you have good test coverage it should be enough to just assertThat() you can actually connect to the database.

Implementation pain points

It is a good practice to limit the assertThat() to a single line, and ideally a single function call. (Same with assertThat() but that's not always practical.)

At a minimum that means the development team must implement the proposed methods - including all of the steps required for proper authentication, etc. That isn't a problem when the database accepts username/password credentials but the tested behavior may require the use of other authentication methods.

Proposed methods

There are four key methods:

public boolean countMatchingRecords(String queryString, Predicate<ResultSetMetaData> condition);

public boolean databaseMetaDataMatches(Predicate<DatabaseMetaData> condition);

public boolean tableExists(String catalog, String schema, String tableName);  // and table type?

public boolean recordMetadataMatches(Predicate<ResultSetMetaData> condtion);

These are wrapped by convenience methods:

public boolean recordExists(String queryString) { return countMatchingRecords(queryString, () -> true) > 0; }

public boolean uniqueRecordExists(String queryString) { return countMatchingRecords(queryString, () -> true) == 0; }

public boolean databaseExists() { return databaseMetadaMatches(() -> true; }

public boolean tableExists(String tableName)  { return tableExists(null, null, tableName);

The value of recordExist() should be self-evident. Many, many tests will look like:

XDatabaseContainer db = ...

public void test_adding_record() throws Exception {
    final String query = "select id from my_table where id = 123";
    assumeThat(not(db.recordExists(query)));

    // insert test data

    assertThat(db.recordExists(query));
}

This test will implicitly verify that the database is running but it will not verify that the table is
present. For that you can use

public void test_adding_record() throws Exception {
    final String query = "select id from my_table where id = 123";
    assumeThat(db.tableExists("my_table"));
    assumeThat(not(db.recordExists(query)));

    // insert test data

    assertThat(db.recordExists(query));
}

(Hmm... perhaps there should be a variant that allows the developer to specify a
list of tables to check before executing the query. This has the benefit of reducing
the number of required connections. It's not worth the effort to implement a full
query parser in order to extract the required table and views.)

The predicate allows the developer to test whether a record has been updated.

public void test_updating_record() throws Exception {
    final String query = "select id from my_table where id = 123";
    final Predicate<ResultSet> precondition = (rs) -> "old value".equals(rs.getString(4))";
    final Predicate<ResultSet> postcondition = (rs) -> "new value".equals(rs.getString(4))";
    assumeThat(recordExists(query, precondition)));

    // insert test data

    assertThat(db.recordExists(query, postcondition));
}

The documentation should make it clear that the developer should use
helper methods.

private static final String MY_TABLE_DESCRIPTION = "description";

public Predicate<ResultSet> doesMyTableDescriptionMatch(String value) {
    return new Predicate<ResultSet> (rs) -> value.equals(rs.getString(MY_TABLE_DESCRIPTION));
}

public void test_updating_record() throws Exception {
    final String query = "select id from my_table where id = 123";
    assumeThat(db.recordExists(query, doesMyTableDescriptionMatch("old value")));

    // insert test data

    assertThat(db.recordExists(query, doesMyTableDescriptionMatch("new value"));
}

This is also an example of where a RecordSetMetadataMatcher() could be useful, esp.
if it can be autogenerated. A one-time test that asserts the query results
include String field labeled MY_TABLE_DESCRIPTION (et al) would tell the developer immediately
that there had been an unexpected schema change.

We could extend the additional method mentioned above so it can check for both
table and column/type but I think this condition will be hit far less often than the complete
absence of an expected table.

There's no need to add a test on the result's metadata since it can be done with the
resultSetMetaDataMatches() method or by incorporating it into the predicate (
via rs.getMetaData()).

Metadata Matchers

The databaseMetaDataMatcher() can be used to verify that the tests are running against the expected
database version, using the expected JDBC driver and version, have required server-side extensions loaded,
etc. It can also be used to check how tables and indices are defined.

The latter is is why there's no tableMetaDataMatcher(Predicate<ResultSet>) method. That's enough to check
a field exists and has the proper type but we're often concerned about whether an index exists, esp.
foreign references, etc.

As mentioned above there should be at least one test without the assumeThat():

public void test_database_accessibility() throws Exception {
    assertThat(db.databaseExists());
}

You should also do that with each table if you use the assumeThat(db.tableExists("tableName")) above.

The recordMetaDataMatcher() can be used to verify that the query returns the expected columns. I don't see
this being widely used but it is a natural addition since there are situations where this is important. E.g., you
may want to check whether a temporal field is stored as a temporal class, a numeric value, or a string before
your tests fail because the table schema was changed.

Impact On Existing Code

There is almost no impact on the existing code. Almost.

The methods that require a queryString can use JdbcDatabaseContainer#createConnection(queryString).
Nothing needs to change.

The methods that require a DatabaseMetaData (databaseMetaDataMatcher() and tableExists()) can also
use JdbcDatabaseContainer#createConnection(queryString) IF they have a valid test query. This is typically
something like

• select 1
• select 1 from dual

The problem is that there's no query acceptable to all databases. This is sometimes provided to the
DataSource but I don't think it's available via the Connection object. Plus it's not guaranteed to be
there.

The best solution is probably to add a default value to JdbcDatabaseContainer (select 1), override the
default value in the database containers you own, and document the need to set that value if the
database requires a different value.

An alternative is to add a JdbcDatabaseContainer#createConnection() method - no arguments - using
the same test query. (Or getConnection() since I think some database containers already implement
this.

Potential Enhancement: Custom Annotation

A potential enhancement is replacing the explicit assumeThat() and assertThat() with custom annotations. This is outside the scope of this discussion but I wanted to call it out since it may be a good alternative, esp. at the class level.

A side-benefit to this approach is that it would require any Predicates to either be static or provided as
part of the annotation. This ensures that the predicate is completely isolated from the ongoing tests. This
may not be possible with complex tests but you can make an argument that those tests shouldn't be
implemented as a simple predicate anyway.

Potential Enhancement: Hamcrest Matchers

(Note: this is preliminary and we can definitely implement the filters as standalone tests like shown in the article below. That said it's also possible for an object to provide Hamcrest Matchers that are preinitialized with the required information. There are costs and benefits for each approach.)

Another potential enhancement is duplicating all methods that accept a Predicate<> with ones that accept a Hamcrest Matcher<>. This comes at the cost of adding a dependency.

The benefit of Hamcrest Matchers is two-fold:

• they can perform multiple tasks in a single call
• they can provide arbitrary information - a list of mismatched fields is just the start

A Predicate<> can write these details to the log but it can't provide it to the test framework unless it throws the AssertionException itself. That can break test frameworks.

Writing custom Matchers is not a trivial task but there are tools to help. For instance I have a prototype implementation of a maven plugin that uses TestContainers to spin up and initialize a database (using Flyway, etc.) followed by a table-by-table conversion of table fields to a custom Hamcrest Matcher. I've put off publishing it since I've been looking at complex cases involving multiple schemas etc but it should be more adequate to work with most databases.

(In fact you might want to consider including it in parallel with this proposed extension.)

Here is a top-level discussion of Hamcrest Matchers: Testing with Hamcrest Matchers.

[beargiles]

Small tweak - in my existing code I have separate implementations for noRecordExist(), uniqueRecordExists(), and countMatchingRecords(). The first two methods allow 'fast fail' since they can stop after the first or second record, respectively. This can save a lot of time if the query returns a large number of records and the failure condition is met early.

My original proposal used an interface and those methods were defined as default methods.

It occurred to me this morning that it's easy to add one new method that supports this. No need for duplicate implementations or a hidden helper class that's hard to override in individual database containers.

public int countMatchingRecords(String queryString, Predicate<ResultSet> test, int maxCount) throws SQLException {
    int count = 0;
    try (ResultSet rs = db.executeQuery(queryString)) { // shorthand that omits the various null checks.
        while (rs.next() != null) {
            if (test.test(rs)) {
                // fast fail: return immediately once max count reached.
                if (++count > maxCount) {
                    return -1;
                }
            }
        }
    }

    return count;
}

public int countMatchingRecords(String queryString, Predicate<ResultSet> test) throws SQLException {
    return countMatchingRecords(queryString, test, Integer.MAX_VALUE);
}

public boolean noRecordExist(String queryString, Predicate<ResultSet> test) thows SQLException {
    return countMatchingRecords(queryString, test, 0) == 0;
}

public boolean uniqueRecordExists(String queryString, Predicate<ResultSet> test) throws SQLException {
    return countMatchingRecords(queryString, test, 1) == 1;
}

and for completeness the bit I omitted above looks something like this. Individual databases may be able to
provide additional information why the connection failed, e.g., is the problem due to a bad query?

// TODO: add check whether database is ready to accept connections. If not should there be a brief timeout?
// TODO: should container have way to provide default properties?
protected Connection precheck(String queryString, Properties info) {
    try {
        return conn = db.createConnection(queryString, properties);
    } catch (NoSuchDriverException e) {
        logger.info("Configuration problem with container: {}: {}", e.getClass().getSimpleName(), e.getMessage());
    } catch (SQLClientInfoException e) {
        logger.info("Configuration problem with JDBC Client: ..."); // TODO: add details
    } catch (SQLException e) {
        logger.info("SQLException: {}: {}", e.getClass().getSimpleName(), e.getMessage(), e);
    }

    return null;
}

[beargiles]

A quick followup on the Hamcrest matchers. They are static methods:

public class JdbcDatabaseContainerMatchers<BASE extends JdbcDatabaseContainer<BASE>>  {
    public static Matcher<BASE> tableExists(String tableName) { ... };
    public static Matcher<BASE> countMatchingRecords(String queryString, Predicate<ResultSet> predicate, int maxCount) { ... }
}

The constructor takes the expected values. The Matcher interface includes a method that takes the actual
value - it needs to be the templated class.

Usage looks like:

import static JdbcDatabaseContainerMatchers.*;

public void test_create_table() throws SQLException {
    final String tableName = "test_table";
    assumeThat(db, not(tableExists(tableName));

    // create table...

    assertThat(db, tableExists(tableName));
}

There is a way to register new Matchers so you can avoid this static import but I think this approach is
better in this situation.

This is natural for all of the metadata-based tests. It looks a little odd with the ResultSet-based tests
since this looks a little more natural:

import static JdbcDatabaseContainerMatchers.*;

public void test_create_table() throws SQLException {
    final String queryString = "select * from test_table";
    assumeThat(db.executeQuery(queryString)), ... something like not(equalTo(4)));

    // modify data

    assumeThat(db.executeQuery(queryString)),  ... something like equalTo(4));
}

Finally I mentioned the ability of Hamcrest matchers to provide arbitrary information about actual and
expected values. This is handled by the Description of the BaseMatcher class. The metadata-based methods can't add much information beyond whether or not the table exists.

The ResultSet-based methods could provide a list of the mismatched values for each entry in the ResultSet. This could be done automatically whenever the predicate failed.

However... that's iffy for several reasons.

• You often only want to check whether one or two fields match.
• You often need to specify the precision with floating point and temporal values. E.g., you may not care if a timestamp has dropped the milliseconds. Or you want to compare a Date against a Timestamp.

There are several good solutions to this and I think it's out of scope to discuss them here. I just wanted to point out the benefits and the
drawbacks of using a Hamcrest Matcher instead of the traditional techniques.

In practice I find a central repository of custom Matchers extremely useful since it provides a Single Point Of Truth on what matters. How precise do floating points need to be? Temporal fields? Can a field be case-insenstive? etc. If requirements change you can make the corresponding changes to your tests in a single file instead of tracking down the affected code in hundreds of tests.