Add a pgx driver

[wokalski]

The current postgres driver depends on libpq and pg-config. Statically linking libpq is very cumbersome. I believe that a pgx driver would be a minor but noticable quality of life improvement for many (including me).

[paurkedal]

Yes, I have been planning on adding pgx support at some point. My main motivation is to make it possible to run Caqti under MirageOS. It will require some refactoring of how drivers are instantiated, but looking into this has become relevant now with the upcoming pgx release supporting it.

[yomimono]

With dream wanting to target SQL databases via caqti, this has become even more relevant and desirable :)

[paurkedal]

Yes, this will be high priority after the next minor release. I have a sketch, but it needs more work, esp. on the driver API, which is currently C-binding centric.

[yomimono]

That's great to hear! Please do let me know if there's any way I, as a PGX and MirageOS user who's also written against Caqti, can help. :)

[paurkedal]

@yomimono Your help will be very welcome. I except to finish the upcoming release next week, and will post an update when I push my current work.

[paurkedal]

There is now a branch with the work-in-progress on the pgx driver. Some of the remaining work I can think of at the top of my head:

• [ ] Add TLS support. Pgx uses conduit for async, but lacks TLS for lwt due to missing support for STARTTLS.
• [x] ~~Consider: Add support in pgx for reporting how many rows were affected by an update.~~
• [x] We should iterate directly on the result rows instead of first collecting them into a list, though there is a clash between pgx and caqti which needs to be sorted out.
• [x] The code needs to be checked for uncaught exceptions caused by runtime issues.
• [x] The time zone should be set to UTC via the setup stub.
• [x] ~~Check if there is something to implement for reset.~~ (we would need a reset on the PGX side)
• [x] Add caqti-mirage.
• [x] Add request log.

This branch breaks backwards compatibility, but I think only because an `Unsupported case was added to Caqti_response_sig.S.affected_count and Caqti_connection_sig.Convenience.exec_with_affected_count.

[paurkedal]

I'm working on the caqti-mirage sub-package and I could use a second opinion on the API regarding how to pass the stack argument. In the first approach, you first "connect" to the network stack, then you get a module with the Caqti connect, with_connection and connect_pool functions. In the second approach, the stack connector is flattened into the connect, connect_pool and with_connection functions, i.e. they are immediately available but takes an stack argument.

• Unikernel usage: first approach and second approach.
• MirageOS API: first apprearch and second approarch.
• Git diff.

I think the latter is a nicer approach, as I suspect the double connect with an intermittent module does not add anything and may only cause confusion. PGX uses the former approach, meaning that Caqti in the second approach creates a local module with the PGX interface per call, though I don't think this has any negative impact.

If you are familiar with MirageOS APIs, do you have a sense of the best practise?

[paurkedal]

The third item in the above list was resolved by delaying, in the PGX driver case, the request to the database from the start of the call invocation to the retrieval functions invoked by its callback. This involves extending the range of errors allowed by retrieval functions to include request errors (non-backwards compatible), and documenting.

The to_stream function still needs to assemble the result into a list before returning, though. This could be solved for lwt and async (or the upcoming multicore), but not for the current blocking.

[paurkedal]

I created a branch caqti2 which will go into Caqti 2.0.0. It includes pgx and mirage support. For the latter I went for the "second approach" above.