7 ms·

You don't use ORMs for gnarly queries -- that's not what they are for! They are for making manipulating the entities easier -- reading the data out of the data

You don't use ORMs for gnarly queries -- that's not what they are for! They are for making manipulating the entities easier -- reading the data out of the database in a way that makes easy to modify. You can (and should) use them for simple queries. You have a list of entities you want to query and filter, that's going to be fine. Joins are fine. But if you're doing some complex analysis, an ORM is the wrong tool. That doesn't mean it's a poor abstraction, or difficult to follow, or something to be avoided. It's not the right tool for that job. For the job it's designed for, it's going to save a lot of effort. SQL is great for analysis -- it's pretty much what it's designed for. But for bringing data into your app and modifying it, SQL is cumbersome and verbose. If you're loading data into objects then you're just creating your own personal ORM anyway.

daenz 7y ago
If the argument is that it's the right tool for simple jobs, then by definition it won't save a lot of effort.
- peterhadlaw 7y ago
  Just because a job is simple doesn't mean it doesn't take time & effort. A tool can be helpful if it reduces the time or effort to accomplish something.
  - Rapzid 7y ago
    > Just because a job is simple doesn't mean it doesn't take time & effort See digging.
- wvenable 7y ago
  That's not true. A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. With SQL you will also have a lot of uncheckable strings containing code. Simple tasks are done maybe thousands of times in any one application. It's the complex tasks are rare.
  - daenz 7y ago
    >A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. That's not a fair comparison unless you include the time and effort involved in creating your ORM models, before you can even write those 3 lines of ORM code. The effort to construct those models isn't even fully amortized over your project, since it must be maintained through schema migrations. ORMs are like putting on an exoskeleton to go buy groceries because it will let you carry your bags more easily. In my opinion, the complexity and indirection they introduce for accomplishing simple tasks do not justify their existence in the vast majority of cases.
    - wvenable 7y ago
      Creating an ORM model is so simple the only time I've even given it a second thought is when composing this reply. It's no more difficult than building tables by hand. There isn't any extra work that you wouldn't also have to do when not using an ORM.
      - daenz 7y ago
        cursor.execute(""" UPDATE employees INNER JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * percentage; """) Please show me how much simpler and fewer lines that would be in ORM code + model definitions.
        wvenable 7y ago
        You would need to show me your DDL statements because that's the equivalent. I can generate the model from the database or the database from the model. It's not much more effort to type "create table employees" with all the fixings as it is to type "class employees" with all the fixings.
        daenz 7y ago
        >I can generate the model from the database or the database from the model. Depends on the ORM. Just like "raw sql mode", not every ORM supports that. It's not an inherent feature of being an object-relational mapper, it's part of the bells and whistles of some ORM packages. And I'm going to guess you're coming from the Python/scripting universe, because generating models in other languages is definitely more complicated. But let's pretend that all ORM software can generate model definitions from the database. Just do the update then. I want to validate your argument that a simple update in SQL is "a lot more code especially if wiring up foreign relationships." If you think an ORM is much less code, or simpler, I'd like to see how.
- kodt 7y ago
  It might not save a lot of mental "effort" but they still save a lot of time that would otherwise be spent on writing simple boilerplate code.
- _frkl 7y ago
  I don't think that is what parent wanted to say. It's still a lot of boilerplate code and config that can be avoided by using an ORM for the 'simple' parts of an application. And if you use a framework like sqlalchemy, you still have access to the lower levels if it turns out the ORM abstraction is unsuitable.
geophile 7y ago
ORMs make the simple things simple, and the complicated things impossible.
- mindcrime 7y ago
  A. that's not even close to true, and B. if it were, that only argues that you don't use the ORM in the latter case. You can have an ORM in your project and still fall back to plain old SQL if you like. It's not an "either-or" thing.
- wvenable 7y ago
  ORMs let you drop into SQL whenever you need, usually in a way that is fully compatible with the model, so that's entirely false.
  - daenz 7y ago
    Running raw user SQL isn't a prerequisite of an ORM needed to make it an "ORM", it's a useful feature that most ORMs try to include because the authors recognize the many shortcomings. Also, by writing raw engine-specific SQL, you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
    - wvenable 7y ago
      > you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. The biggest selling point is a massive reduction in boilerplate code. Database agnostism is a feature that almost nobody ever uses, so who cares! Your proposed alternative is engine-specific SQL so you lose either way. At least with an ORM, you'd lose significantly less. You'd just have to deal with the places you used SQL. Which, in my experience, is pretty small and pretty specific. > this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. I often feel like people who complain about ORMs have either actually never used one or used a poor one. As long as my query matches the structure of my object(s) I don't need any stitching code. And if they didn't match, I wouldn't write stitching code because that would be waste of time. If I'm writing a custom query, I'm probably not looking to integrate into the model anyway -- if it's used for reporting I'd just take the results as is. If I'm writing a query specifically to get a matching model object (to manipulate) I'm going to get the whole model and no stitching would be required.
