Use dummy H2 DB for testing with Jooq

Question

I would like to find a reliable way to test my Maria DB schema with Jooq. This is what I have now:

• let Jooq with Gradle to extract an XML schema from the real DB (i.e. Maria instance) via org.jooq.codegen.XMLGenerator, this step will generate a maria_information_schema.xml;
• use this schema to generate all the Java Classes.

To test all the classes I will write I have develop a technique:

• fire a H2 embedded in ram database;
• build a java InitDatabase.java class which manually set a DB schema as similar as possible with the MariaDB one;
• preform all the test on the in ram DB.

This procedure works perfectly as far as I don't change something on the real DB and I forgot to do the same on the InitDatabase.java class.

My question is: is there a way to use the XML schema or the generated java classes to create an H2 Database with the same schema as the MariaDb one without writing manually all the create table statement?

Thanks

Answer 1

This is an open ended question with no obvious, "correct" answer. jOOQ's official take here is that you may want to re-consider using alternative RDBMS for testing what you could be doing with your target RDBMS directly, specifically using testcontainers.

You could combine this approach with jOOQ's code generation for a more streamlined development process.

In short, jOOQ's recommendation is to use:

• Flyway or Liquibase for database change management
• Testcontainers for code generation
• Testcontainers for integration testing

Other, equivalent products are obviously also possible.