SQL vs NoSQL databases from developer perspective
- What does type of database have impact on?
- Why most apps use relational databases?
- Are NoSQL databases better than relational?
- SQL and NoSQL databases comparision.
- Edge cases.
What does type of database have impact on?
There are eight main aspects you should consider before picking the model of your database.
- Development speed
- Data consistency
- Application performance
- Appliaction maintenance
- Application security
- Application scalability
- Application flexibility
- Appliaction cost
Each of them has various priority and that depends on a type and scale of you application. It's wise to spend some time for consideration and choose rightly as changing your mind in the middle of a developing process can be tricky or near impossible. In terms of possibilites both models are simmilar but only on the surface. There are good reasons why no relational (NoSQL) databases were invented. But it's even more interesting why relationa (SQL) databases still exist and are picked for new applications by many leading companies. In this article I will elaborate on the eight mentioned aspects and sum it up in the table in the penultimate chapter.
Why most apps use relational databases?
First on the market
Relational databases where the first one that could be reliably and easily used on a massive scale at the time. The famous Oracle company was the first to build succesfull commercial database management system (DBMS) based on relational database in 1979. The idea behind DBMS is to enable working with databases with implemented transaction model (ACID) in a fairly easy way using API (quering in specific languages like SQL). After that the market experienced a huge and constant supply of similar products developed by different companies. Most of them failed to succeed but their work impacted the relational databases development and helped make them reliable and mature in fairly short time.
I think it's worth mentioning the fact that Oracle didn't invented relational database nor it competitors. The author of relational database theory is Edgard Codd and thanks to his work (1970) software companies could start implementing workable relational databases.
It turned out that the relational database model is so good and well thought out that even today after 50 years of it's conception it's still worth considerting to implement in your application as it should just work out in most cases by default.
Data consistency and easy usage
Thanks to reliability and simple query language for the very long time there was practically no alternative to relational databases. Reliability was achieved by implementation of transacational model ACID which has been guaranteeing data integration and consistency.
ACID transaction model:
- Atomicity - either all transaction are applied or none
- Consistency - the state of the database is consistent before and after transaction
- Isolation - one transaction doesn't have access to the other
- Durability - changes to the database are solid even if hardware fails
Structured Query Language (SQL) has been reponsible for simple usage. Putting or quering data is very simple. Writing in SQL is commonly compared to writing in English. What can be an obstacle is understanding relation model itself by inexperienced developer though.
Strict database structure
When working with relational databases before inputing any data you have to define a structure of your database first. This has to be really well think out. You can change it afterwards but doing this you have to take into consideration relations between tables and field data types which will become harder as your app grows. On the other hand strict predified strucutre ensures less errors on the production which many companies consider as an acceptable trade-off. Additionally for experienced developers database with relations (ERD digrams) can tell a lot about application itself giving logical understanding of objects and their dependence and thus boosting development in later stages.
This is a field when relational databases realy shines out from developers perspective. SQL gives you a solid arsenal to get almost any answer from your data in mulitdimentional section. The beautity of this lays in build-in solution so you don't need any additional software to get what you want directly in your application. I wrote that SQL is an easy language but it's partialy true. Getting expertise in this field takes time but when you get there many tasks far beyond putting and retrieving data can be done at incomparable performance to programing languages.
Vast variety and constant improving
Because of the long term popularity and the reliability relation databases are still maintained by many well known and established companies like Oracle, Microsoft, Amazon or IBM. These means that this model will be still available on the market with solid support in coming years and many apps will start or continue using it. Currently you can chooose from variety of different free and payable solutions like: MySQL, MariaDB, Microsoft SQL Server, PostgreSQL and many more.
Are NoSQL databases better than relational?
In same cases they are better, in some not so. They are meant to fill a gap rather than going to be a successor of relational databases. To understand this let's look at shortcomings of relational databases to see why no relational models emerged.
The relation model downsides
The relation model is not corresponding well with Object Oriented Programming. For developers using OOP it's counter intuitive to use data retrieved from a sql database. To help them out there are plenty of mappers for almost any language but still you need a proxy to get your work done efficiently.
Another common issue is related to the required structure of a database. It can takes a lot of time to make a good model for an app and because of relational model concept there may be a need of joining multiple tables which can considerably slows down data retrieving.
The above mentioned downsides are related to development speed but it turned out that the biggest flaw lays in scalability of the relational model. The problem was first encountered by Google in around 2000 when started using enormous amount of data. Soon the same problem got Facebook, Amazon and other big companies. Each of them try to fix it in a different way using sharding, buforing or replication. Unfortunately none of it could ultimately guaranted ACID principles. The relation model limit was achieved and there was necessity to create a new one to fullfil requirements of huge and distributed appliactions.
No relation model
Having a big problem to solve companies started to develop new databases based on no relation model. From 2005 there has been an ongoing boom and on the market emerged highly capable databases like HBase, Casandra, DynamoDB or MongoDB. When used with Hadoop they are suited for very large amount of data and are often facilitated in cloud architecture. The interesting thing is the way they store data. Depending on database it can be stored in columns, key-values or documents. The most popular in recent years became MongoDB proprably because it's document base model which is easy for adoption for most developers.
NoSQL databases solve relational models issues related to OOP and complex data modeling. They also scale extremely well but often they can only implement full ACID principles on a document level. Additionaly when used in distributed system with high avaialbility they uses eventual consistency feature which means that it takes time to update data between different locations and this can be inacceptable in some cases.
Why to use SQL databases?
You may wonder why bother to use old fashioned relational databases when no relation model fixed main of it's issues. Notice that only scalability was the reason behind creating the no relational model. The new model also comes with its flaws. Thats why it's still worth consider relation dabase usage in your app as many leading companies still do.
SQL vs NoSQL databases comparision
|Slow at early stages because of predefined schemas and types. It can also slows down adding new features to your app without redesigning the model.||Fast at early stages because no restrictions on documents and collections by default.||NoSQL|
|Great data consistency. Full ACID princples on a database level.||Weaker data consistency. Often ACID applied only on a document level.||SQL|
|Slower but in most application it won't be noticebale. Advanced quries (multiple joins) can slows down the performance but on the other hand can give a final answer without refering to target application making it faster at the end.||By default it has better performance but lacks built-in more complicated queries which have to be delegated to the target application code or other software.||NoSQL|
|Strict schemas and types help to undestand and maintain database. But there is a chance that data model is of bad quality and it will make maintenace a nighmare.||Loosly inserted documents and coupled collections can lead to unmanageable chaos. There is possibility to implement schema tough.||SQL|
|Most SQL databses required at least admin credentials by default. Popular attacks like SQL injections are well know and simple to counter. Strict schemas and types also play an important part in security field.||Most NoSQL databases don't come with obligatory loging but this can be set up after creating database. Most attacks like NoSQL injections are less known to most developers compares to SQL injections.||SQL|
|Applies horizontal scalability. That means there is a limit of your database but no too many applications will ever reach it.||Applies vertical scalability that can be practically endless. Used by Google with success give a nice proof that you don't have to worry about it.||NoSQL|
|Little flexibility. It's similar to strict programming languages in many ways.||Very flexibile. You can switch on/off many features like defined schemas, relations usage (yes, it's possible), embeded documents, strict types etc.||NoSQL|
|There is slower development speed which can result in greater Time To Market. On the other hand there are more experienced developers and that can boost development anyway compared to the NoSQL database and produces lower cost.||Fast development speed by default but many companies try to define schemas anyway. Less pool of experienced devolopers can result in higher Time To Market and greater cost.||Tie|
Both databases will work out in most of the existent and future apps but there are cases when you specifically should use one of them.
|IoT||NoSQL||Internet of Things projects can have incredible requirements in terms of reading and inseting data. NoSQL performance will be noticable in such cases.|
|Online money trasaction||SQL||Relation dabase has by default better data consistency guaranteed by ACID. Many NoSQL databases boasted about the similar accomplishment but there are multiple cases when can't be prooved. Better be on the save side even with worse performance.|
|Huge distributed apps||NoSQL||Only no relation databases can handle such apps as prooved by Google or Facebook more than decade ago. There is also a trade-off as eventual consistency in such cases though.|
What about e-commerce and bloging, two of the most popular web apps? Neither of them is an edge case and it doesn't matter which one you will use as long as you know exacly what impact they will have on your app. Notice that e-commerce is in most cases not related with online money transaction because you just delegate this function to other companies like PayPal or Stripe.