Demystifying Stored Procedures in MongoDB
Rate this tutorial
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.
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.
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 and full-text search using MongoDB’s unified developer data platform, Atlas.
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.
MongoDB Atlas 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.
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 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.
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.
There are three types of triggers in Atlas:
- Database triggers are used in scenarios where you want to respond when a document is inserted, changed, or deleted.
- Authentication triggers can be used where you want to respond when a database user is created, logged in, or deleted.
- Scheduled triggers acts like a CRON job and run on a predefined schedule.
Let's compare how stored procedures can be implemented in SQL and MongoDB using triggers, functions, and aggregation pipelines.
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:
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:
This trigger will call the UpdateTotalRevenueForToday() stored procedure every time a new row is inserted into the orders table.
If you don’t have an existing MongoDB Database deployed on Atlas, start for free 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.
- Select the Trigger Type as “Database” since we want our trigger to respond to database events rather than on a defined schedule.
- Give this trigger a Name of your choice.
- Select a Data Source that you want this trigger to be linked to.
- We are Watching Against a “Collection” since we want this trigger to execute every time a new document is inserted into the orders collection.
- Select the appropriate Cluster, Database, and Collection name where you want this trigger to observe any changes.
- We are only interested in new orders being created in our application, hence we will only choose “Insert Document” in the Operation Type.
- Enable the Full Document option for this trigger.
- You can keep the other options in their default state. Learn more about configuring Atlas database triggers.
Finally, we will implement the following Atlas Function in the function editor:
Hit “Save.”
Now, to test if our trigger is working correctly, let’s insert a document into our orders collection:
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.
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, 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 today and experience the modern alternative to stored procedures in MongoDB.
Rate this tutorial