10 ms·

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.
  - timdev2 7y ago
    > you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays. The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting entities to relational storage. > 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. That's not typical in most uses I've seen. Far more typical are things like: - Go straight to SQL for reporting, since that's what SQL does. Useful in reporting contexts, and also for list/filter UI screens. - Use raw SQL to query a list of entity IDs for updating based on some complex criteria. Iterate over the identifiers and perform whatever logic you need to before letting the ORM handle all the persistence concerns.
    - falcolas 7y ago
      > I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production. As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.
      - asdfaoeu 7y ago
        > As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS. You can use mysql though...
      - sidlls 7y ago
        Unit testing code that touches the database is not useful, and in fact it indicates there is likely a design flaw. Code that acquires from or changes data in the DB should be self contained. An integration test that doesn't use the same DB as production is unsatisfactory.
      - 7y ago
  - Glyptodon 7y ago
    I've always thought the "being SQL database agnostic" theory of ORMs was more about a development team being able to choose from some common choices than about apps being portable in practice.
    - timdev2 7y ago
      Right. That. An ORM for (only) PostgreSQL would seem odd -- though I do wonder if that might pay off in some ways.
    - dkersten 7y ago
      Yeah, I’ve never seen any complex applications, using ORM’s, that were easy to port to another database. Hell, one company I was at switched from MySQL to MariaDB and even that took some work despite that they should be almost the dame thing. If you switch to a more substantially different database, eg, from MySQL to Postgres, then its even harder. I’m also a believer in using a databases features when it makes sense and not limiting myself to the standardised subset of SQL just in case I might want to change databases later.
  - ratww 7y ago
    > you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic 1. If the suggested alternative is writing only SQL from the get-go, then why would one even care about database agnosticism? 2. A large part of SQL is compatible between databases, so this might be a non-issue. 3. You can always use specific in-database abstractions such as Views and Stored Procedures for those complicated bits.
  - pests 7y ago
    The PHP ORM Doctrine supports a custom SQL like language called DQL that integrates your defined model/relationships as well as converting it to the correct SQL dialect for your backing store. Queries look like: SELECT u FROM ForumUser u WHERE (u.username = :name OR u.username = :name2) AND u.id = :id Where ForumUser is your model. https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html https://www.doctrine-project.org/projects/doctrine-orm/en/2....
    - bpicolo 7y ago
      If I remember right, it's hugely lacking in ability to interact with more complex data types in e.g. postgres, like jsonb
      - timdev2 7y ago
        It's certainly limiting, though you can write (or find implementations[0] of) custom types[1] that can be pretty powerful (void where prohibited, limitations apply). [0] https://github.com/martin-georgiev/postgresql-for-doctrine https://github.com/martin-georgiev/postgresql-for-doctrine [1] https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/types.html#custom-mapping-types https://www.doctrine-project.org/projects/doctrine-dbal/en/2...
        bpicolo 7y ago
        Yeah, the missing bit was custom operator support if I remember right.
- pknopf 7y ago
  Just use a Micro ORM from the get-go. It's the perfect type-safe abstraction on top of raw SQL. https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/CollaboratingPlatypus/PetaPoco https://github.com/CollaboratingPlatypus/PetaPoco Any errors you get are likely a result of the underlying database/provider (foreign key constraints, etc). You should never write raw SQL (if possible). You don't need an ORM to achieve that.
- djrobstep 7y ago
  That's absolutely not true. With any of the ORMs I've used, as soon as you do something even slightly unorthodox like using a view, you're on your own.
  - strokirk 7y ago
    Which ones have you used?
  - mst 7y ago
    Try http://p3rl.org/DBIx::Class http://p3rl.org/DBIx::Class in perl sometime. If you're on your own using a view of all the simple shit, you're using an awful and pointless ORM.
  - wvenable 7y ago
    All the ORMs I've used love views because views are just read-only tables. The dumbest ORM might not even notice the difference.
