[Possible epic] Formalize and document the /api/sql endpoint

[benesch]

The /api/sql endpoint has some strange semantics that date from the days when it was only used to power internal developer tools. It's become vastly more important in Materialize Platform, where it will be public API: https://github.com/MaterializeInc/materialize/blob/main/doc/developer/platform/ux.md#region-api

We need to shore up the semantics before we promote this endpoint to users. Current problems include:

• It can't handle all possible ExecuteResponses, and may sketchily ignore the ones it doesn't support.
• It doesn't support parameters.
• The SimpleResult maps to JSON in a strange way—e.g., queries that produce no rows are mapped to null.
• The Datum -> JSON conversion has not had too much thought put into it.

This is particularly important because the Platform web UI will be writing lots of code against this endpoint, and the sooner we do the redesign, the less code we'll need to change in the web UI.

The next step here is to write up a design document for the new semantics of the API.

Task issues

• [x] #14066
• [x] #14067
• [x] #14069
• [x] #14070
  • Fix is in #14400, but is blocked on a clippy change making it to stable
• [x] #14071

Nice to have: the semantics of the WebSocket-based TAIL API as well—see the draft PR #11833.

• [ ] #14068

[benesch]

@heeringa how should we think about prioritizing this? On the one hand we jammed in enough improvements to get the web UI unblocked in #11941; on the other hand, the longer we wait, the more code we write in the web UI against the old API. Is there bandwidth for ADAPTER to address in M1?

[heeringa]

@benesch We have the bandwidth to work on this in M1. Will you kindly point me to all existing document on the API?

[benesch]

The API is lightly described in the platform UX doc: https://github.com/MaterializeInc/materialize/blob/main/doc/developer/platform/ux.md#https

The first step here is to write a design doc that fills in the TODOs in that section.

[sploiselle]

@JLDLaughlin @chaas The design for this is closer than further to completion. Do you all have resources lined up for the implementation, or is that something that would be helpful if I worked on, as well?

[benesch]

I think it'll depend on how extensive the required changes are, @sploiselle! I was hoping a good chunk of the work would be documenting the semantics of what we currently have rather than code changes. But perhaps that hope is naive.

[sploiselle]

@benesch A lot of the work is there, but e.g. providing PG-like transactional semantics + supporting parameters requires more code

[JLDLaughlin]

Hm. @sploiselle I don't think we have anyone that can pick that up at the moment. If you have time and are interested, we'd love to have you! If not, would you mind writing up what we'd need to change and I can figure out when we can get it prioritized.

[sploiselle]

@JLDLaughlin great--I'll get a draft of the changes up for review, and then can carry out the implementation. I'll have a clearer estimate of the timing based on the feedback to the proposal.

[sploiselle]

Just noting that all of this is done except for the WebSocket support, which @necaris is tackling. This is in a much better state for e.g. the front end to use as its API w/ Materialize, so any work blocked on this should be able to proceed.

[benesch]

Thanks so much, @sploiselle! This is wonderful. I think we can and should call this closed even without WS support.

I would like to get this API officially documented in the user docs. I think it mostly amounts to copy/pasting the great docs that you added to the UX doc to the user docs. Can you either file a follow up or knock out if it’s easy?

[sploiselle]

Filed #14903 and assigned to myself; will tackle on Friday

[necaris]

Also +1 to calling it complete with the WS support as icing on the cake after :grin:

[benesch]

'tis done! Thanks again, @sploiselle!