how to use flyway + h2 + jooq codegen together?

Question

I use flyway and jooq to migrate and generate code based on my schema. I have a basic migration script:

CREATE TABLE BOOKS (
                       book_id  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                       title VARCHAR(50) NOT NULL,
                       author_last_name VARCHAR(30) NOT NULL,
                       author_first_name VARCHAR(30),
                       rating INTEGER CHECK (rating IS NULL OR (rating >= 1 and rating <= 10)),
                       UNIQUE (author_last_name, title)
);

and my pom.xml and plugins section looks like this:

<properties>
        <java.version>17</java.version>
        <flyway-maven-plugin.version>10.7.1</flyway-maven-plugin.version>
        <database.user>sa</database.user>   
  <database.url>jdbc:h2:file:./testdb;MODE=MSSQLServer;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS public\;set SCHEMA public</database.url>
        <database.password>password</database.password>
        <jooq.version>3.18.13</jooq.version>
        <flyway.cleanDisabled>false</flyway.cleanDisabled>
        <h2.version>2.1.214</h2.version>
        <testcontainers.version>1.19.1</testcontainers.version>
        <testcontainers-jooq-codegen-maven-plugin.version>0.0.2</testcontainers-jooq-codegen-maven-plugin.version>
    </properties>

...

<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>


            <plugin>
                <groupId>org.testcontainers</groupId>
                <artifactId>testcontainers-jooq-codegen-maven-plugin</artifactId>
                <version>${testcontainers-jooq-codegen-maven-plugin.version}</version>
                <dependencies>
                    <dependency>
                        <groupId>org.testcontainers</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>${testcontainers.version}</version>
                    </dependency>
                    <dependency>
                        <groupId>org.postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>42.6.0</version>
                    </dependency>
                </dependencies>
                <executions>
                    <execution>
                        <id>generate-jooq-sources</id>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                        <phase>generate-sources</phase>
                        <configuration>
                            <database>
                                <type>POSTGRES</type>
                                <containerImage>postgres:15.3-alpine</containerImage>
                            </database>
                            <flyway>
                                <defaultSchema>public</defaultSchema>
                                <createSchemas>true</createSchemas>
                                <locations>
                                    filesystem:src/main/resources/db/migration
                                </locations>
                            </flyway>
                            <jooq>
                                <generator>
                                    <database>
                                        <includes>.*</includes>
                                        <excludes>flyway_schema_history</excludes>
                                        <inputSchema>public</inputSchema>
                                    </database>
                                    <target>
                                        <packageName>example.micronaut.jooqtest</packageName>
                                        <directory>src/main/java</directory>
                                    </target>
                                </generator>
                            </jooq>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>build-helper-maven-plugin</artifactId>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>add-source</goal>
                        </goals>
                        <configuration>
                            <sources>
                                <source>src/main/jooq</source>
                            </sources>
                        </configuration>
                    </execution>
                </executions>
            </plugin>


            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>10.10.0</version>
                <executions>

                    <execution>
                        <id>flyway-migrate</id>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>migrate</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <user>${database.user}</user>
                    <password>${database.password}</password>
                    <url>${database.url}</url>
                    <password>${database.password}</password>
                    <locations>
                        <location>classpath:db/migration</location>
                    </locations>

                </configuration>
            </plugin>

        </plugins>
    </build>

Lastly, I have a simple REST controller and repository for adding books:

@Repository
public class BookRepository {

    @Autowired
    DSLContext dslContext;

    public Book addBook(Book book){
        BooksRecord booksRecord = dslContext.insertInto(Tables.BOOKS).set(toRecord(book)).returning().fetchOneInto(BooksRecord.class);
        book.setBook_id(booksRecord.getBookId());
        return book;
    }

    public List<Book> getAllBooks(){
        List<BooksRecord> booksRecords = dslContext.selectFrom(Tables.BOOKS).fetchInto(BooksRecord.class);
        return booksRecords.stream().map(this::fromRecord).collect(Collectors.toList());
    }

    public Book fromRecord(BooksRecord record){
        return Book.builder()
                .book_id(record.getBookId())
                .author_first_name(record.getAuthorFirstName())
                .author_last_name(record.getAuthorLastName())
                .rating(record.getRating())
                .build();
    }

    public BooksRecord toRecord(Book book){
        BooksRecord booksRecord = new BooksRecord();
        booksRecord.setTitle(book.getTitle());
        booksRecord.setAuthorFirstName(book.getAuthor_first_name());
        booksRecord.setAuthorLastName(book.getAuthor_last_name());
        booksRecord.setRating(book.getRating());
        return booksRecord;
    }
}

I ran "mvn clean install" and jooq did indeed generate my code for me:

However, when I try to add a book by sending a request via Postman, I get the exception:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "public" not found; SQL statement:
select "book_id", "title", "author_last_name", "author_first_name", "rating" from final table (insert into "public"."books" ("title", "author_last_name", "author_first_name", "rating") values (?, ?, ?, ?)) "books" [90079-214]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:632) ~[h2-2.1.214.jar:2.1.214]

I then asked ChatGPT how I can change the schema JOOQ uses to "PUBLIC", and he gave me this solution:

DataSourceConfig.java

@Configuration
public class DataSourceConfiguration {

    @Bean
    public DataSource dataSource() {
        return DataSourceBuilder.create()
                .url("jdbc:h2:file:./testdb;INIT=CREATE SCHEMA IF NOT EXISTS public\\;SET SCHEMA public;")
                .username("sa")
                .password("password")
                .driverClassName("org.h2.Driver")
                .build();
    }
}

JooqConfiguration.java

@Configuration
public class JooqConfiguration {

    @Autowired
    private DataSource dataSource;

    @Bean(name = "jooqConfig")
    public DefaultConfiguration jooqConfiguration() {
        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.set(SQLDialect.H2);
        configuration.setDataSource(dataSource);

        // Set the default schema name
        configuration.set(new Settings().withRenderSchema(false).withRenderSchema(false)
                .withRenderMapping(new RenderMapping().withSchemata(new MappedSchema().withInput("public").withOutput("PUBLIC"))));

        return configuration;
    }

    @Bean(name = "defaultDslContext")
    public DefaultDSLContext dslContext() {
        return new DefaultDSLContext(jooqConfiguration());
    }
}

But afterwards when I send the POST request, I get a different error:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "books" not found (candidates are: "BOOKS"); SQL statement:
select "book_id", "title", "author_last_name", "author_first_name", "rating" from final table (insert into "books" ("title", "author_last_name", "author_first_name", "rating") values (?, ?, ?, ?)) "books" [42103-214]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:502) ~[h2-2.1.214.jar:2.1.214]

Is there any way of getting flyway+jooq+h2 to integrate together?

Answer 1

Why does this happen

You're generating your code using PostgreSQL, whose identifiers default to lower case if case insensitive. With H2, the default case is upper case. jOOQ quotes all identifiers by default, which is why the generated code doesn't work on both RDBMS automatically.

How to solve the problem

You have several options:

• Create the schema in H2 exactly as in PostgreSQL, with lower case identifiers. For this, you'd have to quote all identifiers in your DDL
• Turn off quoting in jOOQ by using Settings.renderQuotedNames
• Transform all identifiers to upper case in jOOQ by using Settings.renderNameCase

The last two options are documented here.