Since the 1980’s and up until the late 2000’s, SQL relational databases reigned dominant and were the only viable data storage solution. Web 2.0 and the internet of things started generating a high volume of unstructured data and this demanded a new type of database different from the relational model. NoSQL databases emerged to meet this demand. There are significant differences between SQL and NoSQL and hopefully this post can help you understand those differences as well as which one will be best for your project.
SQL
SQL stands for Structured Query Language and is the standard language for relational database management systems. SQL statements can be used to read, update or delete data in the database.
Relational databases store collections of data in tables and relationships are created between tables to retrieve data. Emphasis is placed on structure and data integrity.
NoSQL
NoSQL (not only SQL) databases are non-relational databases that store data in documents instead of relational tables. NoSQL databases emerged to handle scenarios with large amounts of unstructured data that traditional SQL was not as optimized for.
Differences
This table outlines a few of the differences between the two database types:
| SQL | NoSQL | |
| Queries | Flexible | Fast queries |
| Scalability | Scales vertically, requires a larger server and is more expensive | Scales horizontally, additional servers can be added relatively inexpensively |
| Schemas | Rigid | Flexible |
| Architecture | ACID properties | CAP theorem |
| Examples | SQL Server, MySQL, Oracle | MongoDB, CouchDB, HBase, DynamoDB |
Queries
Queries in NoSQL are usually faster than those in SQL because in SQL, searching for an object or entity usually requires you to join on multiple tables and the time to search increases as the tables grow in size. On the other hand, data in NoSQL databases is usually stored in a way that is optimized for queries.
Scalability
NoSQL is well known for its efficient scaling. SQL databases scale vertically. As the database grows, you eventually have to upgrade to more powerful hardware—CPU, RAM, SSD capability, etc.—which can be expensive.
On the other hand, NoSQL databases scale horizontally, allowing you to scale across many, cheaper, commodity servers. This makes them ideal for large or growing data sets.
Schemas
SQL schemas are rigid, meaning you typically have to spend much more time upfront planning what your data will look like. However, NoSQL offers flexible schemas so you can much more easily integrate additional features as requirements change. This would work nicely in projects taking an Agile approach.
Architecture
In SQL databases, multi-document ACID transactions are supported. ACID stands for atomicity, consistency, isolation and durability and the properties are meant to ensure consistency when executing transactions. Here is a brief explanation of what each property means:
- Atomicity: All of the pieces of a transaction are executed as a single unit. Either all of the pieces are executed, or none of them are. To think about this with an example, imagine you’re playing a video game and your health is low. You then drink a potion. You would expect a few things to happen—your health would increase and your potion count would decrement by 1. You wouldn’t want your potion count to decrement with no change to your health!
- Consistency: Ensures that all data is consistent before and after a transaction. Going back to the video game example, let’s say you go to a shop to purchase more potions. The merchant has 10 total potions in their inventory that you can purchase and you already have 7 potions in your personal inventory. No matter how many potions you purchase, after the transaction, you would expect the total number of potions in both of you and the merchant’s inventories combined to total 17.
- Isolation: If multiple transactions are being executed simultaneously, each one’s state is encapsulated and cannot be accessed by another. Let’s say our video game is multiplayer and two players are trying to purchase potions from the same merchant. They happen to both purchase their potions at the same time. If player one buys all the remaining potions, isolation would ensure that a scenario like player two getting funds deducted from their account but receiving no potions would not occur.
- Durability: Once a transaction has completed execution and the changes have been made to the database, durability ensures that the changes persist even in the case of system failure.
In contrast, CAP theorem is what’s used to express the limitations of distributed data stores, such as NoSQL databases. According to CAP theorem, NoSQL databases can only satisfy two out of three of the following:
- Consistency: All reads receive the most recent write or an error (not to be confused with consistency from the ACID properties, consistency means something different here).
- Availability: All reads contain data, but there is no guarantee it will be the most recent.
- Partition Tolerance: The system continues to operate despite network failures.
Thus, in the case of network failures, one has to choose between data consistency or data availability. If your application is transaction heavy and data consistency is a high priority, then you’ll want to use SQL. On the other hand, if you are working with large datasets and data integrity is not the highest priority, NoSQL may be the best approach. Also, it is important to note that for the most part, NoSQL databases do not support multi-document ACID transactions, however, MongoDB does.
Examples
- SQL databases
- SQL Server: relational database management system developed and maintained by Microsoft
- MySQL: open-source relational database management system
- Oracle: multi-model database management system produced and marketed by Oracle
- NoSQL databases
- MongoDB: cross-platform document-oriented NoSQL database that uses JSON-like documents with optional schemas
- CouchDB: open-source document-oriented database, implemented in Erlang
- DynamoDB: fully managed proprietary NoSQL database service that supports key-value and document data structures, developed and maintained by Amazon
The Final Verdict
You’re going to be disappointed if you were hoping I was going to conclude by telling you NoSQL is the better database and that you should stop using SQL. SQL is still used in many systems and is not going away anytime soon. Plus, NoSQL—not only SQL— was never meant to replace SQL, it was meant to address SQL’s limitations and shortcomings. The “better” database varies from situation to situation.
To sum it up, NoSQL is excellent in situations where there are large datasets, queries need to be fast and the application needs to be scalable. In addition, NoSQL is great for developing an application quickly and for changing requirements because of it’s schema-less approach. SQL is ideal for situations where data integrity is a high priority—transaction heavy applications like financial applications or eCommerce where consistency is critical.