It’s Time to Get over That Stored Procedure Aversion You Have
rob.conery.io
rob.conery.io
My biggest issue with stored procedures is change management. I've seen plenty of crazy custom tools that try to use Git, SVN, and just file system copies of Stores procedures. All of em have been 'lovely' to work with to say the least.
Having your core business logic on the server is amazing due to version control. If it's performance that's a bottle neck I would say 95% (my POV) it's a developer that doesn't understand what the ORM is doing. The beauty of using EF or another micro ORM (dapper) is that SQL performance is optimized from caching the SQL hash. And the option is still there to execute a stored procedure.
Theres absolutely times for using stored procedures, but I'd sacrifice a little bit of performance for maintainability.
For example, one can keep them in 'stored-procedures.sql' under Git version control and deploy them via one command, like `psql ...`.
Won't it work that way?
Tooling is a devs best friend. Using an IDE or vim/emacs with the correct extensions allows developers to easily see where a class is being used, allowing quick inspection. I do believe stored procedures have a place but they've been abused too much.
No. I was thinking about something like a greenfield project, small or medium size. Where about 100 stored procedures are all named nicely and 2-3 developers working on the project fully understand what they are doing with them :)