4

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

Improve this question
2
  • 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/…
    – Tulains Córdova
    Commented Sep 4, 2016 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
    Commented Sep 4, 2016 at 7:46

1 Answer 1

Reset to default
4

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.
Improve this answer
9
  • 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
    Commented Sep 4, 2016 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
    Commented Sep 4, 2016 at 7:46
  • 1
    Stored procedures, yuck. Good luck debugging those.
    – Andy
    Commented Sep 4, 2016 at 12:32
  • 1
    @mgw854 My thoughts exactly. I work on SaaS apps where performance and scalability are important and have always either had SQL expertise or had experts around who know SQL very well. My team evaluated ORM a few months ago for a new project and the outcome was that it might (questionably) save us a few hours up-front, then cause us to spend lots of time debugging. Or we could spend a few hours longer hand-writing SQL, and spend very little time debugging. It comes down to time-to-market, how important performance is, and what skills the individual developers on a team have.
    – Brandon
    Commented Sep 4, 2016 at 17:54
  • 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
    Commented Sep 4, 2016 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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