SQL vs NoSQL

SQL and NoSQL are the two different approaches in which data is being stored by the companies based on type of data being stored, scalability and few other factors.

SQL

• SQL stores data in a structured way, i.e. in the form of rows and columns. Each column defines the type of data being stored, such as id, name, and price, while each row contains the actual data entries.
• It uses a fixed structure where all fields must be defined first i.e. Fixed Schema. This helps in keeping data organized and consistent.
• SQL databases store data in a vertical structure and scaling happens by increasing the resources of a single server such as CPU, RAM, or SSD capacity.

• It uses SQL to manage and query data. For example, to retrieve users from the 'users' table who live in 'Mumbai', the SQL query would be:

SELECT * FROM users WHERE city = 'Mumbai';

• SQL databases are relational, meaning they organize data into related tables with rows and columns and relationships are defined using keys, which help maintain data integrity and enable powerful querying across multiple tables.
• SQL databases are typically used in applications which requires consistent unchanged data format and also applications that require in depth data insights.
• SQL databases are ACID compliant, meaning they ensure reliable transactions by maintaining Atomicity, Consistency, Isolation, and Durability.

Example: Imagine you want to transfer 100 Rs from Ram’s account to Priya’s account.

Atomicity - All steps complete or none

• Either both the debit from and credit to will happen or neither happens if something fails.

Consistency - Data rules are followed

• Transfer/any operation must follow the rules for example, Account Balance should not be negative and database will move only from one valid state to other.

Isolation - Transactions run independently

• If multiple people are trying to do transaction at the same time, transactions won’t mix up.

Durability - Permanent Changes

• Changes are saved permanently, data won’t be lost.

Examples

Financial Systems & Banking

• Fixed structure and type of data won’t change.
• Handles complex transactions with ACID compliance.
• InDepth Querying of data.

NoSQL

• NoSQL stores data in a unstructured way, i.e. in the form of JSON Documents, key-value pairs etc.
• NoSQL databases don’t require a fixed structure for the data before you store it. We can add, remove, or change fields in our data at any time without needing to update a predefined schema. This flexibility allows for easier handling of unstructured or evolving data.

NoSQL doesn’t have standard structured query language to query the database and the querying options are limited and vary depending on the type of database being used based on how the data is getting stored i.e. in the form of documents or key-value pairs etc.

For example, mongoose provides below query options to name a few and query options are limited and we need to use external querying for in depth query which makes NoSQL database hard and complex for in-depth data insights

- Comparison: $gt (greater than), $lt (less than), $eq (equal), $ne (not equal), $in (in array), $nin (not in array)

- Logical: $and, $or, $not, $nor

- Element: $exists (field exists), $type (field type)

• NoSQL databases scale horizontally, meaning they distribute data and workload across multiple servers to efficiently handle increased demand.

Examples

Ecommerce Applications

Ecommerce Application such as Flipkart uses NoSQL database the reason for the usage of NoSQL database in ecommerce applications is typically ecommerce application will have list of the different types of the products and the data for each products varies for example clothes have different details compared to electronics and in clothes category also different types of clothes will have different details.

Evolving Applications

NoSQL databases are often used in evolving applications that continuously introduce new features due to their flexibility and scalability.

Imagine if the Uber wants to introduce a new feature like car pooling which requires different fields or field format changes it can be done easily using NoSQL databases by simply adding the new data attributes or creating a new Schema for the new feature and adding existing and new data attributes to it.

Hybrid (SQL & NoSQL)

There are applications which needs strong data insights capabilities or where data format is fixed like the user data and also flexible schemas to introduce new features and to store different data attributes of different products. data storing in these type of application involves use of both SQL and NoSQL databases for different type of data storage.

Example

Netflix uses hybrid model. It uses SQL to store the user and billing data as the format is fixed and supports strong querying capabilities and uses NoSQL for the recommendations as recommendations varies for each user based on their watching history and preferences and recommendations keep on changing. It uses NoSQL for the data storage of movies and web series as both of them have few common data attributes and few different data attributes and attributes may change frequently in case of web series.

If you found this helpful, consider bookmarking, sharing, or giving a ❤️.
Happy coding! 💻✨