Get field names at runtime from result?

[edwintorok]

With postgresql it is possible to do a SELECT * FROM and then call #get_fnames_lst on the result to detect at runtime the field names.
This is useful for dynamically updating a UI after introducing a new field in the database (i.e. there is no need to update the application, it automatically learns of new fields and can display them in the UI).

Do results in Caqti always need to have a statically known number? (that makes applications prone to break when the DB schema evolves)

I don't see a way to call an equivalent function in Caqti or Caqti_type, have I missed a function?

[paurkedal]

Would you not also need to inspect the type of the columns and convert them to a Caqti type? Or are treating them as strings?

I think your suggestion would be difficult to integrate with the main API for the following reason. One thing Caqti adds is decoding of rows. In order to use wildcard select, we would need to match up the returned rows to individual parts of the decoder (or construct a decoder matching the returned order). This means associating column names to the row type. However, the row type can be nested, making it non-obvious how to recover a flat list of row names. And we'd need to re-bundle fields accordingly. But there is another approach, which I think works well with Caqti:

Needing something similar I hacked up pg_information_schema.tar.gz, which fetches the PostgreSQL information schema and constructs a description of the tables, including column name and types (but here limited to a only few types). This can then be used to construct a select list and the corresponding row type in parallel, as described in the API reference.

I have though about finishing the above and wrapping this into a library, but it's not on my agenda at the moment. I think MariaDB support could be added, as well, but Sqlite3 might be tricky as it lacks static column types.

[edwintorok]

The column types can be fetched too, but it'd be dynamic: https://github.com/perf101/ocaml-sql/blob/dba2815b0886f6c902e277860258ff937cccd8bd/sql.ml#L99-L112

Thanks for the pointer to the dynamic types, I'll have a look whether it is possible to use #get_fnames_lst and #fname, #ftype, and #nfields to dynamically construct something, at least for the postgresql driver.

[paurkedal]

I don't think those methods will be useful with the Caqti API, since the API requires types to be specified up-front while the those methods are only available after fetching the result.