As most of you probably know, DynamoDB now supports PartiQL, which offers SQL-like statements which Rick Houlihan says are comparable in performance. (Here's his discussion video with a few basic queries.)
I'm just getting started with a few queries myself, but having a hard time with the documentation. The dynamodb implementation is a subset of the full PartiQL specification, and it's tough to tell what is, or is not supported.
So far my takeaways are:
PartiQL is only supported from DynamoDB, NOT the DocumentClient.
Joins are not supported.
Select as, is not supported.
Select foo.bar becomes bar (can cause conflicts).
Statements are picky around " vs '.
Statements seem awesome for:
Filtering to records with nested attributes.
Updating values in nested attributes!
Statements seem annoying for:
Simple upserts/queries, compared to the DocumentClient.
Hey
Levi
! Good question. I'm pretty cautious around PartiQL. The biggest reason is that it makes it less obvious what's happening in DynamoDB. My favorite part about DynamoDB is how explicit everything is. GetItem, PutItem, etc. are single-item actions that will be constant-time no matter how large your table scales. The Query action is pretty consistent as well, with some caveats around result size and whether you need to paginate. Scan is unpredictable and should be used sparingly.
With PartiQL, these things become less obvious. It makes code reviews harder, as you have to think about what's being used and how that matches the primary key patterns. I worry about the potential of bad queries here.
Additionally, I'm already familiar enough with the DynamoDB SDK that I don't get any benefit from PartiQL. That said, I can see how new folks might like the more traditional SQL syntax.
6.5.5. Adding and removing from a set ... Similarly, you could remove elements from the set with the REMOVE verb: <example using REMOVE>
Should this be using DELETE instead of REMOVE? That's what the docs suggest anyway, but I haven't worked with sets very often, so maybe it's more flexible than I think.
I'm watching the dynamo stream for REMOVE events, to trigger downstream events like sending events, cleaning up dependent records, etc.
It would be very convenient to generate a log event with the user who deleted the item. However, I don't see an easy way to add information to a deleted item, without first updating the item to include a field like `deletedBy`, then deleting the item.
So.. anyone have a clever solution? Or is update/delete the best approach?
I'm kinda curious how many people even use dynamo streams..
We use DDB streams quite a lot at our company. For day-to-day production stuff, but also to migrate tables in production environments.
This case is an interesting one. I see two feasible solutions:
Not using a stream, but instead firing a separate event (EventBridge?) in the lambda/API endpoint where you're deleting the record that contains both the record data and any metadata (like the `deletedBy`) that you might want to save. That might mean that you have to do a separate query to get the record before you delete it though.
In your API, only doing an update that adds the `deletedBy` (not delete the record), and then using DDB streams to trigger a function on update that checks for the existence of that field, and based on that then deletes the record and stores a log event.
