Tag: mongo db

Pagination in React, Node.js and MongoDB

Posted on by adnanafzal565

In this tutorial, we will show you, how you can do pagination in your React app having Node.js and MongoDB as backend. Even if you have any other backend technology, you can still get the algorithm and apply it in your language.

React

Following is our React component that will call an AJAX request and fetch the data from API. After fetching the data from API, it will render the data along with the pagination links. Whenever any of the pagination link is clicked, it will refetch the data based on the clicked page number.

<script src="js/babel.min.js"></script>
<script src="js/react.development.js"></script>
<script src="js/react-dom.development.js"></script> 

<div id="app"></div>

<script type="text/babel">

    function App() {

        // Set current page
        const [page, setPage] = React.useState(1);

        // Number of pages on pagination
        const [pages, setPages] = React.useState(0);

        // Whenever it's value is updated, the data will be re-fetched
        const [refetch, setRefetch] = React.useState(0);

        // Data array
        const [users, setUsers] = React.useState([]);

        // Check if there are more pages
        const [hasMorePages, setHasMorePages] = React.useState(false);

        async function loadMore() {
            // Creating a built-in AJAX object
            var ajax = new XMLHttpRequest();

            // Tell the method, URL of request and make is asynchronous
            ajax.open("POST", "http://localhost:3000/fetch-users", true);

            // Detecting request state change
            ajax.onreadystatechange = function () {

                // Called when the response is successfully received
                if (this.readyState == 4) {

                    if (this.status == 200) {
                        // For debugging purpose only
                        // console.log(this.responseText);

                        // Converting JSON string to Javasript array
                        var response = JSON.parse(this.responseText);

                        // Update state variable
                        setUsers(response.users);

                        // Set if there are more pages
                        setHasMorePages(response.hasMorePages);

                        // Set pagination
                        setPages(response.pages);
                    }
                }
            };

            // Create form data object
            const formData = new FormData();

            // Attach current page number
            formData.append("page", page);

            // Send the request
            ajax.send(formData);
        }

        // Run function when page value updates
        React.useEffect(function () {
            loadMore();
        }, [refetch]);

        return (
            <>
                {/* Display data */}

                { users.map(function (user) {
                    return (
                        <p key={ `user-${ user._id }` }>{ user.name }</p>
                    );
                }) }

                {/* Show pagination if required */}
                
                { pages > 1 && (
                    <nav>
                        <ul>
                            { Array.from({ length: pages }, function (_, index) { return index + 1 } ).map(function (p) {
                                return (
                                    <li key={ `pagination-${ p }` } className={ `page-item ${ p == page ? "active" : "" }` }>
                                        <a href="#" onClick={ function (event) {
                                            event.preventDefault();
                                            setPage(p);
                                            setRefetch(refetch + 1);
                                        } }>{ p }</a>
                                    </li>
                                );
                            }) }
                        </ul>
                    </nav>
                ) }
            </>
        );

    }

    ReactDOM.createRoot(
        document.getElementById("app")
    ).render(<App />);

</script>

Node.js and MongoDB

Now in our Node.js server, we will create an API that will return all the records based on the current page from client side. It will also return the number of pagination links.

const express = require("express");
const app = express();
const http = require("http").createServer(app);

const mongodb = require("mongodb");
const ObjectId = mongodb.ObjectId;
const MongoClient = mongodb.MongoClient;

const cors = require("cors");
app.use(cors("http://localhost/pagination-in-react-node-js-and-mongodb"));

const expressFormidable = require("express-formidable");
app.use(expressFormidable({
    multiples: true
}));

const port = process.env.PORT || 3000;
const databaseName = "test"
const MONGO_URI = process.env.MONGO_URI || "mongodb://127.0.0.1:27017";

http.listen(port, async function () {
    console.log("Server started");

    const client = new MongoClient(MONGO_URI);
    try {
        await client.connect();
        const db = client.db(databaseName);
        console.log("Database connected.");

        app.post("/fetch-users", async function (request, result) {
            const limit = 2;
            const page = parseInt(request.fields.page || 1);
            const skip = (page - 1) * limit;

            const users = await db.collection("users")
                .find({})
                .sort({
                    _id: 1
                })
                .skip(skip)
                .limit(limit)
                .toArray();

            const usersArr = [];
            for (let a = 0; a < users.length; a++) {
                const obj = {
                    _id: users[a]._id || "",
                    name: users[a].name || ""
                };

                usersArr.push(obj);
            }

            const totalCount = await db.collection("users").countDocuments({});
            const hasMorePages = (page * limit) < totalCount;
			const pages = Math.ceil(totalCount / limit);

            result.json({
                status: "success",
                message: "Data has been fetched.",
                users: users,
                hasMorePages: hasMorePages,
                pages: pages
            });
        });

    } catch (exp) {
        console.log(exp.message);
    }
});

That’s how you can create pagination links in your React app with Node.js and MongoDB as backend. If you face any problem in following this, feel free to contact me.

Load more in React, Node.js & MongoDB

Posted on by adnanafzal565

Previously, we wrote 2 articles on how to add a “load more” button. First article uses Node.js and MongoDB and EJS for frontend and second article uses PHP and MySQL. In this article, we will show you, how you can create a “load more” button in React with Node.js and MongoDB as your backend. This is the refined version of previous load more tutorial in Node.js and MongoDB.

First, let me show you all the code. Then I will explain everything.

React

function App() {

    // Set current page
    const [page, setPage] = React.useState(1);

    // Data array
    const [users, setUsers] = React.useState([]);

    // Check if there are more pages
    const [hasMorePages, setHasMorePages] = React.useState(false);

    async function loadMore() {
        // Creating a built-in AJAX object
        var ajax = new XMLHttpRequest();

        // Tell the method, URL of request and make is asynchronous
        ajax.open("POST", "http://localhost:3000/fetch-users", true);

        // Detecting request state change
        ajax.onreadystatechange = function () {

            // Called when the response is successfully received
            if (this.readyState == 4) {

                if (this.status == 200) {
                    // For debugging purpose only
                    // console.log(this.responseText);

                    // Converting JSON string to Javasript array
                    var response = JSON.parse(this.responseText);
            
                    // Get data from API
                    const newArr = response.users;

                    // ❌ Less efficient, more readable
                    // Get current data
                    /*const tempArr = [ ...users ];

                    // Loop through all new records
                    for (let a = 0; a < newArr.length; a++) {
                        // And add them in existing array
                        tempArr.push(newArr[a]);
                    }

                    // Update state variable
                    setUsers(tempArr);*/

                    // ✅ Efficient way
                    setUsers([...users, ...newArr]); 

                    // Set if there are more pages
                    setHasMorePages(response.hasMorePages);
                }
            }
        };

        // Create form data object
        const formData = new FormData();

        // Attach current page number
        formData.append("page", page);

        // Send the request
        ajax.send(formData);
    }

    // Run function when page value updates
    React.useEffect(function () {
        loadMore();
    }, [page]);

    return (
        <>
            {/* Display data */}

            { users.map(function (user) {
                return (
                    <p key={ `user-${ user._id }` }>{ user.name }</p>  
                );
            }) }

            {/* Show load more button if there are more pages */}

            { hasMorePages && (
                <button type="button"
                    onClick={ function () {
                        // Increment page value
                        // It will automatically call the loadMore function from useEffect hook
                        setPage(page + 1);
                    } }>Load more</button>
            ) }
        </>
    );
}

