/ SQL

SQL vs NoSQL Databases - When to use what

If you're confused between choosing a SQL and a NoSQL database or have a doubt regarding your current implementation, let me help you decide for yourself based on the use cases you might have and some basics that you should know.

1. Document database

This is the most interesting part about MongoDB because people mistakenly don't use it like one. The blunder is they put relational data into MongoDB.

It would make total sense to you to use a graph database like Neo4j when you want to query for friends of friends that are not friends with a particular user. It is the graph data structure itself that helps you understand, how easy it would be to do that in comparison to using JOIN multiple times.

Same is the case with a document store. Consider a relational setup of tables where users are stored in User table and their addresses in Address table separately. So on the profile page, you'd JOIN both tables and show the result.
If this is the only requirement, this makes a good use case for a document-oriented database like MongoDB. You could store an array of addresses in the User's collection (the equivalent of a table in RDBMS) itself and this would result in faster read operations. This is because, in an RDBMS, both User and Address tables are queried whereas, in MongoDB, only the user document (the equivalent of a row in RDBMS) which already has the addresses is queried for.
Choice - MongoDB

2. Write Loads

Since MongoDB does not provide you with ACID( Atomicity, Consistency, Isolation, Durability ) properties like that of SQL based databases, this means there is less overhead in creating/saving a document to the collection. So MongoDB performs very well on high write loads. This makes a great use case for real-time analytics and logging too.
Choice - MongoDB

3. Flexibility in Full-Text Search

Imagine a Product has attributes model_name, model_number, features, size, etc. The problem at hand is that we don't know what fields users actually want to search over. You could add text indexes to all these fields in a SQL database that supports it. But with MongoDB, you could put an index as { "$**": "text" } and that would index and make the entire document searchable. Isn't that amazing!
Choice - MongoDB

4. Critical Transactions

RDBMSs provide ACID transaction properties which maintain data integrity so they come out as clear choices in the case of critical data. As an unsuccessful order from a customer (e-commerce) would result in a more direct impact on the dollars than an unsuccessful like on a post (social network).
Choice - MySQL

5. Scale and Availability of Application

Usually, SQL databases are vertically scalable, meaning you could upgrade your machine with higher computation power and RAM, but it hits a bottleneck at some point. Whereas NoSQL databases scale horizontally, meaning you could just add more instances to the cluster to handle more queries. This is also because there are no joins supported. With MongoDB, you get replication( availability ) and auto-sharding( scalability ) out-of-the-box so you don't have to worry about this.
Choice - MongoDB

The choice of a database really boils down to what the use case is.

Sometimes the solution isn't even a database -
An in-memory cache store like Redis (which happens to support persistence too) makes a great use case for storing sessions. Since it's a key-value store, you could cache objects and also the result of a lot of queries which could be your entire web page too! All of this is faster than your database.

Sometimes a third party software demands it -
I started using KeystoneJS for content management and soon came across this issue which says I can't use nested schemas in Keystone. But everybody loved Keystone so I was forced to rewrite my schema as relational to make it work though the whole point of using a document database is not to do so. I could get away with it because optimising read performance was not a priority then.

If you've faced any other use-case that I have missed, please add it in the comments.

Umang Ganvir

Umang Ganvir

Techie. Javascript Evangelist. Passionate Learner. Talks about Motivation and Value Creation.

Read More