Show HN: A Schemaless Data Store Within Your SQL Database https://ift.tt/mcD25Oe

Show HN: A Schemaless Data Store Within Your SQL Database Hi HN, we're Eric and Dean, creators of SchemafreeSQL. Its roots go back to an on-line Web App Development Environment we developed back in 1999. It was comprised of an IDE, Web Server, Object Store, Virtual File System, Template System, and polyglot (Java, JavaScript, and Python). Of course, we named it “.OS”. Then we ended up dropping it. But that's a story for another time. It was the ease of use of the Object Store from .OS that we really missed, which brings us back to SchemafreeSQL. It provides an enhanced API to your SQL Database which allows it to function as a Schemaless Objectstore. Yes, "Schemaless Objectstore" might be an oxymoron if you consider that objects have strict "schemas". So what are we talking about here? It started with a simple goal. We wanted to store data just as simply as one could throw a JSON document into a doc store, but while maintaining the power of the data's relations and references in an object sense. What we ended up with was a JSON over HTTP API (for ubiquitous access from any language), that maps any JSON structure into an optimized, static set of indexed tables in any SQL database (MySQL being our first port). Using it, we can immediately store any data we want, without having to create tables and indexes and optimize them. Inserting data is as easy as sending a JSON doc to a doc store, but in SFSQL all nested relations inherent within that document are stored as objects and primitives. And, with a little extra JSON syntax we can use references and eliminate duplication of data (e.g. "#ref": "oid-goes-here"), all from within the same source JSON doc. There is also no added speed penalty for referencing an object. In fact, all parent nodes in the JSON source data are stored as objects. So any object can be referenced from anywhere, allowing for complex relations (e.g. any-to-any), enabling rapid prototyping of data structures (no tables to create), and enabling unique on-the-fly relations if need be. Queries are issued using JOIN-free SQL query syntax (e.g. SELECT COUNT(*) WHERE $s:person.address.state='NY') that eliminates the verbosity of the standard SQL JOIN, yet still maintains access to the primitive and aggregate functions of the underlying SQL database (e.g. FORMAT(), COUNT(), etc.) which simply get passed through. What it's not? It's not a database on it's own and it's not a horizontally scalable big data store. Although, since it can extend practically any SQL database, it's ability to scale is in large part tied to the scaling ability of the chosen SQL database that it's extending. It's also not an ORM as there is no strict mapping of objects in code to objects in the datastore. And yes, there were trade offs in it's design. One design choice was that every primitive be indexed, which results in better performance than against a straight SQL table without the proper indexes, though slower than a straight SQL table with a composite index tailored to that query. But, the ability to JOIN to an existing SQL table within a SFSQL query is on the roadmap which will allow you have the best of both worlds (custom tables w/custom indexes referenced from within a SFSQL query). As well, there is a situation one encounters in indexing choices (but that you won't encounter with SFSQL) that we like to call the "Index Permutation Limitation". Simply put, there is a real limit to the number of composite indexes that you can create for any table/collection. This is especially a problem when - for instance - you want to give end users the ability to query across any of the possible attributes across the myriad product types that a big online store may carry. We get into detail on this point within our FAQ. So what it is? It's an extension to SQL databases that makes storing and querying and modeling and just generally using a datastore... easier. We're hoping it will stir up some interesting use-cases. Thanks in advance for the feedback! https://ift.tt/XQZ9G13 February 11, 2022 at 01:27AM