Here, we are displaying all the data we are getting from API. We are displaying the “load more” button only if there are more pages required. We created a React.useEffect hook and calling the loadMore function everytime the page value gets updated. It will automatically gets called first time when the page loads. After that, every time you click the “Load more” button, we are simply incrementing the page value by 1. It will trigger the React.useEffect hook, thus calling the loadMore function again.

From API, we will receive the data that will be an array, and a boolean variable that tells if there are more pages required. Based on the latter, we will show or hide the “Load more” button. If there are more pages required, you can keep pressing load more button. When there are no more records in the database, then the load more button will be removed.

When the response from API is received, we are appending the new data in our existing data array. The way to do that in React is different. First, we need to create a shallow copy of our existing array using spread operator: const tempArr = [ …users ]; Then we need to loop through all new data received from API, and push it in your shallow copy of array. Finally, we will update our state variable and will render the new data.

For rendering, we are using Javascript map function to iterate over an array. In each paragraph, we should have a unique key value for React to identify each element uniquely. In order to create a unique key, we are using user ID, and we are displaying user name in each paragraph. This is create a “load more” button in our React component. In next step, we will create it’s API.

Node.js & MongoDB

// Run command in your terminal:
// npm install express http mongodb cors express-formidable
const express = require("express");
const app = express();
const http = require("http").createServer(app);

const mongodb = require("mongodb");
const ObjectId = mongodb.ObjectId;
const MongoClient = mongodb.MongoClient;

const cors = require("cors");
app.use(cors("http://localhost/load-more.html"));

// Native way to allow CORS
/*// Add headers before the routes are defined
app.use(function (req, res, next) {
    // Website you wish to allow to connect
    res.setHeader("Access-Control-Allow-Origin", "*");
    // Request methods you wish to allow
    res.setHeader("Access-Control-Allow-Methods", "GET, POST, OPTIONS, PUT, PATCH, DELETE");
    // Request headers you wish to allow
    res.setHeader("Access-Control-Allow-Headers", "X-Requested-With,Content-Type,Authorization");
    // Set to true if you need the website to include cookies in the requests sent
    // to the API (e.g. in case you use sessions)
    res.setHeader("Access-Control-Allow-Credentials", true);
    // Pass to next layer of middleware
    next();
});*/

const expressFormidable = require("express-formidable");
app.use(expressFormidable({
    multiples: true
}));

const port = process.env.PORT || 3000;
const databaseName = "test"
const MONGO_URI = process.env.MONGO_URI || "mongodb://127.0.0.1:27017";

http.listen(port, async function () {
    console.log("Server started");
    
    const client = new MongoClient(MONGO_URI);
    try {
        await client.connect();
        const db = client.db(databaseName);
        console.log("Database connected.");

        // Seeding the data in database
        /*const usersCount = await db.collection("users").countDocuments({});
        if (usersCount == 0) {
            await db.collection("users")
                .insertMany([
                    { name: "Adnan" },
                    { name: "Afzal" },
                    { name: "Ahmad" },
                    { name: "Khalid" },
                    { name: "Tariq" },
                ]);
        }*/

        app.post("/fetch-users", async function (request, result) {
            const limit = 1;
			const page = parseInt(request.fields.page || 1);
			const skip = (page - 1) * limit;
			
			const users = await db.collection("users")
                .find({})
                .sort({
                    _id: 1
                })
                .skip(skip)
                .limit(limit)
                .toArray();

            const usersArr = [];
            for (let a = 0; a < users.length; a++) {
                const obj = {
                    _id: users[a]._id || "",
                    name: users[a].name || ""
                };

                usersArr.push(obj);
            }

			const totalCount = await db.collection("users").countDocuments({});
    		const hasMorePages = (page * limit) < totalCount;

			result.json({
				status: "success",
				message: "Data has been fetched.",
				users: users,
				hasMorePages: hasMorePages
			});
		});
    } catch (exp) {
        console.log(exp.message);
    }
});

Here, we are first starting our server and connecting with MongoDB database. We created an API that accepts the page number as parameter. If the page number is not provided, then the default value of it will be 1. We are setting the limit to 1 for testing. We subtract 1 from current page value and multiply it by the limit, it will give use the number of records we need to skip.

Then we are fetching the data from database using skip and limit. Your document might have a lot of fields, so we created an obj to only return those fields that are need for this API. Finally, in order to check if there is a need for more pages, we first need to find the total number of records in our collection. We can then check if it is greater than the product of limit and page variable. If it is greater, it means there are still more records in the database.

That’s how you can create a “load more” button in React as your frontend and Node.js and MongoDB as your backend. If you face any problem in following this, kindly do let me know.

Joins in Mongo DB

Posted on by adnanafzal565

If you are working in MySQL, you can use joins to match rows between multiple tables. In Mongo DB, however you are working in documents instead of rows, you can still use joins to combine data from 2 different collections.

Here is how you can do it. Let’s say you have 2 collections, users and posts. You want to save user data in each post document so you can know which user created that post.

const userObj = {
    name: "Adnan"
};

await db.collection("users")
    .insertOne(userObj);

const postObj = {
    title: "My first post",
    userId: userObj._id
};

await db.collection("posts")
    .insertOne(postObj);

After that, you will have one document in users collection.

users collection - mongo db joins

And you will have one document in posts collection. That document will have the same user ID as in users collection.

posts collection - mongo db joins

Now in order to fetch all the posts along with the users data, you can run the following query:

const posts = await db.collection("posts")
    .aggregate([
        {
            $lookup: {
                from: "users",
                localField: "userId",
                foreignField: "_id",
                as: "user"
            }
        },
        {
            $unwind: "$user"
        }
    ])
    .toArray();

console.log(posts);

While in traditional way, you use find({}) function, for complex queries like join, you need to use aggregate function. $lookup operator is used to perform a join on multiple collections.

  • from: This tells the collection name whom to perform join with.
  • localField: This is the name of field in current collection, in this case, the current collection is “posts”.
  • foreignField: This is the field in other collection whom you are performing a join operation. In this case, it is “_id” field in “users” collection.
  • as: This is the alias of the object that will be returned as a result of join operation.

By default, the join will return “user” as an array. To convert each to a separate document, you can use $unwind operator. This will deconstruct the array into each document separately. In this case, it will be returning only 1 user as an array, so it will be deconstructed to 1 document only.

posts users joins - mongo db

That’s how you can perform joins in Mongo DB using aggregate and lookup. If you face any issue in performing a join, feel free to contact me.

