Sourabh Bagrecha

Senior Developer Advocate at MongoDB 

If you have ever used a SQL database, you might have heard about stored procedures. Stored procedures represent pre-written SQL code designed for reuse. By storing frequently used SQL queries as procedures, you can execute them repeatedly. Additionally, these procedures can be parameterized, allowing them to operate on specified parameter values. Oftentimes, developers find themselves wondering:

- Does MongoDB support stored procedures? 
- Where do you write the logic for stored procedures in MongoDB? 
- How can I run a query every midnight, like a CRON job?

In today’s article, we are going to answer these questions and demystify stored procedures in MongoDB.

## Does MongoDB support stored procedures?

Essentially, a stored procedure consists of a set of SQL statements capable of accepting parameters, executing tasks, and optionally returning values. In the world of MongoDB, we can achieve this using an [aggregation pipeline](https://mongodb.prakticum-team.ru/docs/manual/core/aggregation-pipeline/). 

An aggregation pipeline, in a nutshell, is basically a series of stages where the output from a particular stage is an input for the next stage, and the last stage’s output is the final result. 

Now, every stage performs some sort of processing to the input provided to it, like filtering, grouping, shaping, calculating, etc. You can even perform [vector search](https://mongodb.prakticum-team.ru/products/platform/atlas-vector-search) and [full-text search](https://mongodb.prakticum-team.ru/cloud/atlas/lp/search) using MongoDB’s unified developer data platform, [Atlas](https://mdb.link/stored-procedures-triggers).

Let's see how MongoDB’s aggregation pipeline, Atlas triggers, and change streams together can act as a super efficient, powerful, and flexible alternative to stored procedures.

## What is MongoDB Atlas?

[MongoDB Atlas](https://mdb.link/stored-procedures-triggers) is a multi-cloud developer data platform focused on making it stunningly easy to work with data. It offers the optimal environment for running MongoDB, the leading non-relational database solution.

MongoDB's document model facilitates rapid innovation by directly aligning with the objects in your code. This seamless integration makes data manipulation more intuitive and efficient. With MongoDB, you have the flexibility to store data of diverse structures and adapt your schema effortlessly as your application evolves with new functionalities.

The Atlas database is available in 100+ regions across AWS, Google Cloud, and Azure. You can even take advantage of multi-cloud and multi-region deployments, allowing you to target the providers and regions that best serve your users. It has best-in-class automation and proven practices that guarantee availability, scalability, and compliance with the most demanding data security and privacy standards.

## What is an Atlas Trigger?

Database triggers enable the execution of server-side logic whenever a document undergoes addition, modification, or deletion within a connected Atlas cluster. 

Unlike conventional SQL data triggers confined to the database server, [Atlas Triggers](https://mongodb.prakticum-team.ru/docs/atlas/app-services/triggers/) operate on a serverless compute layer capable of scaling autonomously from the database server. 

It seamlessly invokes Atlas Functions and can also facilitate event forwarding to external handlers via Amazon EventBridge.

## How can Atlas Triggers be invoked?

An Atlas Trigger might fire on:

- A specific operation type in a given collection, like insert, update, and delete. 
- An authentication event, such as User Creation or Deletion.
- A scheduled time, like a CRON job.

## Types of Atlas Triggers

There are three types of triggers in Atlas:

- [Database triggers](https://mongodb.prakticum-team.ru/docs/atlas/app-services/triggers/database-triggers/) are used in scenarios where you want to respond when a document is inserted, changed, or deleted. 
- [Authentication triggers](https://mongodb.prakticum-team.ru/docs/atlas/app-services/triggers/authentication-triggers/) can be used where you want to respond when a database user is created, logged in, or deleted.
- [Scheduled triggers](https://mongodb.prakticum-team.ru/docs/atlas/app-services/triggers/scheduled-triggers/) acts like a CRON job and run on a predefined schedule.

Refer to [Configure Atlas Triggers](https://mongodb.prakticum-team.ru/docs/atlas/triggers/) for advanced options.

## Atlas Triggers in action

Let's compare how stored procedures can be implemented in SQL and MongoDB using triggers, functions, and aggregation pipelines.

### The SQL way

Here's an example of a stored procedure in MySQL that calculates the total revenue for the day every time a new order is inserted into an orders table:

```
DELIMITER $$

CREATE PROCEDURE UpdateTotalRevenueForToday()
BEGIN
    DECLARE today DATE;
    DECLARE total_revenue DECIMAL(10, 2);

    -- Get today's date
    SET today = CURDATE();

    -- Calculate total revenue for today
    SELECT SUM(total_price) INTO total_revenue
    FROM orders
    WHERE DATE(order_date) = today;

    -- Update total revenue for today in a separate table or perform any other necessary action
    -- Here, I'm assuming you have a separate table named 'daily_revenue' to store daily revenue
    -- If not, you can perform any other desired action with the calculated total revenue

    -- Update or insert the total revenue for today into the 'daily_revenue' table
    INSERT INTO daily_revenue (date, revenue)
    VALUES (today, total_revenue)
    ON DUPLICATE KEY UPDATE revenue = total_revenue;
END$$

DELIMITER ;
```

In this stored procedure:

- We declare two variables: today to store today's date and total_revenue to store the calculated total revenue for today.
- We use a SELECT statement to calculate the total revenue for today from the orders table where the order_date matches today's date.
- We then update the daily_revenue table with today's date and the calculated total revenue. If there's already an entry for today's date, it updates the revenue. Otherwise, it inserts a new row for today's date.

Now, we have to create a trigger to call this stored procedure every time a new order is inserted into the orders table. Here's an example of how to create such a trigger:

```
CREATE TRIGGER AfterInsertOrder
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    CALL UpdateTotalRevenueForToday();
END;
```

This trigger will call the UpdateTotalRevenueForToday() stored procedure every time a new row is inserted into the orders table.

### The MongoDB way

If you don’t have an existing MongoDB Database deployed on Atlas, [start for free](https://mongodb.prakticum-team.ru/docs/guides/atlas/cluster/) and get 500MBs of storage free forever.

Now, all we have to do is create an Atlas Trigger and implement an Atlas Function in it.

Let’s start by [creating an Atlas database trigger](https://mongodb.prakticum-team.ru/docs/atlas/app-services/triggers/database-triggers/#create-a-database-trigger). 

![enter image description here][7]

![enter image description here][2]

![enter image description here][1]

![enter image description here][3]

1. Select the **Trigger Type** as “Database” since we want our trigger to respond to database events rather than on a defined schedule.
2. Give this trigger a **Name** of your choice.
3. Select a **Data Source** that you want this trigger to be linked to.
4. We are **Watching Against** a “Collection” since we want this trigger to execute every time a new document is inserted into the orders collection.
5. Select the appropriate **Cluster**, **Database**, and **Collection name** where you want this trigger to observe any changes.
6. We are only interested in new orders being created in our application, hence we will only choose “Insert Document” in the **Operation Type**.
7. Enable the **Full Document** option for this trigger.
8. You can keep the other options in their default state. Learn more about [configuring Atlas database triggers](https://mongodb.prakticum-team.ru/docs/atlas/app-services/triggers/database-triggers/#configuration).

![enter image description here][6]

Finally, we will implement the following Atlas Function in the function editor:

```javascript
exports = async function (changeEvent) {
    //Get the date from the newly inserted document
    const date = changeEvent.fullDocument.order_date;
    const ordersCollection = context.services.get("triggers-demo").db("business").collection("orders");
    const dailyRevenueCollection = context.services.get("triggers-demo").db("business").collection("daily_revenue");

    const pipeline = [
        {
            $group: {
                _id: date,
                total_revenue: { $sum: "$total_amount" }
            }
        }
    ];

    const result = await ordersCollection.aggregate(pipeline).toArray();
    const totalRevenue = result.length > 0 ? result[0].total_revenue : 0;

    await dailyRevenueCollection.updateOne(
        { date: date },
        { $set: { revenue: totalRevenue } },
        { upsert: true }
    );
};
```

Hit “Save.”

![enter image description here][4]

### Testing

Now, to test if our trigger is working correctly, let’s insert a document into our orders collection:

```json
{
    "_id": { "$oid": "65c357c89b2a791ae372bce3" },
    "order_date": "07-02-2024",
    "total_amount": 1499
}
```

![enter image description here][5]

To verify whether our trigger executed successfully, we can check the **dailyRevenue** collection, and if it contains a new document with the same date as we used in the newly inserted order, congratulations — you have successfully implemented a fully functional Atlas Trigger.

![enter image description here][8]

## Conclusion

Typically, you would use a stored procedure in SQL when you are frequently trying to execute complex queries on the database.

Stored procedures were initially lauded for their perceived advantages in terms of performance, efficiency, and security. However, their drawbacks — including maintenance challenges, limited portability, and blurred roles between developers and database administrators — have become increasingly apparent in modern architectural paradigms.

On the other hand, MongoDB makes it stunningly easy to work with data. It provides modern tools and techniques that help us build event-driven applications blazingly fast.

MongoDB features, such as aggregation pipelines and [change streams](https://mongodb.prakticum-team.ru/docs/manual/changeStreams/), are powerful alternatives to traditional stored procedures. MongoDB Atlas, the developer data platform, further enhances development flexibility with features like Atlas Functions and Triggers, enabling seamless integration of server-side logic within the database environment.

The migration from stored procedures to MongoDB is not just a technological shift; it represents a paradigm shift towards embracing a future-ready digital landscape. As organizations transition, they gain the ability to leverage MongoDB's innovative solutions, maintaining agility, enhancing performance, and adhering to contemporary development practices.

So, what are you waiting for? [Sign up for Atlas](https://mdblink.com/stored-procedures-triggers) today and experience the modern alternative to stored procedures in MongoDB.


  [1]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltcb5b2b2db6b3a2b6/65dce8447394e52da349971b/image1.png
  [2]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt5cbb9842024e79f1/65dce844ae62f722b74bdfe0/image2.png
  [3]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt529e154503e12f56/65dce844aaeb364e19a817e3/image3.png
  [4]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blta2a7b461deb6879d/65dce844aaeb36b5d5a817df/image4.png
  [5]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt5801d9543ac94f25/65dce8446c65d723e087ae99/image5.png
  [6]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt5ceb23e853d05d09/65dce845330e0069f27f5980/image6.png
  [7]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt626536829480d1be/65dce845375999f7bc70a71b/image7.png
  [8]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt1b1366584ee0766a/65dce8463b4c4f91f07ace17/image8.png

Learn how MongoDB’s aggregation pipeline, Atlas triggers, and change streams are a super efficient, powerful, and flexible alternative to stored procedures.

Demystifying Stored Procedures in MongoDB

> **Note:** GraphQL is deprecated. [Learn more.](https://mongodb.prakticum-team.ru/developer/products/atlas/deprecating-mongodb-atlas-graphql-hosting-services/)

One of the things I like the most is building full-stack apps using Node.js, React, and MongoDB. Every time I get a billion-dollar idea, I immediately start building it using this tech stack. No matter what app I’m working on, there are a few features that are common:

- Authentication and authorization: login, signup, and access controls.
- Basic CRUD (Create, Read, Update, and Delete) operations.
- Data analytics.
- Web application deployment.

And without a doubt, all of them play an essential role in any full-stack application. But still, they take a lot of time and energy to build and are mostly repetitive in nature. Therefore, we are left with significantly less time to build the features that our customers are waiting for.
In an ideal scenario, your time as a developer should be spent on implementing features and not reinventing the wheel. With [MongoDB Atlas App Services](https://mongodb.prakticum-team.ru/atlas/app-services), you don’t have to worry about that. All you have to do is connect your client app to the service you need and you’re ready to rock!
Throughout this series, you will learn how to build a full stack web application with MongoDB Atlas App Services, GraphQL, and React. We will be building an expense manager application called Expengo.

## Authentication

Implementing authentication in your app usually requires you to create and deploy a server while making sure that emails are unique, passwords are encrypted, and sessions/tokens are managed securely.
In this blog, we’ll configure email/password authentication on Atlas App Services. In the subsequent part of this series, we’ll integrate this with our React app.

![Expengo app walkthrough](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/walkthrough_a085dddc6a.gif)

## MongoDB Atlas App Services authentication providers
MongoDB Atlas is a developer data platform integrating a multi-cloud database service with a set of data services. Atlas App Services provide secure serverless backend services and APIs to save you hours of coding.
For authentication, you can choose from many different providers such as email/password, API key, Google, Apple, and Facebook. For this tutorial, we’ll use the email/password authentication provider.

## Deploy your free tier Atlas cluster
If you haven’t already, [deploy a free tier](https://mongodb.prakticum-team.ru/docs/atlas/tutorial/deploy-free-tier-cluster/) MongoDB Atlas cluster. This will allow us to store and retrieve data from our database deployment. You will be asked to add your IP to the IP access list and create a username/password to access your database. Once a cluster is created, you can create an App Service and link to it.

## Set up your App Service
Now, click on the “App Services” tab as highlighted in the image below:

![Database Deployments screen highlighting App Services tab](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/App_Services_tab_5d34a6032b.png)

There are a variety of templates one can choose from. For this tutorial, we will continue with the “Build your own App” template and click “Next.”

![Atlas App Services welcome page highlighting the Build Your Own App feature](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Select_a_template_06356e9eb4.png)

Add application information in the next pop-up and click on “Create App Service.”

![Connecting data to Atlas App Services](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Create_App_Services_Dialog_4c6ddcdc76.png)

Click on “Close Guides” in the next pop-up screen.

![Application guides for Atlas App Services](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Close_guides_54b8ce39f3.png)

Now click on “Authentication” in the side-bar. Then, click on the “Edit” button on the right side of Email/Password in the list of Authentication Providers.

![Authentication providers for Atlas App Services](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Turn_on_authentication_d4528fa3b6.png)

Make sure the Provider Enabled toggle is set to On.

On this page, we may also configure the user confirmation settings and the password reset settings for our application.  For the sake of simplicity of this tutorial, we will choose:

1. User confirmation method: “Automatically confirm users.”
2. Password reset method: “Send a password reset email.”
3. Placeholder password reset URL: http://localhost:3000/resetPassword.
    > We're not going to implement a password reset functionality in our client application. With that said, the URL you enter here doesn't really matter. If you want to learn how to reset passwords with App Services, check out the [dedicated documentation](https://mongodb.prakticum-team.ru/docs/atlas/app-services/authentication/email-password/#password-resets).
4. Click “Save Draft.”

![Defining authentication settings within Atlas App Services](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Authentication_provider_06d7d94eee.png)

Once your Draft has been saved, you will see a blue pop-up at the top, with a “Review Draft & Deploy” button. Click on it and wait for a few moments.

![Review Draft & Deploy button in App Services](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Review_Draft_and_Deploy_115d03c3ef.png)

You will see a pop-up displaying all the changes you made in this draft. Click on “Deploy” to deploy these changes:

![Deployment draft in Atlas App Services](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Deploy_Draft_9cafaa3f80.png)

You will see a “Deployment was successful” message in green at the top if everything goes fine. Yay!

![Successful deployment notification](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Deployment_was_successful_ffc92d6da9.png)

## Conclusion

Please note that all the screenshots were last updated in August 2022. Some UX details may have changed in more recent releases.
In the next article of the series, we will learn how we can utilize this email/password authentication provider in our React app.


In less than 6 steps, learn how to set up authentication and allow your users to log in and sign up to your app without writing a single line of server code.

Sourabh Bagrecha

Content by Sourabh Bagrecha

Demystifying Stored Procedures in MongoDB

Configure Email/Password Authentication in MongoDB Atlas App Services