Rails has the concept of Scopes, helper methods that modify queries on Models.
When using honeysql, this is as simple as (where …) or even adding a helper function that adds to the given query. However, Scopes have the additional benefit of letting you set a default scope, so you can say that all User queries return non-deleted users without having to call .where(deleted: false) everywhere.
The motivating situation for this question is adding an is_deleted column to a widely used table and realizing that I’ll now have to find and update all of the individual queries or take the opportunity to do some refactoring.
How do you accomplish this with honeysql+jdbc?
An alternative worth considering is implementing something similar at the DB level.
E.g. in PostgreSQL you can rename all the relevant tables by adding _impl to them and add views that do SELECT * FROM table_impl WHERE NOT is_deleted OR current_setting('my_app.show_deleted', TRUE) = 'true'. Defined like this, views will be auto-updatable (any insert/update/delete will be propagated to the actual table), the app code won’t have to change a thing. And running a query that returns also the deleted items will only need to execute SELECT set_config('my_app.show_deleted', 'true'); within the same transaction.
Apart from being query simple it also gives you a benefit of this working absolutely everywhere, even bodies of triggers (assuming you switch them over from the table to the view).
But if you want to do that in code, it depends on the levels at which you want such scopes to be applied.
If it’s only at the outer query level where the FROM clause includes the relevant table explicitly and not via some nested query, then you can simply use a wrapper around the sql/format function that does that simple check and adds a WHERE clause.
But if you need to add the clause to every single SELECT from the table, including joins, CTEs, nested queries, that simple check now becomes a full HoneySQL DSL parsing task. At least, I’m not aware of any alternative. An additional problem, which might or might not be the case in your codebase, is that the same table can be referred to in many ways, depending on the quoting settings: :the-table, :the_table, :public.the-table, :public/the_table, and so on.