Using Caqti (maybe ppx_rapper?) for complex queries

Learning
1

Hi all, I am writing a GraphQL API using Dream and Caqti to interface with PostgresSQL.

The problem is that I am facing a query with 44 columns as result set, some fixed parameters and a set of optional WHERE/ORDER BY/LIMIT clauses. This is a very classic scenario where the user can filter/sort the data.

Right now the code is written in a single query with ppx_rapper and the fixed parameters. Subsequent filtering/sorting/limiting is done…in the client code, in OCaml, which is far from ideal.

Any advice on how to incrementally construct types for the Caqti query (for both parameters and result set)?

I don’t think ppx_rapper can help in this situation, but if I leave ppx_rapper I’m about to embrace a “44 closed parentheses” scenario that seems diabolical.

2 Likes
2

Is the challenge in accommodating the optional clauses? If so, you can still use ppx_rapper quite easily, by using postgresql’s coalesce to make omitted optional values for their corresponding clauses no-ops.

e.g. if you have a price column that you want to optionally use to constrain results, you can write the clause like so:

...
   and price >= coalesce(%int?{price}, price)
...

…and pass along the optional (i.e. int option) price parameter as just another argument to the ppx_rapper-generated query function.

You might need to get slightly clever with certain types and query semantics, but I’ve not yet been stymied in using ppx_rapper queries with many optional criteria and limits.

2 Likes
3

Does ppx_rapper allow composing together query fragments? E.g. I normally prefer to have a clause like and (%int?{price} is null or price >= %int{price}). If I could put this in a fragment I could build a complex query out of smaller parts.

1 Like
4

I don’t think fragment composition is possible in ppx_wrapper or at least not from the documentation.

5

This is a very nice idea and it solves the filter and I think also limiting with something like:

... LIMIT coalesce(%int{my_limit}, 10000)

Do you know some SQL trick for sorting (ORDER BY xxx ASC/DESC)?

6

Alas, no, there’s no way to parameterize order by AFAIK.

And, no, ppx_rapper provides no query composition facilities, as it uses pg_query for checking query syntax and types, which (again AFAIK) only operates over full queries.

2 Likes
7

I will continue to sort the data “client-side,” but being able to remove sorting and limiting is a lot.