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!
Show HN: A Schemaless Data Store Within Your SQL Database
schemafreesql.com