Stored procedures are used in relational databases to ensure that complex query operations are executed at the database layer as a single unit. Most stored procedures are user-defined procedures. However, there are system-stored procedures for administrative tasks.
Nowadays, the need for stored procedures is replaced by other more advanced techniques, such as aggregation pipelines, serverless functions, and database triggers available in modern data platforms like MongoDB and MongoDB Atlas.
A stored procedure is a segment of prepared SQL code that is 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 relational database management systems (RDBMS), particularly exemplified by platforms like SQL Server.
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, a stored procedure now presents 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.
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 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.
While reusing the same code is a good thing, a stored procedure can be hard to maintain, debug, test, and version. Also, they aren’t very portable.
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.
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 a stored procedure.
Thus, In MongoDB, a stored procedure is not necessary as in relational databases.
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.
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.
Some of the features in MongoDB are:
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.
Atlas, the developer data platform, provides additional features like:
Atlas functions: Server-side JavaScript functions define 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.
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:
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.
A change stream is a MongoDB feature that allows an application to sync and access real-time data, and react to the real-time stream of data from the database to your application. Change streams are well-suited for event-driven architecture and provide a more dynamic and flexible approach to database events without the need to run scheduled jobs or stored procedures.
Consider an example of a banking platform that needs to update database changes as fast as possible. Let’s say you want to pay $200 for a purchase but are running short. You ask a friend to transfer the amount to your account, so you in turn can pay the merchant. All this needs to happen in real time for the transactions to be successful. Change streams update database changes in real time, enabling you to get the latest amount as soon as it is put in the account.
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.
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.
While MongoDB does not offer stored procedures as traditional relational databases—such as SQL Server Management Studio—that have stored procedure parameters do, 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?
MongoDB Atlas offers functions directly within the platform. Atlas functions are serverless JavaScript functions that contain server-side JavaScript code to define your app's behavior. These user-defined functions can call your app's functions directly from a client app or define services that integrate and call functions automatically, like Atlas triggers and Atlas API services.
A comprehensive technical guide that details the technical aspects of building aggregation pipelines and simplifies converting existing stored procedures into aggregation pipelines is available.