What have the STORED PROCEDURES ever done for us?
I'll use terms stored procedures and database functions in text interchangeably.
So, in all seriousness - what have the STORED PROCEDURES ever done for us?
Security?
You can very easily set up any database that has support for SP in a way that:
Your application is only aware of that one database account that can only execute SP required by your application and nothing more.
That application account can't access data at all directly.
That application account is created and managed by another database account with a higher level of privileges and that is stored and protected much more securely.
That system is usually called the least-privilege principle and it can help you to:
Protect your data from theft way more securely because the account exposed to the application doesn't have direct access to data.
Guard your system against SQL injection attacks. The SQL injection is an impossibility (unless you decide to use dynamic SQL).
Prevents well-meaning junior developers to write horribly inefficient queries. Well, that's not security per se, but it is a very useful side-effect.
Militaries around the world use this principle also called - "need-to-know-basis".
And the military takes security very, very seriously.
Do you?
Performances?
Well, this one is easy, everyone knows that at least - STORED PROCEDURES will give you performance gain.
Database code needs to be closer to the actual data and usually, there is much less network utilization between database and application.
Besides that, STORED PROCEDURE can be heavily optimized by the database engine and execution plans cached and ready.
Maintainability?
Imagine this scenario:
Users are seeing data on their screens that shouldn't be there. So, it's a bug, it needs a bit of maintenance obviously.
What do you do?
You can, of course, just create a Jira ticket ... or, or you can do something like this:
Connect to the database server with the query tool Execute the STORED PROCEDURE that serves that screen and examine the data. If data contains the bug, then you know that the problem is within the STORED PROCEDURE, if it doesn't then the bug is somewhere within the application layer. Now, within seconds you narrowed a search for a bug to at least half of the system.
If it is in your database layer, you can fix it immediately with one ALTER PROCEDURE (or REPLACE FUNCTION) call ... and it's fixed within minutes or even less - with zero downtime.
If it is not, then at least you know that it doesn't have anything to do with the database layer, and you can focus your investigation on your classes, designed domain models with patterns, abstract factories, reducers API controllers, injected services and whatnot you name it. Good luck with that, it will take some time for sure, and the application needs to be patched, redeployed, and all that jazz.
Does your user care about that?
Or they usually want to see bugs fixed right here and right now?
But that is not the only factor in this story that affects maintainability.
Your database is a system of course, and if you surround your database with STORED PROCEDURES, which is essentially an API layer - you got yourself a black box.
A black box is a system with known inputs and known outputs - without any knowledge of inner workings.
Your database may serve dozens and dozens of applications, services, micro-services, reporting systems, integrations, etc, etc, and, by having a black box concept you can change the schema and inner workings while respecting known outputs for know inputs with supreme confidence and efficiency.
How's that for maintainability?
Availability?
You want to have a system with maximum availability, don't you? I mean, downtimes are bad for business. Very bad.
I may have already mentioned above that you can patch a bug in STORED PROCEDURE without having to stop anything. Hence, availability.
No downtime.
Simply run "ALTER PROCEDURE" or "REPLACE FUNCTION" and there you go.
But it gets better. Imagine this. You have a huge table with a gazillion record. And for some reason, you have to change the data type of one field in that table. What do you do? Well, of course, you rush to create a migration that alters the table and alters the field type and then you deploy your update and ... nothing happens. For hours. The table is locked, the entire system is unresponsive because everybody has to wait for your alter to finish and that may take hours. Many, many expensive hours of downtime.
But not if you use STORED PROCEDURES!
You could also write a script that will do this.
Create an auxiliary table from your main table without relations Alter procedures to insert data in that auxiliary table Alter the main table to change the data type in your field, wait to finish After it finishes, insert data from the auxiliary table and revert procedure to original version No downtime.
Availability.
Apart from security, performances, maintainability, and availability, what STORED PROCEDURES have ever really done for us?
I really don't know.
About me
I'm an independent software developer and consultant - with more than 20 years of experience in the development of database-backed business applications.
If you're interested in this line of work or related consulting, send me a message or an email (details on my GitHub account or you can find me on LinkedIn) to see if I'm available at the moment.
You can also follow on LinkedIn where I post daily