1–10 of 14 posts
What are your thoughts? and are you aware of any framework/library using such approach?
But it sounds like a lot of overhead to maintain, presuming it's one insert/update/delete proc per table.
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.
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?
I have worked on a small CRUD app where everything was done with sprocs with a small laravel layer and that worked well enough.
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.
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.
For selects I'd have though having views would be a better solution here. But it sounds like a lot of overhead to maintain, presuming it's one insert/update/delete proc per table. 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.
If that’s the case, you don’t want to allow stakeholders to make arbitrary queries, as those could run fine from their perspective, but would be disastrous from that of the other stakeholders.
Team A could be fine having the database be excruciatingly slow for an hour while they run their monthly reporting queries or while they run a huge import or whole they do a select that forces a table scan on a huge table, but teams B, C, etc might not.
That’s why such a database has a separate team “DBA” whose job it is to know the full picture.
For selects I'd have though having views would be a better solution here. But it sounds like a lot of overhead to maintain, presuming it's one insert/update/delete proc per table. 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.
closing down your database, limiting callers to use stored procedures also helps if your database has more than one stakeholder calling into it. If that’s the case, you don’t want to allow stakeholders to make arbitrary queries, as those could run fine from their perspective, but would be disastrous from that of the other stakeholders. Team A could be fine having the database be excruciatingly slow for an hour while…
Earlier quoted context omitted.
closing down your database, limiting callers to use stored procedures also helps if your database has more than one stakeholder calling into it. If that’s the case, you don’t want to allow stakeholders to make arbitrary queries, as those could run fine from their perspective, but would be disastrous from that of the other stakeholders. Team A could be fine having the database be excruciatingly slow for an hour while…
I don't disagree there are uses for stored procedures - but the question as stated is about CRUD operations, not about complex queries or reporting.
Yes, you could enforce that through code review, but it’s easier to do by using the access control mechanisms of the database. That also would catch any problems before code review.
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.)