SQL vs NoSQL (MongoDB)

[Qingquan-Li]

Why Database?

Common website architecture:

Frontend (user interface) - Backend (API, database...)

UI (HTML, CSS, JavaScript) - API (Java) - Database (MongoDB)

Similar examples from the real world: Restaurant - Waiter - Kitchen

What is SQL (Relational Database)?

Popular SQL Databases

PostgreSQL, MySQL

Structure

SQL databases are organized into tables with predefined schemas.

id	name	age	email
123	John Doe	28	john.doe@example.com
124	Jake Lee	18	jakelee@gmai.com

Query Language

SQL (Structured Query Language) is used to interact with relational databases.

To find all users who are over 25 years old:

SELECT name, age
FROM users
WHERE age > 25;

Result (SQL):

name	age
John Doe	28

What is NoSQL (Non-relational Database)?

Popular NoSQL databases

MongoDB, Redis

Structure

NoSQL databases are more flexible and can store data in a variety of formats like documents (e.g., MongoDB) or key-value pairs (e.g., Redis).

Document Structure (NoSQL): In MongoDB, data is stored in documents, which are similar to JSON objects. The collection name is users, and each document looks like this:

{
    "_id": ObjectId("..."),
    "name": "John Doe",
    "age": 28,
    "email": "john.doe@example.com"
}

Querying Data (MongoDB)

To find all users who are over 25 years old:

db.users.find({ age: { $gt: 25 } }, { name: 1, age: 1, email: 0, _id: 0 });

Result (MongoDB):

[
    { "name": "John Doe", "age": 28 }
]

Key Differences

Dimension	SQL (PostgreSQL)	NoSQL (MongoDB)
Data Model	Relational (Tables with rows and columns)	Document-based (JSON-like documents)
Schema	Fixed schema (must define tables and columns upfront)	Flexible schema (can change on the fly)
Data Relationships	Well-suited for structured data and complex relationships (via joins)	Embedding documents or referencing documents, no native joins
ACID Transactions	Fully ACID-compliant (Atomicity, Consistency, Isolation, Durability)	Partial ACID support (starting from version 4.0 for multi-document transactions)
Scaling	Vertical scaling (increase hardware capacity)	Horizontal scaling (scale by adding more servers)
Write Speed	Generally slower due to strict ACID compliance and schema validation	Generally faster due to flexible schema and BASE properties
Read Speed	Optimized for complex queries and joins	Faster for simple queries, but slower for complex queries involving references
Use Case	Suitable for structured data and complex queries. Ex: banking systems, financial transactions, inventory management.	Suitable for unstructured data and large, distributed datasets. Ex: social media, real-time data processing, content management systems.