I just stumbled on this today and apparently some people advocate to use stored procedures for select, insert, update, and delete operations with the argument it helps to reduce network traffic, increases the performance and doesn't allow SQL injection?<p>What are your thoughts? and are you aware of any framework/library using such approach?
About 80% of the business logic at my last job were in SQL stored procedures. This was a giant mistake because there was no automation in place for things like replication across tenants, static analysis, test automation, or anything at all.<p>It became too expensive to keep up with evolving business requirements and I was laid off as a result even though I was a late hire who did not plan any of that madness. Other people were laid off too but I am not sure how many because I was the first person on this small team they released.<p>My suggestion is to impose a tight separation of concerns and let data be just data. If you are planning architecture always remember that software is always a cost center, so always automate the shit out of it at all stages. Tools and frameworks will not save you from a lack of vision.
Worked at a company that used this pattern about 15 years ago. We had a homebuilt ORM that codegen'ed these stored procs. If I recall, stored procedures were faster than straight up SQL at one point, but that hasn't been true for a while (same perf characteristics these days). Nowadays it feels like unnecessary overhead. I also like to keep code in the application layer, so try to avoid stored procs if I can. Any reasonable backend framework/ORM should prevent SQL injection so I don't find that to be a compelling argument either.
For selects I'd have though having views would be a better solution here.<p>But it sounds like a lot of overhead to maintain, presuming it's one insert/update/delete proc per table.<p>IMO stored procedures help where you want to make some reasonably complex logic easier to call into from the application, OR where you want to trigger said reasonably complex logic from multiple DB events.
> doesn't allow SQL injection<p>Here's an injection vulnerable stored proc call.<p><pre><code> string badSql = "exec fooProc " + arg;
</code></pre>
User input need to be parameterized whether you are calling a stored proc or not.
I haven't really seen the "stored procedures for all DB access" pattern in over 20 years. In the Oracle and Sybase days, it seemed common. In general, it feels like too much overhead during development. It's another language you need to work with, another thing you have to update / keep in sync, and it will complicate deploys.<p>I'd only use stored procs for specific niche uses cases. And only if it was really performance critical. If you want to avoid SQL injection, you use prepared statements (or a framework/ORM that uses them.)
I’m running an entire ETL pipeline with sprocs, functions and triggers. It works extremely well, has been running for several months without any downtime or interruption.<p>Snowflake introduced their marketplace and those apps / data shares necessitate sprocs and database functions. I made a couple of those apps in my last role and versioning is definitely a challenge. Rolling back is easy enough but what do you do with lost/corrupted data?<p>I have worked on a small CRUD app where everything was done with sprocs with a small laravel layer and that worked well enough.
This was a much more common practice when the architecture pattern was to have many applications accessing (and writing) to the same db. These days that’s pretty uncommon so the development overhead isn’t usually worth it.<p>That said, I have had use recently to split out which tables can be written by which applications and then abstracting the selects into views.
It probably depends on the use case. We usually try to avoid stored procedures as it is in-transparent, not really easy to debug and finally it is business logic in the data store which isn't a good approach in my perspective.