Creating a MongoDB Stored Procedure 101: Syntax & Examples Simplified

on Database Management Systems, MongoDB, Stored Procedures • February 7th, 2022 • Write for Hevo

mongodb stored procedure - Featured Image

MongoDB is an Open-Source Document-Oriented Database that helps build applications and sites without database configuration. Companies recommend using Relational Database Management Systems in most cases to store and retrieve data. Also, help provide high-speed performance. If you are using MongoDB for storing large data sets, it is suggested to convert the simple functions into MongoDB Stored Procedures for quick productivity. It supports multiple languages and has an active community for user queries.

The main purpose of Stored Procedures is to conceal the SQL statements and enhance database operational performance. These Stored Procedures further help in quick selection, alteration, updating, and deleting data to improve performance. They also help save time as they can be reused. Instead of calling the code again and again to execute a query, it is best to convert functions into MongoDB Stored Procedures and write the code once only.

In this article, you will learn how to easily create a MongoDB Stored Procedure.

Table of Contents

What is MongoDB?

MongoDB Stored Procedure - MongoDB Logo
Image Source

MongoDB is a popular Free and Open-Source Cross-Platform Document Oriented Database built for efficiently storing and processing massive volumes of data. Unlike traditional Relational Databases, MongoDB is classified as a NoSQL Database Management System that uses Collections and JSON-like Documents instead of tables consisting of rows and columns. Each collection consists of multiple documents that contain the basic units of data in terms of key and value pairs. 

Officially introduced as an Open-source development model in 2009, the MongoDB database is designed, maintained, and managed by MongoDB.Inc under a combination of the Server Side Public License and the Apache License. MongoDB is widely used by organizations such as MetLife, Barclays, Viacom,  New York Times, Facebook, Nokia, eBay, Adobe, Google, etc to efficiently meet their exponentially growing data processing and storage requirements. MongoDB is highly flexible as it supports several programming languages such as C, C++, C#, Go, Java, Node.js, Perl, PHP, Python, Motor, Ruby, Scala, Swift, and Mongoid.

Key Features of MongoDB

MongoDB offers a wide range of eye-catching features:

  • High Data Availability: MongoDB’s Replication feature provides multiple servers for disaster recovery and backup. Since several servers store the same data or shards of data, MongoDB provides greater Data Availability & Stability. You don’t need to worry about cases of server crashes, service interruptions, or even good old hardware failure as Real-time Replication of data ensures uninterrupted data access and security. 
  • Supercharged Analytics: You may need to consider thousands to millions of variables while running Ad-hoc queries. MongoDB indexes BSON documents and utilizes the MongoDB Query Language (MQL) that allows you to update Ad-hoc queries in real-time. MongoDB offers full support for field queries, range queries, and regular expression searches along with user-defined functions.
  • Indexing: With Multiple Indices and Language-specific Sort Order Features to support complex data set access patterns, MongoDB provides optimal query performance. For the real-time ever-evolving query patterns and application requirements, MongoDB also provides On-Demand Indices Creation.
  • Horizontal Scalability: Using Sharding, MongoDB provides horizontal scalability by using shard keys to distribute data across multiple server combinations. Each shard in every MongoDB Cluster stores portions of the data, thereby acting as a separate database. This collection of comprehensive databases allows efficient handling of growing volumes of data with zero downtime. The complete Sharding Ecosystem is monitored and managed by Mongos that directs queries to the correct shard based on the Shard Key.
  • Load Balancing: Real-time Replication and Sharding greatly influences large-scale Load Balancing. Ensuring top-notch Concurrency Controls and Locking Protocols, MongoDB can effectively handle several concurrent read and write requests for the same data.  
  • Aggregation: Similar to the SQL Group By clause, MongoDB can easily batch process data and present a single result even after executing several other operations on the group data. MongoDB’s Aggregation framework consists of 3 types of aggregations i.e. Aggregation Pipeline, Map-Reduce Function, and Single-Purpose Aggregation methods.

What are Stored Procedures?

MongoDB Stored Procedure - Stored Procedure
Image Source

