Why doesn’t the insert/upsert rows API endpoint return addedRowIds when keyColumns is not set ?
This behaviour is documented, but it’ odd.
By design, this endpoint can create new rows when keyColumns is set and as long as we pass rows that have values in keyColumns which do not match any existing rows.
So why doesn’t the API return addedRowIds in this case?
As a suggestion, it would have been much more helpful if this endpoint always returned two keys:
addedRowIds : empty Array if no rows are added. otherwise per above
updatedRowIds: similar idea, and also return an empty Array if no rows were updated.
This would be much more helpful for downstream processing of the response.
Hi @Nad - Thanks for the feedback, and I can see how it would be useful to have that information in all cases. I believe the reason we don’t return the added row IDs when key columns are set is due to the collaborative nature of a Coda document.
An edit to the document must be reconciled with the edits from other users / clients, which can take some time to complete. Rather than have your API request wait for that to finish, it instead just starts the process and returns a
getMutationStatus endpoint allows you to check when those asynchronous mutations have complete.
When you are only adding rows the API can go ahead and assign the new row IDs immediately, since it doesn’t matter what data is already in the table. However once you specify a key column it needs to compare your rows with those already in the doc, which means it needs to wait for the data to be reconciled before it knows which rows are new and which are updates.
I think we could in theory return the added rows IDs in the
getMutationStatus result, but that information isn’t available today.
That makes sense @Eric_Koleda , thanks for the explanation.
I would be perfectly happy if getMutationsStatus endpoint returned addedRowIds (and ideally also updatedRowIds).
An alternative, which would probably require a lot more engineering, is to have separation of concerns:
- a ‘insert many rows’ endpoint that only does bulk row inserts,
- a ‘update many rows’ endpoint which takes an array of rowIds and does bulk updates. Currently the only way to update many rows is to repeatedly call the ‘update row’ endpoint, which is very slow due to so many round-trips to server and back.
I am building a python library for Coda (not yet published) and am loving the extensive API and the great documentation. However, I do find it lacking in ability to bulk query and bulk update rows by rowIDs, and I sincerely hope the team addresses this.
I think the existing
upsertRows endpoint already meets the “insert many rows” scenario, but I can see how a bulk update endpoint would be useful. I’ll track that request, but I wouldn’t expect to see it actions in the near term.