How to pass lists of values and list values

[donut]

Looking through Caqti_type, I don't see a way to handle situations like multi-row inserts

INSERT INTO goomba VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?);

where number of the sets of values to be inserted can only be known at run time.

Another example would be something like

SELECT a, b, c FROM goomba WHERE b in (?);

where ? is a list of values whose length is not known until run time.

Is there a way to handle these problems that I'm not seeing? I was expecting something like Caqti_type.list at least for the second example. I don't know where to start with the first. Would Caqti_type.custom be able to take care of these situations? If so, are there any examples that handle situations like these?

[paurkedal]

In your first case, notice that you need a different query string for each number of values inserted. Since the Caqti_request.t represent a single query string, the way to do this is to construct the query dynamically, passing ~oneshot:true or caching the constructed queries. Secondly, notice that the grouping apparent in the VALUES clause have no bearing on the structure of parameters; it is just 9 independent parameters. In Caqti that translates to a concatenation of parameter tuples corresponding to the inserted rows. The Caqti_type.tupN constructors are associative, so the parameters don't need to be flattened.

How to work with dynamic queries as something which deserves to be documented, as it's not obvious unless one is experienced with GADTs. The most straight forward approach is to build the parameter type along with the parameters, so that the dynamic type can be existentially packed:

module Dynparam = struct
  type t = Pack : 'a Caqti_type.t * 'a -> t
  let empty = Pack (Caqti_type.unit, ())
  let add t x (Pack (t', x')) = Pack (Caqti_type.tup2 t' t, (x', x))
end

In your suggested use case, when using ~oneshot, the helper functions could also generate the Caqti_request.query, or at least the VALUES part. Otherwise, one could use a memoized function of the number of values which generated the query.

The same technique applies to your second case, though you hint towards a possibility of passing an aggregate object though the DB client API. As far as I know, only PostgreSQL among the DBs currently supported has array types. The way to pass an array there is to encode it as a string. So, we need to know the quoting convention for the database in case of string arrays, but that's doable as long as we're coding for a single database. I am not sure whether it is possible to have something more complex then arrays of elementary types, but at least it would be possible to add an array/list type for each elementary type. This would likely be limited to a minority of RDBMS (PostgreSQL currently), and in my opinion better avoided unless one really needs the features of a particular RDBMS.

[donut]

Thanks for the detailed response, @paurkedal. I didn't know about GADT and am reading up on it. For now, this is what I came up with to address the first case:

module Dynparam = struct
  type t = Pack : 'a Caqti_type.t * 'a -> t
  let empty = Pack (Caqti_type.unit, ())
  let add t x (Pack (t', x')) = Pack (Caqti_type.tup2 t' t, (x', x))
end

let source_fields (module DB : Caqti_lwt.CONNECTION) source_id fields =
  let placeholders = List.map (fun _ -> "(?, ?, ?)") fields |> String.concat ", " in
  let (Dynparam.Pack (typ, values)) = List.fold_left (fun pack (name, value) ->
    Dynparam.add Caqti_type.(tup3 int string string) (source_id, name, value) pack
  ) Dynparam.empty fields
  in
  let sql = Printf.sprintf
    "INSERT INTO source_field (source_id, name, value) VALUES %s" placeholders
  in
  let query = Caqti_request.exec ~oneshot:true typ sql in
  DB.exec query values >>= Caqti_lwt.or_fail

Having done this, my second case seems pretty straightforward. I was tripped up a bit when I tried to return (typ, values) and got the "[...] would escape its scope" errors. I'm guessing that the values of 'a must be unpacked in the same scope they're set.

[...] though you hint towards a possibility of passing an aggregate object though the DB client API.

That was not intentional. Just a clumsiness of wording born of ignorance. :)

[paurkedal]

Your code looks good.

The use of GADTs makes the API less beginner-friendly, though it's a powerful tool for strengthening typing in general, and decisive here to separate requests from parameters while maintaining strong typing of parameters. And I should add, postponing the choice of driver, since knowing the driver we could have passed the parameter-encoder itself upon request construction.