Multi-purpose platform in Node.js and MongoDB

Posted on by adnanafzal565

A multi-purpose platform is created in Node.js and MongoDB. Developing an API in Node.js and MongoDB allows you to create real-time, high performance and scalable applications. When you are building a website or a mobile app, you might have a different design than what is already built. But your backend will be almost similar to your competitor. Let’s say you want to build a social network like Facebook. You will pick an HTML template or design a UI specific for your business. But you want to have a module to create users, posts, pages, groups, friends, chats and much more.

So we are providing you a multi-purpose platform that allows you to use social network, job portal, manage your media files and much more. It is a self-hosted script so all the data will be stored on your server, no third-party storage service is used here.

Tech stack 💻

  • Bootstrap 5+
  • React 18+
  • Node.js 20+
  • MongoDB 4+

User Registration and Profile 🙋🏻‍♂️

This API allows you to register an account. The passwords are encrypted before saving them in database. User can also login using his email and password that he entered during registration.

On successfull login, a Json Web Token (JWT) is generated and returned in the API response. The client application needs to save that token in its local storage because that token will be use in other APIs in headers to identify the user. This token is also stored in server as well. So if you want to logout a user manually, you can simply remove his token from database.

User can edit his name and profile image. The email field is read-only, it cannot be changed. User can also change his password. For that, he needs to provide his current password and enter his new password 2 times for confirmation.

Social Network Posts 📝

If you are building a social network, then this module allows you to create and manage posts for a social network. You can also share other’s posts on your timeline. You can edit or remove your own posts. Other users will be able to like, comment or share your posts. Users will be able to edit to delete their own comments. You will be able to reply to the comments on your posts, and you can also edit or remove your reply if you want. Users can also see the list of all users who has liked or shared your post.

Pages 📃

If you are running a business, you can create a page for your business and start posting about your products or services in that page. In order to create a page, you need to set it’s name, tell a little about the page, and provide a cover photo for the page. User can follow your page and start seeing your page’s posts in their newsfeed. Only creator of page can create a post in that page.

You can see a list of all users who has followed your page. User can also see a list of all pages he has followed. The owner of the page can edit or delete the page. Once page is deleted, all the posts inside that will be deleted as well.

Groups 🥳

You can create groups to create a community of like-minded people. In order to create a group, you need to enter the name of group, a little description about the group and it’s cover photo. You can see a list of all groups created in the platform. There are separate links from where you can see only the groups that you have created or the groups you have joined. You can join or leave the group whenever you want. However, admin of the group can also remove you from the group if he wants.

Only admin or group members can post in a group. Posts uploaded by admin will be immediately displayed on the groups newsfeed. However, the posts uploaded by group members will be held pending for approval from the admin. Admin can see a list of all posts uploaded by group members. He can accept or decline a post he wants.

Admin of the group can update the name or description of the group, and he can also change the cover photo of the group. Admin can delete the group as well. Upon deleting, all the posts uploaded in that group will be deleted as well.

Media 📂

This API allows you to upload media files (images, videos) on the platform. Each media file will have a title, alt attribute, and caption. These will help you in your Search Engine Optimization (SEO). You can edit or delete your own media files whenever you want.

Friends 🙏🏻

You can create friends by sending them a friend request, just like you do in Facebook. Other user can see a list of all friend requests he has received. He can accept or decline your request. If accepted, then you both will become friends and you can chat with each other. You can see a list of all of your friends. At any point, you can remove a user from your friend list.

Realtime Chat 💬

You can have realtime chat with your friends. Chats are end-to-end encrypted, that means that the messages are encrypted before sending to the server. And they are decrypted only after receiving the response from the server. Your messages will remain secure in-transit.

For realtime communication, we are using Socket IO. When you send a message, an event is emitted. The receiver will be listening to that event and display a notification alert that you have received a new message. If the chat is already opened, then the new message will automatically be appended at the bottom of the chat.

Job Portal 👨🏻‍🔧 👨🏻‍🍳 👩🏻‍🏫 👩🏻‍🎨

Since this is a multi-purpose platform and it is developed in scalable technologies (Node.js & MongoDB), a job portal platform is also added that allows recruiter to post jobs and candidates can apply on that job. Recruiter can see all the applications he has received on a job and can change the status of applicant to shortlisted, interviewing, rejected or selected etc. Candidate can upload multiple CVs and choose the relevant CV while applying for the job. Recruiter can update or delete the job any time.

You can also filter job applications by status. So if you are recruiter, you will post a job. Then you will start receiving applications from candidates. Recruiter can change the status from interviewing to shortlisted employess. Then recruiter can change the status to interviewing. Recruiter can also reject the candidate, but he also needs to provide a reason for rejection. Finally, recruiter can set the candidate as “Hired”. So there must be a way for recruiter to filter job applications by their status.

We also have a PHP and MySQL version of Job Portal website. You can get it from here.

Admin Panel 👨🏻‍💼

An admin panel is created where you can manage all the users, social network posts and jobs created on the platform. It is a single page application created in React JS and can be deployed as a sub-domain to your main website.

Super admin can see the list of all users. He can add a new user if he wants. An email with password set by admin will be sent to the new user. Admin can also update the user password as well. He can also delete the user, all the posts uploaded by that user will be deleted as well.

Super admin can see all the posts uploaded on social network platform. Just like users, he can delete any post he did not feel appropriate.

Admin can also see all the jobs posted by recruiter and if any job post goes against the standards, super admin can remove it.

Blogs 📜

Since this is a multi-purpose platform, so I added a blog module that allows you to write articles on your website. Admin can create blogs from admin panel and they will be displayed on user side. User can post a comment if they are logged-in. Other users or admin can reply to their comments. Admin can manage all posts and comments from admin panel. Admin can delete any post or command he did not find suitable.

We are using sockets to update the blogs in realtime. So if user is reading a post and admin changes something from admin panel, it will immediately gets updated on user side. User does not need to refresh the page.

Page Builder 🏗

We are using page builder to create blog posts. It allows you to create paragraphs, headings, images, videos and buttons.

You can set the attributes like id and class. You can also set CSS properties of each tag.

If you are using image or video, you can add alt and caption attributes as well.

Freelance Platform 👨🏻‍💻 🤑

This multi-purpose platform project also has a freelance platform where you can hire experts to get your job done. Or, if you are a skilled person, you can find work there.

There are 2 entities in freelance platform: Buyer 💰 and Seller 🤑. Buyer will create a task to be done, he will mention his budget and deadline. Buyer must have sufficient balance in their account. They can add balance using Stripe. 💳

Sellers will start bidding on that task. Seller will also send a proposal mentioning how he will get the work done, what is his offer and in how many days he will do it. 🌚

Buyer will see all the bids from sellers. He will also see a freelance profile of each bidder, telling the orders done by each seller, their ratings and reviews etc. ⭐️

Buyer can accept the bid of any seller he seems fit for the job. The seller will be notified and their order will be started. ⏰

