Hard disagree with this attitude but I see it all the time.
Stored procedures are much faster than writing logic in some remote server (just by virtue of getting rid of all the round trips), require far less code (no DAOs, entities and all that crap which simply serves to duplicate existing definitions), and have built-in strong consistency checking primitives - which can even be safely delayed until the end of the transaction.
And what people do is, they throw all these advantages away because they can’t be bothered working out how to integrate the stored procedure code ergonomically into their workflow.
I mean - I even use an IDE (JetBrains) to write pl/pgsql. It’s just another file in my repo. Get to this point and stored procedures are a game changer.
I second that. SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.
> If you don't have the discipline to do these things then they are likely best avoided.
I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.
Before the development of decent migration systems it was incredibly common for database structure - including stored procedures - to be treated independently of source code in a repository.
It's a common thing to miss. There's a reason SQL injections are (unless things have changed recently) among the most prevalent classes of web exploits.
Yeah, I think so. But my hunch is that the majority of people who tell you never to use stored procedures have been burned by these techniques not being used for them.
From my experience only if there are dedicated DBAs and you have too many systems running - then you forget one. If you only have server code and the stored procedures in the same repository, with migrations, this problem goes away.
> SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.
This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
If the developer doesn't know / doesn't document the project has code embedded in the database, that's on the developer, not the tools. Because the use of any developer tools requires a certain level of competence in order to use them successfully.
I thought so for 30 years but changed my opinion recently. I even argued with the author of Redis for some time to add some functionality so we didn't have to write Lua and have another deployment target.
Now I do think there is a benefit in stored procedures and triggers (E.g. for audits) if they don't contain too much logic or complexity.
> if they don't contain too much logic or complexity.
I think this is the catch. Most folks who are arguing against SP have been burned by huge complex stored procedures with nested dependencies with deeply intertwined business logic and rules. I completely agree that you shouldn't use a SP in that way. But to help perform maintenance, or to audit, or perform data correction all make sense when kept small and simple.
I've only given up trying to understand a system once. It was when I was handed over an application that used stored procedures for everything. Including recursive stored procedures... The rest could be figured out, but they were just too much.
I feel this. Once had something locking up a production SQL Server instance, and it turned out to be a dreadful partially-recursive web of sprocs, views, and TVFs that worked fine until apparently one day the query optimiser decided otherwise. Spent hours tracing what the heck was going on.
Why? What is your specific reasoning? Using EXPLAIN and SP's to help fix cache misses, slow queries, poor index performance, etc. is generally considered a good thing.
As a side note, I did not realize $diety was concerned about DDL/DML, so thanks for pointing it out. I never really thought about it.
Stored procedures has some advantages (fast to debug/try out a query from your service without copy+paste all the time, etc), but also disadvantages (unreadable git diffs, big bang rollouts on changes)
Stored procedures are much faster than writing logic in some remote server (just by virtue of getting rid of all the round trips), require far less code (no DAOs, entities and all that crap which simply serves to duplicate existing definitions), and have built-in strong consistency checking primitives - which can even be safely delayed until the end of the transaction.
And what people do is, they throw all these advantages away because they can’t be bothered working out how to integrate the stored procedure code ergonomically into their workflow.
I mean - I even use an IDE (JetBrains) to write pl/pgsql. It’s just another file in my repo. Get to this point and stored procedures are a game changer.