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 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.

Here are some useful links:

Alex DeBrie , do you have any thoughts on PartiQL?  Any chance for a new chapter in the book? =D
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.

Hope that helps!
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:
  1. 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.
  2. 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.
Levi replied
  ·  1 reply
Hi Alex, loving the book so far, thanks for writing this!

I did find what looks like a minor syntax mistake.  Would you like to hear about things like that, and where should we send them?
Thanks, Levi!

Yep, it'd be great to hear about those fixes. A bunch of folks have helped me out already, which I'm grateful for :). 

You can leave them here, or you can email them to me -- . Thanks!
I'll post them here in the hopes that you can avoid getting duplicate bug reports:

Cheatsheets, page 31/54 has two 'KeyConditionExpression'

Cheetsheets, pages 30, 41, and 52 use the same copy/paste typo "CleintRequestToken"
Again, loving the book so far.  Your tips have already improved the schema I'm currently working on, by opening up several new access patterns.