9 ms·

Ok, let’s break this down: > Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query compositi

Ok, let’s break this down: > Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. > providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL So this is the real ORM juice. And ya, without an ORM you have to do this by hand. So here’s the question: How well can an ORM map to your data model out of the box? In a lot of cases this is where the mess comes from. You need to set up basically a low level AI that can figure out the “right” thing to do as call sites all over your codebase are making arbitrary queries on a huge API surface. And so the argument against ORMs is that it will take less time and be less error-prone to write bespoke loaders that take rows and build your data model than it will be to configure that AI such that it can do the “right” thing with arbitrary queries. (If you don’t like the word “AI” here, use “expert system” instead.)

avmich 9y ago
> A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. What's the difference? To me, an ORM is largely a query builder.
- int_19h 9y ago
  Is it a good one, though?
  - JamesBarney 9y ago
    Probably better than the one I could build by hand, with far better documentation, and greater ease in googling problems :)
    - failedxyz 9y ago
      while ORMs can do quite a bit of optimization, they're still general query builders and can't construct the optimal queries for your use case. if you don't know SQL, or you don't know what's going on behind the scenes, your ORM could be performing much larger queries than it really needs to, costing performance and time
      - stouset 9y ago
        But that's a completely orthogonal problem — the point made (way) above is that you need to understand SQL whether or not you use an ORM.
      - gjjrfcbugxbhf 9y ago
        Most ORMs have way too write raw SQL. Furthermore someone using an ORM well will understand how their queries are mapping to SQL. Your scenario happens with people that either don't know or don't care. They will write crappy queries with any tool.
        collyw 9y ago
        Sounds about right. I built a moderately complex application in Django at a previous workplace, using the ORM for most things, until the queries were too complex for the ORM. Another guy connected to the same database and built some graphs using PHP and SQL. Guess who had to help him write the SQL when the queries got too complex for him? The ORM user.
  - jbergens 9y ago
    Bascially all in-house built query builders I've seen has been much worse.
- emilsedgh 9y ago
  ORM: You ask for something and you don't care _how_ its fetched. Query Builder: You build a query, just not in SQL. So you can get around SQL's limitations (like composition)
  - andybak 9y ago
    > ORM: You ask for something and you don't care _how_ its fetched. Try this: > ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. or possibly: > ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case. There's plenty of places for bottlenecks to hide and premature optimization is the root of at least some evils.
    - jcadam 9y ago
      > ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. Ha. More like: ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 months trying to get the damn ORM to generate performant SQL (you even call Oracle support, which promptly dispatches an "engineer" who tries to sell you on another $500k of crap you don't need that won't really address the problem). You eventually just start writing queries by hand wherever you find a bottleneck caused by the naive ORM, which you could have done at the outset, but no one who actually knew SQL well enough was on the team back then.
      - paulryanrogers 9y ago
        IME, it should never take more than a few hours to run down why the ORM made such a query. At a minimum most RDBMS's have query logging and can explain queries. Ironically the last time I had a big ORM performance problem was Hibernate eager loading all joins. Diagnosing and fixing it didn't take more than an hour. (Though we did have a very experienced DBA at the time.) YMMV
- Animats 9y ago
  ORMs are more useful as insert builders. Putting data from an object into the database is something of a boilerplate process. Queries vary with what you want to ask. Most of the time, you don't need all the fields, so filling up some object just because it has slots for everything is a waste of effort. Especially if it means references to multiple tables.
  - egeozcan 9y ago
    Good ORMs have Partial<T> and lazy loading of complex properties through proxies, which can be overridden with something like .With(x => x.ComplexProperty). But of course, as the queries get more and more complex, the flexibility of the ORM syntax approaches the flexibility SQL. In the end, there are many situations one would rather just use SQL.
  - majewsky 9y ago
    I think the best ORMs are those that just leave out the "Relational" part entirely. So... "OM"? For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous design. And when you have to do a query that returns results from multiple tables? Guess what, you just use the normal SQL module from the standard library.
    - scarface74 9y ago
      Why is it an either or? EF can do that but then you loose the benefits of a type safe language.
      - oceanswave 9y ago
        If type safety is so great, why isn’t sql statically type checked
        9y ago
- petre 9y ago
  A query builder aids you at constructing queries, while an ORM builds queries for you, runs them and maps the output to objects. It's more sophisticated than a query builder.
  - zimpenfish 9y ago
    > A query builder aids you at constructing queries ... that you understand and can be sure are sensible. > while an ORM builds queries for you ... that you have to hope are sensible. That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.
    - scarface74 9y ago
      How so? I can log the the sql being generated and look at the logs. Something I should be doing either way.
      - zimpenfish 9y ago
        If you have to log the generated SQL to understand what's happening, you're already behind the curve. And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?
        mgkimsal 9y ago
        > And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place? The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.
        zaphar 9y ago
        "saved time" at the least optional time to save time. ORM's are a maintenance burden. They obfuscate DB performance behind usually an enormous API surface. Most software work is maintenance work. Optimizing for initial deployment is shortsighted.
