DynamoDb.SQL – a SQL-like external DSL for Amazon DynamoDB

Amazon’s DynamoDB is a won­der­ful prod­uct – scal­able, durable, fast, with pre­dictable laten­cy num­bers unlike Sim­pleDB. How­ev­er, the only gripe I have with DynamoDB is that there is no built-in sup­port for a query lan­guage, which makes life rather dif­fi­cult when you want to per­form a query or a scan against the data you have in DynamoDB.

Whilst the stan­dard AWS SDK for .Net pro­vides a num­ber of dif­fer­ent ways to per­form queries and scans:

  • using the low-lev­el Ama­zon­Dy­namoD­B­Client
  • using the Table helper class
  • using the DynamoD­B­Con­text ORM

none of these ways are easy to use and the few attempts to use them in our code­base left a bad taste in my mouth and an exter­nal DSL is des­per­ate­ly need­ed to make it eas­i­er to express the query we’d like to per­form against data stored in DynamoDB.

Introducing DynamoDb.SQL

It is because of these lim­i­ta­tions that I decid­ed to add a SQL-like exter­nal DSL on top of exist­ing func­tion­al­i­ties to make it eas­i­er for .Net devel­op­ers to work with DynamoDB.

Hav­ing spent a cou­ple of week­ends I have put togeth­er a sim­ple library called DynamoDb.SQL, which you can down­load and try it your­self from Nuget here. This library adds an exter­nal DSL on top of the exist­ing func­tion­al­i­ties of the .Net AWS SDK and allows you to query and scan DynamoDB using nat­ur­al, SQL-like syn­tax.

Using this syn­tax, a query can be expressed with the gen­er­al for­mat:

image

where @HashKey and @RangeKey are spe­cial key­words to mean the hash and range key in your table, and oper­a­tor can be one of the allowed com­par­i­son oper­a­tors for a query request :

=, >=, >, <=, <, BEGINS WITH and BETWEEN .. AND ..

Sim­i­lar­ly, a scan can be expressed with the gen­er­al for­mat:

image

where operator1 to oper­a­torN can be one of the allowed com­par­i­son oper­a­tors in a scan request :

=, !=, >=, >, <=, <, CONTAINS, NOT CONTAINS, BEGINS WITH, IS NULL, IS NOT NULL, BETWEEN .. AND .., and IN (…)

To learn more about the syn­tax and how to use DynamoDb.SQL, take a look at the Get­ting Start­ed guide here.

 

Links:

DynamoDB API – Query­ing and Scan­ning

DynamoDB – Query­ing and Scan­ning using low-lev­el Ama­zon­Dy­namoD­B­Client

DynamoDB – Query­ing and Scan­ning using Table helper class

DynamoDB – Query­ing and Scan­ning using high-lev­el DynamoD­B­Con­text