DynamoDB Dynomite Querying PartiQL SQL Like Support

AWS Docs:



The find_by_pql returns a Lazy Enumerator with model objects, IE: Post, Product, etc.

Product.execute_pql('SELECT * FROM "demo-dev_products" WHERE name = ?', ['Laptop'])
Product.find_by_pql('name = ?', ['Laptop'])

The items are loaded as needed when Enumerator methods like .each are called. You can also force load all items with .force or .to_a.

Also, notice how the lower-level execute_pql method requires you to specify the full namespaced table name. The convenience wrapper methods like find_by_pql infer the information.


The select_all returns Ruby Hashes.

Product.execute_pql('SELECT * FROM "demo-dev_products" WHERE name = ?', ['Laptop'])
Product.select_all('name = ?', ['Laptop'])


post = Post.first
Post.execute_pql('UPDATE "demo-dev_posts" SET title = ? WHERE id = ?', ['post 1b', post.id])
Post.update_pql('SET title = ? WHERE id = ?', ['post 1c', post.id])


post = Post.first
Post.execute_pql('DELETE FROM "demo-dev_posts" WHERE id = ?', [post.id])
Post.delete_pql('id = ?', [post.id])


Post.execute_pql(%Q|INSERT INTO "demo-dev_posts" VALUE {'id': ?, 'title': ?}|, ['post-1', 'post 1'])
Post.insert_pql("{'id': ?, 'title': ?}", ['post-3', 'post 3'])

Notation Limits

PartiQL currently does not support using hardcoded values. Also, placeholder notation is not supported. Examples:

Product.find_by_pql('name = ?', ['Laptop'])         # works
Product.find_by_pql('name = "Laptop"')              # does not work
Product.find_by_pql('name = :name', name: "Laptop") # does not work

This is because Dynomite uses the AWS DynamoDB SDK execute_statement, and it does not support that syntax. Dynomite can probably add support for it by modifying the expression before passing the parameters to the SDK. Still, it’s not worth the effort because the underlying AWS DynamoDB SDK may add support one day.