Skip to content

Latest commit

 

History

History
573 lines (437 loc) · 14.1 KB

notes001-013.md

File metadata and controls

573 lines (437 loc) · 14.1 KB

Topic 013: Complex DB queries in Node.js

Topic 013: Complex DB queries in Node.js

MongoDB Cheatsheet

Basic Commands

Connect to MongoDB

mongo

Connect to a specific database

use <database_name>

Show Databases

show dbs

Show Collections

show collections

CRUD Operations

Create (Insert)

db.collection.insertOne({ key: "value" });
db.collection.insertMany([{ key: "value1" }, { key: "value2" }]);

Read (Query)

db.collection.find({ key: "value" });
db.collection.findOne({ key: "value" });

Update

db.collection.updateOne({ key: "value" }, { $set: { key: "new_value" } });
db.collection.updateMany({ key: "value" }, { $set: { key: "new_value" } });
db.collection.replaceOne({ key: "value" }, { key: "new_document" });

Delete

db.collection.deleteOne({ key: "value" });
db.collection.deleteMany({ key: "value" });

Query Operators

Comparison Operators

{
  key: {
    $eq: value;
  }
} // Equal
{
  key: {
    $ne: value;
  }
} // Not Equal
{
  key: {
    $gt: value;
  }
} // Greater Than
{
  key: {
    $gte: value;
  }
} // Greater Than or Equal
{
  key: {
    $lt: value;
  }
} // Less Than
{
  key: {
    $lte: value;
  }
} // Less Than or Equal

Logical Operators

{
  $and: [{ key1: value1 }, { key2: value2 }];
}
{
  $or: [{ key1: value1 }, { key2: value2 }];
}
{
  $not: {
    key: value;
  }
}
{
  $nor: [{ key1: value1 }, { key2: value2 }];
}

Element Operators

{
  key: {
    $exists: true;
  }
} // Exists
{
  key: {
    $type: "type";
  }
} // Type

Array Operators

{ key: { $size: value } }    // Size
{ key: { $all: [value1, value2] } }  // All
{ key: { $elemMatch: { key1: value1, key2: value2 } } }  // Element Match

Update Operators

Field Update Operators

{
  $set: {
    key: "new_value";
  }
}
{
  $unset: {
    key: "";
  }
}
{
  $rename: {
    old_key: "new_key";
  }
}
{
  $inc: {
    key: value;
  }
}
{
  $mul: {
    key: value;
  }
}

Array Update Operators

{
  $push: {
    key: value;
  }
}
{
  $pull: {
    key: value;
  }
}
{
  $addToSet: {
    key: value;
  }
}
{
  $pop: {
    key: 1;
  }
} // Remove last element
{
  $pop: {
    key: -1;
  }
} // Remove first element

Aggregation

Aggregation Pipeline

db.collection.aggregate([
  { $match: { key: value } },
  { $group: { _id: "$key", total: { $sum: "$another_key" } } },
  { $sort: { total: -1 } },
  { $project: { key: 1, total: 1 } },
]);

Common Aggregation Stages

{ $match: { key: value } }
{ $group: { _id: "$key", total: { $sum: "$another_key" } } }
{ $sort: { key: 1 } }
{ $project: { key: 1, another_key: 1 } }
{ $limit: number }
{ $skip: number }
{ $unwind: "$array_key" }
{ $lookup: {
    from: "another_collection",
    localField: "local_key",
    foreignField: "foreign_key",
    as: "new_field"
  } }

Indexes

Create Index

db.collection.createIndex({ key: 1 }); // Ascending index
db.collection.createIndex({ key: -1 }); // Descending index

List Indexes

db.collection.getIndexes();

Drop Index

db.collection.dropIndex("index_name");

Miscellaneous

Count Documents

db.collection.countDocuments({ key: value });

Distinct Values

db.collection.distinct("key", { query });

Backup Database

mongodump --db database_name --out /path_to_backup

Restore Database

mongorestore --db database_name /path_to_backup/database_name

MongoDB Q&A

Comparison with MySQL

