re: ORM vs Query Builders vs Raw SQL VIEW POST


Does anyone have experience with having the most used queries as stored procedures on the database side?


Good question and definitely one that struck me whilst looking into this more.

If I move towards utilising more raw SQL, then really I've made some assumptions on the type of DB I'm going to be working against, therefore most of this could be moved to the DB as procedures or even views.

In some ways I can see that I'm dumbing down my application to be more concerned with working with defined interfaces, but I'm maybe losing some control / flexibility too.


Yes: it sucks, don't do it. Procedures/functions are great for manipulating large amounts of data in place, for implementing complex transactional processes, and for other sorts of heavy lifting or close-to-the-data work. They are not meant to encapsulate your SELECT * FROM users WHERE ... queries and they're very bad at doing so.

Many common queries are dynamic, which tends to be awkward in procs, but the principal reason this is a bad idea is that databases change much more slowly and deliberately than applications. If you need to filter users by another field from your application code, it's two seconds of typing and a deploy job away from production and all your teammates' dev environments. If you need to do it in a proc, every other copy of the schema is now out of date. Migration frameworks help, but only so much, and even minor migrations need to be handled with care.

code of conduct - report abuse