DEV Community

Priyanka Sarode
Priyanka Sarode

Posted on

SQL vs NoSQL

#database #webdev #interview

In today’s data world, there are two databases mostly used in the market to perform operations ( add, retrieve, update and delete) on data :- SQL database and NoSQL database

SQL

  • SQL database stores data in structured way means in the form of rows and columns (tabular format like Excel). And that data doesn’t change frequently such as bank transaction.
  • It uses SQL (structured query language). e.g. to retrieve products from products table where price is greater than 1000 Rs, SQL query would be 
select * from products where price > 1000
Enter fullscreen mode Exit fullscreen mode
  • It follows a fixed schema⁣⁣ - need to define all fields first. It ensures consistency and predictability.
  • SQL databases scales vertically meaning you can increase the load on a single server by adding more CPU, RAM, or SSD capacity i.e. you have to increase hardware..
  • Also SQL queries helps in deriving insights from data.

NoSQL

  • NoSQL stores data in unstructured way means it is stored in JSON-like documents, key-value pairs, etc.⁣ and data changes frequently such as social media posts.
  • NoSQL databases don’t have a single standardized query language. Their queries depending upon database type. e.g. to retrieve products from products table where price is greater than 1000 Rs, MongoDB query would be 
db.products.find({ price: { $gt: 1000 } })
Enter fullscreen mode Exit fullscreen mode
  • It follows flexible schema - no need to define everything upfront⁣. You can add fields later on as needed.
  • NoSQL databases scales horizontally, meaning they use multiple servers to handle increased workloads
  • In NoSQL, you can’t derive insights effectively as data changes dynamically.

Examples

  1. If you go to Flipkart site and in one tab select men’s t-shirt and in second tab select any brand of mobile, you will get to see different fields in Product Details section for both products even if it comes under product database table. So in this case, Flipkart uses NoSQL database.

  2. Netflix uses hybrid model. For user and billing data, it uses SQL because for every user fields/ schema won’t change like username, password, email. And for recommendation data it uses NoSQL because recommendation movie cards that display on each user page are different as per user’s interest or likings.

  3. Uber also uses NoSQL because if company introduces new feature later on, so need to add that additional fields in database too.

  • While SQL follows ACID properties, NoSQL follows the CAP theorem

CAP theorem states that you can only guarantee two out of three properties in a distributed system.

Consistency - with every request I am going to fetch latest response for you
Availability - with every request I am going to fetch some result even if it is not recent one.
Partition tolerance: The system keeps working even if network issues occur

e.g.
1) Banking follows CP - User need to enter correct account data. If some data is wrong, cancel that transaction.
2) Uber follows AP - If there is delay in locating vehicle's exact position, show some nearby location. Means it works on availability basis.

Conclusion
SQL is best for banking, inventory systems, CRMs⁣
Examples of SQL database are Oracle, MySQL, PostgreSQL, SQLite, SQL Server⁣
Choose SQL when you need strict consistency.⁣

NoSQL is best for real-time apps, social media, IoT⁣ etc.
Examples: MongoDB, Cassandra, DynamoDB⁣, Redis, HBase, Neo4j
Choose NoSQL when you need flexibility.

Top comments (0)