Run SQL on DynamoDB Tables (Using a Visual Editor)

DynamoDB and SQL

One of the most popular complaints about DynamoDB is its learning curve. Not only is the way it works radically different from most of the SQL-based databases we know, but also, the syntax used to query and manipulate data is hard to grasp.

The same fact was also causing migrations to be much harder to conduct. If you're going from, let's say, a MySQL engine, it's not a regular "heterogeneous" migration. It's a massive effort in which you need to rethink and reimplement your database structure and querying language, often effectively leading to extensive rewrites.

This is about to get a bit easier because DynamoDB now supports PartiQL - an SQL-like language for querying and manipulating data. PartiQL provides a familiar SQL syntax while abstracting the underlying complexities of DynamoDB's architecture.

Dynobase and SQL / PartiQL

We're excited to announce that Dynobase supports querying and modifying data using PartiQL / SQL too!

Moreover, with Dynobase you can now also bookmark, stream, and browse the history of your PartiQL/SQL queries that ran on your DynamoDB tables. This feature is particularly useful for tracking changes and debugging issues.

Learn more about using PartiQL for querying DynamoDB here.

FAQ

What is PartiQL?

PartiQL is an SQL-like query language for accessing your data from multiple data sources like S3, Redshift, QLDB, and now - DynamoDB. PartiQL is compatible with SQL in most basic cases.

What are the limitations of PartiQL / SQL in DynamoDB?

The limitations of querying and modifying the data remain the same. You still cannot update or delete records in batches; each update or delete operation must be performed on a singular record. In order to delete or update multiple items, you need to run multiple queries or use our built-in Terminal feature which allows running conditional and mass updates.

How expensive is using SQL with DynamoDB?

The costs of running queries and modifying data in DynamoDB using PartiQL and using standard syntax are exactly the same.

Should I use PartiQL or learn DynamoDB syntax?

While PartiQL allows you to use SQL-like syntax you're already familiar with, it abstracts away the details of how DynamoDB works. This may lead to confusion and suboptimal performance in some cases. PartiQL may transform certain queries into scan operations which are slow and generally speaking discouraged unless used with caution.

How can I use PartiQL/SQL to query my table?

There are four ways to start using SQL to query your tables:

• Using Dynobase
• Using the new AWS Console
• Using AWS Athena
• Using SDK/CLI

Are there any prerequisites before I can start using SQL to query DynamoDB?

Make sure that you have dynamodb:PartiQL* IAM actions added to your IAM policies.

Learn more about PartiQL here.

Use Cases and Best Practices

PartiQL can be particularly useful for teams that are transitioning from traditional SQL databases to DynamoDB. It allows developers to leverage their existing SQL knowledge while gradually learning the nuances of DynamoDB. However, it's important to understand that while PartiQL simplifies querying, it may not always be the most efficient way to interact with DynamoDB. For instance, PartiQL queries can sometimes result in full table scans, which are less efficient than using DynamoDB's native query capabilities. Therefore, it's crucial to monitor the performance of your queries and optimize them as needed.