NoSQL No More
technosophos.com
technosophos.com
The only good reason not to use a relational database is that they suck at modeling complex entities and their relationships. Shattering nice entities into small relations to get them normalized, adding join tables to model many to many relationships and all that fun. Everything that is not roughly tree-like is just painful.
More than once I finally decided to just stuff the really complex pieces into a blob - XML or what ever worked best - and just deal with it at the application layer. Not pretty, not fun, but still less painful than modeling and dealing with it in the relational model.
Haven't scaled it yet, though...
They're meant for situations where you have data that doesn't fit well into a relational database model, and you need to run BIG queries (think PageRank) over the entire data in a fault-tolerant way. For instance thinking about PageRank, how many discrete data elements are in a single web page? This is "higher-order" data that in a relational database might have hundreds or thousands or tens of thousands of columns per row.
Of course there are also in-memory transactional databases called NoSQL databases which are meant to be replacements for the traditional relational data store, but those are a very different beast from the original NoSQL data model.
Try out Couchbase Mobile http://mobile.couchbase.com and compare it to best of breed relational sync.
My own little WebSQL Object/Relation mapping library uses promises to get away from callback hell, and it's a pure joy to work with.
It handles creating tables and inserting fixtures before the first connect promise is returned, so that you don't have to worry about the setup process and your data being available.
Creating and inserting entities is as easy as
> var P = new Presentation();
> P.set('name', 'test');
> P.Persist().then(function(result) { console.log('Done!') });
I'm using it extensively in my Chrome plugin, and it runs on 10.000+ clients right now.
Real world offline synchronisation example right here:
https://github.com/SchizoDuckie/DuckieTV/blob/angular/js/ser...
Sadly one cannot use it everywhere as WebSQL is not implemented in Firefox nor IE :(
The good thing is WebSQL works also great on mobile devices.
In the other hand, I'm stuck in how sync with a relational model :(
So, it sync well and broke my relations and transactions or it sync hard but keep my data correct.
> The NoSQL data model - please read as document oriented
> is fundamentally flawed because it forces you into
> denormalization...
Start anywhere, for example, pick your users and create a document for each. Now you take the orders of each user and also put them into the document belonging to the respective user. Fine, that works. Now we come to the products they ordered, and now we have a problem. Unless no product has ever been ordered by two different users we have to duplicate product information and put it into several different documents. And there is the same problem within documents if the user repeatedly ordered the same product.
All interesting problem domains just don't fit the idea of piece of JSON without references, internally or externally. There is of course support for reference in document oriented databases, but then lets just call your documents rows in a table and your references foreign keys and we are almost back at the relational model besides that documents still have a richer model, a bit like a NF² relational model or database with deep support for XML or JSON columns.
> Now you take the orders of each user and also put them
> into the document belonging to the respective user.
> All interesting problem domains just don't fit the idea
> of piece of JSON without references, internally or
> externally.
I am not saying there are no usecases where a document oriented model fits well, but I argue that it is not a good fit for modeling your average business.
These few words are pointing to a fundamental problem I'm already getting annoyed about, as little as I know about "NoSQL" databases so far: The term NoSQL-database is flawed, just as Fowler says in his introduction talk. Theres's document-oriented databases, and array-ish data storages (column family-oriented), there's graph based databases - and they have very, very different strengths.
The article falls flat on that problem as well. Some data is not relational and you are correct that there is a strong overlap between document based storage systems and relational storage systems. However, I'm dealing with time series data, or with lock-graphs. I could shoe-horn those problems into a relational database, but after tinkering around with better storage engines for my problem... I'd call myself stupid for doing so.
So, as the article says: Use the right tool for the job. And stop calling the tools "SQL" and "NoSQL", those terms are useless.
[1] Actually I said that in my first comment, but the wording is just bad and probably way to sensationalistic. What I really wanted to say is that it is a really bad fit for modeling you usual business with user, orders, products and the like.
Safari, Chrome and Opera implement WebSQL using the public domain SQLite library. This means WebSQL is available on almost all smartphones/tablets and on the majority of PCs.
Btw. Mozilla implemented their "NoSQL" IndexedDB on top of SQLite in the first place. Various features like bookmarks are also stored in a SQLite database. As well as in FirefoxOS.
My user's Databases regularly exceed 3 MB.
It's relational data : Serie -> Seasons -> Episodes (sometimes hundreds, sometimes thousands)
Here's a 5MB example: https://dl.dropboxusercontent.com/u/44645464/DuckieTV.sqlite
The project is still in beta, so the database will most likely grow with more tables, easily linking into the existing episodes table with an actors table, for instance.
It's not about SQL it's about a proper relational database with tables, indexes, ACID, etc.
You may remember Google Gears (http://en.wikipedia.org/wiki/Google_Gears ), even back in 2007 one could use the Google Mail (GMail) web interface during a flight, train ride, etc. to read mails, write mails, edit drafts, etc. As soon as the connection had reestablished the data synced between client and server. Later Google rewrote it to HTML5.
There was a trend to move everything to the "cloud". But with the arise of peer-to-peer web applications (WebRTC, etc) and thanks to the bad NSA news we need good client side cache and data storage APIs.
I haven't seen a SQL library as shim on top of IndexedDB. It would be pervert anyway as IndexedDB is implemented on top of SQLite in Firefox.
I would like to ask Mozilla to finally implement WebSQL. With a relational database, more powerful web applications with optional offline support would be possible.
I've just searched for a WebSQL on IndexedDB adapter yesterday and it's certainly possible, but it's a nightmare from the perspective of any sane person. This polyfill I found runs SQLite compiled with CLANG to javascript, which means running 2MB of javascript to support something that they already support 'under the hood'. I refuse to work with that.
There is also a similar one called SQL.js - SQLite compiled to JavaScript through Emscripten (probably a big library too): https://github.com/kripken/sql.js
Change your entity? Regenerate the wrapper and generate a migration.
Problem still is: Nobody is writing tooling for WebSQL. Everybody just seems to automatically grab NoSQL, throwing away 30 years of built up software developing knowledge, including the golden rules of Database Normalisation: Don't duplicate your data, and compartmentalize.
But you need to know the shape of your graph first. The document-store model tends towards giving up the graph entirely and just going with a tree. Us programmers love tree structures. They make things nice and tidy and can be traversed in finite time.
But I've never worked on a project that could easily be modeled as a tree. Unfortunately, this was often realized after I had been working on the project for a year. You know how phenomenally fucked you are having a tree-like data structure when what you really need is a directed graph? You're basically all the fuckeds.
Directed graphs generalize trees, so trying to shoehorn a tree where a directed graph is needed is basically bringing a Fischer Price tool set with you to Habitat for Humanity.
Thus, from experience, I never start with a tree now. I always start with a directed graph. If I learn that the data is a tree after a year and not actually a directed graph, oh well, look at all this flexibility we didn't need.
Now can somebody convince the W3 and Mozilla that there's nothing wrong with implementing SQLite next to IndexedDB so that I can move forward without having to write an IndexedDB adapter for my clearly relational TV Show -> Season -> Episode data please?
https://github.com/SchizoDuckie/DuckieTV/blob/angular/js/CRU...
Foreign Relationships on foreign keys are not difficult
Many to many Relationships are not difficult
SQL is not difficult
Joining is not difficult
Grouping is not difficult
Now try to do that in IndexedDB / NoSQL, and suddenly you're in a world of hurt. It can work, but can it perform? Maybe. With time and patches. So wham, let's throw the option of implementing the probably most well tested piece of software (in the universe probably, see http://sqlite.org/testing.html ) out of the window.
There's nothing wrong with implementing SQLite next to IndexedDB, and its not like W3 is going to send the Internet Standards cops to bust the browser vendors that have.
OTOH, there is something wrong with standardizing "whatever the version of SQLite happens to be used in the most recent version of Chrome happens to do" in a W3C spec. Speccing out an API and a specific supported subset/dialect of SQL for WebSQL that could support multiple independent compatible implementations would be appropriate (and it could even be based closely on what a particular version of SQLite does) -- but no one involved was interested enough in doing that to actually, well, do it, and that's why WebSQL ended up in limbo.
Sqlite has been around for almost 15 years now. It's time to adopt it as a standard. Even if they're not satisfied with it, they can easily put a 'draft' stamp on it and provide people with a working, well-tested way to use it today. Firefox already has the support for it since your internal settings and favorites are also stored in guess what.... SQLite databases!
What's even more confusing is that Mozilla is actually listed on the Sqlite.org webpage as a sponsor, but they refuse to land it in Firefox. All because of hipster politics and creative arguments like the one above.
Sure it can use work. Migrations suck. But it will never evolve any further as long as they refuse to adopt it.
No, because the proposed spec did not actually specify the behavior in a way that was independently implementatble.
The spec did not specify the supported query language, either by simple reference to a specific version of the SQL standard (which would be problematic, because a complete and correct implementation of any, at least recent, version of the SQL standard is rare, and probably inappropriate for the use case), or by more complex reference-with-identified-exceptions, or by just listing out the supported features and expected behavior.
It was, therefore, not possible even in principle to have mutually-compatible, independent implementations (and all of the existing implementations just did "link to some version of SQlite and do whatever it does".)
> Sqlite has been around for almost 15 years now. It's time to adopt it as a standard.
Its a good, widely used, tool -- and one that is rapidly changing. But the whole point of web standards is to specify behavior in a way which permits mutually compatible, independent implementations. And WebSQLDB didn't do that, and didn't really seem to be progressing toward doing that.
> What's even more confusing is that Mozilla is actually listed on the Sqlite.org webpage as a sponsor, but they refuse to land it in Firefox.
SQlite is -- or has been, at least, not sure if it still is -- used in Firefox. What they haven't included in Firefox is a WebSQL database implementation, because they believe -- and rightly so -- that WebSQL database wasn't appropriate as a web standard, and didn't show any sign of heading toward something that would be appropriate as such a standard.
SQLite library is in public domain, has more lines of code test cases than lines of code C library code and its SQL API is stable. Plus the SQL language is well documented: https://www.sqlite.org/lang.html
W3C could also simply just fork SQLite at any time and modify the SQL dialect.
Mozilla and Oracle both are official gold sponsors of SQLite, both companies use the library in their own products though made a lot of effort to create IndexedDB and especially tried to deprecate WebSQL - nice job!
Microsoft has several embedded SQL libraries (JetBlue, JetRed, SQL Server Express) and it would be a piece of cake for them to modify the SQL parser a bit to the WebSQL SQL dialect.
The fact that SQLite is available is entirely irrelevant to any of this. The W3C can't just say "do it like SQLite does it" - that's not a standard. The WebSQL spec that exists says:
> User agents must implement the SQL dialect supported by Sqlite 3.6.19.
Unless someone actually sits down and goes through the standardisation process to define WebSQL properly, this is not the way to go, or the next thing will be "implement this tag like Firefox does".
Istill haven't seen any argument on why it isn't possible to freeze it at some point and agree on the fact that that's already an interoperable standard that works in the field right now.
This is IMO still an the result of a NoSQL biased group of people that decided to go another way for the sake of being cool. SQLite is specced and tested inside out and back and forth (LITERALLY!) You can write a spec on that better than you can write a spec on how IE 6 behaves.
It's still sad that because of Mozilla and Microsoft we have no SQL API in all HTML5 browsers :(
http://en.wikipedia.org/wiki/WebSQL
IndexedDB is fine for storing JSON objects, etc. but a relational database with SQL query syntax, indexes, etc. more powerful and means less code to write. With IndexedDB one has to reinvent the wheel to just get basic query features.
WebSQL is not deprecation, the W3C Working Group Note actually says:
'This specification is no longer in active maintenance
and the Web Applications Working Group does not intend to
maintain it further'.
As SQLite is in public domain, no company would "loose their face" if they choose to use it. They could fork off SQLite and change the SQL query syntax (parser) to whatever the W3C finds suitable. https://www.sqlite.org
Mozilla Firefox and FirefoxOS both already ship SQLite for years and can be accessed by its internal JavaScript API. And several Microsoft products already use it anyway (e.g. Forza Xbox games). Microsoft has of course also various other SQL database libraries like MS Access JetRed (http://en.wikipedia.org/wiki/Extensible_Storage_Engine ), MS Outlook JetBlue (http://en.wikipedia.org/wiki/Extensible_Storage_Engine ) and MS SQL Server Express (http://en.wikipedia.org/wiki/SQL_Server_Express ) the SQL backend originally forked off for WinFS for Longhorn (Vista beta). It would be trivial for Microsoft to choose one of its many SQL engines and add it to IE 12. The same goes for Mozilla (just expose the API of SQLite).
For some reason Oracle and Mozilla pushed IndexedDB. Oracle has conflicting interests, as it owns OracleDB(SQL), MySQL (SQL) and BerkeleyDB (NoSQL and now also SQL support, based on SQLite). Oracle is an official "sponsor" of SQLite development and even ships it as part of it's BerkeleyDB package: http://www.oracle.com/technetwork/database/database-technolo... and https://www.sqlite.org/index.html#consortium_members
Mozilla tried to explain why they prefer IndexedDB over WebSQL: http://hacks.mozilla.org/2010/06/beyond-html5-database-apis-... and by an Mozilla dev https://web.archive.org/web/20130723044210/http://blog.vlad1... (I generally like Firefox and FirefoxOS but not implementing WebSQL is one of the worst actions that of Mozilla org, IMHO).
One can speculate that a less powerful HTML5 API translates in the long run to more SQL server licenses for Oracle and Microsoft. If the web app devs cannot do the processing & storage on the client side, one has to do it on the server side.
Anyway, I hope that we get an SQL API for HTML 5.x that also Mozilla and/or Microsoft implements. As of now WebSQL works fine in Webkit based browser which includes Safari, Chrome, Opera and includes also 95% of all smart phones.
PgSQL is great for a lot of things, but I would argue that if you're using it you're betting on your overall product/service having some other killer advantage than data processing. The competent wing of the NoSQL crowd are using it in strange ways that enable new classes of product and service that cannot be achieved with PgSQL.
My broader point is that if your project fits into pgSQL then you need another unique selling point and the data functions are just an implementation detail of some other aspect of your offering, whereas for many of the people not using that kind of thing their analytics and data systems are their selling point. (That's a backhanded compliment to pgSQL, in that it's easy enough to get right on small systems that any competent developer should be able to manage it, thus reducing the market value though). There is, of course, the blurred line of crazy MySQL deployments, many of which are barely relational.
That doesn't make sense to me because it only really applies when there is a high likelihood of competitors using one product but not the other. Although postgres is doing great, in most markets it's still far more likely that your competitors are using oracle or sql server. So any advantage postgres has -- and I believe there are many -- offers a potential competitive advantage.
For instance, you could argue that data systems are a critical selling point of Heroku, and they use postgres.
I think your point ultimately boils down to: "postgres is not quite at the forefront of certain analytic use cases", which I agree with. It is at the forefront of many other use cases though.
pgSQL does what it does easily enough that it reduces the barriers to entry to such a level for traditional RDBMS workloads (which there are plenty of) that such workloads are simply not economically worth pursuing (especially for startups) except as small components in larger systems where the value add is elsewhere. The "other" world of big data/time series/graphs/nosql is hard to get right, and so is worth more, as if you crack it someone else copying you is decidedly non-trivial, meaning that it alone can form the core of a successful business.
This is a bit like what the web people are trying to do in mobile, where if HTML5 was magically the best cross platform mobile deployment option when we wake up tomorrow the value of mobile developers will collapse, and the web people will then cease to be remotely excited about mobile.
An interesting point. More broadly: if what you're working on is not hard (and awkward), then others can copy your idea easily. Technology like postgres makes a new class of problems easy, and thus you need to find new problems to solve if you want a sustainable business.
Though your last line makes me curious. What is an example of one of these new classes of product and service that can't be achieved with PgSQL?
Who is this "many?"
Keeping data that is by it's nature relational in a relational database is, to my mind, obvious. That it isn't for startups that are building their entire business on data foundations (because it's OLD!) is genuinely mind-boggling to me. I guess I'm the one that's old now.
Although I don't share this view, I cant say its always wrong. If you're building simple MVPs to find product-market fit for ideas why bother? If there's a 90% chance what you're building will never evolve to need those features, why invest in them?
But if your "product" ends up being a 10% survivor you better have a plan to move to something more powerful before you compound too much technical debt from your NoSQL database. Once you start scaling your business and have to face competition, NoSQL becomes productivity tarpit. Queries that take 20 minutes to write and optimize in the SQL realm can turn into day-long exercises in NoSQL.
The reality is most developers aren't cranking out disposable software used to test the market for new ideas. We're working on things that already have a place, we just need to make them better. For us, the best option right now is SQL + maybe some specialized databases for certain purposes (Columnar for analytics, graph for relationship analysis, full-text for text, etc)
They're about as tech-savvy as the people that think voting machines are progress.
A developer being below average (as half of all developers are), using the wrong tool for the job, not understanding things etc will make a mess. A claim that developers can screw things up is uninteresting (and true).
A claim that there is no scenario in which a "NoSQL" database is the better solution is a lot more interesting, and requires more than "some developers could screw it up" to substantiate.
I cannot criticize him for leaving Couch|Mongo if their data is rich in relations, but I would like to read his thoughts on graph databases for instance.
Maybe their data is too large for Neo4J, but for querying n:m relationships, this model can offer advantages.
NoSQL / Document databases got so cool so fast, and were usable with little to no actual know-how that people just lost their minds and used them by default. That was always the wrong decision.
RDBMS are the swiss army knives. They do "all the things". But power, responsibility, etc.
I use various types of NoSQL models for various special purposes, they are great, and should be used, for those purposes. You almost always need an RDBMS. If it's not your gold record (because you need quick writes and can lock a whole document at a time), then you need to replicate to RDMBS for better ad hoc reporting. OTH, if RDBMS is your gold, you may want to replicate to NoSQL for fast lookups.
I'm currently working through a situation where the latter is required. I have hundreds of thousands of records and the need to solve a classic backpack problem (with hundreds of thousands of potential items to go in the pack, each of which can have n instances)... so I've replicated a few billion pre-solved solutions to Azure Table Storage. It solves a particular problem, but its not my WHOLE application's data store.