Comment by pjmlp
17 hours 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.
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.
The ergonomics are the same as any language, when using IDEs with the SQL vendors plugins, instead of vi and CLI admin for queries.
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.)