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

user974407
  • 211
  • 2
  • 6
  • Take a look at this question of mine and its answers. I think I will accept the less voted one. https://programmers.stackexchange.com/questions/328788/pure-pojo-refactoring-active-record-pattern-using-repository-pattern-no-orm-or – Tulains Córdova Sep 04 '16 at 00:47
  • 2
    I can highly recommend [CQRS](http://martinfowler.com/bliki/CQRS.html). 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 04 '16 at 07:46

1 Answers1

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.
Brandon
  • 4,555
  • 19
  • 21
  • 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 04 '16 at 05: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 04 '16 at 07:46
  • 1
    Stored procedures, yuck. Good luck debugging those. – Andy Sep 04 '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. – Greg Burghardt Sep 04 '16 at 14:24
  • @DavidPacker I have never had a problem debugging stored procedures. But I have had problems debugging SQL code which is generated dynamically. I don't care to debate ORM vs. non-ORM (been there - found everybody generally has their mind made up, either way), but I feel I need to respond because your comment is very subjective in my opinion. – Brandon Sep 04 '16 at 17:51
  • 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 Sep 04 '16 at 17:54
  • Somebody care to explain the down vote? I gave the OP a suggestion based on real-world experience and they were happy with it. What do you suggest would have made this answer better? – Brandon Sep 04 '16 at 17:57
  • @Brandon You are an experienced developer, that's why you're fine with that. I would most likely be too. But take a mediocre programmer, put them in front of a, let's say, C# project containing most of business logic inside bugged SQL stored procedures and they are going to be lost for quite some time. Should the bugs be within the C# code itself they would be able to find a solution much quicker, because they are a C# developer after all, not an SQL enginner. From my experience moving procedures to code has proved to be cheaper when searching for new developers, which clients appreciate. – Andy Sep 04 '16 at 18:26
  • 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 04 '16 at 18:30