Last modified: March 17, 2025

This article is written in: 🇺🇸

CRUD in SQL vs NoSQL

Comparing common CRUD operations in SQL (relational databases) and MongoDB (a NoSQL document store) provides valuable insights into the differences between relational and non-relational databases. Understanding these differences is crucial for developers and database administrators when designing and implementing data storage solutions tailored to specific application requirements.

After reading the material, you should be able to answer the following questions:

Overview

CRUD Operations Comparison

Understanding how CRUD operations are implemented in both SQL and MongoDB highlights the practical differences in data manipulation between relational and non-relational databases. Below is an in-depth comparison of each CRUD operation.

Create

SQL: Inserting New Records

In SQL databases, adding new records to a table is performed using the INSERT INTO statement. This operation requires specifying the table name, the columns where data will be inserted, and the corresponding values. The fixed schema ensures that the data conforms to the table's structure, maintaining consistency across all records.

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);

This statement adds a new user with the first name "John," last name "Doe," and age 25 to the users table.

MongoDB: Inserting Documents

In MongoDB, new data is added to a collection using the insertOne or insertMany methods. Unlike SQL tables, MongoDB collections do not enforce a fixed schema, allowing each document to have a different structure. This flexibility is advantageous for applications that require dynamic data models.

MongoDB Syntax:

db.collection.insertOne(document);

MongoDB Example:

db.users.insertOne({ first_name: 'John', last_name: 'Doe', age: 25 });

This command inserts a new document into the users collection with the specified fields and values.

Read

SQL: Retrieving Data with SELECT

The SELECT statement in SQL is used to retrieve data from one or more tables. It allows for precise querying through the use of conditions, joins, and projections, enabling users to extract exactly the data they need.

SQL Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition;

SQL Example:

SELECT first_name, last_name, age FROM users WHERE age = 25;

This query retrieves the first_name, last_name, and age of all users in the users table who are 25 years old.

MongoDB: Querying Documents with find

In MongoDB, the find method is used to retrieve documents from a collection that match a specified query. The method also supports projections, allowing users to specify which fields to include or exclude in the returned documents.

MongoDB Syntax:

db.collection.find(query, projection);

MongoDB Example:

db.users.find({ age: 25 }, { first_name: 1, last_name: 1, age: 1, _id: 0 });

This command retrieves all documents from the users collection where the age is 25, including only the first_name, last_name, and age fields while excluding the _id field.

Update

SQL: Modifying Existing Records with UPDATE

The UPDATE statement in SQL is used to modify existing records in a table. It requires specifying the table to update, the columns to change, and the new values, along with conditions to target specific records.

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';

This statement updates the age of the user named "John Doe" to 26 in the users table.

MongoDB: Updating Documents with updateOne/updateMany

In MongoDB, the updateOne and updateMany methods are used to modify existing documents within a collection. These methods allow for atomic updates and support a variety of update operators to manipulate document fields.

MongoDB Syntax:

db.collection.updateOne(filter, update);
db.collection.updateMany(filter, update);

MongoDB Example:

db.users.updateOne(
  { first_name: 'John', last_name: 'Doe' },
  { $set: { age: 26 } }
);

This command updates the age field to 26 for the first document in the users collection where first_name is "John" and last_name is "Doe."

Delete

SQL: Removing Records with DELETE

The DELETE FROM statement in SQL is used to remove records from a table based on specified conditions. It's a straightforward operation but must be used with caution to avoid unintended data loss.

SQL Syntax:

DELETE FROM table_name WHERE condition;

SQL Example:

DELETE FROM users WHERE first_name = 'John' AND last_name = 'Doe';

This statement deletes all records from the users table where the first_name is "John" and the last_name is "Doe."

MongoDB: Deleting Documents with deleteOne/deleteMany

MongoDB provides the deleteOne and deleteMany methods to remove documents from a collection. These methods allow for precise targeting of documents to delete based on filter criteria.

MongoDB Syntax:

db.collection.deleteOne(filter);
db.collection.deleteMany(filter);

MongoDB Example:

db.users.deleteOne({ first_name: 'John', last_name: 'Doe' });

This command deletes the first document in the users collection that matches the criteria where first_name is "John" and last_name is "Doe."

CRUD Operations Table

The table below provides a concise comparison of CRUD operations between SQL and MongoDB, highlighting their syntax and examples to facilitate quick reference and understanding.

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, _id: 0 });
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

When working with SQL and MongoDB, adhering to best practices ensures optimal performance, scalability, and maintainability of your database systems. Here are some recommended strategies:

Table of Contents

    CRUD in SQL vs NoSQL
    1. Overview
    2. CRUD Operations Comparison
      1. Create
      2. Read
      3. Update
      4. Delete
    3. CRUD Operations Table
    4. Best Practices