On their order detail page, they can chat with each other. They can send messages 💬 with attachments 🧷. At any point, buyer or seller can cancel the order.

After the work is done, buyer can complete the order ✅. Once it is completed, the amount that was offered in the bid will be deducted from buyer’s account. 5 percent will be deducted as a “platform fee”, and the remaining 95% of the amount will be added in the seller’s account 🤑.

Buyer can also gives ratings ⭐️ to a seller and also can give a review about his experience. This will help seller build his freelance profile.

Seller can withdraw the money by entering his bank account details 🏦. These details will be sent to the admin panel 👨🏻‍💼. Admin can transfer the funds and once the transfer is completed, seller’s balance will be returned to 0.

Notifications 🔔

User will receive a notification when:

  • His freelance order gets completed/cancelled.
  • A new message has been received on his order.
  • His bid got accepted on freelance project.
  • His withdrawl request has been updated.
  • Someone commented on his post.
  • Someone replied to his comment.
  • Someone likes his post on social network.
  • His post has been shared by someone.

These notifications will be displayed as a bell icon 🔔 on header when user is logged-in. If he has any unread notifications, then it will be displayed as read.

Unread notification’s background will be gray. On clicking the notification, it will be marked as read.

Installer 🤹🏻‍♂️

Run the following commands in your “admin” folder:

npm install
npm start

In order to create a super admin, you need to run the following command in your “premium-api” folder once:

node installer.js

This will create a super admin in your database. You can set the email and password of your choice from “installer.js” file.

Paste the “multi-purpose-platform-nodejs-mongodb” folder in your “htdocs” folder if you are using XAMPP or MAMP, or in “www” folder if you are using WAMP.

Run the following commands in the “premium-api” folder:

npm update
npm install -g nodemon
nodemon index.js

Project can be accessed from:

http://localhost/multi-purpose-platform-nodejs-mongodb/web/index.html

Demos 🎯

1. Social network – Posts

2. Pages

3. Groups

4. Friends & chat

5. Job portal

6. Admin panel

7. Blogs

8. Freelance Platform

More features can also be added on-demand in this multi-purpose platform. If you do not want all the features, we can remove or change features as per your needs.

Files included 🗄

  • api
    • index.js
    • installer.js
    • modules
      • admin
        • admin.js
        • auth.js
      • auth-optional.js
      • auth.js
      • freelance.js
      • banks.js
      • payments.js
      • blogs.js
      • categories.js
      • chats.js
      • files.js
      • job-portal
        • cvs.js
        • gigs.js
        • jobs.js
      • mails.js
      • media.js
      • notifications.js
      • sn
        • friends.js
        • groups.js
        • pages.js
        • posts.js
      • users.js
    • package.json
    • uploads
      • private
      • public
  • web
    • freelance
      • buyer.html
      • order-detail.html
      • orders.html
      • seller.html
      • task-detail.html
    • blogs
      • index.html
      • detail.httml
    • change-password.html
    • index.html
    • balance.html
    • withdraw.html
    • job-portal
      • applied.html
      • create.html
      • cv-manager.html
      • detail.html
      • edit.html
      • index.html
      • my.html
    • login.html
    • media
      • index.html
      • edit.html
    • profile.html
    • public
      • css
      • js
      • img
    • register.html
    • sn
      • chat.html
      • edit-comment.html
      • edit-post.html
      • edit-reply.html
      • friends.html
      • groups
        • create.html
        • detail.html
        • edit.html
        • index.html
        • members.html
        • my-joined.html
        • my.html
        • pending-posts.html
      • index.html
      • notifications.html
      • pages
        • create.html
        • detail.html
        • edit.html
        • index.html
        • followers.html
        • my-followed.html
        • my.html
      • post.html
      • profile.html
      • search.html
      • send-reply.html
  • admin (SPA)
    • package.json
    • public
    • src
      • App.css
      • App.js
      • index.js
      • components
        • blogs
          • AddPost.js
          • Comments.js
          • EditPost.js
          • Posts.js
        • categories
          • AddCategory.js
          • Categories.js
          • EditCategory.js
        • Dashboard.js
        • ecommerce
          • AddProduct.js
          • EditProduct.js
          • Orders.js
          • Products.js
        • files
          • Files.js
        • jobs
          • Jobs.js
        • layouts
          • Header.js
          • Footer.js
          • Sidebar.js
        • Login.js
        • sn
          • Posts.js
        • users
          • AddUser.js
          • EditUser.js
          • Users.js

TrustPilot clone – PHP, MySQL, Laravel

Posted on by adnanafzal565

A clone of TrustPilot website is created in PHP and MySQL using Laravel framework version 11. For frontend rendering, I am using Vue JS 3 and on admin side I am using React JS.

Files included:

  • .php
  • .css
  • .js

Features:

  1. User can post reviews about a company.
  2. Can flag a review.
  3. Can share reviews on social media.
  4. Company owners can claim a company by verifying their email address.
  5. Automatically takes screenshot of a company home page.
  6. Admin can add companies from admin panel.
  7. Admin can view all reviews, flags and claims.

Demo:

Save and display images in Binary – NodeJS

Posted on by adnanafzal565

In this tutorial, you will learn, how you can save and display images in Binary in NodeJS and MongoDB.

We will also create an API that will return a binary image as a response.

Saving images in the database has many advantages over saving images in file storage.

  1. First, if you are deploying in Heroku, they do not provide persistent storage for their free tier. This means that the files uploaded on Heroku will automatically be removed after 30 minutes of inactivity.
  2. Second, migrating from one deployment platform to another is easy. Since you do not have to move all the uploaded files too. You can use mongodb.com for your MongoDB database and use this on all platforms.
  3. Third, we will be saving images in Binary format so it will take less space than saving in Base64.

Video tutorial:

The following route will save the user-uploaded image as Binary in MongoDB using NodeJS.

// npm install fs
// import file system module
const fs = require("fs")

app.post("/upload", async function (request, result) {
    // get user-uploaded file
    const image = request.files.image
  
    // reading file data
    const fileData = await fs.readFileSync(image.path)
    
    // converting to binary
    const binary = Buffer.from(fileData)
    
    // saving in database
    await db.collection("images").insertOne({
        path: binary
    })
    
    // sending response back to client
    result.send("Done")
})

Check out this tutorial if you want to know how to connect with MongoDB.

Now that the image has been saved, we will create a GET route that will return the image as a base64 string.

// set EJS as templating engine
app.set("view engine", "ejs")

app.get("/", async function (request, result) {
    // get image from collection
    const image = await db.collection("images")
        .findOne({})
        
    // variable to get base64 string
    let imageString = ""
    
    // check if document exists
    if (image != null) {
        // image.path will return binary
        // buffer() function is called on binary object
        imageString = "data:image/png;base64," + image.path.buffer.toString("base64")
    }
    
    // sending data to file "views/index.ejs"
    result.render("index", {
        image: imageString
    })
})