- akvadrako 9y ago
  SqlAlchemy Core vs SqlAlchemy ORM
  - cosarara97 9y ago
    And doctrine DBAL vs doctrine ORM
voodootrucker 9y ago
It depends on how willing you are to allow your object graph to match your relational schema. The key is to let SQL be SQL. I wrote one that allows you to load data using standard SQL resource files, but it handles the persistence automatically: https://github.com/bgard6977/sqorm https://github.com/bgard6977/sqorm Flyway & jOOq take a similar approach: https://www.jooq.org/ https://www.jooq.org/ Taking the SQL-first approach also allows you to serialize without circular reference problems, since you don't just load the data, you also define a path to decompose the graph into a DAG.
- keredson 9y ago
  also https://github.com/keredson/DKO https://github.com/keredson/DKO
bpanon 9y ago
No, no, no, I know more than you. Here is why. Bitcoin cryptocurrency AI biomedical supply chain networking quantum systems-thinker.
- oceanswave 9y ago
  Here, take my money!
- dang 9y ago
  Please don't post unsubstantive comments here. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newswelcome.html https://news.ycombinator.com/newswelcome.html
scarface74 9y ago
So the easiest way to avoid using an ORM is to create your own ORM? What AI do you need? You map your tables to objects and relationships between objects via FK relationships.
- pjmlp 9y ago
  I can get very creative with SELECTs, making use of Prolog style queries, which are fully done server side on the database. Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.
  - scarface74 9y ago
    If your ORM is downloading all of your data and querying client side, then your ORM "is doing it wrong". Entity Framework and any other Linq to data provider translates the Linq expression to the native language of the source data and does it server side.
    - pjmlp 9y ago
      I know EF, which is why my comment also mentions "code that is even more convoluted that the SQL one and thus with less performance". LINQ only allows for a fraction of what is possible with SQL, and good luck having the best queries generated out of it, if the RDMS doesn't happen to be SQL Server.
      - scarface74 9y ago
        Actually my DMS is sometimes Mongo and the Mongo driver does pretty well at translating Linq to Mongo Query using the aggregation framework. It all depends on the talent of the authors of the drivers.
  - zzzeek 9y ago
    > Most ORMs will download all the data and evaluate them on the client side I've seen that pattern in lots of homegrown applications, but I've never seen such a thing in a mainstream ORM. Care to provide examples ?
spdionis 9y ago
5 years down the road, good luck refactoring your application to not query the same object from the database 3-4 times, and load it only once instead.
- oceanswave 9y ago
  You’ve probably refactored the ORM Data layer 3 times in that time period as ORM producers have a hard time figuring out the interface which they wish to provide you
  - spdionis 9y ago
    Agreed. I'd say my point still stands because duplicated database calls affect performance and scalability and this can grind the project to a halt at a certain point. Also this refactor is much more dangerous/probably buggy. On the other hand refactoring your data logic is just business as usual, and you will probably do it in both cases anyway.
- guru4consulting 9y ago
  caching is a very tricky area with tons of pitfalls. In my opinion, the ORM should not be caching. Let the clients (or any other layer) cache/clear based on their needs.
pweissbrod 9y ago
My experiences have led me to the standpoint that most ORMs handle three major things: 1. provide idiomatic domain-object oriented query interface which it in turn translates to SQL 2. provide CRUD sql generation 3. provide some sort of session-based object lifecycle change tracking and management #1 - The generated SQL is important on many levels. As things like HQL/linq/<your QL here> deviates further from the generated sql transparency is lost. SQL is normally brittle compared to your domain language which has better testability and type safety but still you have a handful of queries where it feels more sensible to write the SQL yourself. #2 - Code which reflects on a type and generates basic insert/select/update/delete is usually pretty naive and easily done. With the exception of complicated legacy databases and iBatis-style tools ORMs which only support #2 arent really worth bothering. #3 - I've found this to be the real benefit of an ORM. Being able to scope object lifecycles into clear units of work, buffer pending changes until a discrete point and get scoped caches for "free" have been hugely beneficial. Each ORM unfortunately/inevitably come with great learning curve. It seems to unfortunately/inevitably bring lots of new concepts and conventions to the table required for the user simply must understand. Sometimes it requires you to re-arrange the way you may write your code to be more session-oriented. I dont like the idea of AI managing how/when to apply changes to storage media. AI can be smart and efficient yes but you lose that important transparency/predictability. On the contrary I prefer very dumb/mechanical/predictable ORM, something not elegant but the behavior of what happens when is well-understood and easily scaled out to a large team. In my experience hibernate, EF, sqlalchemy have that sort of dumb/predictable behavior (however the SQL they sometimes generate can be performant but unreadable).

