15 ms·

Exactly why ORMs are a bad idea. I've always wondered whether ORMs help or harm. I feel like the one reason to use it is if you have a development team that isn

by xfour 9y ago

Exactly why ORMs are a bad idea. I've always wondered whether ORMs help or harm. I feel like the one reason to use it is if you have a development team that isn't capable of writing SQL which in itself is bad.

jackmott 9y ago
it can be nice to have the whole stack type checked, so you know the query is good as you write it. There are a few languages/libraries where you can do this while also writing SQL directly, so you can kind of get the best of both worlds. Like F# sql type providers, where you can get compile time (or editor time) checks of your queries against the DB or a schema.
stevenou 9y ago
I think ORMs are there to make developing easier - but doesn't obviate the need for the user to understand how the underlying database works. For one based on my understanding of their use-case, could've used a validation :on => :create only. Likewise, if case-insensitivity is needed, they can enforce that on record insertion or, if they're using something else like MySQL, just use case insensitive collation. The fact that I know how to write SQL doesn't mean it's always easier/faster/better than using an ORM...
- megous 9y ago
  > I think ORMs are there to make developing easier Only for object oriented apps, where you want to tightly link behavior to your objects. For other kinds of app architectures, there may be no relational/object oriented mismatch to smooth out.
jbverschoor 9y ago
Sounds like stating that we should all use assembler.
- bdcravens 9y ago
  No, you could write raw SQL (we do this alot in our Rails app) or perhaps push out your abstractions to stored procedures (not a good option IMO, but maybe better than letting my framework come up with the best SQL)
wyc 9y ago
ORMs are great for prototyping new business systems. The performance is not great, but when your priority is to quickly model business logic into something functioning, there are few better tools for it. However, if you have well-defined requirements and are expecting high-volume traffic from the get-go, then it could be wise to skip the ORM.
porker 9y ago
> Exactly why ORMs are a bad idea I'm not a full-out fan for ORMs but... this is not an ORM issue. It's an issue with an ORM of the ActiveRecord variety that pushes database-logic back to code level for some strange reason. The ORM I'm most familiar with (Doctrine2, Object Mapper pattern) doesn't do this. It does other weird stuff, but not this.
- Semiapies 9y ago
  Just so. SQLAlchemy and many other ORMs allow you to simply define an index. ActiveRecord always reimplemented far too much of the database. I'm not sure whether that was due to the state of MySQL functionality at the time or what.
