Introducing PartiQL For DynamoDB

#aws #dynamodb #database #data

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.

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:

You can use well-known language to start using DynamoDB. You don't need to know DynamoDB query language.
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

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

SELECT summary, title FROM "rssfeed" WHERE "id"='23445-inf'

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.