In MongoDB, data is stored in flexible, JSON-like documents. Let's compare this with MySQL terminology:

  1. Document in MongoDB:

    • In MongoDB, a document is a single record or data object, similar to a row in a relational database.

    • Documents are stored in collections.

    • Each document in MongoDB is a JSON-like data structure, typically containing key-value pairs.

    • Documents in MongoDB can have nested fields and arrays, making it easy to represent complex data structures.

    • Example of a document in MongoDB:

      {
  "_id": ObjectId("60a24d4c5e1a0d0a54a9f220"),
  "name": "John Doe",
  "age": 30,
  "email": "john@example.com"
}

  2. Collection in MongoDB:

    • In MongoDB, a collection is a group of documents, similar to a table in a relational database.

    • Collections do not enforce a schema, so documents within a collection can have different structures.

    • Collections are schema-less, meaning each document in a collection can have its own unique set of fields.

    • Collections are created implicitly when the first document is inserted.

    • Example of accessing a collection in MongoDB:

      // Accessing the "users" collection
db.users.find();

In comparison to MySQL:

  1. Row in MySQL:

    • In MySQL, a row represents a single record or data object within a table.

    • Rows are organized into tables.

    • Each row consists of a fixed number of columns, each with a specific data type.

    • Rows in MySQL must adhere to the table's schema, meaning they must have values for all defined columns.

    • Example of a row in MySQL:

      | id | name      | age | email           |
|----|-----------|-----|-----------------|
| 1  | John Doe  | 30  | john@example.com|

  2. Table in MySQL:

    • In MySQL, a table is a collection of rows, similar to a collection in MongoDB.

    • Tables enforce a schema, meaning all rows within a table must have the same structure.

    • Tables are explicitly created with a specified schema before any data can be inserted.

    • Example of accessing a table in MySQL:

      SELECT * FROM users;

In summary, MongoDB's document model is more flexible than MySQL's table-based model. Documents in MongoDB can have varying structures within the same collection, making it suitable for handling data with varying schema requirements or evolving data models. MySQL, on the other hand, enforces a fixed schema for each table, requiring all rows to have the same structure.

CRUD (Create, Read, Update, Delete) operations in MongoDB using the MongoDB shell

  1. Insert Document: To insert a document into a collection, you can use the insertOne() or insertMany() method. Here's an example using insertOne():

    // Insert a single document into the "users" collection
db.users.insertOne({
  name: "John Doe",
  age: 30,
  email: "john@example.com",
});

    This inserts a single document with the specified fields into the "users" collection.

  2. Update Document: To update a document in MongoDB, you can use the updateOne() or updateMany() method. Here's an example updating a document using updateOne():

    // Update a document in the "users" collection
db.users.updateOne(
  { name: "John Doe" }, // Filter criteria
  { $set: { age: 31 } } // Update operation
);

    This code updates the document in the "users" collection where the name is "John Doe" by setting the "age" field to 31.

  3. Delete Document: To delete a document from a collection, you can use the deleteOne() or deleteMany() method. Here's an example using deleteOne():

    // Delete a document from the "users" collection
db.users.deleteOne({ name: "John Doe" });

    This code deletes a single document from the "users" collection where the name is "John Doe".

  4. Create Document: To create a new collection in MongoDB, you don't explicitly use a "create" command. MongoDB automatically creates a collection when you insert the first document into it. However, if you want to ensure a collection exists without inserting any document, you can use createCollection() method:

    // Create a new collection named "products"
db.createCollection("products");

    This creates an empty collection named "products" in the database.

These are basic examples of CRUD operations in MongoDB using the MongoDB shell. In a real-world scenario, you might also want to handle errors, handle bulk operations efficiently, and consider the impact on performance when dealing with large datasets.

Indexing, Replication and Pipeline concept in MongoDB

Certainly! Let's illustrate each concept with some code examples:

  1. Indexing in MongoDB: Here's how you can create an index on a specific field in a MongoDB collection using the MongoDB shell:

    // Create an index on the "name" field of the "users" collection
db.users.createIndex({ name: 1 });

    This code creates a single-field index on the "name" field of the "users" collection, which will improve the performance of queries that involve filtering or sorting by the "name" field.

  2. Replication in MongoDB: Setting up a basic MongoDB replica set involves starting multiple MongoDB instances and configuring them to form a replica set. Here's a simplified example of how you can initiate a replica set with three nodes using the MongoDB shell:

    // Start the first MongoDB instance
mongod --port 27017 --dbpath /data/db1 --replSet rs0

