Creating a MongoDB Stored Procedure 101: Syntax & Examples Simplified

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.

What is MongoDB?

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.

What are Stored Procedures?

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. 

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:

• MongoDB Stored Procedures Syntax
• MongoDB Stored Procedures Example
• MongoDB Stored Procedure in PHP

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.