Of all the technologies out there today, databases are among the most important. Boring as the idea may be, the storage of data drives the information age. Indeed, databases are as ubiquitious as the webpage itself. Order information from Amazon, posts on Facebook, content from WordPress sites – all stored in databases. Because of this, database management is hugely important – so much so that people work full time jobs in database management.
One of the technologies that databases provide is what’s called a stored procedure. Stored procedures are code that runs on the database to aggregate data, calculate information, perform business logic, etc. Many people love to put all kinds of logic in stored procedures so that they don’t have to rewrite the business login on multiple platforms. And, indeed, they can be useful at times. However, during my career I have grown to despise stored procedures. Why? While stored procedures give the promise of simplifying logic on your application code, what they also do is increase the complexity of deployment and circumvent source control systems.
Since stored procedures are external to the application code, deploying an app is more complicated than simply placing an application on a user’s computer. Now, you have to first upgrade the database functions and then upgrade the application. No big deal, of course, since applications often require database updates. However, unlike table structure, stored procedures are more likely to change frequently in order to fix or tweak the logic. Thus, you end up with different versions of the stored procedure on different databases. Now, the same application will behave differently when running against a different database. This becomes a support nightmare. Is the issue a software bug, or is it a problem with the stored procedure? This leads directly to the second problem – no source control. Traditional application code is managed by source control systems. Every change is logged, and the history of the project can be observed for the entire lifetime of the project. Stored procedures are code applied directly to the database, and tweaks are often not entered in source control. Thus, you can’t easily revert back to a previous version since you have no record of it. This lack of history means we can’t see what other developers did and you have no idea why something was changed.
In my opinion, stored procedures often become the wild west of software development. For all the value they can bring, what I’ve seen them bring is additional bugs and confusion to the software process. When I work on projects, I will fight against the use of stored procedures unless an incredibly strong argument can be provided for their inclusion.