... that is the question on many lips in darkened rooms where Database admins and software Architects dwell among 30 years worth of discarded SQL texts ...

It's a bit of a pig really when you spend 30 years learning the industry standard for database systems only to find some idiot comes along and says, what you really need is something different, and we're going to call it exactly the opposite of what you know! .. politically the term noSQL could have been chosen with more thought.

The problem is that noSQL does actually have merit, and the bigger problem is that it's old technology with a new name. It's not so much putting lipstick on a pig, but more a case of not realising that the pig was actually a first class international model you've just ignored it for 30 years.

So what is noSQL? Well in a nutshell, for those of us who watched it go by, it's PICK DB with an upgraded user interface. Getting a little bit technical, the storage format is JSON blob rather than ASCII formatted with special separators, but it's essentially the same thing. A key / value database engine with fields and attributes stored in a blob field.

Ok, I over simplify, taking Mongo for example, it has it's own query language and lots of exciting stuff like sharding and database replication, but fundamentally, under the hood, key / value DB.

Quick Comparison

Very brief demo of how to create a table of users using SQL and noSQL database technology, then list the contents of the created database. First let's have a go with MySQL (or MariaDB as it is now) ...

>create database demo;
>use demo
> create table users ( id int, name varchar(64), age int );
> insert into users (id,name,age) values (1,"Gareth",99);
> insert into users (id,name,age) values (2,"Fred",21);
> select * from users;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | Gareth |   99 |
|    2 | Fred   |   21 |
+------+--------+------+
2 rows in set (0.00 sec)

Looks fairly familiar and sane, now what about MongoDB?

use demo
db.users.insert({id:1,name:'Gareth',age:99})
db.users.insert({id:2,name:'Fred',age:21})
> db.users.find()
{ "_id" : ObjectId("560572c41708bf21de5695e1"), "id" : 1, "name" : "Gareth", "age" : 99 }
{ "_id" : ObjectId("5605732d0f34207ecd48d6a0"), "id" : 2, "name" : "Fred", "age" : 21 }

Not so different really, although things that immediately jump out at you are;

  • We didn't need to create a database in Mongo (it's implicit)
  • We didn't need to create a table (same)
  • We didn't need to create a schema in Mongo (there is no schema!)
  • Mongo automatically added a unique ID to each inserted record
  • noSQL looks to be less intuitive than SQL, certainly less English

So what about searching?

> select * from users where age=99;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | Gareth |   99 |
+------+--------+------+

And now in Mongo;

> db.users.find({age:99})
{ "_id" : ObjectId("560572c41708bf21de5695e1"), "id" : 1, "name" : "Gareth", "age" : 99 }

Not really that much in it .. but here's the real killer. Let's say we need to have an additional field that only applies to a couple of users, in MySQL we would have to add this field to the schema and it would then be there as an optional field for all users, and altering the table schema is a non-trivial operation. However, let's see it done in Mongo ..

> item=db.users.findOne({age:99})
> item.extra="I'm nearly 99"
> db.users.update({_id:item._id},item)
> db.users.find()
{ "_id" : ObjectId("560572c41708bf21de5695e1"), "id" : 1, "name" : "Gareth", "age" : 99, "extra" : "I'm nearly 99" }
{ "_id" : ObjectId("5605732d0f34207ecd48d6a0"), "id" : 2, "name" : "Fred", "age" : 21 }

Ok, so this isn't the most efficient way of doing this in Mongo, but is does demonstrate the flexibility of not having to worry about a schema. The technically correct (and maybe a little less obvious) we to do this would be;

> db.users.update({age:99},{$set:{extra:'I am a squirrel'}})
> db.users.find()
{ "_id" : ObjectId("560572c41708bf21de5695e1"), "id" : 1, "name" : "Gareth", "age" : 99, "extra" : "I am a squirrel" }
{ "_id" : ObjectId("5605732d0f34207ecd48d6a0"), "id" : 2, "name" : "Fred", "age" : 21 }

Note that this works in the same way as an UPDATE command in SQL, so all records matching the filter (i.e. all users aged 99) would be subject to the update.

Summary

So there's still a place for SQL, certainly in instances where data schema's are very well defined and long-lived, but on the web where schemas need to be very flexible and can potentially change frequently, I'm finding Mongo is much nicer to work with. When you pair this with built-in support for sharding and replication, it does come across as a very attractive alternative, not least for those of us who miss PICK .. and incidentally, that language you're looking at on the Mongo command line, yes, it is Javascript .. :-)