cammasmith

Of all the NoSQL databases, DynamoDB is one of the most frustrating to query. MongoDB has an expressive query language. Cassandra has CQL, which is close enough to SQL that most engineers can pick it up quickly. DynamoDB gives you key condition expressions, attribute name placeholders for reserved words, and a query model where you have to pick the right index manually for every call. PartiQL exists but doesn't support JOINs, GROUP BY, subqueries, or most of ANSI SQL.

I built DynamoSQL to fix that.

It accepts any ANSI SQL SELECT statement and converts it to the right DynamoDB API calls. The interesting part is the index selection: given an incoming query, the system inspects the available indexes on the table, figures out the most efficient access pattern, and constructs the call. Partition key lookup when possible, secondary index when that fits better, scan only as a last resort.

JOINs were the most involved part. DynamoDB doesn't support them natively, so they execute client-side after fetching from both tables. The full list of supported SQL includes WHERE, all JOIN types, GROUP BY, HAVING, ORDER BY, LIMIT, UNION, INTERSECT, EXCEPT, subqueries, CTEs, and a wide range of aggregate and string functions.

We're looking for beta testers. If you work with DynamoDB and want to run SQL against it, I'd like to hear from you.

https://dynamosql.com/

My dad and I built a SQL layer for DynamoDB (DynamoSQL) because we kept running into the same problem: we needed to query information across multiple tables (or multiple record types within a single table) and had no good way to do it. We would either duplicate data across lots of records (denormalize everything upfront) and hope we guessed right, do multiple fetches and join in application code, or export somewhere else entirely.

We tried PartiQL but it doesn't support JOINs and the aggregation support is limited. Athena works but the latency and cost model felt wrong for anything that needs to be synchronous. We got so fed up that we went ahead and built something that runs standard SQL directly against existing DynamoDB tables (using partition keys and GSIs so it's not just doing full scans).

How is everyone else getting around this problem? I've heard people say "design your schema correctly and you won't need JOINs" which, sure, but that's not always possible. If you work with DynamoDB, do you have a better solution? Or is this a struggle that most teams just live with?