These are the main benefits of using SQL Database Management Systems over NoSQL Databases. Let’s face it
To make this clear. NoSQL Databases are fantastic! And they are either a perfect fit or substitution, based on the software niche. But now, let’s talk about the SQL DBMS Strengths
Some Examples
In Case you haven’t heard about the most used systems:
- Famous SQL Database Management Systems are Oracle, MySQL (MariaDB), Microsoft SQL Server
- Famous NoSQL (Not only SQL) Systems are MongoDB, Redis, CouchDB
These are very widely used.
You can easily check out all of these 6 with a test account or simply downloading a free version → Very simple for MySQL on Linux Servers or Microsoft SQL Server (Express License) on Windows Servers.
A) Transaction Security
Based the your business model, we must ensure transaction security. Everything related to personal information or financial data relies on this concept.
What is a Transaction? A Transaction is some kind of “working Unit” We are able to check whether our instructions worked or not. This check could be written in a backend language (PHP, Python). Based on the result we can commit (save permanent) or rollback ( → no changes on the permanent storage).
For example, we can:
- count up an index and ensure that no one else counts up to the same value
- insert or update in various tables. When an error occurs, we break the chain an ensure data consistency
- convert. For example currency. When we encounter inconsistency we rollback and not a single customer loses his money
It is very possible, that NoSQL Databases will adapt this concept in the future (it is possible, but not that simple). And if you were brave, you could write your own transaction security engine with your backend language. The truth is: Don’t waste time on this. Use the present possibilities.
→ Concentrate on the real problem and provide business value.
→ Make life easier!
B) Another Layer of application logic or simply: SQL
True Power comes from flexibility. Imagine a basic full stack Application:
Front End → API → Data Storage
You have 2 Abstraction Layers. For example we can handle every data related task in Python.
But what about adding a third? Would this harm our architecure? Could it benefit from this? It will add more flexibibility!
Front End → API → SQL DBMS → Data Storage
Are there any Tasks that are easier to manage and could be better managed in SQL? YES! There are 3 tiers of database-related tasks in an app.
- Passing through data from your API → Simple Selects, Inserts, Updates, Deletes
- More complex Data processing with “stored procedures”, which can be fired up from the back end
- automated data processing → which can be very handy for Point “C” or as a fallback (you need some data in a special processed form every x minutes)
The bold ones are the interesting ones. These are the special Database Layer benefits. SQL stands for Structured Query Language. It should be named powerful data language. Cursor loops or merge statements are examples of simple to write data manipulation commands with a big impact.
Using these is worth an article on each. If you are interested, just comment or write a message
Important to mention: Setting up a database Management System results in some work. And the administration is additional work! Keep that in mind.
C) Various Possibilities to connect and work with the data
No matter what other business application you need or already have in use. I am sure that a connection to a SQL DBMS is possible.
I don’t know any programming language or framework that is not able to connect to the top three SQL Databases with predefined libraries.
So let’s imagine your application is finished. It is running for a few months and has collected a representable amount of data. A manager or an executive comes to you and wants you to develop one of the following solutions:
- a reporting to show some simple data analysis in an easy to use environment (Reporting-Tools)
- an interface to an external partner. He needs some special data every hour, day, or week uploaded to an FTP Server as CSV-file. You have to develop a fully integrated data interface (ETL-Tools)
- a platform to provide self-service BI Reporting and complex data analysis. This usually relies on a data cube (BI-Tools)
All of this is possible with a NoSQL Database and solid knowledge in your choice of the backend language. The basic reporting could be done with a little JavaScript and a little JSON Data Exchange. But even this simple task would eat up time, if you had to add various predefined query parameters.
SQL Databases will provide you with special solutions for these jobs. Just google the purpose (Reporting, ETL, BI) and the Database. You will find a solution.
Conclusion — What should we use?
So how do we know if we need or want an SQL Database? Maybe a simple NoSQL Datastore is enough? There is no flat answer. But a simple approach would be:
- You need easy transaction security (Point A) → I highly recommend SQL Databases
- You know SQL and want that additional layer of logic (Point B) → I recommend SQL Databases
- You wand easy options to connect and work with your data (Point C) → I recommend… you know what I mean
If any of the mentioned points sound reasonable to you, then you have a good reason to rely on classic SQL databases. But you should keep in mind, that NoSQL Databases easily outperform classic SQL Databases in scalability and query speed (btw I have seen SQL Databases with about 100GB in size, performing very well on a single Server Node…. just to clearify what I am talking about).
So when your data grows it will definitely be worth it to separate it. You could Store Warehouse Data and critical Data in SQL Databases. Data which doesn’t rely on transaction security and SQL abstraction in NoSQL Databases.
What do you think?
Which Database concept is your favorite? And why?
Please tell me what you like to use or would like to use.
Disagree? Why don’t start a conversation and continue on this topic. I am looking forward to hearing from you.