After that, we need to create a folder named “views” and inside it a file named “index.ejs” and write the following code in it:

<img src="<%= image %>" style="width: 100%;" />

That’s how you can save and display images in Binary in NodeJS and MongoDB.

MongoDB GridFS

Posted on by adnanafzal565

In this tutorial, you will learn, how you can upload, retrieve, and delete files using MongoDB GridFS.

Video tutorial:

Upload the file to MongoDB GridFS

First, in your Node JS file, you need to create an instance of your GridFS bucket. You can create as many buckets as you want.

// include MongoDB
const mongodb = require("mongodb")

// get MongoDB client
const mongoClient = mongogb.MongoClient

// connect with MongoDB server
const client = await mongoClient.connect("mongodb://localhost:27017")

const db = client.db("mongodb_gridfs")

// create GridFS bucket instance
const bucket = new mongodb.GridFSBucket(db)
  1. First, it includes the MongoDB module.
  2. Then it gets a Mongo client object that helps in connecting with the database.
  3. Then we connect to the server.
  4. After that, we set the database.
  5. And finally, we are creating an instance of a bucket.

You can also give your bucket a name to identify.

// create GridFS bucket instance named "myBucketName"
const bucket = new mongodb.GridFSBucket(db, {
  bucketName: "myBucketName"
})

Following POST route will save the file.

// npm install fs
const fs = require("fs")

// npm install ejs
app.set("view engine", "ejs")

// npm install express-formidable
const expressFormidable = require("express-formidable")
app.use(expressFormidable())

app.post("/upload", function (request, result) {
  // get input name="file" from client side
  const file = request.files.file
  
  // set file path in MongoDB GriDFS
  // this will be saved as "filename" in "fs.files" collection
  const filePath = (new Date().getTime()) + "-" + file.name
  
  // read user uploaded file stream
  fs.createReadStream(file.path)
  
    // add GridFS bucket stream to the pipe
    // it will keep reading and saving file
    .pipe(
      bucket.openUploadStream(filePath, {
        // maximum size for each chunk (in bytes)
        chunkSizeBytes: 1048576, // 1048576 = 1 MB
        // metadata of the file
        metadata: {
          name: file.name, // file name
          size: file.size, // file size (in bytes)
          type: file.type // type of file
        }
      })
    )
    // this callback will be called when the file is done saving
    .on("finish", function () {
      result.send("File saved.")
    })
})

Now if you check in your “mongodb_gridfs” database, you will see 2 new collections.

  1. fs.files
    • This will save all uploaded files.
  2. fs.chunks
    • This will save all chunks of each file with that file ID.

Fetch all files from MongoDB GridFS

The following GET route will fetch all files uploaded to MongoDB GridFS.

app.get("/", async function (request, result) {
  const files = await bucket.find({
    // filename: "name of file" // 
  })
    .sort({
      uploadDate: -1
    })
    .toArray()
  result.render("index", {
    files: files
  })
})

Now you need to create a folder named “views” and inside that folder create a file named “index.ejs”.

Then you can loop through all files and display them in the image tag.

<% if (files) { %>
  <% files.forEach(function (file) { %>
    <p><%= file.filename %></p>
    <img src="image/<%= file.filename %>" style="width: 200px;" />
  <% }) %>
<% } %>

Right now, you will see a broken image. Now we need to create an API that will return the image as a response.

Return image as API response

app.get("/image/:filename", async function (request, result) {
  // get file name from URL
  const filename = request.params.filename
  
  // get file from GridFS bucket
  const files = await bucket.find({
    filename: filename
  })
  .toArray()
  
  // return error if file not found
  if (!files || files.length == 0) {
    return result.status(404).json({
      error: "File does not exists."
    })
  }
  
  // it will fetch the file from bucket and add it to pipe
  // result response is added in the pipe so it will keep
  // returning data to the client
  bucket.openDownloadStreamByName(filename)
    .pipe(result)
})

Now you will be able to view the image.

Delete file from MongoDB GridFS

First, you need to create a button after each file.

<% if (files) { %>
  <% files.forEach(function (file) { %>
    <p><%= file.filename %></p>
    <img src="image/<%= file.filename %>" style="width: 200px;" />
    
    <form action="/files/del" method="POST">
      <input type="hidden" name="_id" value="<%= file._id %>" />
      <button type="submit" class="btn btn-danger">Delete</button>
    </form>
  <% }) %>
<% } %>

Then you need to create an API in your Node JS file.

// 
const ObjectId = mongodb.ObjectId

app.post("/files/del", async function (request, result) {
  // get ID from data
  const _id = request.fields._id
  
  // delete file from bucket
  await bucket.delete(ObjectId(_id))
  
  // return response
  result.send("File has been deleted.")
})

This will delete the file and all its chunks from the database. So that’s all for now if you face any problem in following this, kindly do let me know.

You can learn more about the grid file system from Mongo DB’s official website.

Create a URL shortener app in Vue JS, Python, Mongo DB

Posted on by adnanafzal565

In this tutorial, we will create a URL shortener app in Vue JS, Python and Mongo DB. We will use Vue JS as frontend, Python as backend and Mongo DB as database.

We will be creating a single page application in Vue JS 3, FastAPI for creating APIs in Python and standard Mongo DB for storing data.

User will enter a URL and the app will create a shorter URL of it. User can share that short URL on social media. Whenever someone access that short URL, we will show ads to him for 5 seconds. Then he will be able to move to the actual URL.

User can see how many people have clicked on his shorten URL and how many have accessed the real URL.

We will create the frontend in Vue JS 3, it will be a single page application (SPA). The backend will be in Python, we will be using FastAPI.

Add URL – Vue JS

The first step is to show user a form where he can enter his URL to shorten. So go ahead and create a folder anywhere in your computer (I have created on desktop). And inside that folder, create 2 more folders named “web” and “api”.

All our frontend code will go in “web” and all our backend code will go in “api”. Now open your command prompt (CMD) or terminal in your “web” folder and run the following command to install Vue JS.

> npm install -g @vue/cli

Then we will create an SPA in that folder.

> vue create .

If asked for Vue JS version, select Vue JS 3. Then run the following command to start Vue JS on localhost.

> npm run serve

You can access your app from http://localhost:8080/

Now go in your “web/src/components” and delete the “HelloWorld.vue” file. And create a file named “HomeComponent.vue”. Following will be the content of that file:

<template>
	HomeComponent
</template>

<script>
	export default {
		name: "HomeComponent"
	}
</script>

Open “src/main.js” and remove the “HelloWorld” component occurrences from there too.

In your components folder, create a folder named “layouts” and create 2 files in it: “AppHeader.vue” and “AppFooter.vue”. Following will be the content of these files:

// AppHeader.vue

<template>
	AppHeader
</template>

<script>
	export default {
		name: "AppHeader"
	}
</script>

// AppFooter.vue
<template>
	AppFooter
</template>

<script>
	export default {
		name: "AppFooter"
	}
</script>

