MySQL's answer to MongoDB & NoSQL

Planet Earth
1

MySQL 5.7 introduced a new JSON datatype, allowing the creation of tables with fields that store arbitrary JSON objects. The SQL syntax was also extended to support JSON CRUD operations on these fields.

MySQL 5.7.12 introduced the X Plugin, enabling MySQL to function as a Document Store, communicating over a new protocol called the X Protocol and accessed via a NoSQL Mongo-like query language called X DevAPI, supported in Node.js using mysql-connector-nodejs.

Here is some more information:

X DevAPI User Guide

MySQL Internals Manual: Chapter 15 X Protocol

MySQL Reference Manual: Chapter 3 Using MySQL as a Document Store

Node.js Connector - an asychronous promise-based client library for the X DevAPI protocol

According to the docs for the MySQL Connector/Node.js library, it requires Node.js 4.2 and above. This means that it will only be officially supported in the next version of Meteor 1.4 which will contain a compatible version of Node.js.

Also, its asynchronous interface relies on promises instead of traditional callbacks. This means, you will need to call it using Meteor’s Promise.await() method instead of Meteor.wrapAsync().

I also note that this asynchonous interface can be used for executing traditional SQL statements as well.

Here are a couple of articles I found on Percona’s blog with practical examples demonstrating its usage:

Using MySQL 5.7 Document Store with Internet of Things (IoT)

Asynchronous Query Execution with MySQL 5.7 X Plugin

I see this new MySQL plugin as a great enabling tool to allow Meteor & Node.js applications to leverage the benefits of SQL-relational and NoSQL paradigms with a single instance of the MySQL DBMS.

For example, key packages like Meteor Accounts can now be far more readily ported to MySQL with far less effort than before.

I also see this as having the potential to fuel increased adoption of Meteor & Node.js in more conservative tech companies that have existing ecosystems based on MySQL and relational data, who may be suspicious or hesitant in trusting NoSQL databases like MongoDB in enterprise applications.

5 Likes
2

PostgreSQL has JSON too, by the way.

3 Likes
3

For whatever this is worth, I used Postgres 9.5 as a NoSQL database… One of the most painful and bad choices in my coding life.

I had nested data. The nested data/ SQL storage problem is well known, and I did not want the expensive and annoying 4+ table JOINs. But, I’ve never used a NoSQL db. Enter JSONB for Postgres! Fantastic.

Whoops.

If you have single level JSON data, I think it can work. But if you have truly nested data (e.g. arrays of objects in an object), you will jump off a building with Postgres. Updating key value within an embedded object array required a query spanning 40 lines with such common language words as “ordinality.” (Truth be told, I should have read the Postgres field, manipulated it on the server, and updated the entire field. It felt dirty doing it, and I was worried that a bad update would erase all of the data, but it would have saved many hours)

I don’t know if MySQL improves on Postgres or not. My advice is if you need NoSQL, use it. But if you need to store blobs of JSON that don’t need to be manipulated, a NoSQL/SQL like Postgres and now MySQL can work fine.

4

To be true, I’m still not seeing the advantages of NoSQL save for some very limited applications like handling streaming data or something.

Rare is the case where data on a website is not relational. And when you’re using NoSQL databases you’re actively throwing away decades of development to re-implement relational mapping just so that you have the same feature set as a standard SQL db.

I honestly don’t get this love for NoSQL. Granted, due to the schema-less architecture you can whip up prototypes faster. You’re paying for that in maintenance later.

Am I the only one who sees the deep irony behind packages like https://www.npmjs.com/package/node-simple-schema ?

3 Likes
5

I agree with your overall sentiment. However, I’ve come to appreciate NoSQL.

The big advantage is for sites that work heavily with third party APIs and read speeds. For instance, creating the nested structure I wanted (vehicle inspection report) took 3 seconds for a 4 table JOIN. And that’s on a big, dedicated machine.

But going further, I needed to see that status of those inspection reports for multiple vehicles at once. (3 seconds plus 3 seconds plus…)

Obviously I could have added more tables for statistics with triggers, etc. Or I could just use a NoSQL database.

6

Yes, prototyping is faster. I’m talking about data integrity, though. Foreign keys, defaults and other constraints. Not to mention strict data types.

And if you’re talking about query speeds: I’m really doubting that a NoSQL will fare better in such scenarios.

1 Like
7

It really depends if you are a well funded project with a rock solid spec (such as a consulting project for a bank) or you are venturing into the unknown and just trying to get business information ASAP (startup/MVP/prototype).

iI you are building something on a contract for TD Ameritrade, great. You have all the time and money in the world. You also probably know exactly what you need, and you aren’t wondering if there is a customer base because you already have a paying customer. Compare this to a startup prototype you are building on sweat equity. The prototype is just to see if anyone would even use it… in the latter case, there is a high probability you won’t be around to gain from any lower maintenance cost afforded by a SQL database.

These are two completely different beasts. You shouldn’t even bother tryring to compare them. In one case the obvious choice is noSQL unless you are a SQL ninja who is just plain faster in SQL.

8

Where did I say anything about a “rock solid spec”? That’s a bit of a strawman, considering that having to change the schema will also hurt you a lot on a NoSQL db in a number of scenarios (the same ones where it will hurt you using SQL, by the way)

Plus, you’re once again ignoring maintenance. Yes, rapid prototyping is nice. A prototype is not something you want to use in production, however.

Also, please. I’m currently working on a C# app using Azure to sync data between clients. To facilitate offline sync the whole app has access to a local SQLite db. I need a new column? I simply add another property to my model, compile and run the whole thing and, presto! The SQLite db now has a modified table schema.

You’re making it sound as if working with SQL is some onerous task. It’s not. It’s probably just hurting some people that they have to give some thought on how to structure their db. And not simply being able to wing it (and end up with a half-assed unnecessarily denormalized abomination as a result).

9

I wasn’t targeting your response in particular with my comments, rhywden. I just hit reply to the last comment and was giving my two cents in general.

That said, it sounds like you are comfortable with SQL, so it makes a lot of sense to use it (on top of all the other upside you pointed out).

I was mainly just saying if that, (1) assuming you are a person who is comfortable with a NoSQL, (2) you aren’t an expert in SQL or data/schemas (and all the implications of your decisions) AND (3) you are mainly just trying to get something out of the door to test your idea, then you can probably skip the investment of time in learning and implementing an SQL solution.

More general thoughts (not in response to you rhywden)
I also think the word “production” gets thrown around a lot without context (which is vital). One man’s production is another man’s alpha test. Again, if you are startup who is unsure if ANYONE is going to even use your new tinder for pet rabbits app (where you have no paying customers and may never), your idea of what is okay for “production” is going to be different than if you are webdev shop putting a fortune 500’s app into “production” (for an app you are being paid on contract now to build).

1 Like
10

I’m unsure on how an SQL schema is such a heineous task to implement that it would bar someone from getting an app out of the door quickly.

I also dare say that SELECT firstname FROM Users WHERE id=1234 is quite a bit more readable than db.find({id:1234},{fields:{firstname:1}})

Granted, the JOIN syntax is a bit more complicated. But it has the huge advantage of being 1 query. Instead of n+1-queries like you have to do with MongoDB (granted, you can use $aggregate. Which in turn disables reactivity and also has non-trivial syntax).

11

AWS Aurora. Mongo -> fork -> AWS Optimized.