2 ms·

Given your experience, what is your opinion on stored procedures? I love them, think that what can be done in the database should stay in the database, and man

by pjmlp 2mo ago

Given your experience, what is your opinion on stored procedures? I love them, think that what can be done in the database should stay in the database, and many of these abstraction on top are all ways to avoid just having to implement them. And the main reason, DB portability, seldom happens in reality, most product die still using the database they were original created with.

geophile 2mo ago
I agree with you on all points. SPs are incredibly useful. DB portability is such a strange goal. Very common for some reason, but rarely actually needed. I think there are probably two reasons for the hate that SPs get. 1) Come on, I learned SQL, isn't that enough? I have to learn SPs too? 2) Architecture astronauts love them their tiers, and logic belongs in the tier above the database, not the database tier itself. (I expressed this opinion in a job interview -- without disparaging any group of techies -- and I believe this is the reason I was not invited back.)
zem 2mo ago
I like the idea behind stored procedures, but the ergonomics of developing and maintaining them are not great. if they could be made to look like a library of code sitting in a directory somewhere, and transparently compiled and imported by the database but still workable with using external tools like git, I think they would feel a lot less strange.
- pjmlp 2mo ago
  The ergonomics are the same as any language, when using IDEs with the SQL vendors plugins, instead of vi and CLI admin for queries.
  - ninkendo 2mo ago
    > when using IDEs with the SQL vendors plugins Do these plugins mean you don't get to store them in git? You're just going to open up the developer studio and YOLO a change to the stored procedure, live in production? Because the whole argument is that the way we do version control, code review, bisecting, single-artifact deployment, etc is generally at odds with how stored procedures work. Saying "but my IDE has a good plugin" solves maybe 1/100th of the problem. Some answers to doing stored procedures in a version control system that I've seen: - Put everything in a migrations directory, and every time you change the stored procedure, introduce a new migration that completely rewrites it. (Merge conflicts are hell with this, plus all the massive amount of waste it generates in the checked-out tree) - Put the stored procedures in a directory as normal code and then "sync" them to the database at runtime (with all the massive foot-guns this entails, trying to detect if they've changed versus what's in the database, etc) - Eschewing stored procedures in favor of using prepared statements and having your ORM figure out when to use them There may be others but I think they're all going to look like some form of one of the above.

by pjmlp 2mo ago

geophile 2mo ago

I agree with you on all points. SPs are incredibly useful. DB portability is such a strange goal. Very common for some reason, but rarely actually needed. I think there are probably two reasons for the hate that SPs get. 1) Come on, I learned SQL, isn't that enough? I have to learn SPs too? 2) Architecture astronauts love them their tiers, and logic belongs in the tier above the database, not the database tier itself. (I expressed this opinion in a job interview -- without disparaging any group of techies -- and I believe this is the reason I was not invited back.)

zem 2mo ago

I like the idea behind stored procedures, but the ergonomics of developing and maintaining them are not great. if they could be made to look like a library of code sitting in a directory somewhere, and transparently compiled and imported by the database but still workable with using external tools like git, I think they would feel a lot less strange.

pjmlp 2mo ago
The ergonomics are the same as any language, when using IDEs with the SQL vendors plugins, instead of vi and CLI admin for queries.
- ninkendo 2mo ago
  > when using IDEs with the SQL vendors plugins Do these plugins mean you don't get to store them in git? You're just going to open up the developer studio and YOLO a change to the stored procedure, live in production? Because the whole argument is that the way we do version control, code review, bisecting, single-artifact deployment, etc is generally at odds with how stored procedures work. Saying "but my IDE has a good plugin" solves maybe 1/100th of the problem. Some answers to doing stored procedures in a version control system that I've seen: - Put everything in a migrations directory, and every time you change the stored procedure, introduce a new migration that completely rewrites it. (Merge conflicts are hell with this, plus all the massive amount of waste it generates in the checked-out tree) - Put the stored procedures in a directory as normal code and then "sync" them to the database at runtime (with all the massive foot-guns this entails, trying to detect if they've changed versus what's in the database, etc) - Eschewing stored procedures in favor of using prepared statements and having your ORM figure out when to use them There may be others but I think they're all going to look like some form of one of the above.