A developer’s guide: The differences between SQL and NoSQL

Overview

Choosing the right method to store your data can be a critical decision early in the development process. When deciding on which type of database to choose for your project it really comes down to the data structure you wish to use rather than the specific product or provider. In this blog post, we are going to look at relational and non-relational databases and explore the differences and use cases for both.

Relational databases – SQL

So, to clarify things upfront, Structured Query Language, or SQL is the common language used across multiple different underlying relational database management systems (RDBMS). 

Structure

A relational database system consists of either one or multiple tables. Tables are simply structured (and type constrained) storage for your data and consist of rows of records that can have one or many columns of data.

An example of records in a relational database table

Access

When accessing SQL databases, you use what is known as CRUD operations (create, read, update, and delete)

A simple SQL read query looks something like this:

SELECT [Name], [Age] FROM CLIENTS WHERE [Gender] = ‘Male’

This statement is querying the [CLIENTS] table for any records that have a [Gender] value of ‘Male’. Then return the name and age of each client record that matches.

Some of the most well-known RDBMS are

• MySQL
• PostgreSQL
• Microsoft SQL Server

The real selling point of SQL systems is the fact that you can build relational connections between tables and then run a variety of queries against it to return multiple different datasets based on your requirements.

Scaling and storage

SQL databases are typically vertically scaled, meaning the database instance is located on one server and to scale up you keep adding/upgrading that server.  This typically means your storage is also concentrated, and one location contains your entire database. It is possible to scale horizontally in SQL, this usually involves copying your database across multiple different machines, then requests can be load-balanced across these instances.

SQL database solutions are great if you have a clear understanding from the inception of the project of your data storage requirements and how your data is to be organised – schema.

Non-relational databases – NoSQL

Structure

As the name hints, NoSQL databases are used for data that is non-relational. The term NoSQL tends to cover quite a few different implementations such as 

• Table structures – Similar to the examples above but non-relational 
• Document – These implementations store your data as JSON objects
• Graph – These tend to be structured to model things like social network friend relationships

An example of a non-relational database document in MongoDB

The common link between all these implementations is that they use a Key-Value structure for storing records. Therefore, you need to know the key you are looking for beforehand to get at your data.

Access

This varies across vendors and products but generally you have the option of using vendor-specific CRUD queries like SQL or REST APIs to access your data.

Scaling and storage

In terms of storage, NoSQL uses a hashing function to decide where to store your data. You provide the key, and the result of the hashing function is a value distributed onto one of the multiple nodes. This design makes NoSQL ideally suited for horizontal scaling as you just need to add more partitions/node to scale up.

NoSQL solutions are generally built to be able to scale for high performance, but you sacrifice some of your query flexibility due to the key-value nature of the storage.

What solution is best for your project?

This table summarised the details listed above, hopefully, this format makes it easier to decide what makes more sense for your project.

SQL	NoSQL
If the way you want to access your data isn’t defined	When you know how specifically you want to access your data
If you want to use flexible queries	When you know your primary keys
You need to make use of the relational nature of SQL	If you want to use a nonstandard data model like graphs or documents
If you want to constrain the values being written to your database	When you are focused on high performance and scalability
When data integrity and consistency is an important deciding factor	When you want to store large sets of unrelated and unstructured data.

Example situations 

• You are starting a small project that you don’t expect to scale very large and you’re not sure of the final date storage solution structure yet – Choose SQL
• You have a large project that you expect to scale up and you know you want to leverage relation tables – Choose SQL 
• In my opinion, you have a medium to a large project that you want to be able to easily scale and get the best possible performance out of -> Choose NoSQL

Conclusion

Like most things in life, there is rarely a one size fits all option. This isn’t a scenario where one is the fundamentally better technology. You need to assess your project requirements and make an informed decision of what option provides you with desirable functionality.

As always, I hope you have enjoyed this blog post!