madhadron 7y ago
> They are for making manipulating the entities This is where I disagree. I dislike having a value which is the entity. The basic lesson from relational databases and later data oriented design is that you don't have an entity. All you have are aspects that are related.
- daenz 7y ago
  This is also tapping into one of the many optimizations you have to know about when using ORMs. Can you "select" 10M rows from a table? Is an object instantiated for each one? If they're lazily created, when are they destroyed, and where is the buffer of rows held, client side or server side? How do you efficiently update each one without incurring a sql statement for each update? All of these questions require deep knowledge of the inner workings of the ORM, when in SQL, this is a lot more straightforward.
  - Daishiman 7y ago
    Not really. It isn't a SQL issue; it's a database driver problem. The Django ORM takes take a whole of an hour to read, and after that you have 90% of the use cases covered. Considering that I've been using that framework for many years now, the overhead of that knowledge is irrelevant.
learc83 7y ago
I tend to use Active record for simple queries. And for anything complex, I write a SQL query to define a view and build a view backed model. That way I can use SQL when I want/need to, but I can keep it contained in one place, and I don't have constantly rewrite tedious queries.
rhacker 7y ago
You're going to love this: about the time I left one place I worked one top engineer did a meeting on using Hibernate for... drum roll... analytic queries. My brain fried with questions of why, how is this better, and this is definitely the result of someone that hasn't touched real SQL in a while and just wanted to use the same hammer for everything.
gernak 7y ago
>You can (and should) use them for simple queries. This is not a very compelling argument to use ORMs. It is saying "it makes easy things easier". This doesn't really buy you much value. The simple things are already simple. Bringing in a very large, complicated external dependency to make simple things simpler, is not a good idea. >If you're loading data into objects then you're just creating your own personal ORM anyway. By this definition, the Postgres driver I use for node is an ORM as it takes a row of string/type identification codes and turns them into javascript objects and javascript types. It isn't an ORM though, that is not what an ORM does. An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.
- scarface74 7y ago
  Eventually if you’re working with an object oriented language or if you have to convert the result to JSON, you’re going to have to convert one paradigm to another. Unless you’re programming in assembly, everything you do is being converted into another paradigm that is what every compiler and interpreter does.
  - gernak 7y ago
    You can only use a fraction of the features of a SQL database when you use an ORM because they don't translate to the new paradigm. When I am using a high level programming language, it is merely helping me do things like manage memory. I am not constantly wishing I could drop down and work with pointers and so on. It is a foundational paradigm that builds on the top of the one before it. ORMs just present a different, incompatible paradigm.
    - shkkmo 7y ago
      > You can only use a fraction of the features of a SQL database when you use an ORM because they don't translate to the new paradigm How does an ORM prevent you from using any SQL features? When you do the sorts of complicated things that are out of scope for an ORM, you do them in SQL. I don't see any incompatibility.
    - 7y ago

daenz 7y ago

If the argument is that it's the right tool for simple jobs, then by definition it won't save a lot of effort.

peterhadlaw 7y ago
Just because a job is simple doesn't mean it doesn't take time & effort. A tool can be helpful if it reduces the time or effort to accomplish something.
- Rapzid 7y ago
  > Just because a job is simple doesn't mean it doesn't take time & effort See digging.
wvenable 7y ago
That's not true. A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. With SQL you will also have a lot of uncheckable strings containing code. Simple tasks are done maybe thousands of times in any one application. It's the complex tasks are rare.
- daenz 7y ago
  >A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. That's not a fair comparison unless you include the time and effort involved in creating your ORM models, before you can even write those 3 lines of ORM code. The effort to construct those models isn't even fully amortized over your project, since it must be maintained through schema migrations. ORMs are like putting on an exoskeleton to go buy groceries because it will let you carry your bags more easily. In my opinion, the complexity and indirection they introduce for accomplishing simple tasks do not justify their existence in the vast majority of cases.
  - wvenable 7y ago
    Creating an ORM model is so simple the only time I've even given it a second thought is when composing this reply. It's no more difficult than building tables by hand. There isn't any extra work that you wouldn't also have to do when not using an ORM.
    - daenz 7y ago
      cursor.execute(""" UPDATE employees INNER JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * percentage; """) Please show me how much simpler and fewer lines that would be in ORM code + model definitions.
      - wvenable 7y ago
        You would need to show me your DDL statements because that's the equivalent. I can generate the model from the database or the database from the model. It's not much more effort to type "create table employees" with all the fixings as it is to type "class employees" with all the fixings.
        daenz 7y ago
        >I can generate the model from the database or the database from the model. Depends on the ORM. Just like "raw sql mode", not every ORM supports that. It's not an inherent feature of being an object-relational mapper, it's part of the bells and whistles of some ORM packages. And I'm going to guess you're coming from the Python/scripting universe, because generating models in other languages is definitely more complicated. But let's pretend that all ORM software can generate model definitions from the database. Just do the update then. I want to validate your argument that a simple update in SQL is "a lot more code especially if wiring up foreign relationships." If you think an ORM is much less code, or simpler, I'd like to see how.