esaym 9y ago
Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly. And new hires will end up taking a ton of time to learn this "custom" orm framework of yours. And there are many bad, or simply, "too simple" ORMs out there that really don't help you much. I haven't really found one better than Perl's DBIx::Class (though outside of Java, Python, Ruby, I have haven't really looked). Case in point, I recently found this little gem[0] allowing easy correlated subqueries. I basically took a webpage that was loading in over 2 minutes, and reduced it to about 100ms and the outputted SQL was about 2 pages long (from about half a page of custom resultset orm code). I used several correlated subqueries to sort of pivot part of a table (well several tables actually). The original author of the code I was working on was fetching entire tables of data, with each row fetching (joining) to another entire table (it did this in several levels really) just to sum some values. This was all valid ORM code (even with handy 'if' statements checking every row id to do in ORM join (yes he didn't even use a dang where clause!)) but it showed a true lack of knowledge of the ORM at hand and even SQL in general. Nonetheless, I saved the day :) [0] https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-resultset-correlaterelationship/ https://blog.afoolishmanifesto.com/posts/introducing-dbix-cl...
- joncrocks 9y ago
  ORMs try and solve a hard problem, and the devil is in the detail. https://martinfowler.com/bliki/OrmHate.html https://martinfowler.com/bliki/OrmHate.html
  - marcosdumay 9y ago
    It surely is hard. But is it worth solving? The price you pay for a one-size-fits-all solution is that you lose the flexibility of arranging your queries the way you want. You'll automatically lose a lot of performance (that is not always important), but you also lose a kind of code clarity. If you don't get a one-size-fits-all solution, you will have to write the translations by yourself. That leads to more code, but in a better structure because you don't need to beat your code until it fits the ORM's model. All said, I do think an ORM is not something useful by itself, but may be part of a very useful solution. Python Django's CRUD generation and Haskell Persistent (not really ORM, but very similar) type checking are examples of that.
zip1234 9y ago
ORMs are a great idea and can save a lot of time and prevent a lot of errors. However, they are not a silver bullet. If you have performance bottlenecks you still need to know SQL and should be looking at the SQL that the ORM generates. There is also a 'mixed-mode' ORM or a lightweight ORM such as Dapper. Dapper allows you to write the queries in SQL but it makes it trivially easy to convert those to actual objects and allow you to parameterize them.
- StavrosK 9y ago
  I don't really understand why people throw the baby out with the bathwater like that. What's the problem of using an ORM for 99% of your queries and doing the other 1% in raw SQL? Many ORMs (like Django's) even help you by mapping the results back into models automatically[1]. [1] https://docs.djangoproject.com/en/1.11/topics/db/sql/#mapping-query-fields-to-model-fields https://docs.djangoproject.com/en/1.11/topics/db/sql/#mappin...
  - dpark 9y ago
    I use something like Dapper on one of my services. I don’t consider it “throwing the baby out with the bath water”. My experience with ORMs is that they waste far more time debugging odd behavior and working around quirks and poor generated queries than they save in coding effort. I’ve never felt that ORMs actually saves me that much effort except for the boilerplate of deserializing into objects. Everything else I feel is a net negative. Writing SQL generally isn’t that hard. For the cases where it is hard, ORM generally does a poor job anyway.
  - zaptheimpaler 9y ago
    The search for the holy grail continues..
weberc2 9y ago
ORMs aren't a bad idea; they just aren't a drop-in replacement for in-memory data structures for traditional imperative languages. They drop in rather well for functional languages though. Also, many ORMs are simply flat-footed, but we shouldn't fault ORMs generally any more than we should call C "slow" because some compiler is particularly bad.
FLUX-YOU 9y ago
SQL strings in source code are worse than ORMs, even if you move it out to a resources file of some kind. No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source. If you don't do that, you're dropping all of the productivity that syntax checkers do for you and risk losing productivity to typos or other dumb mistakes.
- Amezarak 9y ago
  Maybe I'm doing it wrong, but I like to keep simple queries in an ORM, and then execute anything complicated as a stored procedure.
  - FLUX-YOU 9y ago
    No, that's a perfectly practical approach. You're much less likely to make mistakes on procedure names vs. full queries. The trade off is managing procedure versioning and deployments of procedures. Ultimately it's a style preference but I've seen larger projects with thousands of lines of SQL in source with some of those queries spanning 6-7 joins with dozens of where clauses (plus string manipulation functions, ugh), so that is why I'm pretty fiercely against SQL in strings.
    - jandrese 9y ago
      Sometimes I see monster queries like that and think that they could have saved themselves several lines of code if they were willing to export the data and run some of the logic in the native language. Sometimes it seems like people get hung up on doing everything in one query and end up making a Frankenstein's monster that nobody will want to touch when it inevitably breaks on the next database upgrade. Although sometimes the system forces this behavior by ingesting the output of the query automatically.
      - Amezarak 9y ago
        Well, it depends. At least in my experience, assuming those queries are written "correctly" (that is, in a set-manner than a C-in-SQL manner), leaving it in the database means is usually vastly more performant and easier to verify it's correct. I've written a lot of many-hundred-line monsters with joins, CTEs, table-valued functions, and a whole lot of other stuff piled into them that were both more clear and much faster than the arcane application logic they once consisted of. Usually if it's used in the application, some management type will also want some kind of report based on pretty much the same query, too. And having it as SQL also makes it much quicker to compare the before-and-after if any changes are made. Of course, YMMV and what I said isn't true all the time, and would certainly also depend on your database server - if queries did break between upgrades I'd be much more hesitant to rely on the db server. But either way, I wouldn't dump a monster like that right in source code - that's crazy!
maxxxxx 9y ago
I think ORMs are very nice if you take the time to sometimes profile your DB and also look at the SQL it generates. You still can optimize slow queries by hand writing SQL. I definitely prefer an ORM codebase over one with direct SQL statements. Best in my view is to abstract all database access into a separate layer. A little more work but much easier to maintain and tune if needed.
meritt 9y ago
ORMs aren't the problem, they're simply a tool. Developers who never bothered to learn SQL, indexing, relational theory or schema design are the problem. Incidentally, this aversion to learning is also how we ended up with MongoDB.
tynpeddler 9y ago
ORM's let you write a lot less code since you don't have to do the object-relational mapping yourself. That's just one of several reasons I usually recommend them. The issue is, as you've pointed out, that people sometime use ORM's as an excuse to not understand SQL, which is a terrible idea. But the bottom line is that you have to optimize all your database calls, including ones implemented with ORM.
dalore 9y ago
Well this actually had nothing to do with using an ORM. If he put that same validation into an SQL call and ran it on update it would have been the same effect.
lostboys67 9y ago
If your in that position run away and join the foreign legion to forget the "horror". All web developers ought to know the basics of SQL ie how to SELECT INSERT and UPDATE

by xfour 9y ago

jackmott 9y ago

it can be nice to have the whole stack type checked, so you know the query is good as you write it. There are a few languages/libraries where you can do this while also writing SQL directly, so you can kind of get the best of both worlds. Like F# sql type providers, where you can get compile time (or editor time) checks of your queries against the DB or a schema.

stevenou 9y ago

I think ORMs are there to make developing easier - but doesn't obviate the need for the user to understand how the underlying database works. For one based on my understanding of their use-case, could've used a validation :on => :create only. Likewise, if case-insensitivity is needed, they can enforce that on record insertion or, if they're using something else like MySQL, just use case insensitive collation. The fact that I know how to write SQL doesn't mean it's always easier/faster/better than using an ORM...

megous 9y ago
> I think ORMs are there to make developing easier Only for object oriented apps, where you want to tightly link behavior to your objects. For other kinds of app architectures, there may be no relational/object oriented mismatch to smooth out.

jbverschoor 9y ago

Sounds like stating that we should all use assembler.

bdcravens 9y ago
No, you could write raw SQL (we do this alot in our Rails app) or perhaps push out your abstractions to stored procedures (not a good option IMO, but maybe better than letting my framework come up with the best SQL)

wyc 9y ago

ORMs are great for prototyping new business systems. The performance is not great, but when your priority is to quickly model business logic into something functioning, there are few better tools for it. However, if you have well-defined requirements and are expecting high-volume traffic from the get-go, then it could be wise to skip the ORM.

porker 9y ago

> Exactly why ORMs are a bad idea I'm not a full-out fan for ORMs but... this is not an ORM issue. It's an issue with an ORM of the ActiveRecord variety that pushes database-logic back to code level for some strange reason. The ORM I'm most familiar with (Doctrine2, Object Mapper pattern) doesn't do this. It does other weird stuff, but not this.

Semiapies 9y ago
Just so. SQLAlchemy and many other ORMs allow you to simply define an index. ActiveRecord always reimplemented far too much of the database. I'm not sure whether that was due to the state of MySQL functionality at the time or what.

esaym 9y ago

Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly. And new hires will end up taking a ton of time to learn this "custom" orm framework of yours. And there are many bad, or simply, "too simple" ORMs out there that really don't help you much. I haven't really found one better than Perl's DBIx::Class (though outside of Java, Python, Ruby, I have haven't really looked). Case in point, I recently found this little gem[0] allowing easy correlated subqueries. I basically took a webpage that was loading in over 2 minutes, and reduced it to about 100ms and the outputted SQL was about 2 pages long (from about half a page of custom resultset orm code). I used several correlated subqueries to sort of pivot part of a table (well several tables actually). The original author of the code I was working on was fetching entire tables of data, with each row fetching (joining) to another entire table (it did this in several levels really) just to sum some values. This was all valid ORM code (even with handy 'if' statements checking every row id to do in ORM join (yes he didn't even use a dang where clause!)) but it showed a true lack of knowledge of the ORM at hand and even SQL in general. Nonetheless, I saved the day :) [0] https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-resultset-correlaterelationship/ https://blog.afoolishmanifesto.com/posts/introducing-dbix-cl...

joncrocks 9y ago
ORMs try and solve a hard problem, and the devil is in the detail. https://martinfowler.com/bliki/OrmHate.html https://martinfowler.com/bliki/OrmHate.html
- marcosdumay 9y ago
  It surely is hard. But is it worth solving? The price you pay for a one-size-fits-all solution is that you lose the flexibility of arranging your queries the way you want. You'll automatically lose a lot of performance (that is not always important), but you also lose a kind of code clarity. If you don't get a one-size-fits-all solution, you will have to write the translations by yourself. That leads to more code, but in a better structure because you don't need to beat your code until it fits the ORM's model. All said, I do think an ORM is not something useful by itself, but may be part of a very useful solution. Python Django's CRUD generation and Haskell Persistent (not really ORM, but very similar) type checking are examples of that.

zip1234 9y ago

ORMs are a great idea and can save a lot of time and prevent a lot of errors. However, they are not a silver bullet. If you have performance bottlenecks you still need to know SQL and should be looking at the SQL that the ORM generates. There is also a 'mixed-mode' ORM or a lightweight ORM such as Dapper. Dapper allows you to write the queries in SQL but it makes it trivially easy to convert those to actual objects and allow you to parameterize them.

StavrosK 9y ago
I don't really understand why people throw the baby out with the bathwater like that. What's the problem of using an ORM for 99% of your queries and doing the other 1% in raw SQL? Many ORMs (like Django's) even help you by mapping the results back into models automatically[1]. [1] https://docs.djangoproject.com/en/1.11/topics/db/sql/#mapping-query-fields-to-model-fields https://docs.djangoproject.com/en/1.11/topics/db/sql/#mappin...
- dpark 9y ago
  I use something like Dapper on one of my services. I don’t consider it “throwing the baby out with the bath water”. My experience with ORMs is that they waste far more time debugging odd behavior and working around quirks and poor generated queries than they save in coding effort. I’ve never felt that ORMs actually saves me that much effort except for the boilerplate of deserializing into objects. Everything else I feel is a net negative. Writing SQL generally isn’t that hard. For the cases where it is hard, ORM generally does a poor job anyway.
- zaptheimpaler 9y ago
  The search for the holy grail continues..

weberc2 9y ago

ORMs aren't a bad idea; they just aren't a drop-in replacement for in-memory data structures for traditional imperative languages. They drop in rather well for functional languages though. Also, many ORMs are simply flat-footed, but we shouldn't fault ORMs generally any more than we should call C "slow" because some compiler is particularly bad.

FLUX-YOU 9y ago

SQL strings in source code are worse than ORMs, even if you move it out to a resources file of some kind. No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source. If you don't do that, you're dropping all of the productivity that syntax checkers do for you and risk losing productivity to typos or other dumb mistakes.

Amezarak 9y ago
Maybe I'm doing it wrong, but I like to keep simple queries in an ORM, and then execute anything complicated as a stored procedure.
- FLUX-YOU 9y ago
  No, that's a perfectly practical approach. You're much less likely to make mistakes on procedure names vs. full queries. The trade off is managing procedure versioning and deployments of procedures. Ultimately it's a style preference but I've seen larger projects with thousands of lines of SQL in source with some of those queries spanning 6-7 joins with dozens of where clauses (plus string manipulation functions, ugh), so that is why I'm pretty fiercely against SQL in strings.
  - jandrese 9y ago
    Sometimes I see monster queries like that and think that they could have saved themselves several lines of code if they were willing to export the data and run some of the logic in the native language. Sometimes it seems like people get hung up on doing everything in one query and end up making a Frankenstein's monster that nobody will want to touch when it inevitably breaks on the next database upgrade. Although sometimes the system forces this behavior by ingesting the output of the query automatically.
    - Amezarak 9y ago
      Well, it depends. At least in my experience, assuming those queries are written "correctly" (that is, in a set-manner than a C-in-SQL manner), leaving it in the database means is usually vastly more performant and easier to verify it's correct. I've written a lot of many-hundred-line monsters with joins, CTEs, table-valued functions, and a whole lot of other stuff piled into them that were both more clear and much faster than the arcane application logic they once consisted of. Usually if it's used in the application, some management type will also want some kind of report based on pretty much the same query, too. And having it as SQL also makes it much quicker to compare the before-and-after if any changes are made. Of course, YMMV and what I said isn't true all the time, and would certainly also depend on your database server - if queries did break between upgrades I'd be much more hesitant to rely on the db server. But either way, I wouldn't dump a monster like that right in source code - that's crazy!

maxxxxx 9y ago

I think ORMs are very nice if you take the time to sometimes profile your DB and also look at the SQL it generates. You still can optimize slow queries by hand writing SQL. I definitely prefer an ORM codebase over one with direct SQL statements. Best in my view is to abstract all database access into a separate layer. A little more work but much easier to maintain and tune if needed.

meritt 9y ago

ORMs aren't the problem, they're simply a tool. Developers who never bothered to learn SQL, indexing, relational theory or schema design are the problem. Incidentally, this aversion to learning is also how we ended up with MongoDB.

tynpeddler 9y ago

ORM's let you write a lot less code since you don't have to do the object-relational mapping yourself. That's just one of several reasons I usually recommend them. The issue is, as you've pointed out, that people sometime use ORM's as an excuse to not understand SQL, which is a terrible idea. But the bottom line is that you have to optimize all your database calls, including ones implemented with ORM.

dalore 9y ago

Well this actually had nothing to do with using an ORM. If he put that same validation into an SQL call and ran it on update it would have been the same effect.

lostboys67 9y ago

If your in that position run away and join the foreign legion to forget the "horror". All web developers ought to know the basics of SQL ie how to SELECT INSERT and UPDATE