Then run the following command to install “vue-router” that will be used for navigation, “sweetalert2” for displaying popups and “axios” for making AJAX calls.

npm install vue-router sweetalert2 axios

Then in your “main.js”, initialize the vue-router module.

import { createRouter, createWebHistory } from "vue-router"
import HomeComponent from "./components/HomeComponent.vue"

const app = createApp(App)

const routes = [
	{ path: "/", component: HomeComponent }
]

const router = createRouter({
	history: createWebHistory(),
	routes
})

app.use(router)
app.mount('#app')

Open your “web/src/App.vue” and replace the existing content with the following.

<template>
  <app-header />
    <router-view />
  <app-footer />
</template>

<script>

import AppHeader from "./components/layouts/AppHeader.vue"
import AppFooter from "./components/layouts/AppFooter.vue"

export default {
  name: 'App',
  components: {
    AppHeader,
    AppFooter
  }
}
</script>

After that, download Bootstrap 5 and paste the CSS and JS files in your “web/src/assets” folder. Then include them in your AppHeader component.

import "../../assets/css/bootstrap.css"
import "../../assets/js/jquery.js"
import "../../assets/js/bootstrap.js"

If you get any error regarding popper, just run the following commands.

> npm install @popperjs/core
> npm remove @vue/cli-plugin-eslint

Learn more about them in here.

Then open your HomeComponent and create a form inside <template> tag. The form simply have 1 field to enter the URL and a submit button.

<div class="container">
	<div class="row">
		<div class="offset-md-3 col-md-6">
			<form v-on:submit.prevent="shorten_url">
				<div class="form-group">
					<label class="form-label">Enter URL</label>
					<input type="url" name="url" placeholder="Enter URL" class="form-control" required />
				</div>

				<input type="submit" class="btn btn-primary" style="margin-top: 10px;" value="Shorten" />
			</form>

			<!-- [table goes here] -->
		</div>
	</div>
</div>

Then create a function “shorten_url” in your <script> tag in HomeComponent.

import axios from "axios"
import swal from "sweetalert2"

export default {
	name: "HomeComponent",

	methods: {
		async shorten_url() {
			const form = event.target
			const formData = new FormData(form)
			formData.append("timezone", Intl.DateTimeFormat().resolvedOptions().timeZone)

			try {
				const response = await axios.post(
					this.$api_url + "/shorten-url",
					formData
				)

				if (response.data.status == "success") {
					// [on url shortened success]
				} else {
					swal.fire("Error", response.data.message, "error")
				}
			} catch (exp) {
				console.log(exp)
			}
		}
	}
}

In your “main.js” add the following line that defines the URL of our Python API.

app.config.globalProperties.$api_url = "http://127.0.0.1:8000"

In the above function, we are sending form data to the server. We are also sending current timezone, so the server can return the time (in local timezone) when the URL was added.

Add URL – Python, Mongo DB

So the view is created, now we need to create our backend API that will handle this request.

Making sure you have installed Python 3 in your system, run the following commands in your “api” folder.

pip3 install fastapi uvicorn pymongo python-multipart

Create a file named api.py and write the following code in it:

import random, string
from fastapi import FastAPI, Form
from typing_extensions import Annotated
from fastapi.middleware.cors import CORSMiddleware
from pymongo import MongoClient
from bson.objectid import ObjectId
from datetime import datetime, timezone as timezone_module
from dateutil import tz

MONGO_CONNECTING_STRING = "mongodb://localhost:27017"
client = MongoClient(MONGO_CONNECTING_STRING)
db_name = "url_shortener"
db = client[db_name]

app = FastAPI()

app.add_middleware(
	CORSMiddleware,
	allow_origins=["*"]
)

@app.post("/shorten-url")
def shorten_url(url: Annotated[str, Form()], timezone: Annotated[str, Form()]):

	while True:
		random_characters = "".join(random.choice(string.ascii_lowercase) for _ in range(8))
		url_obj = db["urls"].find_one({
			"hash": random_characters
		})

		if (url_obj == None):
			break

	inserted_doc = {
		"_id": ObjectId(),
		"url": url,
		"hash": random_characters,
		"views": 0,
		"clicks": 0,
		"created_at": datetime.now(timezone_module.utc)
	}

	db["urls"].insert_one(inserted_doc)
	inserted_doc["_id"] = str(inserted_doc["_id"])
	inserted_doc["created_at"] = convert_utc_to_local(timezone, inserted_doc["created_at"])

	return {
		"status": "success",
		"message": "URL has been shortened.",
		"url": inserted_doc
	}

def convert_utc_to_local(timezone, utc):
	# Hardcode zones:
	from_zone = tz.gettz('UTC')
	to_zone = tz.gettz(timezone)

	# Tell the datetime object that it's in UTC time zone since 
	# datetime objects are 'naive' by default
	utc = utc.replace(tzinfo=from_zone)

	# Convert time zone
	utc = utc.astimezone(to_zone).strftime("%b %d, %Y %H:%M:%S")

	return utc

To start the API, run the following command in your “api” folder:

uvicorn api:app --reload

You can access it from here:

http://127.0.0.1:8000

You might see it blank and that’s okay. Because it will be accessed via AJAX calls.

Explanation:

First, we are making connection with Mongo DB. Then we are initializing FastAPI and setting CORS middleware to prevent CORS error.

After that, we are creating a function that will handle the request. It accepts URL and timezone.

It will generate random 8 characters and make sure they are unique in database using do-while loop.

Then it will insert the record in database. And in order to return the inserted document to the client side, we need to convert the ObjectId to string.

Date & time will be stored in UTC in database. But to return to client side, we have to convert UTC to user’s local timezone. So we have created a separate function “convert_utc_to_local” for that.

Show all added URLs – Vue JS

In your HomeComponent, write the following code in [table goes here] section.

<table class="table table-bordered" style="margin-top: 50px;">
	<thead>
		<tr>
			<th>URL</th>
			<th>Views</th>
			<th>Clicks</th>
			<th>Created at</th>
		</tr>
	</thead>

	<tbody>
		<tr v-for="(url, index) in urls">
			<td>
				<router-link v-bind:to="'/url/' + url.hash" v-text="url.hash"></router-link>
			</td>
			<td v-text="url.views"></td>
			<td v-text="url.clicks"></td>
			<td v-text="url.created_at"></td>
		</tr>
	</tbody>
</table>

Then in your <script> tag, initialize an empty array.

data() {
	return {
		urls: []
	}
},

And write the following code at [on url shortened success] section:

this.urls.unshift(response.data.url)

Add a “mounted” event in your Vue JS object.

mounted() {
	this.get_data()
}

After that, create the following method inside your “methods” object.

async get_data() {
	const formData = new FormData()
	formData.append("timezone", Intl.DateTimeFormat().resolvedOptions().timeZone)

	try {
		const response = await axios.post(
			this.$api_url + "/fetch-urls",
			formData
		)

		if (response.data.status == "success") {
			this.urls = response.data.urls
		} else {
			swal.fire("Error", response.data.message, "error")
		}
	} catch (exp) {
		console.log(exp)
	}
}

