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 an open-source and the leading NoSQL database. It is a document-oriented database that offers high performance, easy scalability, and high availability. It uses documents and collections to organize data rather than relations. This makes it an ideal database management system for the storage of unstructured data.

MongoDB uses replica sets to ensure data availability. Each replica set is made up of two or more replicas of data. This gives its users the ability to access their data at any time. The replica sets also create fault tolerance. MongoDB scales well to accommodate more data. It uses the sharding technique to scale horizontally and meet the changing storage needs of its users. MongoDB was developed to help developers unleash the power of data and software.

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.

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.

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).

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.  

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

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

FAQs