I have a use case which need to batch insert or update dynamodb requests, and I could not find way to solve it. Any suggestions are welcome, thank you.
My use case
- Use case #1: For user entity, we will send Add/Update/Delete requests
- Use case #2: For Credit entity, we will only send Update request no matter there is record existed or not.
And Use case #1 and #2 are mixed, we need to batch them all together.
I tried couple of ways like below:
Using batchWrite
The issue is we can only batch PutRequest and DeleteRequest. It fits well for Use case #2, because if there is no record there, PutRequest will create one. If there is, PutRequest will replace it totally. But it didn't fit Use case #1 well, because for Update User request, it will left the database only the modified columns. e.g, if I only update name column, PutRequest will replace totally and only name column will be left. Yes, we can provide all the columns when we update, but it is not ideal.
Using PartiQL
PartiQL does support to batch Insert Update Delete statements. It fits well for Use case #1, but it didn't fit Use case #2 well. e.g., if I only use Insert, when there is already record existed, Insert will complain duplication error. If I only use Update, if there is no record existed, Update will complain conditional request error. If I batch Insert and Update together, PartiQL will complain that two statements could not point to the same record. And I don't think PartiQL will support something like store procedure, query before insert or update.
Summary
Anyway, if there is no better way, I will have to use batchWrite and put all columns into Update request. It is not ideal but it is workable, unlike PartiQL approach which has no walk-around for Use case #2.