Jooq & R2DBC execute DDL

Question

I have my tables schema generated from the database, I'm trying to execute the ddl to create the tables using an r2dbc connection to an h2 in memory, but I cannot figure out what should I do.

The DSLContext.ddl method returns a Queries object that is not a Publisher and I cannot find any way to execute those queries with a Publisher but only doing a .block() method that is failing because r2dbc doesn't allow for blocking calls.

Any help?

score 0 · Answer 1 · answered Feb 27 '23 at 07:47

0

There's a pending feature request #12207 to let Queries extend Publisher<Integer> for convenience. It might not be implemented, though. There's no obvious choice between:

Executing the queries as a Batch
Executing the queries one-by-one
Executing the queries as a Block

There are probably preferenes, but not necessarily a strict winner. Turning a Queries object into a Batch can already be done using DSLContext.batch(Queries), and starting with jOOQ 3.18 and #14708, also with Queries.batch(). Batch is already a Publisher<Integer>.

Alternatively, you can obviously do this:

Flux.fromIterable(queries);

answered Feb 27 '23 at 07:47

Lukas Eder

211,314
129
689
1,509

Solved using the `Batch` solution, `Flux.fromIterable(queries);` seems to not be feasiable as it then the single `Query` is not a `Publisher` and its `execute*` methods cannot be invoked as using r2dbc they are failing because blocking operations – rascio Mar 04 '23 at 18:31
Nope seems the batch solution doesn't work with H2 db :\ – rascio Mar 05 '23 at 11:04
@rascio: Well, if you have a minimal reproducer, you could report it here: https://github.com/jOOQ/jOOQ/issues/new/choose – Lukas Eder Mar 06 '23 at 07:43

score 0 · Accepted Answer · answered Mar 05 '23 at 11:04

It seems DDL with R2DBC have weird issues in JOOQ, the only way I found to make it work across different databases (MySQL, H2) is, execute the raw SQL of the DDL manually with:

    public static void initDB(ConnectionFactory connectionFactory) {
        var dsl = DSL.using(connectionFactory);
        Mono.from(connectionFactory.create())
            .flatMapMany(conn -> Flux.fromIterable(dsl.ddl(MySchema.MY_SCHEMA))
                .flatMap(query -> conn.createStatement(query.getSQL()).execute(), 1)
            )
            .blockLast();
    }

Jooq & R2DBC execute DDL

2 Answers2