We can’t reproduce that on our test system

As soon as a web application makes it to production (and actually draws some attention), the developers are confronted with support cases which are coupled with specific data constellations. The entire test pipeline might be successful, but when a user dares to attempt a checkout without any items in the shopping cart, they get an ArrayIndexOutOfBoundsException. The next support case might not be that easy, because when one single user attempts to change her address, she gets a NullPointerException at a code line where there simply cannot be a null-reference.

“We apologize, but we cannot reproduce this system behavior.” is a nice way of saying that the development team does not have the means to take a data set from production, and run it in a test / development environment. Such an export / import functionality is a crucial part to providing first-class support (and continuously improving the web application).

As funnel.travel uses jOOQ, implementing such an export / import was straight-forward. Our export includes all master data (organization structure, vendors etc.) for a given account. We chose to simply export ‘all’ as that data set is never huge, and the added complexity of choosing what is needed doesn’t outweigh the benefit of a smaller export package. (If you’re Amazon, you probably wouldn’t want to export your entire product catalog, though).

final Writer streamWriter = new OutputStreamWriter(stream, Charset.forName("UTF-8"));

    .formatJSON(streamWriter, JSON_FORMAT)

A caveat here was the required Writer. When streaming to a ByteArrayOutputStream, jOOQ will use the JVM default encoding, while JSON RFC standard asks for UTF-8. Thus the JSON produced via BAOS was actually invalid and could later not be parsed using Jackson.

Also noteworthy is that the JSON format must contain ‘headers’, as these will be used later for the import. using a

new JSONFormat().header(false)

will produce a syntactically valid JSON, but the import will subsequently fail with obscure exceptions.

A second issue with jOOQ JSON handling are custom data types (see this Google Group thread). formatJSON() will use toString() on a custom type, while later the loadJSON() will attempt to load the value as database type. If you’re mapping an enum to an ordinal (eg TINYINT on the database):

enum FOOBAR → formatJSON (using FOOBAR#toString()) → JSON contains ‘FOOBAR’ → loadJSON() → fails to convert ‘FOOBAR’ to a TINYINT (value is set to null)

We’ve written an intermediate workaround to convert the custom data type to the database type (see our comment on the aforementioned group thread), making use of a ‘CustomDatatypeProcessor’. Thus, importing JSON back into the database becomes:

final CustomDatatypeProcessor processor = new CustomDatatypeProcessor<>(OrganizationUnit.ORGANIZATION_UNIT);

final Loader loader = jooq.loadInto(OrganizationUnit.ORGANIZATION_UNIT)//         


Note the incredibly useful “onDuplicateKeyUpdate” option there. If the account has been imported into our test system earlier, we want to be able to import a different trip, and thereby updating the account’s master data. The processor’s “parse” method does the custom data type magic described earlier. The “fields” method returns the fields in the order contained in the JSON (whereas the default loadJSON() expects fields in the order of the current table schema).

Now that we have the data set on our test system, we can debug that NPE when changing the address, and eventually find that in this case the call to the account’s CRM results in a response where a filed is null, despite specs describing the field a @NotNull. Specs adapted, new test to cover that use case, code fixed, CI deploys, and user can change her address.

Bottom line: while there were a few bumps, we nevertheless were able to implement an export / import functionality in funnel.travel within 2 days, largely thanks to the awesome formatJSON() / loadJSON() of jOOQ, and will thus be able to debug using a subset of actual productive data. And the need for that will arise, I’m sure.


In the process of developing funnel.travel, a corporate post-booking travel management tool, I’m sharing some hopefully useful insights into Angular 4, Spring Boot, jOOQ, or any other technology we’ll be using.