The debate over which database to use for your app is white-hot.
Traditional SQL databases have dominated over the past 40 years. Data servers around the world were built on the back of the relational database model. Then came Cloud computing.
Scalability, big data, and instant access became the norm. SQL databases had difficulty coping so a new format was developed - NoSQL.
It's estimated that 75% of all databases will be migrated to the cloud in the next four years. NoSQL databases work best on Cloud platforms so does this trend signal the death of the RDBMS?
This article compares the two systems for developing your app. The stalwart veteran SQL database format versus the new kid in town NoSQL.
Which is better between NoSQL vs SQL and which to choose? Keep reading to learn more!
What is SQL?
SQL stands for Structured Query Language. It offers a way to interrogate and retrieve information from a database.
The keyword there is structure.
If you don't have much experience with how an SQL database works then think of a simple spreadsheet. Each sheet stores information about a customer or an order. Those sheets could be termed tables and tables underpin how a SQL database works.
The goal of a SQL database is to offer a precise way to store and access data. Relationships are formed between tables to separate data and avoid duplication.
For example, a customer's name shouldn't appear in their orders. What if their name changes? You'd need to update all orders to match the new content.
Relational databases or RDBMS have strict rules to adhere to. This makes sure the integrity of the data is never compromised.
What is NoSQL?
NoSQL databases also hold data but don't have strict rules. Structures can change, data types can vary, and duplicate data can exist.
So why have a system which has no rules?
Social media sites like Facebook had a problem. They ran on RDBMS databases which struggled under the influx of new visitors. Millions of rows of data needed to be added within seconds. SQL couldn't cope.
A new architecture was developed, one which could handle big data and also scalability.
Cloud computing shared files around global datacenters within milliseconds. Data storage was now relatively cheap. NoSQL took advantage of speed and storage to provide a document system style database.
There are 4 types of NoSQL databases. Each is suited to a different task. They include:
- Key/Value - most basic
- Document - offers different types of data
- Graph - stores connections like social media posts
- Column - switches how data is stored
Flexibility and scalability are therefore the foundations of NoSQL.
Examples of SQL Systems
When developing a native app you have one main SQL database to choose from - SQLite.
Both Android and iOS support SQLite to store and retrieve data locally. Room architecture in Android is specifically designed to work with this RDBMS. Tools like DB Browser allow you to create and manage SQLite databases using a simple GUI.
There are more database options for backend systems to administer your apps.
Here's a list of other popular SQL databases:
Examples of NoSQL Systems
It is cross-platform so it will run on lots of devices. It offers high performance and is easy to scale on the cloud.
Redis is close behind in popularity. It is stored in memory which means major speed performance. It can also handle multiple data structures, from strings to bitmaps.
We've created an article highlighting the differences between Redis vs MongoDB in our blog.
Here's a list of other popular NoSQL databases:
- Google Firebase Realtime Database
- Amazon DynamoDB
- Microsoft Azure Cosmos DB
You can see the full rankings of all database types on DBEngines.
Non-Relational vs. Relational
NoSQL uses a document system to store data while SQL databases use tables. NoSQL data might not relate to each other, but SQL does.
Who cares, you may ask? It depends on what you want from the data.
Relational databases are best suited to statistical reporting. For example:
- Customer order reports based on a certain date range
- Interrogating log data to spot trends
- Financial data for tax calculations
- Transactional bank deposits
All of these are suited to a stable, relational database. Data doesn't change constantly so it can be brought together more easily.
Non-relational NoSQL systems thrive on movement.
- Social media posts and follow-up likes
- Video uploads and views
- Customer interactions and engagement
Millions of pieces of information constantly in flux suit a non-relational system.
Data Storage and Movement
When data is inserted into a relational database its validity is checked. This takes time and slows down access to other data. But in the case of a financial transaction, this is vital. Every penny counts.
In a non-relational database, data is stored in JSON documents. When parts of those documents are updated they're shared globally almost instantaneously. Data integrity may not be 100% accurate but it will be soon. Performance is key in this scenario.
The winner of the NoSQL vs SQL speed test has a clear winner if your data is constantly changing. But what about flexibility?
Dynamic vs. Structured Schema
A schema relates to the structure of the data. It's a blueprint to a house. Get it wrong and everything could collapse.
Before an SQL database is created a structured schema needs to be drawn. A process called normalization ensures there's no duplication of content. This keeps things lean and fast.
CREATE and INSERT statements build tables and fields and add data rows. JOIN groups data together and creates a view to hold all the data you need for a query.
Dynamic schemas say forget all that.
Because things change so quickly you need flexibility. Rapid prototyping your app allows your customers to see what works best at an early stage. Dynamic NoSQL architecture says 'make the database schema up as you go along'.
Data in NoSQL is constantly evolving. Adding a new data type is as simple as running db.collection.insert(). You aren't restrained by the past.
Google's Firebase platform gives your apps access to a REST API to control all your app's content. It can also offer push notifications and real-time data synchronization. It works for iOS, Android, web, and all platforms.
NoSQL sounds incredible but what about data integrity? How does that work on multiple servers like Cloud computing?
Horizontal Scalability vs. Vertical Scalability
And so we come to the reason why NoSQL was created: the digital brick wall of scalability.
SQL databases are stored on one computer, usually a server. That means when your app goes viral ten thousand users suddenly turn into ten million. The lowly server has a heart attack and keels over.
The only way to resuscitate it is vertical scalability. That's a fancy way of saying give it more resources like extra RAM or processing power. Except for one day, those resources will hit a brick wall too.
Horizontal scalability says don't build up like a skyscraper but build to the side.
Adding extra servers combines the power of a vast network. Data is copied between them at nearly the speed of light. You don't need a supercomputer when hundreds of cheaper processors do the same job.
The bottom line is if your React or Angular apps draw large numbers your database needs to scale with them. Ease of scalability with NoSQL, while not decisive in and of itself, is a decisive factor.
NoSQL vs SQL database scalability has one clear winner here. But what about help and support?
Active Community vs. Established Community
Because SQL databases have existed for years, there is an established support community in place. For example, StackExchange has a dedicated section for MySQL related questions.
Advice is available for a multitude of platforms and programming languages. Want to tie your app to an SQLite database in iOS using Swift? There are dozens of YouTube tutorials to guide you.
One major drawback of a NoSQL database is lack of cohesion. Each database is created differently and queries vary on each system. Structured Query Language is, for the most part, uniform. One query works on all relational databases.
That's not to say NoSQL has no support.
There's a growing, active community to offer you guidance. Unsure of the differences between graph or document storage? Tutorials exist to assist you. As the medium is modern you won't see out-of-date articles like you would for SQL.
Although SQL wins this round don't count out the passion of NoSQL users. They want their platform to succeed and are willing to support it.
CAP vs ACID
SQL systems use a rigid structure to guarantee data is correct. But is that always the case?
There's a computer science theory called CAP or Brewer's theorem. It says your shared data in a distributed system can't be guaranteed. These three cannot all happen at the same time:
- Consistency - data is always up-to-date
- Availability - never any errors so data is always available
- Partition Tolerance - delays have no impact on the data
Does that make a liar out of SQL databases?
Notice I said shared data in a distributed system. SQL systems are designed to be on one server. Because of this, data follows the ACID principle.
- Atomicity - a transaction where it all works or nothing works
- Consistency - changing data must follow the rules
- Isolation - only the current connection can make changes to a record
- Durability - changes will be permanent
If all users access one computer then data integrity should always be consistent. It should if it's set-up correctly.
Configuring a SQL database is more than designing schemas. Triggers and joins must be maintained. And if a transaction is by-passed then data may become duplicated or lost.
Regardless, most SQL databases need to sit on one computer. As we've seen, that doesn't scale well. On a NoSQL database, the data will re-form over time. That may take a few seconds but it will get there.
Who Should Use What System?
You may be leaning towards one side but consider your own use case.
Here are a few questions to consider:
Will Your App Always Be Connected to the Internet?
NoSQL databases offer the edge here. They provide speed and scale to handle as many connections as you need. Cloud providers like Azure or AWS offer all major NoSQL databases to get you going within minutes.
Does Your App Need to Work Offline?
Offline storage of data on a mobile device is vital for certain services where Internet connections are poor.
SQLite is the go-to method for native Android and iOS apps. It can even store BLOB data for binary content. When the user does get connected the data is synced to the server and new data downloaded.
NoSQL vs SQLite for offline apps? SQLite wins.
What If Your Data Is Very Small?
Does your app even need a database?
For simple data storage like the user's name or email, you can use SharedPreferences in Android. These key/value pairs are stored in your app's content area which no other app can access. iOS uses the NSUserDefaults class to do the same thing.
You can cache temp files to store data for a short time or save it in external storage using a JSON file.
Are Live Reports Required?
SQL databases are geared for crunching numbers. Live, accurate reporting often uses legacy relational systems.
Is User Interaction Vital?
For social media platforms or interactive apps, NoSQL offers the advantage here. Traditional SQL systems have a difficult time keeping up with thousands of current connections.
Winner of the NoSQL vs SQL War
In the modern era of app design, NoSQL shines. It works seamlessly with the Cloud and scales to suit your needs.
Here's one last thought - why not use both?
Separate your app’s back-end into two: group high transactional data where data integrity is a must in an SQL database while keeping the rest of the data in a NoSQL format, which is quicker to adapt and scale.
Uber uses MongoDB and MySql to run its service. Each database has advantages so it combines the power to leverage both. Why not do the same?
As the NoSQL vs SQL Reddit debate continues to rage you can rely on the professionals to guide you.
SF AppWorks offers consultation, UX design, optimization, and integrated software platforms. We can help launch and support your app and provide secure validation.