avmich 9y ago

> A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. What's the difference? To me, an ORM is largely a query builder.

int_19h 9y ago
Is it a good one, though?
- JamesBarney 9y ago
  Probably better than the one I could build by hand, with far better documentation, and greater ease in googling problems :)
  - failedxyz 9y ago
    while ORMs can do quite a bit of optimization, they're still general query builders and can't construct the optimal queries for your use case. if you don't know SQL, or you don't know what's going on behind the scenes, your ORM could be performing much larger queries than it really needs to, costing performance and time
    - stouset 9y ago
      But that's a completely orthogonal problem — the point made (way) above is that you need to understand SQL whether or not you use an ORM.
    - gjjrfcbugxbhf 9y ago
      Most ORMs have way too write raw SQL. Furthermore someone using an ORM well will understand how their queries are mapping to SQL. Your scenario happens with people that either don't know or don't care. They will write crappy queries with any tool.
      - collyw 9y ago
        Sounds about right. I built a moderately complex application in Django at a previous workplace, using the ORM for most things, until the queries were too complex for the ORM. Another guy connected to the same database and built some graphs using PHP and SQL. Guess who had to help him write the SQL when the queries got too complex for him? The ORM user.
- jbergens 9y ago
  Bascially all in-house built query builders I've seen has been much worse.
emilsedgh 9y ago
ORM: You ask for something and you don't care _how_ its fetched. Query Builder: You build a query, just not in SQL. So you can get around SQL's limitations (like composition)
- andybak 9y ago
  > ORM: You ask for something and you don't care _how_ its fetched. Try this: > ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. or possibly: > ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case. There's plenty of places for bottlenecks to hide and premature optimization is the root of at least some evils.
  - jcadam 9y ago
    > ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. Ha. More like: ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 months trying to get the damn ORM to generate performant SQL (you even call Oracle support, which promptly dispatches an "engineer" who tries to sell you on another $500k of crap you don't need that won't really address the problem). You eventually just start writing queries by hand wherever you find a bottleneck caused by the naive ORM, which you could have done at the outset, but no one who actually knew SQL well enough was on the team back then.
    - paulryanrogers 9y ago
      IME, it should never take more than a few hours to run down why the ORM made such a query. At a minimum most RDBMS's have query logging and can explain queries. Ironically the last time I had a big ORM performance problem was Hibernate eager loading all joins. Diagnosing and fixing it didn't take more than an hour. (Though we did have a very experienced DBA at the time.) YMMV
Animats 9y ago
ORMs are more useful as insert builders. Putting data from an object into the database is something of a boilerplate process. Queries vary with what you want to ask. Most of the time, you don't need all the fields, so filling up some object just because it has slots for everything is a waste of effort. Especially if it means references to multiple tables.
- egeozcan 9y ago
  Good ORMs have Partial<T> and lazy loading of complex properties through proxies, which can be overridden with something like .With(x => x.ComplexProperty). But of course, as the queries get more and more complex, the flexibility of the ORM syntax approaches the flexibility SQL. In the end, there are many situations one would rather just use SQL.
- majewsky 9y ago
  I think the best ORMs are those that just leave out the "Relational" part entirely. So... "OM"? For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous design. And when you have to do a query that returns results from multiple tables? Guess what, you just use the normal SQL module from the standard library.
  - scarface74 9y ago
    Why is it an either or? EF can do that but then you loose the benefits of a type safe language.
    - oceanswave 9y ago
      If type safety is so great, why isn’t sql statically type checked
      - 9y ago
petre 9y ago
A query builder aids you at constructing queries, while an ORM builds queries for you, runs them and maps the output to objects. It's more sophisticated than a query builder.
- zimpenfish 9y ago
  > A query builder aids you at constructing queries ... that you understand and can be sure are sensible. > while an ORM builds queries for you ... that you have to hope are sensible. That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.
  - scarface74 9y ago
    How so? I can log the the sql being generated and look at the logs. Something I should be doing either way.
    - zimpenfish 9y ago
      If you have to log the generated SQL to understand what's happening, you're already behind the curve. And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?
      - mgkimsal 9y ago
        > And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place? The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.
        zaphar 9y ago
        "saved time" at the least optional time to save time. ORM's are a maintenance burden. They obfuscate DB performance behind usually an enormous API surface. Most software work is maintenance work. Optimizing for initial deployment is shortsighted.