- philwelch 7y ago
  Let's talk about how this works in reality. In ActiveRecord, there's a method called find_by_sql. You can't call it directly; it's a class method on an ActiveRecord model. So you have to choose which of your ActiveRecord models should be used to instantiate the rows of your result set. (What if your result set doesn't really match any of your models? Pick one arbitrarily.) Your SQL has some extra columns. What happens to the data in those columns? They get monkey-patched onto the individual objects. (Which is stupidly expensive in Ruby.) Other than that, the individual objects are fine. They even have all your smart instance methods, which may or may not behave properly with all the ad-hoc monkey patching. If you tried to short-circuit all of that nonsense, you tend to get arrays of hash tables. Which is, in my opinion, already a perfectly adequate interface!
  - james-mcelwain 7y ago
    > What if your result set doesn't really match any of your models? Pick one arbitrarily. I don't want to sound like the ORM defender, but I'm not sure I understand. This sounds like a deficiency of Ruby and the ActiveRecord record model. In Java, for example, you'd just write a new POJO for your query, which isn't exactly difficult. There are no "smart methods" or whatever. It is a valid criticism that this can proliferate data classes, but that depends on the application.
    - philwelch 7y ago
      I was writing in terms of what I actually understand and have used—which doesn’t include any Java ORM. In fact if there are Java ORMs that consist solely of POJOs which are populated by raw SQL queries, I would gladly use them!
  - slicebo123 7y ago
    You can execute a "non-model" query like so: results = ActiveRecord::Base.connection.execute(sql)
    - philwelch 7y ago
      The API docs don’t make it clear whether that’s still possible so I didn’t mention it explicitly, but I have done that before and that’s what I was alluding to with the “array of hash tables” comment.
- dkersten 7y ago
  In my personal experience, ORM’s seem to encourage queries to get scattered throughout your logic (they’re just normal code and function calls, afterall... at least, they look like it) and encourage mixing application-side logic with query logic. The former makes it incredibly hard to remove if you need to reach for raw SQL and the latter leads to bad performance due to many application-database roundtrips snd not filtering enough before sending data to the application. Yes, both of these things can be solved through disciplined modularisation of ORM logic, but in my personal experience across multiple companies, most developers simply aren’t that disciplined and treat ORM code as any other application code, instead of treating it as the remotely executed database code that it actually is. In my experience, writing raw SQL (through https://www.hugsql.org/ https://www.hugsql.org/ in my case), you are instead encouraged to think of them as separate and carefully consider the boundaries, which helps keep the queries and application logic modular and allows for more carefully crafted queries that minimise roundtrips and data shuffling. Again, this has been my experience, across a number of companies. Perhaps your experience differs, in which case, I’m jealous.
- kimi 7y ago
  Hibernate promises that you use the database "as if" it was not there. You can drop into SQL whenever you need, but you cannot remove all the stuff you had to add just to make it work (saving, reloading, handling caching....). So instead of being "out of the way" it poisons your entire code-base.
geodel 7y ago
Now this is excellent point which is true in my experience. It gives little ramp up in starting and keep creating speed breakers as development proceeds to handle real complex business scenarios.
pmontra 7y ago
They make simple things simpler, which is a good thing. I wouldn't mind writing queries in SQL so I don't have to learn a new ORM for every language I use. However I like that ORMs unmarshal results for me in objects / tuples / maps of the language sparing me the work. For complicated things I write SQL and possibly decode the results manually.
deleted 7y ago
[deleted]
0xDEFC0DE 7y ago
If you know SQL and know the ORM, you can just do the complicated things in SQL. If you know a problem is going to be complicated, you know to use SQL. If you don't know if a problem is going to be complicated, you can default to SQL and decide to use the ORM later if it's a good fit once you've got more problem details.

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.
- timdev2 7y ago
  > you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays. The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting entities to relational storage. > 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. That's not typical in most uses I've seen. Far more typical are things like: - Go straight to SQL for reporting, since that's what SQL does. Useful in reporting contexts, and also for list/filter UI screens. - Use raw SQL to query a list of entity IDs for updating based on some complex criteria. Iterate over the identifiers and perform whatever logic you need to before letting the ORM handle all the persistence concerns.
  - falcolas 7y ago
    > I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production. As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.
    - asdfaoeu 7y ago
      > As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS. You can use mysql though...
    - sidlls 7y ago
      Unit testing code that touches the database is not useful, and in fact it indicates there is likely a design flaw. Code that acquires from or changes data in the DB should be self contained. An integration test that doesn't use the same DB as production is unsatisfactory.
    - 7y ago
- Glyptodon 7y ago
  I've always thought the "being SQL database agnostic" theory of ORMs was more about a development team being able to choose from some common choices than about apps being portable in practice.
  - timdev2 7y ago
    Right. That. An ORM for (only) PostgreSQL would seem odd -- though I do wonder if that might pay off in some ways.
  - dkersten 7y ago
    Yeah, I’ve never seen any complex applications, using ORM’s, that were easy to port to another database. Hell, one company I was at switched from MySQL to MariaDB and even that took some work despite that they should be almost the dame thing. If you switch to a more substantially different database, eg, from MySQL to Postgres, then its even harder. I’m also a believer in using a databases features when it makes sense and not limiting myself to the standardised subset of SQL just in case I might want to change databases later.
- ratww 7y ago
  > you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic 1. If the suggested alternative is writing only SQL from the get-go, then why would one even care about database agnosticism? 2. A large part of SQL is compatible between databases, so this might be a non-issue. 3. You can always use specific in-database abstractions such as Views and Stored Procedures for those complicated bits.
- pests 7y ago
  The PHP ORM Doctrine supports a custom SQL like language called DQL that integrates your defined model/relationships as well as converting it to the correct SQL dialect for your backing store. Queries look like: SELECT u FROM ForumUser u WHERE (u.username = :name OR u.username = :name2) AND u.id = :id Where ForumUser is your model. https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html https://www.doctrine-project.org/projects/doctrine-orm/en/2....
  - bpicolo 7y ago
    If I remember right, it's hugely lacking in ability to interact with more complex data types in e.g. postgres, like jsonb
    - timdev2 7y ago
      It's certainly limiting, though you can write (or find implementations[0] of) custom types[1] that can be pretty powerful (void where prohibited, limitations apply). [0] https://github.com/martin-georgiev/postgresql-for-doctrine https://github.com/martin-georgiev/postgresql-for-doctrine [1] https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/types.html#custom-mapping-types https://www.doctrine-project.org/projects/doctrine-dbal/en/2...
      - bpicolo 7y ago
        Yeah, the missing bit was custom operator support if I remember right.
pknopf 7y ago
Just use a Micro ORM from the get-go. It's the perfect type-safe abstraction on top of raw SQL. https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/CollaboratingPlatypus/PetaPoco https://github.com/CollaboratingPlatypus/PetaPoco Any errors you get are likely a result of the underlying database/provider (foreign key constraints, etc). You should never write raw SQL (if possible). You don't need an ORM to achieve that.
djrobstep 7y ago
That's absolutely not true. With any of the ORMs I've used, as soon as you do something even slightly unorthodox like using a view, you're on your own.
- strokirk 7y ago
  Which ones have you used?
- mst 7y ago
  Try http://p3rl.org/DBIx::Class http://p3rl.org/DBIx::Class in perl sometime. If you're on your own using a view of all the simple shit, you're using an awful and pointless ORM.
- wvenable 7y ago
  All the ORMs I've used love views because views are just read-only tables. The dumbest ORM might not even notice the difference.
philwelch 7y ago
Let's talk about how this works in reality. In ActiveRecord, there's a method called find_by_sql. You can't call it directly; it's a class method on an ActiveRecord model. So you have to choose which of your ActiveRecord models should be used to instantiate the rows of your result set. (What if your result set doesn't really match any of your models? Pick one arbitrarily.) Your SQL has some extra columns. What happens to the data in those columns? They get monkey-patched onto the individual objects. (Which is stupidly expensive in Ruby.) Other than that, the individual objects are fine. They even have all your smart instance methods, which may or may not behave properly with all the ad-hoc monkey patching. If you tried to short-circuit all of that nonsense, you tend to get arrays of hash tables. Which is, in my opinion, already a perfectly adequate interface!
- james-mcelwain 7y ago
  > What if your result set doesn't really match any of your models? Pick one arbitrarily. I don't want to sound like the ORM defender, but I'm not sure I understand. This sounds like a deficiency of Ruby and the ActiveRecord record model. In Java, for example, you'd just write a new POJO for your query, which isn't exactly difficult. There are no "smart methods" or whatever. It is a valid criticism that this can proliferate data classes, but that depends on the application.
  - philwelch 7y ago
    I was writing in terms of what I actually understand and have used—which doesn’t include any Java ORM. In fact if there are Java ORMs that consist solely of POJOs which are populated by raw SQL queries, I would gladly use them!
- slicebo123 7y ago
  You can execute a "non-model" query like so: results = ActiveRecord::Base.connection.execute(sql)
  - philwelch 7y ago
    The API docs don’t make it clear whether that’s still possible so I didn’t mention it explicitly, but I have done that before and that’s what I was alluding to with the “array of hash tables” comment.
dkersten 7y ago
In my personal experience, ORM’s seem to encourage queries to get scattered throughout your logic (they’re just normal code and function calls, afterall... at least, they look like it) and encourage mixing application-side logic with query logic. The former makes it incredibly hard to remove if you need to reach for raw SQL and the latter leads to bad performance due to many application-database roundtrips snd not filtering enough before sending data to the application. Yes, both of these things can be solved through disciplined modularisation of ORM logic, but in my personal experience across multiple companies, most developers simply aren’t that disciplined and treat ORM code as any other application code, instead of treating it as the remotely executed database code that it actually is. In my experience, writing raw SQL (through https://www.hugsql.org/ https://www.hugsql.org/ in my case), you are instead encouraged to think of them as separate and carefully consider the boundaries, which helps keep the queries and application logic modular and allows for more carefully crafted queries that minimise roundtrips and data shuffling. Again, this has been my experience, across a number of companies. Perhaps your experience differs, in which case, I’m jealous.
kimi 7y ago
Hibernate promises that you use the database "as if" it was not there. You can drop into SQL whenever you need, but you cannot remove all the stuff you had to add just to make it work (saving, reloading, handling caching....). So instead of being "out of the way" it poisons your entire code-base.

geodel 7y ago

Now this is excellent point which is true in my experience. It gives little ramp up in starting and keep creating speed breakers as development proceeds to handle real complex business scenarios.

pmontra 7y ago

They make simple things simpler, which is a good thing. I wouldn't mind writing queries in SQL so I don't have to learn a new ORM for every language I use. However I like that ORMs unmarshal results for me in objects / tuples / maps of the language sparing me the work. For complicated things I write SQL and possibly decode the results manually.

deleted 7y ago

[deleted]

0xDEFC0DE 7y ago

If you know SQL and know the ORM, you can just do the complicated things in SQL. If you know a problem is going to be complicated, you know to use SQL. If you don't know if a problem is going to be complicated, you can default to SQL and decide to use the ORM later if it's a good fit once you've got more problem details.