// Start the second MongoDB instance
mongod --port 27018 --dbpath /data/db2 --replSet rs0

// Start the third MongoDB instance
mongod --port 27019 --dbpath /data/db3 --replSet rs0

// Connect to one of the MongoDB instances
mongo --port 27017

// Initiate the replica set
rs.initiate({
  _id: "rs0",
  members: [
    { _id: 0, host: "localhost:27017" },
    { _id: 1, host: "localhost:27018" },
    { _id: 2, host: "localhost:27019" }
  ]
});

    This code starts three MongoDB instances on different ports and then initiates a replica set named "rs0" with these instances as members.

  3. Pipeline Concept in MongoDB: Here's an example of an aggregation pipeline in MongoDB that calculates the average age of users in a collection:

    // Aggregation pipeline to calculate average age of users
db.users.aggregate([
  {
    $group: {
      _id: null,
      avgAge: { $avg: "$age" },
    },
  },
]);

    This aggregation pipeline consists of a single stage using the $group operator to group all documents into a single group (_id: null) and then calculate the average age ($avg) of the users based on the "age" field.

MongoDB use in Node.js

To implement a complex MongoDB query in a Node.js application, you'll typically use the MongoDB Node.js driver or an ODM (Object Data Modeling) library like Mongoose. Here's a step-by-step guide and example using both the MongoDB Node.js driver and Mongoose.

Using MongoDB Node.js Driver

  1. Install MongoDB Node.js Driver:

    npm install mongodb

  2. Connect to MongoDB:

    const { MongoClient } = require("mongodb");

const uri = "your_mongodb_connection_string";
const client = new MongoClient(uri, { useNewUrlParser: true, useUnifiedTopology: true });

async function run() {
  try {
    await client.connect();
    console.log("Connected to database");

    const database = client.db("your_database");
    const collection = database.collection("your_collection");

    // Define your complex query
    const query = {
      $and: [{ age: { $gt: 25 } }, { $or: [{ status: "A" }, { score: { $gt: 80 } }] }],
    };

    const projection = { _id: 0, name: 1, age: 1, status: 1, score: 1 };

    // Execute the query
    const results = await collection.find(query).project(projection).toArray();

    console.log("Query Results:", results);
  } finally {
    await client.close();
  }
}

run().catch(console.dir);

Using Mongoose

  1. Install Mongoose:

    npm install mongoose

  2. Connect to MongoDB and Define Schema:

    const mongoose = require("mongoose");

const uri = "your_mongodb_connection_string";
mongoose.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true });

const userSchema = new mongoose.Schema({
  name: String,
  age: Number,
  status: String,
  score: Number,
});

const User = mongoose.model("User", userSchema);

async function run() {
  try {
    console.log("Connected to database");

    // Define your complex query
    const query = {
      $and: [{ age: { $gt: 25 } }, { $or: [{ status: "A" }, { score: { $gt: 80 } }] }],
    };

    // Execute the query
    const results = await User.find(query, "name age status score").exec();

    console.log("Query Results:", results);
  } finally {
    await mongoose.connection.close();
  }
}

run().catch(console.dir);

Explanation of the Example

MongoDB Node.js Driver Example:

  1. Install the driver: You first install the MongoDB driver using npm.
  2. Connect to MongoDB: Use the MongoClient to connect to the MongoDB server.
  3. Define the Query: Construct a complex query using MongoDB query operators ($and, $or, $gt).
  4. Execute the Query: Use find method with the query and project specific fields.
  5. Output the Results: The results are logged to the console.

Mongoose Example:

  1. Install Mongoose: You install Mongoose, an ODM for MongoDB.
  2. Connect to MongoDB: Connect to the MongoDB server using Mongoose.
  3. Define Schema and Model: Define a Mongoose schema and model for the collection.
  4. Define the Query: Similar to the MongoDB driver example, but uses Mongoose's query syntax.
  5. Execute the Query: Use Mongoose's find method with the query and projection.
  6. Output the Results: The results are logged to the console.

Summary

Both examples show how to connect to a MongoDB database, define and execute a complex query, and handle the results in a Node.js application. The MongoDB Node.js driver gives you direct access to the MongoDB API, while Mongoose provides an abstraction layer with additional features like schema validation and middleware. Choose the approach that best fits your application's needs.