kodt 7y ago
It might not save a lot of mental "effort" but they still save a lot of time that would otherwise be spent on writing simple boilerplate code.
_frkl 7y ago
I don't think that is what parent wanted to say. It's still a lot of boilerplate code and config that can be avoided by using an ORM for the 'simple' parts of an application. And if you use a framework like sqlalchemy, you still have access to the lower levels if it turns out the ORM abstraction is unsuitable.

geophile 7y ago

ORMs make the simple things simple, and the complicated things impossible.

mindcrime 7y ago
A. that's not even close to true, and B. if it were, that only argues that you don't use the ORM in the latter case. You can have an ORM in your project and still fall back to plain old SQL if you like. It's not an "either-or" thing.
wvenable 7y ago
ORMs let you drop into SQL whenever you need, usually in a way that is fully compatible with the model, so that's entirely false.
- daenz 7y ago
  Running raw user SQL isn't a prerequisite of an ORM needed to make it an "ORM", it's a useful feature that most ORMs try to include because the authors recognize the many shortcomings. Also, by writing raw engine-specific SQL, you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
  - wvenable 7y ago
    > you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. The biggest selling point is a massive reduction in boilerplate code. Database agnostism is a feature that almost nobody ever uses, so who cares! Your proposed alternative is engine-specific SQL so you lose either way. At least with an ORM, you'd lose significantly less. You'd just have to deal with the places you used SQL. Which, in my experience, is pretty small and pretty specific. > this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. I often feel like people who complain about ORMs have either actually never used one or used a poor one. As long as my query matches the structure of my object(s) I don't need any stitching code. And if they didn't match, I wouldn't write stitching code because that would be waste of time. If I'm writing a custom query, I'm probably not looking to integrate into the model anyway -- if it's used for reporting I'd just take the results as is. If I'm writing a query specifically to get a matching model object (to manipulate) I'm going to get the whole model and no stitching would be required.

madhadron 7y ago

> They are for making manipulating the entities This is where I disagree. I dislike having a value which is the entity. The basic lesson from relational databases and later data oriented design is that you don't have an entity. All you have are aspects that are related.

daenz 7y ago
This is also tapping into one of the many optimizations you have to know about when using ORMs. Can you "select" 10M rows from a table? Is an object instantiated for each one? If they're lazily created, when are they destroyed, and where is the buffer of rows held, client side or server side? How do you efficiently update each one without incurring a sql statement for each update? All of these questions require deep knowledge of the inner workings of the ORM, when in SQL, this is a lot more straightforward.
- Daishiman 7y ago
  Not really. It isn't a SQL issue; it's a database driver problem. The Django ORM takes take a whole of an hour to read, and after that you have 90% of the use cases covered. Considering that I've been using that framework for many years now, the overhead of that knowledge is irrelevant.

learc83 7y ago

I tend to use Active record for simple queries. And for anything complex, I write a SQL query to define a view and build a view backed model. That way I can use SQL when I want/need to, but I can keep it contained in one place, and I don't have constantly rewrite tedious queries.

rhacker 7y ago

You're going to love this: about the time I left one place I worked one top engineer did a meeting on using Hibernate for... drum roll... analytic queries. My brain fried with questions of why, how is this better, and this is definitely the result of someone that hasn't touched real SQL in a while and just wanted to use the same hammer for everything.

gernak 7y ago

>You can (and should) use them for simple queries. This is not a very compelling argument to use ORMs. It is saying "it makes easy things easier". This doesn't really buy you much value. The simple things are already simple. Bringing in a very large, complicated external dependency to make simple things simpler, is not a good idea. >If you're loading data into objects then you're just creating your own personal ORM anyway. By this definition, the Postgres driver I use for node is an ORM as it takes a row of string/type identification codes and turns them into javascript objects and javascript types. It isn't an ORM though, that is not what an ORM does. An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.

scarface74 7y ago
Eventually if you’re working with an object oriented language or if you have to convert the result to JSON, you’re going to have to convert one paradigm to another. Unless you’re programming in assembly, everything you do is being converted into another paradigm that is what every compiler and interpreter does.
- gernak 7y ago
  You can only use a fraction of the features of a SQL database when you use an ORM because they don't translate to the new paradigm. When I am using a high level programming language, it is merely helping me do things like manage memory. I am not constantly wishing I could drop down and work with pointers and so on. It is a foundational paradigm that builds on the top of the one before it. ORMs just present a different, incompatible paradigm.
  - shkkmo 7y ago
    > You can only use a fraction of the features of a SQL database when you use an ORM because they don't translate to the new paradigm How does an ORM prevent you from using any SQL features? When you do the sorts of complicated things that are out of scope for an ORM, you do them in SQL. I don't see any incompatibility.
  - 7y ago