DEV Community

Cover image for Introducing PartiQL For DynamoDB
Harinder Seera πŸ‡­πŸ‡² for AWS Community Builders

Posted on • Updated on

Introducing PartiQL For DynamoDB

I have been working with AWS DynamoDB (NoSQL database) for last couple of years. One of the frustrating thing with it is that it does not have SQL statement-like capability to interact with tables, not until now.

In late 2019, AWS introduced a query language called PartiQL. And recently it was announced that DynamoDB will now support PartiQL. The PartiQL editor option is currently only available in the new DynamoDB console. If you want to use it, you will need to switch from the old console.

option

PartiQL is a SQL-compatible query language that makes it easy to efficiently query data in DynamoDB using DML statements select, insert, update, and delete. This is huge for DynamoDB users because of the following reasons:

  1. You can use well-known language to start using DynamoDB. You don't need to know DynamoDB query language.
  2. You now have a choice on how you choose to interact with DynamoDB. You can use PartiQL if you don't like to deal with FilterExpressions.

Here are some example of using PartiQL to query DynamoDB table.

1: For given id, return item detail.

SELECT * FROM "rssfeed" WHERE "id" = '23445-inf' and guidislink=false
Enter fullscreen mode Exit fullscreen mode

Full detail

2: For given id, only return summary and title information

SELECT summary, title FROM "rssfeed" WHERE "id"='23445-inf'
Enter fullscreen mode Exit fullscreen mode

Selected fields

Internally the SELECT SQL statement will be converted to Scan or Query operation depending on the WHERE clause. Not being familiar with the internal workings of DynamoDB and cost structure, PartiQL can be a two-edged sword for novice users.

On one hand, it will be easier to execute SQL statements without knowing how DynamoDB internally translates these statements. On the other hand, there are chances that a valid SQL statement will result in a "Scan" operation which will increase the cost. Both in terms of execution latency, consumed capacity and dollar. And no one wants to see that.

I have a feeling in the future PartiQL will address my other frustration with DynamoDB. That is you can not delete all the items in the table without needing to drop & recreating the table. This can be a time-consuming activity.

For now, I am just happy to have PartiQL in my arsenal when working with DynamoDB.

References
  1. PartiQL
  2. PartiQL AWS Annoucement
  3. PartiQL AWS Developer Guide

Thanks for reading!

If you enjoyed this article feel free to share on social media πŸ™‚

Say Hello on: Linkedin | Twitter | Polywork

Blogging: Dev | Hashnode

Github: hseera

Discussion (4)

Collapse
zachjonesnoel profile image
Jones Zachariah Noel

Harinder, yes PartiQL is beautiful recently tried it out on the web console waiting for programmatic access. And this is a good writing!

Collapse
khanh profile image
Khanh Do • Edited

There is already programmatic access to PartiQL with ExecuteStatement and its Batch analog.

docs.aws.amazon.com/amazondynamodb...

Collapse
zachjonesnoel profile image
Jones Zachariah Noel

Awesome. Thanks Khanh. I thought only Java support is available now I see NodeJS and Python as well. Can't wait to get my hands dirty.

Collapse
harinderseera profile image
Harinder Seera πŸ‡­πŸ‡² Author

Thank you. Yes, addition of PartiQL to DynamoDB is a great initiative.