How to serialize runtime Postgres queries in OCaml using Caqti?

I have a use case where I receive a query from the user as a string. execute the query, and return the output as json.

I'm using the Caqti library, which let me do type safe queries, the problem is that the type of the query depends on the query, which is unknown at compilation time in my case.

I'm receiving the query in a HTTP request and need to respond with the results in JSON, for simplicity, assuming that all columns are strings is admissible. In such case a function with the type string -> string list list which receives the query as string and returns the results as string list list is enough.

-- update --

I created an example, since this requires dune I put it in this gist: https://gist.github.com/dhilst/f1ac36163ba08bbeb8cf6fa806b6aaa2
To run you can initialize a project with dune init project foo, then paste the .ml file from gist in bin/main.ml created by dune, and replace the bin/dune file with the dune file from the gist.

The relevant parts for the question are:

I want to get a string from the user which is a SQL query

  let query: string = Sys.argv.(1) in (* this is the user query *)

Then I want to run this query and receive a string string list with the values. Which may me done by this function

val do_query (query : string) : (string list list, string) Lwt_result.t

where string list is a single row, and string list list is a list of rows. I'm using string list because I don't know how many columns it would have.

Now the problem is that in Caqti I have to express the type of my query. In the example I have a table todos with these columns (id int, content text), I can express the type of the query SELECT id, content FROM todos like this

  Caqti_type.(unit ->* (tup2 int string))
    "SELECT id, content FROM todos"

My problem is that I don't know the query user will input so I can't know what to put in Caqti_type.( ??? ) user_query

In the example I left a function like this

let user_query q =
  Caqti_type.(unit ->* unit (* ??? what I put here? *)) q

q is a string with the user query (which I don't know), I don't know what to put in place of the second unit in the above function, that is precisely my problem.

-- update --

I use Richard's idea but instead of creating a function I used to_jsonb. The solution was to define user_query like this

let user_query q : (unit, string, [ `Many | `One | `Zero ]) Caqti_request.t =
  let cte = Format.sprintf {|
    WITH q AS (%s)
    SELECT to_jsonb(q.*)::text FROM q
  |} q in
  Caqti_type.(unit ->* string) cte

Now it outputs each row as JSON. This is even better because I can send the results row by row so that I don't need to put it all in memory.

I update the gist here: https://gist.github.com/dhilst/d0f878cc19b43c78136d69e035f5370d

OK, so anything involving directly executing SQL from a user makes me very nervous, but presuming you know what you are doing...

Get PostgreSQL to work with you on this, rather than fighting the static typing. If you want JSON returned then just get PostgreSQL to return JSON.

WITH source_rows AS (
    SELECT i, CURRENT_DATE + i AS dt FROM generate_series(0, 2) i
)
SELECT jsonb_agg(row_to_json(source_rows.*))
FROM source_rows;

┌────────────────────────────────────────────────────────────────────────────────────────────┐
│                                         jsonb_agg                                          │
├────────────────────────────────────────────────────────────────────────────────────────────┤
│ [{"i": 0, "dt": "2023-07-03"}, {"i": 1, "dt": "2023-07-04"}, {"i": 2, "dt": "2023-07-05"}] │
└────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

Write a plpgsql function to accept your SQL string, wrap it in the aggregation and use EXECUTE to run the composed query.

• https://www.postgresql.org/docs/current/functions-json.html
• https://www.postgresql.org/docs/current/functions-aggregate.html
• https://www.postgresql.org/docs/15/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN