Jooq querying tables with soft delete

Question

I want to write soft-delete records instead of hard-deleting them in jooq. But what about querying tables with soft delete?

So I dont want to write

SELECT id, name FROM users WHERE DELETED=false

But instead I want just to write

SELECT id, name FROM users 

Answer 1

We currently do not support this sort of SQL transformation out of the box, but you can extend jOOQ using a VisitListener. Soft deletion is a simpler case of row level security as described in this blog post here: https://blog.jooq.org/implementing-client-side-row-level-security-with-jooq

One example approach how to implement this would be to add an interface to all generated tables, if they have a DELETED column:

interface SoftDeletable {
    TableField<? Boolean> deleted();
}

And then match that in your VisitListener, adding a predicate in case the FROM clause contains a reference to this table. You could then also make this work for aliased tables, derived tables, etc.

We're working on improving jOOQ's internal query object model in jOOQ 3.13 and future releases, such that these use cases are easier to implement than with a VisitListener. Specifically, support for soft deletion is on our roadmap: https://github.com/jOOQ/jOOQ/issues/2683