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
Top comments (6)
So true. I've had sober senior developers argue that SPs are a dinosaur, they are hard to debug, you can do everything they do easier in business-layer code and LINQ queries, etc.
You effectively skewered most of that, especially about abstraction and separation of business/storage layer. VIEWs and SPs let DBAs change their minds (somewhat) about the details of storage, how data is extracted, etc. And, while people are touting the utility of "server-less" functions, SPs and SQL functions are the original version of that, pieces of logic that are tuned and optimized to run closer to the data ... where they belong.
Stored Procedures don't get a lot of love and that's because very often parts of the app that should be in the app find their way into the queries. Or because sometimes if all we do is simple CRUD it's seen as additional complication. But they have their place and I'm a firm believer in them. I use them mainly for security, isolation, performance, and centralization. Not every kind of query is suited for a stored procedure, tho. I work with realtime analytical data and while we can do it with stored procedures we can achieve more more interesting results much easier with other methods.
And beside, I do not like building queries but joining a bunch of strings in the app, and I am not a fan or ORMs.
Hi there. Yes, am in agreement. Good post.
thanks
How about debugging, unit testing and version control?
For unit testing and version control you can use something similar in this article: dev.to/vbilopav/net-identity-with-...
Only instead of SQL, you can use stored procedure and you can test them. Store them in migration files or in separate script files referenced by migration which can be version controlled as any other file.
For debugging you debug them, like any other SQL. Normal classic debuggers just won't work with the statement because of the vast difference in paradigm and code execution. SQL is a higher generation programming language that can abstract algorithms, so it can't debugged with step and continue. Just use print statements (or raise debug in Postgres) for procedural code and relay on examination of query results and execution plans.
Hope this helps.