Using Postgres (and AWS Redshift) DB Link with JOOQ

Question

We are trying to establish a DB link between Postgres and AWS Redshift DB (which isn't a problem) but we are using JOOQ to construct DB Query for the same.

What is working? We are able to write JOIN SQL queries for the data we want to fetch perfectly fine if the two tables were in the same database. For example, if we have a query:

SELECT somefields
FROM dblink('global_database'::text, '
SELECT
    ... some data selected...) t1(username text, location int, createdAt timestamptz)
         JOIN user_meta t2 on "userId" = t1.userId
    AND createdAt between ... some date range ...
WHERE ...'
GROUP BY ...
ORDER BY ... DESC;

Now we are constructing the query with JOOQ:

Query query = dslContext
                .select(somefields))
                .from(table(TABLE))
                .rightJoin(TABLE_TWO).on(getJoinOnCondition())
                .where(whereCondition)
                .groupBy(groupByFields)
                .orderBy(orderByFields)
                .limit((int) pageRequest.getPageSize());

How to establish the DB link in the JOOQ Query?

Answer 1

Do it in 2 steps:

• Generate code for both of your databases separately. Make sure the Redshift tables don't have a schema associated with them, using <outputSchemaToDefault>true</outputSchemaToDefault>, see the manual
• Whenever you use a dblink, wrap your Redshift table in a plain SQL template when you put it in the FROM clause, and use the generated class otherwise, using e.g. the below utility:

static Table<?> dblink(String conn, Table<?> table, Field<?>... fields) {
    return table("dblink({0}, {1}) as {2}({3})",
        inline(conn),
        inline(DSL.using(POSTGRES).render(select(fields).from(table))),
        table.getUnqualifiedName(),
        list(
            Stream.of(fields)
                  .map(f -> DSL.sql("{0} {1}",
                       f.getUnqualifiedName(),
                       sql(f.getDataType().getCastTypeName())
                  ))
                  .toArray(QueryPart[]::new)
        )
    );
}

The usual static import is implied:

import static org.jooq.impl.DSL.*;

And then use this utility like this:

Query query = dslContext
                .select(somefields))
                .from(dblink("global_database", TABLE, TABLE.fields()))
                .rightJoin(TABLE_TWO).on(getJoinOnCondition())
                // You can now use TABLE as if it were a local table
                .where(whereCondition)
                .groupBy(groupByFields)
                .orderBy(orderByFields)
                .limit((int) pageRequest.getPageSize());