Stored Procedures are a set of Structured Query Language (SQL) statements available in applications with assigned names. These statements are stored in a Relational Database Management System (RDBMS) to perform a few tasks and share with multiple programs. It is a group of precompiled SQL statements not restricted to a specific database or object inside the database server. Stored Procedures are compatible with all relational database systems and comprise an assigned name, parameter list, and Transact-SQL statements.

When a developer invokes a Stored Procedure for the first time, the SQL server creates an execution plan and stores it in the cache memory for reuse. These procedures can be called using triggers and applications (Python, PHP, Java, etc.). Stored Procedures in SQL servers can return multiple values for tasks, preserve data integrity, and more. These offer great advantages over queries and improve productivity. 

System and User-defined are the two types of Stored Procedures in a SQL Server. The system Stored Procedures help in the management of the server’s administrative tasks and prevent modifying the system. However, the User-defined Stored Procedures are built for selecting, updating, and deleting information from database tables. It supports DDL and DML Commands to accept input parameters and return values.

Key Features of a Stored Procedure

  • Less Network Traffic: The applications need to share only the Stored Procedure and its parameters rather than sharing several SQL statements. As a result, it helps in reducing the network traffic generated between the database server and the application. This reduced traffic by the Stored Procedures results in enhanced performance. 
  • Stronger Security Features: A Stored Procedure uses data access controls to provide a layer of security. They allow entering data consistently. Thus, it helps preserve data integrity. Furthermore, to simplify the security layer, the Stored Procedures eliminate the feature to grant permissions at the database object level.
  • Reusable: In a Stored Procedure, statements are written only once. Thus, it improves productivity, prevents unnecessary rewrites, lessens code inconsistency, and makes it transparent for all applications. These are stored in RDBMS and can be reused in subsequent executions.
  • Easy to Maintain: You do not require to restart or deploy the application each time to perform tasks and queries. The Stored Procedures are easy to maintain and troubleshoot if a problem arises.
  • Improved Productivity and Performance: Using Stored Procedures helps enhance the performance of the applications. Developers can reuse the procedure for subsequent executions once created and compiled for the first time. The reused procedures are processed faster as the query processor does not require creating new plans each time.

Advantages of a Stored Procedure

  • Easy Deployment: You can use Java Integrated Development Environment (IDE) to create a MongoDB Stored Procedure. These are easy to deploy on any tier of network architecture and require less maintenance.
  • Better Security: Using Stored Procedures will help preserve data integrity. Also, it will help restrict users from manipulating data. Furthermore, eliminating the grant permission feature from the database object level will add an important layer to the security.
  • Enhanced Performance: As the developers need to create and compile Stored Procedures only once, they are quick to call and can be managed efficiently. These are saved in executable form for reuse when needed. Hence, the response time to execute the Stored Procedure is fast. Also, these executable codes support automatic caching, which helps reduce the memory requirement.
  • Scalability: Developers recommend using Stored Procedures as they help isolate application processing on the server. This leads to an increase in scalability.
  • Improved Productivity: As the same code is used over and over again, the network traffic will reduce and queries will be performed faster. As a result, you can avail higher productivity.
  • Maintainability: All the Stored Procedure scripts are available at a central location. As a result, Stored Procedures are much easier to share and maintain. Developers can maintain copies on different client machines and share them with multiple programs. 

Simplify MongoDB ETL with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports MongoDB & MongoDB Atlas, along with 100+ data sources (Including 40+ Free Data Sources), and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

Get Started with Hevo for Free

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Connectors: Hevo supports 100+ Integrations to SaaS platforms such as WordPress, FTP/SFTP, Files, Databases, BI tools, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake, Firebolt, Data Warehouses; Amazon S3 Data Lakes; Databricks, MySQL, SQL Server, TokuDB, MongoDB, DynamoDB, PostgreSQL Databases to name a few.  
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.   
Sign up here for a 14-Day Free Trial!

How to create MongoDB Stored Procedures?