akvadrako 9y ago
SqlAlchemy Core vs SqlAlchemy ORM
- cosarara97 9y ago
  And doctrine DBAL vs doctrine ORM

voodootrucker 9y ago

It depends on how willing you are to allow your object graph to match your relational schema. The key is to let SQL be SQL. I wrote one that allows you to load data using standard SQL resource files, but it handles the persistence automatically: https://github.com/bgard6977/sqorm https://github.com/bgard6977/sqorm Flyway & jOOq take a similar approach: https://www.jooq.org/ https://www.jooq.org/ Taking the SQL-first approach also allows you to serialize without circular reference problems, since you don't just load the data, you also define a path to decompose the graph into a DAG.

keredson 9y ago
also https://github.com/keredson/DKO https://github.com/keredson/DKO

bpanon 9y ago

No, no, no, I know more than you. Here is why. Bitcoin cryptocurrency AI biomedical supply chain networking quantum systems-thinker.

oceanswave 9y ago
Here, take my money!
dang 9y ago
Please don't post unsubstantive comments here. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newswelcome.html https://news.ycombinator.com/newswelcome.html

scarface74 9y ago

So the easiest way to avoid using an ORM is to create your own ORM? What AI do you need? You map your tables to objects and relationships between objects via FK relationships.

pjmlp 9y ago
I can get very creative with SELECTs, making use of Prolog style queries, which are fully done server side on the database. Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.
- scarface74 9y ago
  If your ORM is downloading all of your data and querying client side, then your ORM "is doing it wrong". Entity Framework and any other Linq to data provider translates the Linq expression to the native language of the source data and does it server side.
  - pjmlp 9y ago
    I know EF, which is why my comment also mentions "code that is even more convoluted that the SQL one and thus with less performance". LINQ only allows for a fraction of what is possible with SQL, and good luck having the best queries generated out of it, if the RDMS doesn't happen to be SQL Server.
    - scarface74 9y ago
      Actually my DMS is sometimes Mongo and the Mongo driver does pretty well at translating Linq to Mongo Query using the aggregation framework. It all depends on the talent of the authors of the drivers.
- zzzeek 9y ago
  > Most ORMs will download all the data and evaluate them on the client side I've seen that pattern in lots of homegrown applications, but I've never seen such a thing in a mainstream ORM. Care to provide examples ?

spdionis 9y ago

5 years down the road, good luck refactoring your application to not query the same object from the database 3-4 times, and load it only once instead.

oceanswave 9y ago
You’ve probably refactored the ORM Data layer 3 times in that time period as ORM producers have a hard time figuring out the interface which they wish to provide you
- spdionis 9y ago
  Agreed. I'd say my point still stands because duplicated database calls affect performance and scalability and this can grind the project to a halt at a certain point. Also this refactor is much more dangerous/probably buggy. On the other hand refactoring your data logic is just business as usual, and you will probably do it in both cases anyway.
guru4consulting 9y ago
caching is a very tricky area with tons of pitfalls. In my opinion, the ORM should not be caching. Let the clients (or any other layer) cache/clear based on their needs.

pweissbrod 9y ago

My experiences have led me to the standpoint that most ORMs handle three major things: 1. provide idiomatic domain-object oriented query interface which it in turn translates to SQL 2. provide CRUD sql generation 3. provide some sort of session-based object lifecycle change tracking and management #1 - The generated SQL is important on many levels. As things like HQL/linq/<your QL here> deviates further from the generated sql transparency is lost. SQL is normally brittle compared to your domain language which has better testability and type safety but still you have a handful of queries where it feels more sensible to write the SQL yourself. #2 - Code which reflects on a type and generates basic insert/select/update/delete is usually pretty naive and easily done. With the exception of complicated legacy databases and iBatis-style tools ORMs which only support #2 arent really worth bothering. #3 - I've found this to be the real benefit of an ORM. Being able to scope object lifecycles into clear units of work, buffer pending changes until a discrete point and get scoped caches for "free" have been hugely beneficial. Each ORM unfortunately/inevitably come with great learning curve. It seems to unfortunately/inevitably bring lots of new concepts and conventions to the table required for the user simply must understand. Sometimes it requires you to re-arrange the way you may write your code to be more session-oriented. I dont like the idea of AI managing how/when to apply changes to storage media. AI can be smart and efficient yes but you lose that important transparency/predictability. On the contrary I prefer very dumb/mechanical/predictable ORM, something not elegant but the behavior of what happens when is well-understood and easily scaled out to a large team. In my experience hibernate, EF, sqlalchemy have that sort of dumb/predictable behavior (however the SQL they sometimes generate can be performant but unreadable).