You're sadly fighting against the "but... but... it's how people DO it" mentality here.
Not to mention, as computing expands, the average capability of people is dropping. Seats need to be filled, and those seats don't all need to be high quality devs.
So ORMs are there to reduce the workload, in terms of what needs to be learned to be productive. Database tuning is becoming a lost art.
(For DEVs not getting this, on a largish DB, I can literally write a single query which can take 10 seconds in MySQL's console, or hours. Yes, hours.
This isn't a flaw, any more than doing a tree search wrong is a flaw in <insert language here> when it is slow.
And most ORMs don't come close to properly handling all edge cases here.)
Beyond all of that, there is another bonus to using stored procedures or functions. Security.
There are many tables I want to give read only access to, yet want to allow writing in very limited scenarios. A stored procedure can have different user permissions (at least under most DBMS), which means you can:
- make a table read only for a web application user
- then, allow a stored procedure to perform INSERT/UPDATES, via a different user
- however, let the web user execute the stored procedure
The stored procedure thereby limiting the INSERT/UPDATE to a very specific action.
Mostly, I've used this when dealing with tables/databases which have a financial component. Said stored procedures can even perform additional checks on other tables, whatever is required to ensure that the update == OK.
Beyond that, it also allows for tiered security, in case you have someone in house, maybe a new dev, who decides they'd like to modify that financial table a bit.
And beyond that, it also allows logging to be done at the DB level.
While you are more limited here, in that you really do not want to be burdening the DB with more writes, you can definitely log all modifications to said financial table.
(You could use triggers for this last bit too, of course, but still...)
> Not to mention, as computing expands, the average capability of people is dropping. Seats need to be filled, and those seats don't all need to be high quality devs.
Also, a lot of businesses have analysts/data scientists that know SQL and have access to the DBs: stores procedures make business domain-level functionality more available to them because they don’t have to guess what application code is actually doing.
> ORMs are there to reduce the workload, in terms of what needs to be learned to be productive
The problem is (which you seem to recognize but most seem to miss) is that the opposite happens: the ORM lets you shoot yourself in the foot quickly, but that's about it. In the end, you lose more time using an ORM than you actually gain.
Not to mention, as computing expands, the average capability of people is dropping. Seats need to be filled, and those seats don't all need to be high quality devs.
So ORMs are there to reduce the workload, in terms of what needs to be learned to be productive. Database tuning is becoming a lost art.
(For DEVs not getting this, on a largish DB, I can literally write a single query which can take 10 seconds in MySQL's console, or hours. Yes, hours.
This isn't a flaw, any more than doing a tree search wrong is a flaw in <insert language here> when it is slow.
And most ORMs don't come close to properly handling all edge cases here.)
Beyond all of that, there is another bonus to using stored procedures or functions. Security.
There are many tables I want to give read only access to, yet want to allow writing in very limited scenarios. A stored procedure can have different user permissions (at least under most DBMS), which means you can:
- make a table read only for a web application user - then, allow a stored procedure to perform INSERT/UPDATES, via a different user - however, let the web user execute the stored procedure
The stored procedure thereby limiting the INSERT/UPDATE to a very specific action.
Mostly, I've used this when dealing with tables/databases which have a financial component. Said stored procedures can even perform additional checks on other tables, whatever is required to ensure that the update == OK.
Beyond that, it also allows for tiered security, in case you have someone in house, maybe a new dev, who decides they'd like to modify that financial table a bit.
And beyond that, it also allows logging to be done at the DB level.
While you are more limited here, in that you really do not want to be burdening the DB with more writes, you can definitely log all modifications to said financial table.
(You could use triggers for this last bit too, of course, but still...)