Stored procedures are used in relational databases to ensure that complex query operations are executed at the database layer. Nowadays, the need for stored procedures is replaced by other more advanced techniques, such as aggregation pipelines, serverless functions, and database triggers — all available in MongoDB and MongoDB Atlas.
Table of contents
What is a stored procedure?
A stored procedure can be envisioned as a segment of prepared SQL code that is meticulously preserved within a database, poised for recurrent utilization. Analogous to the concept of functions in conventional programming languages, SQL-stored procedures encapsulate a set of operations that can be invoked seamlessly, thereby enhancing the efficiency and maintainability of database interactions. Traditionally, stored procedures found their niche in the realm of RDBMS (relational database management systems), particularly exemplified by platforms like SQL Server.
However, modern databases have redefined the strategies for addressing complex queries and data manipulation tasks. For example, MongoDB has harnessed the power of the document model as well as advanced techniques such as aggregation pipelines, which provide a powerful and flexible mechanism for performing data transformations and analysis, akin to the functionalities traditionally achieved through stored procedures.
Stored procedures: historical perspective
Stored procedures have been essential to relational databases since their inception. They were introduced as a means to store and execute complex database system operations, containing pre-compiled SQL statements that could be invoked on demand. Although they offered some benefits in the past, stored procedures now present several limitations compared to modern, agile development practices.
In this example, stored procedures are misplaced in current architectures.
Historically, stored procedures emerged during the era of early client-server architectures in the 1970s and 1980s. Back then, business logic was an encrypted stored procedure in the database server rather than the client. When three-tier architectures became popular in the 1990s and 2000s, stored procedures continued to be part of the database layer.
Stored procedures were initially perceived as fast, efficient, and secure. However, over time, their drawbacks became increasingly apparent. Today, a stored procedure is considered less flexible and harder to maintain than its modern counterparts. Stored procedures are also less portable and can be challenging to debug, test, and version. Furthermore, they blur the lines between developer and DBA roles regarding access rights and responsibilities.
Are stored procedures required in modern applications?
While stored procedures may have had some advantages when initially proposed, they now simply offer large trade-offs for limited benefits in return.
In this example, we have the expected benefits and real challenges of using stored procedures.
Stored procedures were expected to perform operations fast
Stored procedure calls can be quick due to the fact they are compiled once and stored in executable code form. While they may have been performant at one time, that is never the case when the database itself is under load. Also, it’s hard to generate an optimal execution plan because not all arguments are supplied at compile time. So in terms of performance, results can be very mixed.
Stored procedures were expected to perform operations efficiently
While reusing the same code is a good thing, stored procedures can be hard to maintain, debug, test, and version. Neither are they very portable. Nowadays, they can often be the cause of the slowdown of other in-flight database operations. They can also introduce risk in terms of breaking changes, and developers may shy away from changing them. This can lead to technical debt in many cases, and stifle innovation.
Stored procedures were expected to perform operations securely
Nowadays, they just blur the lines between developer and DBA roles regarding access rights and responsibilities. Business logic in modern implementations needs to reside on the middleware and run as an API. It is by far easier to deploy and run different versions of a web service than two versions of a set of stored procedures.
Stored procedures are a technical hangover from early client-server architectures in the 1970s and 1980s, when business logic was in other stored procedures in the database server rather than the client. Even when three-tier architectures became more popular in the 1990s and 2000s, stored procedures were still part of the database layer.
Current architectural paradigms call for stored procedures, or more accurately, their modern-day equivalents, to be made part of the application source code itself. In doing so, they can then be part of the overall software development life cycle involving code review, collaborative software development, change management, and so on.
Does MongoDB support stored procedures?
In MongoDB, stored procedures are not necessary as in relational databases. While stored procedures may have had some advantages when initially proposed, they now simply offer large trade-offs for limited benefits in return.
Stored functions in MongoDB
In earlier versions of MongoDB, a concept similar to stored procedures, called stored functions, was available. These JavaScript functions could be stored on the server and later reused as part of a Map-Reduce API.
However, both stored functions and map-reduce API have been deprecated. A wide variety of stages and operators within the aggregation pipeline have significantly reduced the need for custom code.
The modern equivalent of stored procedures
MongoDB replaces traditional stored procedures with powerful, flexible, and efficient alternatives tailored to the demands of today's digital landscape.
A host of features available in the database and additional features available on the developer data platform, MongoDB Atlas, simplify building modern applications.
Features in MongoDB, the database
Aggregation pipelines: Process and analyze data records, grouping values from multiple documents and performing operations on grouped data to deliver results in a single set.
Change Streams: Subscribe to real-time data changes for single collections, databases, and entire deployments, with the ability to leverage aggregation pipelines for filtering or transforming notifications.
Additional features in Atlas, the developer data platform
Atlas Functions: Use server-side JavaScript functions for defining app behavior, allowing direct calls from client applications or integration with services like Atlas Triggers and Atlas API services for automatic execution.
Atlas Triggers: Automatically execute serverless functions in response to events or on a schedule. Triggers and functions run on a serverless compute layer that scales independently of the database server, unlike traditional SQL data triggers.
Aggregation pipelines
Stored procedures are often used when complex queries need to be performed on the database server. In MongoDB, this is where aggregation pipelines shine.
An aggregation pipeline consists of one or more stages that process documents:
Each stage performs an operation on the input documents. For example, a stage can filter documents, group documents, and calculate values.
The documents that are output from a stage are passed to the next stage.
An aggregation pipeline can calculate results for groups of documents. For example, compute the total, average, maximum, and minimum values.
In this example, we are breaking down how an aggregation pipeline functions in MongoDB.
Aggregations are best suited to:
- Group values from multiple documents together.
- Perform operations on the grouped data to return a single result.
- Analyze data changes over time.
The aggregation pipelines framework is a fully Turing complete programming language and can be used for many things, ranging from complex data analytics to mining bitcoins.
Aggregation pipelines can be called directly from your application using one of the native drivers to connect to your database.
The MongoDB Atlas web UI (user interface) provides an easy-to-use interface to learn how to build aggregation pipelines. Compass, the GUI for MongoDB, also offers an interface to build pipelines. It even includes previews at each of the stages to better understand how to optimize your queries.
The Practical MongoDB Aggregation book is a valuable resource for learning more about aggregation pipelines and how to use them optimally.
Atlas Functions and Triggers
In MongoDB Atlas, when functions need to be stored on the database server, you can use Atlas Functions. You can then invoke these functions via Atlas Triggers or Atlas API services. These services run on a fully managed, serverless compute layer that scales independently from the database server, allowing you to implement them quickly without the hassle of spinning up or managing an app server.
Atlas functions use ES6+ JavaScript and can import Node.js modules, just like any other Node.js application that you would run on your server.
Migrating stored procedures to MongoDB Atlas
Transitioning stored procedures into MongoDB by employing aggregation pipelines or leveraging Atlas Functions and Triggers enables organizations to maintain agility, enhance performance, and embrace modern development practices.
Discover how MongoDB's innovative solutions provide a compelling alternative, empowering you to transform your data management and foster a future-ready digital landscape in our comprehensive datasheet, or dive into the details and start migrating with the technical guide.
Summary
While MongoDB does not offer stored procedures as traditional relational databases do — such as SQL Server Management Studio, which has stored procedure parameters — it provides multiple tools that you can use to achieve the same result. By using aggregation pipelines with the proper indexes, you can perform resource-intensive queries from the database server. You can also use Atlas Functions to write full JavaScript functions that you can then use from your applications or as serverless endpoints. All of these features are available on the MongoDB Atlas free tier. Why not try it out right now?