from Hacker News

API Mismatch: Why bolting SQL onto noSQL is a bad idea

by chris_armstrong on 1/12/23, 12:21 AM with 17 comments

  • by Arch-TK on 1/13/23, 10:12 AM

    What's so weird about this article is that the author recognizes that querying KV databases and querying relational databases is not the same thing and trying to overlay relational semantics over a KV database causes problems but then doesn't seem to take much notice of the fact that the interface ORMs provide (querying graph databases) is also mismatched with relational databases.

  • by rhacker on 1/13/23, 12:28 PM

    I think the one clean SQL bolted onto NoSQL that I've seen is spark. Since spark treats the NoSQL as having a structure that matches the underlying database, but it also runs an SQL layer, it's kind of an interesting way to do it. Now that's also up to the spark "driver" like for mongodb - the driver code has to tell mongo to give it some "shape" for the collection otherwise spark can't work with it. Now it's likely to have a shape, but the driver may skip a random column if it's not present enough.

  • by CSDude on 1/13/23, 9:57 AM

    PartiQL on DynamoDB is just syntactic sugar. DynamoDB is not meant to be used that way, but it's query and insert language can be a bit tricky, PartiQL just a helper for that weird query syntax, you can't do joins or aggregates. PartiQL is supported by QLDB, Redshift as well, to unify a querying language somewhat. Docs should be much more clearer to indicate the dangers of it , to avoid confusion like this.

    But you can scan MongoDB, Elastic with Presto in parallel it works great when you need to run it, a few times. But if you find yourself using a NoSQL data store as relational, or OLTP cases just because you have ability to run SQL on it is going to hurt you and it should be obvious. As with everything in software, it depends.

    We use PartiQL library directly at Resmo https://www.resmo.com because it makes querying the datastore with nested values easier and the its storage independent.

  • by tjansen on 1/13/23, 10:16 AM

    Even weirder than PartiQL is Microsoft's CosmosDB for NoSQL. Its query language is called SQL, despite the database name being NoSQL. Also a very limited SQL dialect, just a bit more convenient than PartiQL. Without joins across tables/containers it is a very different experience than real SQL.

    https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/quer...

  • by joshstrange on 1/13/23, 8:31 PM

    > This got me thinking about DynamoDB (a database I use day to day, and for which I maintain dynaglue, a single-table mapping layer for TypeScript/JavaScript), and made me wonder if a DynamoDB adapter existed.

    I hadn't seen dynaglue when I went looking recently for a TypeScript library for DynamoDB. I played around with a couple of options out there and ended up settling on TypeDORM. Overall I'm happy with it. I find it a little odd that so few TypeScript/DynamoDB wrappers support fetching multiple entities in a single call (Example: PK is userId, SK is userId for the User entity, and SK is addressId for the user's addresses, get user and all their addresses in a single call by only querying with the shared PK). I guess I understand why, you'd need to be hydrating the objects returned from DynamoDB into classes (and thus storing something on the Items in DynamoDB that hint you the class to hydrate into) and it can be weird to get back an array of mixed class instances. In the end I just query for 1 entity at a time or a group of children entities for a given parent and I'm pretty happy overall.

    If you want learn more about Single Table Design with DynamoDB then you should absolutely check out the book: https://www.dynamodbbook.com/ -- I was skeptical as I'm not really a programming book type of guy but this was an amazing resource for how to think about Single Table Design and how to structure your data. There is a hacker news coupon "HACKERNEWS" for $20 off that I found in an old thread and it still works.

  • by TylerE on 1/13/23, 10:20 AM

    Postgres with jsonb is pretty great, though.

  • by tybit on 1/13/23, 10:51 AM

    Tangential to the authors point, but it’s funny to note many new SQL databases(e.g CockroachDB, TiDB, MyRocks) are written on top of RocksDB, a “NoSQL” key value store.

  • by adamzegelin on 1/13/23, 11:14 AM

    See also: Cassandra and CQL.