Targeting multiple database types with generated JOOQ code

user_1283519 0
Viewed 887

I imagine that it nowadays is quite common to use one RDBMS during development and another RDBMS in production. I'd like to use H2 in development and MariaDB in production for a Spring Boot and JOOQ based application.

Is there some clever way to make the same generated JOOQ code work in both development and production environments, or do I need to generate two sets of code depending on the target environment? If the latter is true, how to do that in a sane way e.g. using the nu.studer.jooq gradle plugin?

Exceptions like this are thrown whenever I try to use the sources generated from a H2 database against a MariaDB server:

org.mariadb.jdbc.internal.util.dao.QueryException: SELECT command denied to user 'foo'@'localhost' for table 'FOO'
Query is: select `PUBLIC`.`FOO`.`ID`, `PUBLIC`.`FOO`.`NAME`, `PUBLIC`.`FOO`.`INFO` from `PUBLIC`.`FOO`

I use the same flyway initialization/migration scripts for both H2 and MariaDB.

jooq mariadb spring-boot h2 spring-jdbc
1 Answers

You don't need to generate two sets of classes for each production environment. jOOQ's generated classes are pretty vendor agnostic, unless you use vendor specific features, e.g. like MariaDB's enum type or stored procedures, etc.

The error you're getting is probably related to one of these things:

  • You might not have a PUBLIC schema in your MariaDB database. You can either make sure the schema names match between H2 and MariaDB, or you can turn off schema name generation in jOOQ by using either Settings.renderSchema on your configuration, or by using a schema mapping.
  • Different databases have different default case sensitivity settings. In H2, by default, all tables are upper case, but this might not be the case in your MariaDB installation. You can either make sure the casing is the same in both databases, or you turn off the generation of the backticks / quotes. This can be done with Settings.renderNameStyle, setting it to AS_IS
  • It might be unrelated to jOOQ and you simply don't have the appropriate privilege to query the table.

Unrelated, a short note on using different vendors for development and production

You said:

I imagine that it nowadays is quite common to use one RDBMS during development and another RDBMS in production. I'd like to use H2 in development and MariaDB in production for a Spring Boot and JOOQ based application.

I really really advise against this practice. You can very easily set up your production database in docker and work directly against it. While H2 can emulate a couple of MariaDB features, it is nowhere near the same. By artificially restricting yourself to the least common denominator between H2 and MariaDB, you're missing out on a lot of cool MariaDB features, including CTE, window functions, stored procedures, etc. etc. And you will constantly fight the subtle differences between the vendors on various levels of your stack.

You should only do this when:

  • You actually need to support several databases in production
  • You really really really benefit from the slightly increased performance, e.g. for integration testing (but I doubt it, with docker).
user_5217990
Related