Introduction to MongoDB with PostgreSQL
Shrishail Wali
Software Engineer | Backend & Gen AI Enthauist | Python, Java, Javascript
"First, let's explore the differences between MongoDB and PostgreSQL."
1. Data Model:
2. Query Language:
3. Schema and Flexibility:
4. Transactions:
5. Use Cases:
6. Scaling:
In summary, the choice between MongoDB and PostgreSQL depends on the specific requirements of your application, including the nature of the data, the need for flexibility, and the importance of transactions and relationships.
Create database in MongoDB different in the PostgreSQL
MongoDB Syntax:
## use database Name -This command will Switch to your database
use MyDatabase
PostgreSQL Syntax:
## Create database databaseName
CREATE DATABASE Mydatabase;
Embedded Document in MongoDB (Nested Document List)
Nested Structure:
{
"_id": 1,
"name": "John Doe",
"address": {
"street": "123 Main St",
"city": "Anytown",
"zip": "12345"
},
"contacts": [
{"type": "email", "value": "[email protected]"},
{"type": "phone", "value": "555-1234"}
]
}
You can query for specific fields within embedded documents using dot notation. For example, to query for the city in the above example:
db.collection.find({"address.city": "Anytown"})
db.collection.find({"contacts.type": "email"})
db.collection.update({"contact.type":"email"},{$set:{idCards:{haspancard:False, hasAdhaarCard:True}}})
In MongoDB, the dollar sign $ is used as a special character to denote various operators and variables within query and update operations.
Find vs Findone in MongoDB and Filter in Postgrsql
find in MongoDB:
The find method is used to query multiple documents in a collection based on specified criteria.
It returns a cursor, which is a pointer to the result set. You can iterate over the
db.collection.find({ "status": "active" })
findOne in MongoDB:
The findOne method is used to retrieve a single document from the collection that matches the specified criteria.
It returns an individual document, not a cursor.
db.collection.findOne({ "status": "active" })
WHERE clause in PostgreSQL:
In PostgreSQL, the WHERE clause is used to filter rows returned by a SELECT statement
SELECT * FROM table_name WHERE status = 'active';
Insert Statement in MongoDB and Insert in PostgreSQL
MongoDB INSERT Example:
In MongoDB, you use the insertOne or insertMany method to insert documents into a collection. Here's an example using insertOne and insertMany:
db.collection.insertOne({
"name": "John Doe",
"age": 30,
"email": "[email protected]"
})
db.collection.insertMany([
{
"name": "Jane Doe",
"age": 28,
"email": "[email protected]"
},
{
"name": "Bob Smith",
"age": 35,
"email": "[email protected]"
}
])
PostgreSQL INSERT Example:
In PostgreSQL, you use the INSERT INTO statement to add rows to a table. Here's an example:
INSERT INTO table_name (name, age, email)
VALUES ('John Doe', 30, '[email protected]');
INSERT INTO table_name (name, age, email)
VALUES
('Jane Doe', 28, '[email protected]'),
('Bob Smith', 35, '[email protected]');
DataTypes :
Both MongoDB and PostgreSQL support various data types, but their implementations differ. Here's an overview of common data types in MongoDB and PostgreSQL:
MongoDB Data Types: PostgreSQL Data Types:
Schema validation in Mongo-DB
MongoDB provides a feature called "Schema Validation" that allows you to enforce a schema on a collection, ensuring that the documents inserted or updated adhere to a specified structure and set of rules. This feature helps maintain data integrity and consistency within a collection. Schema validation is available starting from MongoDB version 3.2.
Here's an example of how you can use schema validation in MongoDB:
Define a Validation Schema:
db.createCollection("myCollection", {
validator: {
$jsonSchema: {
bsonType: "object",
required: ["name", "age", "email"],
properties: {
name: {
bsonType: "string",
description: "must be a string and is required"
},
age: {
bsonType: "int",
minimum: 0,
description: "must be an integer greater than or equal to 0"
},
email: {
bsonType: "string",
pattern: "^\\S+@\\S+$",
description: "must be a string and match the regular expression pattern"
}
}
}
}
});
Update an Existing Collection with Validation:
db.runCommand({
collMod: "myCollection",
validator: {
$jsonSchema: {
bsonType: "object",
required: ["name", "age", "email"],
properties: {
name: {
bsonType: "string",
description: "must be a string and is required"
},
age: {
bsonType: "int",
minimum: 0,
description: "must be an integer greater than or equal to 0"
},
email: {
bsonType: "string",
pattern: "^\\S+@\\S+$",
description: "must be a string and match the regular expression pattern"
}
}
}
}
});
Atomicity in MongoDB:
In MongoDB, an operation is considered atomic on the level of a single document. MongoDB operations are atomic for a single document, meaning that even if an operation involves multiple steps, it is guaranteed to either fully complete or have no effect at all. This is known as the Atomicity of Operations.
For example, when you perform an update operation using $set or $inc to modify multiple fields in a document, the entire operation is atomic. If the update operation is interrupted, MongoDB ensures that either all the modifications are applied, or none of them are.
However, it's important to note that transactions, as they are commonly understood in relational databases, were only introduced in MongoDB with version 4.0. Transactions provide atomicity for operations that involve multiple documents or multiple operations on a single document.
Atomicity in PostgreSQL:
PostgreSQL supports ACID (Atomicity, Consistency, Isolation, Durability) properties, providing strong guarantees regarding transactional behavior. Atomicity, in the context of PostgreSQL, ensures that a series of database operations either completes entirely or has no effect at all.
PostgreSQL's atomicity applies to transactions, which can involve multiple SQL statements. If any part of a transaction fails, the entire transaction is rolled back, and the database is left unchanged.
Here's a basic example of a transaction in PostgreSQL:
BEGIN;
UPDATE table_name SET column1 = 'new_value' WHERE condition;
INSERT INTO another_table (column1, column2) VALUES ('value1', 'value2');
-- If all operations succeed, commit the transaction
COMMIT;
-- If any operation fails, roll back the entire transaction
ROLLBACK;
In this example, the BEGIN starts a transaction, and the COMMIT statement commits the changes if all operations are successful. If any part of the transaction encounters an error, the ROLLBACK statement is used to undo all changes made within the transaction.
Comparison operators in MongoDB and PostgreSQL are used to perform comparisons between values in queries. Here's a comparison of some commonly used comparison operators in both databases:
MongoDB Comparison Operators:
"In PostgreSQL, the syntax for equality is 'field = value', for greater than it's 'field > value', and for greater than or equal to it's 'field >= value', and so on."