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

  • Take a look at this question of mine and its answers. I think I will accept the less voted one. programmers.stackexchange.com/questions/328788/… Sep 4 '16 at 0:47
  • 2
    I can highly recommend CQRS. It's absolutely amazing! Simple, very performant and lays strong rules about data manipulation through your domain (commands) while not limiting you to certain data representation using your domain models, because queries are a separate layer. Take a look at that. Posibbly the greatest pattern I have ever adapted.
    – Andy
    Sep 4 '16 at 7:46

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:

  • Spring JDBC to minimize boiler plate (see JdbcTemplate and BeanPropertyRowMapper - we have a general abstraction which takes a stored procedure name, parameters, and a java.lang.Class, and automatically binds the result set.
  • Spring for dependency injection allows for easily wiring in DAO's and mocking them out for testing.
  • This is the way to go... stored procedures are more powerful than any ORM I've ever come across. Wearing them in a DAO keeps everything loosely coupled and allows for mocking and unit testing. If I need to do anything but simple CRUD work, I'd rather write SQL than let the ORM generate something that would never pass a code review. It may be an unpopular opinion, but with the scale of data that I work with, even a few bad queries are unacceptable.
    – mgw854
    Sep 4 '16 at 5:46
  • What about MyBatis? It works just with the sql statements you define for each operation (insert, delete, update, select) and it also does object mapping. It supports procedures aswell.
    – Laiv
    Sep 4 '16 at 7:46
  • 1
    Stored procedures, yuck. Good luck debugging those.
    – Andy
    Sep 4 '16 at 12:32
  • The select N+1 problem is a common one with ORM's, however this can be mitigated by configuring it properly to do a join when fetching relationships. I think time wise they are the same, however you have two sets of code in two languages to maintain. I think to just go ORM and nothing else, or just go stored procedures ignores the strengths of both tech stacks. A combination, leaning towards ORM I've found to be more useful. Sep 4 '16 at 14:24
  • 1
    The downvote was by me, because I don't like your suggestion. I wouldn't want to hear that suggestion in my company. I believe stored procedures are a thing of the past and should not be used until absolutely necessary. Among other things like caching we started adapting SOAP/REST web services so we could transform the stored procedures from SQL into a more common, and to a common programmer, easier-to-understand code (Java, C#, PHP, JavaScript).
    – Andy
    Sep 4 '16 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.