OrmHate

Relational algebra or Datalog.

> 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.

Active record tells you where it came from in the log.

> 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.

I really have never understood the ORM hate. I've found them to be immensely useful in 99% of circumstances, and for the remaining 1%, a good ORM will always let you fall back to raw SQL. Aside from providing a simpler syntax for performing basic queries, there are a few features that ORMs provide that have greatly simplified my life:

1. Automatically using prepared statements and validating/escaping query arguments to prevent injection. You have to be quite a bit more careful when you're working with raw SQL.

2. Providing an clean API to construct complex queries.

This typically becomes an issue when you have a query where you are filtering and/or sorting by multiple fields which are specified by the user, some of which are conditional. If you're writing raw SQL you end up needing to do a lot of string manipulation which can get fairly messy (and makes the code more difficult to understand). An ORM which provides some sort of query builder syntax that lets you do:

  if (some condition):
    query.addWhere(clause)
  if (some other condition):
    query.addWhere(other clause)
  etc.

is pretty convenient.

3. Collating repeated result rows from joins

Whenever you're working with joins you end up with repeated data in your result set, which you generally end up having to collate before display. For example if I have Recipes Categories and do a query to load the both of them, I might end up with something like this:

  recipe_id | recipe_name  | recipe_ingredients | category_id | category_name
  --------------------------------------------------------------------------
  1         | Shrimp Pasta | 1 cup tomato sauce | 1           | Pasta
  1         | Shrimp Pasta | 1 cup tomato sauce | 2           | Seafood
  1         | Shrimp Pasta | 1 cup tomato sauce | 3           | Shrimp
  2         | Fruit Cake   | 4 cups flour...    | 4           | Dessert
  2         | Fruit Cake   | 4 cups flour...    | 5           | Cakes

Without an ORM I have to loop through the result set to re-format the data the way I wanted before displaying it. An ORM takes care of that for me and gives me back 2 recipes with their categories accessible via recipe.categories.

4. Simplified manipulation of many-to-many relationships. Following the above example, if I want to add a new Category to a Recipe I can simply do:

  recipe.categories.add(category)

If I want to set the categories to something entirely different, I can do:

  recipe.categories = [category1, category2, category3, etc]

Without the ORM I would have to manually sync up the entries in the join table which is kind of a pain in the ass. Working with join tables in general is rather obnoxious, so I'm quite glad that the ORM takes care of that one for me.

5. Some ORMs give you notifications when an object (or collection of objects) changes. This is pretty important on the client side when you want to make sure the data you're displaying stays up to date, even as it is being manipulated. For example: if I'm viewing a recipe on my iPad and I update that recipe on my desktop. A background thread is running which keeps the two synchronized, and at some point the underlying recipe is updated in the database on my iPad.

If I'm working with raw SQL there's basically no way to know when that object is changed (short of polling it periodically, or rolling your own notification system). But my ORM will keep me notified of changes to the object so I can refresh the user interface with the updated recipe after the sync completes.

6. Some ORMs implement a unit of work that allows you to track what changes have been made to an object since it was retrieved from the database. So you can easily see which fields have been modified, and then when you go to save the object back out, it will intelligently only issue the SQL to update the columns which have changed, or won't even touch the database if nothing has actually changed.

7. Some ORMs put their objects into an identity map, so if you query for the same object under multiple different scenarios (e.g different areas of your UI), you always get the same underlying instance back. This means that you don't have multiple copies of what is semantically the same object floating around in different places of your app, and the object is always up to date with the latest changes.

Note: my use case is typically client-side database backed software, so features like (5), (6), and (7) save me from having to do a TON of work. If you're doing more web oriented stuff, I can see how those particular features may be less useful to you. Still, I think ORMs are a huge win overall.

Of course, none of this absolves you from needing to know what's going on at the database level. You still have to know what SQL your ORM is generating in order to make sure you're using it correctly. But I get really confused when people badmouth ORMs and try to tell me that it's simpler to use raw SQL, cause it never has been for me and my use cases. And the funniest thing is, if I stuck with raw SQL while attempting to solve all of the problems I listed above, I probably would end up with a half-assed version of a full-fledged ORM anyway.

(The only use case I can think of where I'd prefer to use raw SQL over an ORM is with report generation type activities: usually those types of queries aren't very dynamic, they can often be too complex to be expressed via the ORM's API, and once you have the data you're just dumping it to display without worrying about interactivity anyway.)