Ask HN: Do you use stored procedures for CRUD operations in your database?

[aredirect]

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?

What are your thoughts? and are you aware of any framework/library using such approach?

[austin-cheney]

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.

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.

[romanhn]

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.

[stephenr]

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.

[Someone]

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 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.

[stephenr]

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.

[Someone]

But if you let callers use SQL for CRUD operations, you have to give them the ability to execute arbitrary SQL, and there’s no guarantee they’ll only do CRUD operations.

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.

[foobarbaz33]

> doesn't allow SQL injection

Here's an injection vulnerable stored proc call.

    string badSql = "exec fooProc " + arg;

User input need to be parameterized whether you are calling a stored proc or not.

[icedchai]

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.

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.)

[kgdiem]

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.

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.

[kasey_junk]

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.

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.

[ensocode]

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.

[max_hammer]

I work as data engineer and won't advise using stored procedures.

It's pain to debug and document.

[gardenhedge]

It works but having DB compiles in your release pipelines is a pain