Search code examples
javapostgresqlunit-testinghsqldbjooq

How to make jOOQ use test tables during unit tests?

I am struggling to figure out how to unit test my persistence level methods. For production, I use postgres, I had jOOQ generate Java classes from my DB schema. For testing, I have configured an in-memory HSQLDB and included a schema.sql file for spring to create a table exactly matching the one in production DB, each configuration in a separate application.yaml file in src or test.

Now here is the problem. My persistence layer has methods, that directly reference the table class generated by jOOQ, obviously because only that class has member fields corresponding with individual columns. An example:

private Data DATA = Tables.DATA;

public Timestamp findLatestNonRealtimeClose(String ticker) {
    return (Timestamp) jooq
            .select(max(DATA.DATE))
            .from(DATA)
            .where(DATA.TICKER.eq(ticker))
            .and(DATA.REALTIMECLOSE.eq(Boolean.FALSE))
            .fetch()
            .getValue(0, 0);
}

The method simply returns the latest existing date entry for a given stock ticker. When writing test, this method will still go to the real production DB, instead of the testing one. I can't just pass the table as a polymorphic parameter type Table that exists in jOOQ, because I would then have to cast it to Tables.DATA to gain access to columns. If I wanted to use the test DB, I wouldn't even know what type to cast it to, because the HSQLDB table doesn't have a runtime Java representation, and even if I did, the method has no knowledge if it was run as part of a test or not. How do I rewrite the method to account for testing or production run?

In an article authored by Lukas Eder, it is said that you are not supposed to unit test your DB code, but I still would like to do it.

Solution

  • Replace your jOOQ Configuration for your test environment

    I'm not sure why you think that the jOOQ table references are the problem here. You should be able to run the exact same jOOQ query against any type of database without much effort. The only difference should be the way how you configure that jooq instance (i.e. the DSLContext and its Configuration):

    • Production: Use the existing instance
    • Test: Replace it with an instance that connects to HSQLDB and uses SQLDialect.HSQLDB

    Unit testing vs. integration testing

    In an article authored by Lukas Eder, it is said that you are not supposed to unit test your DB code, but I still would like to do it.

    You're misunderstanding what I call "unit testing", and what I call "integration testing" (which is what you're doing). The latter is perfectly fine in my opinion. Of course, I would always suggest to integration test your application against the production database product (i.e. PostgreSQL, e.g. in Docker), but if there are good reasons not to do that, maybe HSQLDB will be sufficient.

    Unit testing would (according to my definition) replace the database with mocks on any level, including:

    The criticism from my article suggests that at some point, mocking a database is equivalent to implementing a database, so why not just use an off-the-shelf database for integration testing, instead.