When it comes to storing data, we generally have two options: SQL (relational databases) and NoSQL (non-relational databases).
The idea for SQL was first introduced in 1970 by Edgar F. Codd in his model for relational database management. This type of database stores data in rows and columns like a spreadsheet, assigning a specific key for each row.
NoSQL came along in the 1990s, with the term officially being coined in 1998 by Carlo Strozzi. This type of database is not limited to the tabular schema of rows and columns found in SQL database systems.
In this post, I'm going to cover the differences between SQL and NoSQL databases and when to use either of the two. I'll start by providing more in-depth definitions of SQL and NoSQL databases, followed by some general guidelines on picking one over the other when building a project.
What is SQL?
SQL stands for Structured Query Language, which is the language used when communicateing with databases.
A snippet of SQL typically looks something like this:
SELECT * FROM TABLE ...
SQL allows you to create, read, update, and delete—also known as CRUD operations—through a universal language that is pretty much consistent across multiple underlying relational database engines, such as MySQL, PostgreSQL, or Microsoft SQL Server.
When talking about databases, there are 4 key components that are important to consider:
Let's take a look at how they relate to SQL.
In a relational database engine, you typically interact with tables.
A table consists of rows and columns; the columns correspond to types, while rows correspond to the individual entities that exist in the table
In a SQL table, you must have a primary key which corresponds to the unique identifier that identifies a specific row on the table.
In terms of storage, the pattern is concentrated. So in a relational database engine, there's typically one node that contains the entirety of your data; it's not partitioned or segregated in any way unless you're using some advanced strategies.
There are two approaches in terms of scale:
- Horizontal scaling: This means adding more machines. When you add more machines to a horizontally scaled RDS environment, you typically perform that by distributing your data across multiple nodes.
- Vertical scaling: If you have a machine hosting your database engine and you're not getting enough performance based on the machine's physical limitations, the option here is to build a better machine (more RAM, better CPU, and faster SSD) to host your database engine.
In terms of access, it's typically raw SQL, so you'll be writing the CRUD syntax for your queries.
You'll need a direct database connection to the endpoint of the database, and these days people are using ORM (Object Relational Mapper) to construct their queries. These are abstractions that are used to add criteria to an object in a very programmatic way, and they allow that to generate a SQL statement.
What is NoSQL?
NoSQL is basically anything that is non-relational. If you need humongous data storage and a distributed data store, a NoSQL database fulfills those needs.
Relational database management systems use SQL syntax to store and retrieve data for further insights. On the other hand, a NoSQL database system encompasses a wide range of database technologies that can store data that is structured, semi-structured, unstructured, or polymorphic.
There are many different implementations of NoSQL, so it’s an overloaded term. Here are just a few of the implementations:
- Table structure
The basic idea here is that NoSQL is built to scale with high performance, but it also comes at a cost: your queries are less flexible.
Let's go through the same key components of databases we discussed earlier, but this time discuss how they relate to NoSQL.
NoSQL is very implementation-dependent in terms of structure (as I said earlier, these are table, document, and graph implementations). However, the general theme among all of them is that they rely on the key-value store.
So, generally, you need to know the key you're looking for when you create your query in a NoSQL database.
In terms of storage, NoSQL relies on hashing the input. So if you have a key, the key is passed to a hashing function, and the result of the hashing function is a value distributed onto one of the multiple nodes.
The real advantage of NoSQL is horizontal scaling—aka sharding—a method of splitting and storing a single logical dataset in multiple databases.
There are two primary options:
- REST APIs: This has to do with sending a request to an endpoint with certain functionalities associated with it.
- CRUD in vendor-specific language: If you use Mongo DB, it has a unique way of performing a query.
Now that we've covered the definitions of SQL and NoSQL, let's take a look at the differences between them.
What are the differences between SQL and NoSQL?
The following list points out the key differences between a SQL and a NoSQL database.
- Schema: The term schema refers to the database structure, defined in a conventional language supported by the database management system. In SQL, the schemas are static, while NoSQL has dynamic schemas.
- Relational vs. non-relational databases: A non-relational database does not use the tabular schema of rows and columns found in most traditional database systems. In contrast, relational databases store data in rows and columns like a spreadsheet. SQL is used in relational database management systems, while NoSQL is used in non-relational, distributed database systems.
- Data storage: Using SQL, data is structured and stored in tables arranged in rows and columns. On the other hand, NoSQL data is unstructured and stored in a JSON format.
- Schema flexibility: NoSQL schemas are non-rigid and highly flexible. In contrast, SQL schemas are rigid and bound to relationships.
- Query design: SQL can be helpful when working on a project that requires complex query design. NoSQL, on the other hand, provides no interface for complex query design.
So, when should you use a SQL database versus a NoSQL one? Here's a quick and dirty guide:
|Use a SQL database when||Use a NoSQL database when|
In this quick comparison of SQL and NoSQL, we touched on:
- the definitions of SQL and NoSQL,
- the 4 critical components of a database and how they relate to SQL and NoSQL
- the differences between a SQL and NoSQL database
- and when to use SQL vs. when to use NoSQL
I hope this article helped you understand SQL and NoSQL databases a little better and when to use one or the other for your project.