Fetch all URLs – Python, Mongo DB

Now we need to create an API in Python that will fetch all URLs from Mongo DB. Write the following code in your “api/api.py” file.

@app.post("/fetch-urls")
def fetch_urls(timezone: Annotated[str, Form()]):
	urls = db["urls"].find().sort("created_at", -1)

	url_arr = []
	for url in urls:
		url["_id"] = str(url["_id"])
		url["created_at"] = convert_utc_to_local(timezone, url["created_at"])

		url_arr.append(url)

	return {
		"status": "success",
		"message": "Data has been fetched.",
		"urls": url_arr
	}

Test the app now and you will see all your added URLs. Now whenever a URL is clicked, we will show a new page where user will have to wait for 5 seconds to access the real URL.

Access the URL – Vue JS

In your “web/src/components” folder, create a file named “URLComponent.vue” and write the following code in it:

<template>
	URLComponent
</template>

<script>

	export default {
		name: "URLComponent"
	}
</script>

Register it in your “main.js” file.

import URLComponent from "./components/URLComponent.vue"

And add it in “routes” array too.

{ path: "/url/:url", component: URLComponent }

Back to your URLComponent, write the following code inside <template> tag.

<div class="container">
	<div class="row">
		<div class="col-md-12">
			<div class="float-end" style="padding: 15px;
			    border-radius: 5px;
			    background-color: lightblue;
			    cursor: pointer;">
			    <span v-if="remaining_seconds > 0">
			    	Redirecting in <span v-text="remaining_seconds"></span> seconds
			    </span>
				
			    <span v-else v-on:click="goto_website">Go to website</span>
			</div>
		</div>
	</div>
</div>

This will show a text that will show the number of seconds remaining till user has to wait. When the time is over, it will show a clickable text which when clicked, should move the user to the actual URL.

import axios from "axios"
import swal from "sweetalert2"

export default {
	name: "URLComponent",

	data() {
		return {
			url: this.$route.params.url,
			remaining_seconds: 5,
			url_obj: null,
			interval: null
		}
	},

	methods: {
		async goto_website() {
			if (this.url_obj == null) {
				return
			}

			window.location.href = this.url_obj.url
		},

		async get_data() {
			const formData = new FormData()
			formData.append("url", this.url)

			try {
				const response = await axios.post(
					this.$api_url + "/get-url",
					formData
				)

				if (response.data.status == "success") {
					this.url_obj = response.data.url
				} else {
					swal.fire("Error", response.data.message, "error")
				}
			} catch (exp) {
				console.log(exp)
			}
		}
	},

	mounted() {
		const self = this
		this.get_data()

		setTimeout(function () {
			self.interval = setInterval(function () {
				self.remaining_seconds--

				if (self.remaining_seconds <= 0) {
					clearInterval(self.interval)
				}
			}, 1000)
		}, 500)
	}
}

When this component is mounted, we will call “get_data” function that will fetch the single URL data from Python API.

Also, it will start a countdown timer from 5. Once timer hits 0, you can click the button and go to your actual URL.

Fetch single URL – Python, Mongo DB

Create the following API in your “api/api.py” file.

@app.post("/get-url")
def get_url(url: Annotated[str, Form()]):

	url = db["urls"].find_one({
		"hash": url
	})

	if (url == None):
		return {
			"status": "error",
			"message": "URL not found."
		}

	url["_id"] = str(url["_id"])

	return {
		"status": "success",
		"message": "Data has been fetched.",
		"url": url
	}

This will fetch the single URL from Python API.

Number of clicks on Shorten URL

When you share the shorten URL with your friends, you might want to know how many have clicked the shorten URL and how many clicked the actual URL.

To increment number of clicks on shorten URL, we will simply go to “api.py” function “get_url” and add the following code before converting ObjectId “_id” to string.

db["urls"].find_one_and_update({
	"_id": url["_id"]
}, {
	"$inc": {
		"views": 1
	}
})

Test now and you will see whenever someone access your shorten URL page, your “views” gets incremented in HomeComponent.

Number of clicks on actual URL

In order to see how many has clicked on your actual URL, you need to open your “URLComponent.vue” file. Add the following lines in your “goto_website” function before the “window.location.href” line.

const formData = new FormData()
formData.append("url", this.url)
window.navigator.sendBeacon((this.$api_url + "/url-clicked"), formData)

Then create its API in Python that will handle this request.

@app.post("/url-clicked")
def url_clicked(url: Annotated[str, Form()]):
	db["urls"].find_one_and_update({
		"hash": url
	}, {
		"$inc": {
			"clicks": 1
		}
	})

Now whenever someone visit the actual URL, the “clicks” counter gets incremented.

Header and Footer

Write the following code insde <template> tag of your AppHeader component.

<nav class="navbar navbar-expand-lg navbar-light bg-light">
  <div class="container-fluid">
    <router-link class="navbar-brand" to="/">URL Shortener</router-link>
    <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <span class="navbar-toggler-icon"></span>
    </button>
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav me-auto mb-2 mb-lg-0">
        <li class="nav-item">
          <router-link class="nav-link active" aria-current="page" to="/">Home</router-link>
        </li>
      </ul>
    </div>
  </div>
</nav>

And change your AppFooter component to the following.

<template>
	<footer class="container-fluid" style="margin-top: 50px; background-color: #e9e9e9; padding: 25px;">
		<p class="text-center" style="margin-bottom: 0px;">
			&copy;<span v-text="year"></span>&nbsp;
			adnan-tech.com
		</p>
	</footer>
</template>

<script>
	export default {
		name: "AppFooter",

		data() {
			return {
				year: new Date().getFullYear()
			}
		}
	}
</script>

So this is how you can create a URL shortener app using Vue JS as frontend, Python as backend and Mongo DB as database. If you face any problem in following this, kindly do let me know.

MongoDB and MySQL equivalent queries

Posted on by adnanafzal565

Hello. In this article, we are going to show you some MongoDB and MySQL equivalent queries. This will help you greatly if you want to convert a MySQL project into MongoDB or vice-versa.

Video tutorial:

Introduction

First, let’s give a small introduction to both of these databases.

MongoDB

MongoDB is schema-less database architecture. Means that the schema or structure of the database does not needs to be defined. Schema will automatically gets created as data comes in. It is used where data needs to be loosely-coupled.

MySQL

MySQL is a structured query language. The structure of the database and its tables needs to be defined before creating them. It is used where data needs to be tightly-coupled.

MongoDBMySQL
It is schema-less.Schema needs to be defined.
Non-relational database.Relational database.
It has collections.It has tables.
It has documents.It has rows.
Used for loosely-coupled data.Used for tightly-coupled data.
Horizontal scaling.Vertical scaling.
Each document can have different structure.Each row must have same structure.
Data is not dependent on other collections.Data might be dependent on other tables.
Uses nested objects and arrays.Uses separate tables and joins them using foreign keys.

