Should we use a SQL vs NoSQL DB?
Whenever we have to start a new project where data storage is needed, we have to think about databases and how we will use them. Fortunately, today we have many options we can choose from, it might be just a simple JSON file or a complex MongoDB cluster.
Generally, we try to use the technology that is well-known by the whole team, even though others could fit better with the project we are developing.
In this article, we will show you some key points of the different options out there.
Nowadays, there are two main groups of databases SQL and NoSQL.
Right below, we are going to define and analyze them based on five topics: language, scalability, structure, properties, and community.
Note: This is also a document that we share with our coworkers internally at Loopstudio, this guide is essential to understand which one you should use.
Definition
According to IBM we can define a SQL database as:
SQL, which stands for “Structured Query Language,” is the programming language that’s been widely used in managing data in relational database management systems (RDBMS) since the 1970s. In the early years, when storage was expensive, SQL databases focused on reducing data duplication.
Examples: SQL Server, Oracle, MySQL, PostgreSQL, etc.
On the other hand, it defines a NoSQL database as:
NoSQL is a non-relational database, meaning it allows different structures than a SQL database (not rows and columns) and more flexibility to use a format that best fits the data. The term “NoSQL” was not coined until the early 2000s. It doesn’t mean the systems don’t use SQL, as NoSQL databases do sometimes support some SQL commands. More accurately, “NoSQL” is sometimes defined as “not only SQL.”
Examples: MongoDB, Redis, DynamoDB, Elastic Search, etc.
SLQ vs NoSQL – Comparison
Now, we are going to compare both according some topics:
1. Language
The different SQL databases have a very similar syntaxis, which is so important if you have to move between different motors (i.e from MySQL to Oracle). In fact SQL was adopted as a standard by the ANSI in 1986 as SQL-86 and the ISO in 1987 (currently SQL:2019).
Apart from that, it’s well documented and you can find different tutorials wherever you are.
Regarding NoSQL group the syntaxis, methods and strategies will change for every database, which could be a problem if you have to change them. Documentation is good enough, despite not being as widely spread as SQL ones.
2. Scalability
SQL databases can be scaled vertically, by improving the processing power of existing hardware (that means improving the processor, adding more RAM memory, etc). Meanwhile, NoSQL databases can be upgraded horizontally, which means that more traffic can be handled by sharding or adding more servers in your NoSQL database.
That said, we should mention that horizontal scaling is better than vertical one, in the long run, it’s easier and cheaper to add new servers than RAM, CPU, or SSD.
Thus NoSQL can ultimately become larger and more powerful, making these databases the preferred choice for large or ever-changing data sets. Even though, it’s more complex because the engineering team needs to handle the different shards.
3. Structure
SQL database schemata represent relational, tabular data, with rules about consistency and integrity. They contain tables with columns and rows, and keys have constrained relationships. SQL could be restrictive: it requires you to use predefined schemas to determine the structure of your data before you work with it. This can require significant upfront preparation which means that a change in the structure would be difficult and disruptive to the system.
NoSQL databases need not stick to a specific format, but they are generally some of these ones:
- Column oriented: Similar to SQL databases but with varying attributes.
- Key-value: Dictionaries where you can find a value according to a given key.
- Document: Stores semi-structured data.
- Graph: Based on some graph structures.
This flexibility means that documents can be created without having a defined structure first. Also, each document can have its own unique structure. The syntax varies from database to database, and you can add fields as you go.
This difference in schema makes relational SQL databases a better option for applications that require multi-row transactions such as an accounting system or for legacy systems that were built for a relational structure. However, NoSQL databases are much better suited for big data as flexibility is an important requirement which is fulfilled by their dynamic schema.
4. Properties
SQL is based on ACID principles:
- Atomicity: All transactions must succeed or fail completely.
- Consistency: On each step inside the database is followed invariantly.
- Isolation: Prevents concurrent transactions from affecting each other.
- Durability: Even system failure cannot roll-back the effects of a successful transaction.
Meanwhile NoSQL on CAP theorem
- Consistency: Every request receives the most recent result or an error.
- Availability: Every request has a non-error result, regardless of how recent that result is.
- Partition tolerance: Any delays or losses between nodes will not interrupt the system’s operation.
5. Community
SQL databases have massive communities, stable codebases, and proven standards. Multitudes of examples are posted online and experts are available to support those new to programming relational data.
NoSQL technologies are being adopted quickly, but communities remain smaller and more fractured. However, many SQL languages are proprietary or associated with large single vendors, while NoSQL communities benefit from open systems and concerted commitment to onboarding users.
So… When to use SQL vs When to use NoSQL?
The choice between SQL and NoSQL depends entirely on individual circumstances as both of them have advantages as well as disadvantages.
But we can make some generalizations to help you identify which suits you better:
Use SQL when:
- The application require multi-row transactions, such as an accounting system, and that requires strong data integrity. This means, ****it is not enough to rely on code, you need to ensure that the database protects you from possible business logic errors
- You and your team has strong knowledge about the required schema before starting the project, it’s highly structured and will not change frequently.
- You don’t know exactly which queries will be the most demanded and your system will routinely perform complex queries, therefore you will rely on the power of SQL joins and selects to get the right data
- Its possible to, in a future, you need to migrate your data to another SQL database system.
Use NoSQL when:
- There is a need to handle big data applications and that data its going to scale quickly. For example business intelligence , real-time analytics and events processing applications.
- You don’t know the required structure of your data before the start of the project or the schema will probable change on an Agile development environment.
- You know that there are some specific queries that the system needs to satisfy and it must do it with high performance. Queries in NoSQL databases can be faster than SQL databases. Why? Data in SQL databases is typically normalized, so queries for a single object or entity require you to join data from multiple tables. As your tables grow in size, the joins can become expensive. However, data in NoSQL databases is typically stored in a way that is optimized for queries. The rule of thumb when you use NoSQL is Data that is accessed together should be stored together.
- You have no worries about having non-normalized data and redundant information without any integrity restriction at DB level. Your schema also requires to be flexible enough to accommodate different models, including key-value, document and graph.
- You require a database system that can be scaled horizontally, perhaps. across multiple geographic locations to ensure high availability.
