DynamoDB Dynomite Querying PartiQL SQL Like Support
AWS Docs:
Examples
find_by_pql
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.
select_all
The select_all returns Ruby Hashes.
Product.execute_pql('SELECT * FROM "demo-dev_products" WHERE name = ?', ['Laptop'])
Product.select_all('name = ?', ['Laptop'])
update_pql
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])
delete_pql
post = Post.first
Post.execute_pql('DELETE FROM "demo-dev_posts" WHERE id = ?', [post.id])
Post.delete_pql('id = ?', [post.id])
insert_pql
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.