I am putting together an web app with relatively complex but standard database relationships. I notice that anytime I use an ORM, in python/ruby/php etc, in general, a lot of queries are generated. This is stating the obvious. ORM's typically create more queries than necessary to get the job done. They aren't perfect.
I could create these queries myself. This seems old school but, the app will most likely run faster and I won't have to fool around with ORM's. Instead I will be fooling around with SQL. I would rather fool around with SQL, than a language specific/ version specific library.
I am not afraid of ORM's, I've used them with great success in the past.
I am wondering how people generally (on a macro level) organize their code to support raw SQL queries.
For example, let's say I use an MVC like architecture in my application.
- My views are in HTML with some tempting library.
- My controllers get data from the models and send it to the views.
- My models map individual/multiple db tables to properties and methods in a class like structure for easy access.
Where in there should the sql commands be put? I can imagine it could be a recipe for sloppy code or models with miles of sql queries. Also it would make it impossible to add something like a default sort. And we're back to ORM's again.
If I were to think about this, I might create a sql helper for each model, which contains the query. Wondering if there are other ways
Best Answer
What we're doing on my team is using dedicated Data Access Object for putting our DB access code and storing the actual SQL code in stored procedures.
I know it sounds old fashioned, but it's tried and true. We evaluated an ORM framework at the insistence of a particular team member and we discovered an N+1 select problem in a very trivial code path. Since we have SQL expertise on our team, we made the same decision as you.
DAO sound painful? Lots of boilerplate? To keep it simple and clean, we are using: