Last modified: December 03, 2024
This article is written in: 🇺🇸
CRUD in SQL vs NoSQL
Comparing common CRUD operations in SQL (relational databases) and MongoDB (a NoSQL document store) helps understand the differences between relational and non-relational databases.
- CRUD stands for Create, Read, Update, and Delete, which are the four basic operations for data manipulation.
- In SQL, data is organized in tables with fixed schemas, while MongoDB stores data in flexible, JSON-like documents.
CRUD Operations Comparison
Create
In SQL, you use the INSERT INTO statement to add new records to a table.
- SQL Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); - SQL Example:
INSERT INTO users (first_name, last_name, age) VALUES ('John', 'Doe', 25);
In MongoDB, you use the insertOne or insertMany method to add documents to a collection.
- MongoDB Syntax:
db.collection.insertOne(document); - MongoDB Example:
db.users.insertOne({first_name: 'John', last_name: 'Doe', age: 25});
Read
In SQL, the SELECT statement retrieves data from one or more tables.
- SQL Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition; - SQL Example:
SELECT first_name, last_name, age FROM users WHERE age = 25;
In MongoDB, the find method retrieves documents from a collection that match a query.
- MongoDB Syntax:
db.collection.find(query, projection); - MongoDB Example:
db.users.find({age: 25}, {first_name: 1, last_name: 1, age: 1});
Update
In SQL, the UPDATE statement modifies existing records in a table.
- SQL Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; - SQL Example:
UPDATE users SET age = 26 WHERE first_name = 'John' AND last_name = 'Doe';
In MongoDB, the updateOne or updateMany method modifies existing documents in a collection.
- MongoDB Syntax:
db.collection.updateOne(filter, update); - MongoDB Example:
db.users.updateOne({first_name: 'John', last_name: 'Doe'}, {$set: {age: 26}});
Delete
In SQL, the DELETE FROM statement removes records from a table.
- SQL Syntax:
DELETE FROM table_name WHERE condition; - SQL Example:
DELETE FROM users WHERE first_name = 'John' AND last_name = 'Doe';
In MongoDB, the deleteOne or deleteMany method removes documents from a collection.
- MongoDB Syntax:
db.collection.deleteOne(filter); - MongoDB Example:
db.users.deleteOne({first_name: 'John', last_name: 'Doe'});
CRUD Operations Table
| Operation | SQL Syntax | SQL Example | MongoDB Syntax | MongoDB Example |
| Create | INSERT INTO table_name (columns) VALUES (values); |
INSERT INTO users (first_name, last_name, age) VALUES ('John', 'Doe', 25); |
db.collection.insertOne(document); |
db.users.insertOne({first_name: 'John', last_name: 'Doe', age: 25}); |
| Read | SELECT columns FROM table_name WHERE condition; |
SELECT first_name, last_name, age FROM users WHERE age = 25; |
db.collection.find(query, projection); |
db.users.find({age: 25}, {first_name: 1, last_name: 1, age: 1}); |
| Update | UPDATE table_name SET column = value WHERE condition; |
UPDATE users SET age = 26 WHERE first_name = 'John' AND last_name = 'Doe'; |
db.collection.updateOne(filter, update); |
db.users.updateOne({first_name: 'John', last_name: 'Doe'}, {$set: {age: 26}}); |
| Delete | DELETE FROM table_name WHERE condition; |
DELETE FROM users WHERE first_name = 'John' AND last_name = 'Doe'; |
db.collection.deleteOne(filter); |
db.users.deleteOne({first_name: 'John', last_name: 'Doe'}); |
Best Practices
- Understanding the differences between SQL and MongoDB CRUD operations is helpful when choosing the appropriate database.
- Selecting the right database and query syntax based on application requirements can improve performance.
- Regularly monitoring and analyzing query performance helps identify areas for optimization.