First and foremost, MongoDB does not support Stored Procedures, but it does provide a stored javascript feature. This feature offers similar functions and allows writing code in Javascript. It might seem strange but in a real language, it is better to program. Developers can store the Javascript logic inside a specific collection db.system.js and reuse it when needed. They are powerful and use a similar JavaScript runtime to Firefox. It also supports Iterators, closures, and XML via E4X. 

A MongoDB Stored Procedure is a simple Javascript function that is saved in a special collection labeled as db.system.js. Remember, if the mongod command is not running in the terminal, install it now. Also, it is not mandatory but recommended to have some command-line experience to implement MongoDB Stored Procedures with Javascript functions. You can go through the following aspects to undertand how to easily create a MongoDB Stored Procedure:

A) MongoDB Stored Procedures Syntax

The basic syntax to create a MongoDB Stored Procedure is shown below:

db.system.js.save
(
   {
      _id:"MongoProcedureName",
      value:function(argument1,....N)
      {
         statement1,
         .
         .
         N
      }
   }
);

B) MongoDB Stored Procedures Example

Consider the following simple function to add 2 numbers and follow the steps given below to create a MongoDB Stored Procedure:

function addNumbers( x , y ) {
    return x + y;
}
  • Step 1: To convert this simple function into a Stored Procedure, insert it into the special collection labeled db.system.js:
> db.system.js.save({_id:"addNumbers", value:function(x, y){ return x + y; }});
  • Step 2: There is not much difference in the functionality. The MongoDB Stored Procedures can also be selected, viewed, updated, and removed in the same manner as any other document in the stored collection. This helps in easy monitoring and tracking of the code successfully stored with the help of find ():
> db.system.js.find()
{ "_id" : "addNumbers", "value" : function cf__3__f_(x, y) {
    return x + y;
} }
  • Step 3: It is recommended to ignore the function name generated from the machine. Now start with the use of MongoDB Stored Procedure using db.eval():
> db.eval('addNumbers(67, 10)');
77

C) MongoDB Stored Procedure in PHP

You can also create a MongoDB Stored Procedure in PHP. You will need to install the PHP MongoDB Driver via PECL to make it simple. Here is the simple PHP code to create a MongoDB Stored Procedure.

<?php
$mongo      = new Mongo('mongodb://mongo-server-ip');
$database   = $mongo->selectDB('dbname');
$collection = $database->selectCollection('system.js');
$proccode = 'function addNumbers(x, y) { return x + y; }';
$collection->save(
                array(
                    '_id'   => 'addNumbers',
                    'value' => new MongoCode($proccode),
));

In the above code, you can set _id to whatever you want the function to be called. Now, use the following code to call the MongoDB stored procedure from PHP.

<?php
$toexec = 'function(x, y) { return addNumbers(x, y) }';
$args   = array(34, 23);
$response = $database->execute($toexec, $args);

Conclusion

In this article, you have learned how to effectively create a MongoDB Stored Procedure. Stored Procedures are prepared SQL code that can be reused. Rather than create SQL statements each time, it is best to save them as a MongoDB Stored Procedure. These Stored Procedures include an assigned name, parameter list, and Transact-SQL statements. Mostly Stored Procedures in SQL servers are maintained in the relational database management system (RDBMS). However, MongoDB does not support Stored Procedures but carries javascript features. In an SQL Server, most developers use the EXEC command to call Stored Procedures. Whereas, to create MongoDB Stored Procedures, it is suggested to run the mongod command in the terminal. It is best to get some command-line experience for better implementation of MongoDB Stored Procedures with Javascript functions. 

Apart from MongoDB, you would be using several applications and databases across your business for Marketing, Accounting, Sales, Customer Relationship Management, etc. To get a complete overview of your business performance, it is important to consolidate data from all these sources. To achieve this you need to assign a portion of your Engineering Bandwidth to Integrate Data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-Based ETL tool such as Hevo Data.  

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 100+ sources such as MongoDB & MongoDB Atlas to a Data Warehouse or a Destination of your choice to be visualized in a BI Tool. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

If you are using MongoDB as your NoSQL Database Management System and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 100+ sources & BI tools(Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of creating a MongoDB Stored Procedure! Share your thoughts with us in the comments section below.

No-code Data Pipeline for MongoDB