JOOQ and Liquibase Syntax error in SQL statement

Question

I want to generate code using Liquibase migrations

public class JooqCodegen {
    public static void main(String[] args) throws Exception {
        Database database = new Database()
            .withName("org.jooq.meta.extensions.liquibase.LiquibaseDatabase")
            .withProperties(
                new Property().withKey("rootPath").withValue("migrations/liquibase/changelog"),
                new Property().withKey("scripts").withValue("master.yaml")
            );
        
        Generate options = new Generate()
            .withGeneratedAnnotation(true)
            .withGeneratedAnnotationDate(false)
            .withNullableAnnotation(true)
            .withNullableAnnotationType("org.jetbrains.annotations.Nullable")
            .withNonnullAnnotation(true)
            .withNonnullAnnotationType("org.jetbrains.annotations.NotNull")
            .withJpaAnnotations(false)
            .withValidationAnnotations(true)
            .withSpringAnnotations(true)
            .withConstructorPropertiesAnnotation(true)
            .withConstructorPropertiesAnnotationOnPojos(true)
            .withConstructorPropertiesAnnotationOnRecords(true)
            .withFluentSetters(false)
            .withDaos(false)
            .withPojos(true);

        Target target = new Target()
            .withPackageName("ru.tinkoff.edu.java.scrapper.domain.jooq")
            .withDirectory("scrapper/src/main/java");

        Configuration configuration = new Configuration()
            .withGenerator(
                new Generator()
                    .withDatabase(database)
                    .withGenerate(options)
                    .withTarget(target)
            );
        GenerationTool.generate(configuration);
    }
}

The scheme of the problem table:

CREATE TABLE links
(
    id   BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    link VARCHAR(200) NOT NULL,
    CONSTRAINT unique_link UNIQUE (link)
);

But for some reason, the code that is validly executed in Liquibase does not want to be executed through JOOQ

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Синтаксическая ошибка в выражении SQL "CREATE TABLE links\000a(\000a    id   BIGINT PRIMARY KEY [*]GENERATED ALWAYS AS IDENTITY,\000a    link VARCHAR(200) NOT NULL,\000a    CONSTRAINT unique_link UNIQUE (link)\000a);"; ожидалось "HASH, CONSTRAINT, COMMENT, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"
Syntax error in SQL statement "CREATE TABLE links\000a(\000a    id   BIGINT PRIMARY KEY [*]GENERATED ALWAYS AS IDENTITY,\000a    link VARCHAR(200) NOT NULL,\000a    CONSTRAINT unique_link UNIQUE (link)\000a);"; expected "HASH, CONSTRAINT, COMMENT, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:
CREATE TABLE links
(
    id   BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    link VARCHAR(200) NOT NULL,
    CONSTRAINT unique_link UNIQUE (link)
); [42001-214]

As I mean, H2 does not support such an SQL query, I cannot find information on how to fix it. Would it be the right decision to just take and fix my SQL migration to one that JOOQ supports?

Answer 1

Looks like GENERATED ALWAYS AS IDENTITY is giving the problem.

You could try AUTO_INCREMENT instead of GENERATED ALWAYS AS IDENTITY, if nothing works you could take a look at this link I believe it will help you with this type of things. (Look at text that has highlighted words)

It would look like:

CREATE TABLE links
(
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    link VARCHAR(200) NOT NULL,
    CONSTRAINT unique_link UNIQUE (link)
);

Hope it helps you!