New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
API to create or update records in bulk #2844
Comments
@silentninja I need this API for the "paste" feature that I'm implementing as part of the Usability Improvements project, for which you are a helper. Would you be able to help me out with this? We first need to agree on the API design. Would you be able to propose a design which specifies the endpoint, HTTP verb, request schema, and response schema? I'm not sure who else should be involved in approving the API design, but it might be worth soliciting feedback more broadly once we have a clearer proposal. After we have an approved design, I'll also need a backend engineer to take on the implementation. Might that be you as well? |
Is this meant to be "paste feature"? I can work on the API design and the implementation. I will start working on this once I finish up tasks related v0.1.2 milestone. |
Yes. Thank you. Fixed.
Excellent. Thanks! |
The request will FAIL. Being strict with the request body is much easier and safer than assuming that the user knows about the API behavior.
The "Failing Behaviour" is hard to implement in SQL. The "Record Creation" behavior is quite easy to implement. The steps for the simple implementation of the
This could be accomplished easily using the In order to accomplish the "Failing Behaviour" we would have to
Postgres does not natively support this implementation. So we would have to come up with a very complicated logic that is prone to concurrency issues mentioned in the various threads in the POSTGRES DOCS. I would prefer to avoid going in that direction. |
Your plan looks good to me, @silentninja |
Moving to "High Priority" milestone since this work is 80% complete but Dom will no longer be working on it. I'd like to finish this up soon so that we can make use of this existing work before accumulating too many merge conflicts. |
@mathemancer I'm assigning this to you so that we can get this finished up soon. It can wait until after the 0.1.4 release. |
Limitations of the current API
Use cases
Similar work
Add bulk delete endpoint #2641 added a new endpoint to delete records in bulk
Add existing_import API #1442 added a a new endpoint to create records in bulk
In Matrix @silentninja said:
A "Paste" example
User copies these eight cells:
User pastes the copied data into the sheet at the following starting position:
The front end uses the API to:
First Name
andLast Name
.First Name
andLast Name
.The API responds with the full records for all the records modified and created, allowing the front end to display the new data.
Thoughts on API design
I want to leave this ticket somewhat open-ended to start, in case the backend team would like to propose an API design.
It would be nice to perform these changes within a single transaction. From a UX perspective, if something goes wrong, no changes should be made. That would mean creating one API to handle both updating records and creating records.
How does the API behave when we tell it to update a record which no longer exists?
Pretend we're following the "Paste" example above but in between steps
1.
and2.
another user deletes record 249.For the copy-paste use case at least, we can rely on our our data being rectangular. That is, we don't need to update one field in record 249 and a different field in record 250 — we need to update all the same fields in all records. I think it's fine to bake this assumption into the API, especially since I predict it may be necessary for a performant implementation. For example, we'd would want to avoid a naive implementation which makes one
UPDATE
query per row.Assuming we do bake this "rectangular" assumption into the API behavior, it may be worth considering the ways in which the API design convey that behavior. For example, we might be inclined to design the request body like this:
But with that design, an API consumer could submit a rather strange request to update two records like this:
Because of the potential for requests like the the above, we'd need to clearly communicate the behavior of the API under these circumstances. For record 249, column 2, would the API leave the value untouched? Or would it set the value to
NULL
? Or maybe the request would fail?Just as food for brainstorming, a request schema like this might go further towards communicating this "rectangular" requirement to API consumers. I don't know.
Unlike our proper
records
endpoint, the front end will not have any good way to display cell-level errors for this paste feature, or for the other features I'm imagining we'd build with this API. For that reason, the requirements around the error response are more loose. When the request fails due to some data problem (e.g. constraint violation, etc) the front end will display a toast message. The API should provide enough info in the error message to help the user fix their problem, but it certainly doesn't need to list all the error, or even be super specific about which cell caused the error.The text was updated successfully, but these errors were encountered: