6 ms·

> There is a lot of truth to these charges, but such charges miss a vital piece of context. The object/relational mapping problem is hard. I suspect that conte

• dasil003 14y ago
  And yet many ORMs such as ActiveRecord let you do this trivially, maintaining as much of it's functionality as you want, such as mapping column types to appropriate pl types. In my experience, even if you had a policy of all queries being written explicitly in SQL by all developers and encapsulating these into your own data access layer, you would still have quite a bit to gain from the well-tested community boilerplate in one of the available ORM packages.
  • davesims 14y ago
    This. Use ORMs for what they do well, and move into straight SQL when it makes sense.
• debacle 14y ago
  > Those people who best understand the inherent object-relational impedance mismatch tend to be the very people who conclude that the effort isn't worth it. You're showing a severe bias. > I have found that ORMs are the most useful for the most trivial queries. That's really what ORMs are for. Three joins, max. Any more than that and you really can't trust the ORM. It's a computer, after all, not a DBA.
  • RyanMcGreal 14y ago
    In other words, ORMs are at their most useful when solving the least challenging problems. To which we must ask: why bother?
    • debacle 14y ago
      I'm going to make an argument here, and you might disagree but that's fine: If you don't understand the purpose of an ORM, you haven't worked on a sufficiently large enough software project or don't fully understand what the definition of an ORM is. If you need to write a report of any fashion, do not use an ORM. You can help write the report with your ORM of choice, but at some point you need to get down to the language that the database speaks because you're doing the kind of set mathematics that ORMs aren't made for. BUT, if you need to add a user permission, and you're doing anything other than: $user->addPermission( Permissions->get( 'CAN_EAT_CAKE' ) ) you're doing something wrong.
      • Philadelphia 14y ago
        But that doesn't require or imply an ORM. That's just a matter of having an object model.
        • debacle 14y ago
          If you have a database, any object model is at some level an ORM.
        • stevenwei 14y ago
          If your object model at some point gets persisted into a database, then you're going to need to write code to (load from/save to) the database. At which point, if it walks like a duck, and quacks like a duck...
    • Daniel_Newby 14y ago
      Because most tasks are simple. If you need to write SQL to put the three most recent blog postings on the company homepage, you're doing it wrong.
• brazzy 14y ago
  Any good ORM will let you write your query directly, and still save your the work of mapping the resultset to a collection of objects.
  • yatendra 14y ago
    I agree. There are some micro ORMs in .Net world that do exactly this and lately I have started using Dapper http://code.google.com/p/dapper-dot-net/ http://code.google.com/p/dapper-dot-net/ that came out of stackoverflow and I really enjoy using this over nhibernate.
• maratd 14y ago
  > In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries. Bingo. In other words, non-relational queries work well and relational queries don't. When I'm joining multiple tables and looking for specific rows/objects that match multiple restrictions, ORM turns into a migrane of epic proportions. SQL was specifically designed to solve that exact problem. Abstracting away from SQL will simply increase the pain factor.
  • gnaritas 14y ago
    > When I'm joining multiple tables and looking for specific rows/objects that match multiple restrictions, ORM turns into a migrane of epic proportions. Perhaps you're doing it wrong. Put the complex query in a proc or view, and use the ORM to map the result set into memory. Using an ORM doesn't mean not using SQL when it works better.
    • y0ghur7_xxx 14y ago
      >> When I'm joining multiple tables and looking for specific >> rows/objects that match multiple restrictions, ORM turns into a >> migrane of epic proportions. > > Perhaps you're doing it wrong. Put the complex query in a proc or > view, and use the ORM to map the result set into memory. Using an ORM > doesn't mean not using SQL when it works better. that's what he sad: use sql when it works better. if you put it in a proc, a view or your application makes no difference.
• jerf 14y ago
  That's the view I've come around to. The more I learn, the more I see that the ORM problem isn't just hard, it's effectively unsolvable. They don't map. There is no such map. It turns out to be a general principle that just because something is basically impossible doesn't mean that you can't write code that sort of kind of does it mostly most of the time, but the usually when that happens you end up with an enormous endless tar pit of a mess that even after massive resource expenditure still has lots of quirky problems and doesn't really work. It's better to build your system on foundation that doesn't involve solving an impossible problem. Another example: "Multitasking" in Mac OS prior to OSX. No amount of hackery can turn a cooperative multitasking system into a preemptive one... but you sure can make a big mess trying. There's a lot of other examples if you look around. Look for a big, endlessly-growing mass of code that never seems to stabilize no matter how many resources are poured into it, and you'll probably find it's trying to do something impossible deep down in its core.
• lucian1900 14y ago
  And tools that help one write SQL queries more easily (like SQLAlchemy) are so much more useful than ORMs. The fact that SQLAlchemy also has an ORM built on top of those tools only sweetens the deal.
  • RyanMcGreal 14y ago
    Heh. I use SQLAlchemy, but I don't use the associated ORM.
• Spooky23 14y ago
  > In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries. For anything complicated, I find it easier to drop into SQL and write the query directly As a recovering DBA, I want to jump out of my seat and say "Yes, exactly!" In many situations, "simple" queries can easily cover 90% of what you actually need, and simple queries are often an order of magnitude less expensive than even modestly complicated ones. If your devs are in the habit of writing lots of SQL, they'll often decide to do things like write one complex query to avoid the need for a few trivial queries. From a developer point of view, that makes sense, but the result is that you end up chewing lots of database resources. Databases tend to become scarce resources quickly, and it's usually very expensive to scale up. In my previous experience, the ORM helped reduce needlessly complex queries, and freed up resources on the DBA side of the house to help optimize the complex stuff that needed to be there. Granted, I was a DBA a long time ago, and resources were far less plentiful back in the good old days. But I think this is still relevant whenever you get into "big" databases.
  • sjwright 14y ago
    > If your devs are in the habit of writing lots of SQL, > they'll often decide to do things like write one complex > query to avoid the need for a few trivial queries. As someone who single-handedly maintains a large codebase and a large database for a large website, I can assure you you've hit the nail on the head but completely missed the point. Unless you're writing a hello world style to-do list, trivial queries tend to be the vast minority of the application logic needed. Most queries need some degree of tuning, whether that be to pull out specific rows, associate related data, summarise, or sort. Do this right, and you can achieve in a single query what would take multiple, dozens, perhaps hundreds of round trips to the database if written with "trivial" queries. And let's not forget, it's particularly rare for the "complex" query to be more complex than the application code needed to implement the equivalent task. SQL isn't always beautiful, but it can often express ideas more succinctly than most general purpose languages. One GROUP BY can save you from writing big loops. One INNER JOIN or subquery can save you from subsequent database calls. One HAVING clause can save you pages of twisted logic. A "complex" query generally avoids the need for a few trivial queries that are comparatively inefficient, both in terms of resources and latency. The goal is to allow the database to do the hard work of extracting every byte you need and nothing else. Databases are good at this shit, and even with a good ORM you're often forced to compromise or maintain two worlds within one app. --------- That said, I acknowledge that there are multiple legitimate points of view on this debate. I tend to find that the SQL vs ORM debate tends to fall depending on the sort of code that's being written, and the sort of logic that's needed. Desktop apps tend to suit ORMs more readily than web apps. Smaller and self-contained apps tend to survive ORMs more gracefully than broad-scoped apps. Nothing can excuse stored procedures though. Useless garbage.
• davesims 14y ago
  > In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries. True, but 'trivial queries' generally cover the majority of use cases within your average CRUD application, and that's exactly why ORMs are useful -- you don't end up re-typing 'select bar from fu where id = 1' or 'select * from fu, bar where fu.bar_id = bar.id' etc., etc. Beyond that, most of the common joins are trivial as well, and can be abstracted away, and a lot of those queries end up being optimized according to known SQL patterns for the common use cases, so you can avoid n+1 and so forth. For these work-a-day queries, ORMs work just fine. That said, there are tons of situations where ORMs make no sense at all, like complex migrations, large OLAP-style analytic queries, etc. For these, it's important to keep your SQL chops up and know the pitfalls. So, I use ORMs where they work well, and drop into SQL all the time where they don't. The trick, as with most things, is finding the right balance: don't try to abstract away complex, singular use cases, but also don't duplicate trivial code all over your code base where the ORM has done the work for you.
  • crazygringo 14y ago
    But ORM's don't actually help for the trivial queries. I generally find myself writing functions to automate the trivial ones: get_single_field_by_primary_key(), get_row_by_primary_key(), get_values_by_field(), etc. There are maybe 4 or 5 of these. An entire ORM is total overkill. As for the common joins, in my experience they rarely are that trivial. Sometimes it's a left join, sometimes an inner join. Sometimes there's a limit or not. Sometimes there's an aggregate function. If you find yourself writing the same query structure 20 times, then create a function for it. But I've never found that to be the case in projects I work on. I just personally find that ORM's are total overkill in the situations where they can be usefully applied. So the disdain for them kind of makes sense.
    • gnaritas 14y ago
      > I generally find myself writing functions to automate the trivial ones: get_single_field_by_primary_key(), get_row_by_primary_key(), get_values_by_field(), etc. There are maybe 4 or 5 of these. An entire ORM is total overkill. Making a homebrew data layer isn't avoiding an ORM, it's building one. > If you find yourself writing the same query structure 20 times, then create a function for it. What do you think ORM's do? That is what they do, they provide a standard API for all the common queries.
• jakejake 14y ago
  That's exactly how I feel, in fact I was inspired to write an article about it when this topic came up on HN a few weeks ago http://verysimple.com/2012/05/14/re-orm-haters-in-defense-of-orms/ http://verysimple.com/2012/05/14/re-orm-haters-in-defense-of... As an ORM author I may be a bit biased, but I really think that, like all things, ORMs are a tool that can be used or abused. That's no reason to throw them out. It must have snuck up on me that there was even an anti-ORM thing happening!