1. Creating collections/tables

MongoDB

As mentioned above, MongoDB collections does not needs to be created. They will be created automatically once a document is inserted in it.

MySQL

To create a table in MySQL database, you can run the following query:

CREATE TABLE IF NOT EXISTS users(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL,
    age INTEGER DEFAULT 0
);

This will create a table named “users” having 3 columns “id”, “name” and “age”. ID will be a unique key that will automatically be incremented once a new row is inserted. While “name” will be a text string field and “age” will be an integer number.

2. Inserting documents/rows

MongoDB

To insert a document in a MongoDB collection, you can run the following query:

db.users.insertOne({
    name: "Adnan",
    age: 30
});

This will insert a new document in “users” collection. It will automatically assign unique ObjectId to it named “_id”.

{
	"_id": ObjectId("6474680ef3c486d92597e787"),
	"name": "Adnan",
	"age": 30
}

To insert a row in MySQL table, you would do:

INSERT INTO users(name, age) VALUES ("Adnan", 30);

3. Fetching data

MongoDB

To fetch multiple records from MongoDB collection, you can run the following query:

db.users.find({
    name: "Adnan"
}).toArray();

This will return all the documents where “name” is “Adnan”.

MySQL

In MySQL, you can run the following SQL query:

SELECT * FROM users WHERE name = "Adnan";

3.1 AND clause

You can use $and operator in MongoDB to fetch data where all conditions must met.

db.users.find({
    $and: [
        {
            name: "Adnan"
        },
        {
            age: 30
        }
    ]
}).toArray();

This will return all the users whose name is “Adnan” and their age is 30.

Same filter can be applied on MySQL using the following query:

SELECT * FROM users WHERE name = "Adnan" AND age = 30;

3.2 OR clause

You can use $or operator in MongoDB to fetch data where any of the condition met.

db.users.find({
    $or: [
        {
            name: "Adnan"
        },
        {
            age: 30
        }
    ]
}).toArray();

This will return all the users whose name is “Adnan” or if their age is 30.

In MySQL, we would apply the above filter like this:

SELECT * FROM users WHERE name = "Adnan" OR age = 30;

3.3 Limiting, sorting and skipping data

To limit the number of records to fetch, order them by their name in ascending order and skip 1 document, in MongoDB you would do:

db.users.find({
        age: 30
    })
    .sort({
        name: -1
    })
    .skip(1)
    .limit(1)
    .toArray()

This will sort the users documents by name in descending order, skip 1 record and return only 1 record.

Similar query can be run on MySQL in the following way:

SELECT * FROM users WHERE age = 30 ORDER BY id DESC LIMIT 1, 1

LIMIT {skip}, {limit} this is the syntax of LIMIT command in SQL.

3.4 Less or greater than

In MongoDB, you can use $lt (less than) and $gt (greater than) operators like this:

db.users.find({
    age: {
        $lt: 30
    }
}).toArray()

This will return the users whose age is less than 30. Following query will return the users whose age is greater than 30:

db.users.find({
    age: {
        $gt: 30
    }
}).toArray()

You can also use $lte and $gte operators for “less than and equal to” and “greater than and equal to” conditions respectively.

Above are the MongoDB queries, following are their equivalent MySQL queries:

/* less than */
SELECT * FROM users WHERE age < 30;

/* less than and equal to */
SELECT * FROM users WHERE age <= 30;

/* greater than */
SELECT * FROM users WHERE age > 30;

/* greater than and equal to */
SELECT * FROM users WHERE age >= 30;

4. Updating data

To update a document in MongoDB collection, you would do the following:

db.users.updateMany({
    name: "Adnan"
}, {
    $set: {
        age: 31
    }
})

This will set the age to 31 of all users having name “Adnan”. If you want to update only one user then you can use updateOne() function instead.

In MySQL, you can do:

UPDATE users SET age = 31 WHERE name = "Adnan" LIMIT 1

4.1 Incrementing/decrementing values

To increment the value, in MongoDB you can do:

db.users.updateOne({
    name: "Adnan"
}, {
    $inc: {
        age: 3
    }
})

This will increment the value of age by 3 where name is “Adnan”. Same thing can be done for decrementing, you can just set the value in negative.

db.users.updateOne({
    name: "Adnan"
}, {
    $inc: {
        age: -3
    }
})

It’s equivalent MySQL query would be:

UPDATE users SET age = age + 3 WHERE name = "Adnan"

5. Delete data

To delete a document from MongoDB collection, you can run the following query:

db.users.deleteOne({
    name: "Adnan"
})

This will delete one document from users collection whose name is “Adnan”. To delete multiple, you can use deleteMany() function instead.

In MySQL, you can do:

DELETE FROM users WHERE name = "Adnan" LIMIT 1

6. Relationships

MongoDB

MongoDB is not a relational database. Data saved in one collection is not dependent on another collection’s data.

For example, if you want to save job history of each user. You do not have to create a separate collection for that. You can simply push a new job in document’s array.

db.users.findOneAndUpdate({
    name: "Adnan"
}, {
    $push: {
        "jobs": {
            _id: ObjectId(),
            title: "Developer",
            company: "adnan-tech.com",
            period: "3 years"
        }
    }
})

This will create an array “jobs” if not already created and insert a new element in that array.

{
	"_id" : ObjectId("64748227f3c486d92597e78a"),
	"name" : "Adnan",
	"age" : 30,
	"jobs" : [
		{
			"_id" : ObjectId("647490a4f3c486d92597e78e"),
			"title" : "Developer",
			"company" : "adnan-tech.com",
			"period" : "3 years"
		}
	]
}

MySQL

Whereas, MySQL is a relational database. Data saved in one table might be dependent on another table’s data.

If you want to achieve the above in MySQL, you would have to create a separate table for that and create user_id as foreign key and reference it to your “users” table.

CREATE TABLE IF NOT EXISTS jobs(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title TEXT NOT NULL,
    company TEXT NOT NULL,
    period TEXT NOT NULL,
    user_id INTEGER NOT NULL,

    CONSTRAINT fk_user_id_jobs FOREIGN KEY (user_id) REFERENCES users(id)
)

After that, you can insert a new job of that user using the following query:

INSERT INTO jobs (title, company, period, user_id) VALUES ("Developer", "adnan-tech.com", "3 years", 1)

This will insert a new row in “jobs” table and link it with “users” table using its foreign ID “user_id”.

jobs-table - mongodb mysql equivalent queries
jobs-table

To fetch the data both from users and jobs table, you have to perform a join operation.

SELECT * FROM users INNER JOIN jobs ON users.id = jobs.user_id

This will return all the users along with their jobs.

jobs-table-join-users-table
jobs-table-join-users-table

So you have learned to create many MongoDB queries to their equivalent MySQL queries and vice-versa. If you have any question related to this, feel free to comment it in the section below. You can also check our more tutorials on MongoDB to learn more.