4 ms·

ORM has never worked for me in any language. Sooner or later we always hit the n+1 query problem which could only be resolved by a query builder or just plain

ORM has never worked for me in any language. Sooner or later we always hit the n+1 query problem which could only be resolved by a query builder or just plain old sql. It always was a mess and these days I can't be bothered to try it even anymore because it has cost me a lot of hours and money.

srik 2y ago
Yes, plain sql is indeed the bees knees but there are good ORMs like django/ecto etc. that let you consider N+1 query issues ahead of time. Most ORMs these days have escape hatches anyway. Patience might be needed to keep it all tidy but they don't necessarily have to be a mess.
- s6af7ygt 2y ago
  I don't get why to use an ORM in the first place. Just define a bunch of structs, run a query, map results to structs. It's a few lines of simple code. You're in control of everything (the SQL, the running, the mapping). It's transparent. With any ORM, you give away control and make everything more complex, only to make it slightly easier to run a query and map some results.
  - JodieBenitez 2y ago
    > Just define a bunch of structs, run a query, map results to structs Congrats, you now have your own little ORM.
    - RandomThoughts3 2y ago
      No, absolutely not. Op is never implying they intend to maintain one to one correspondence between the DB and objects and do that through manipulating objects only. Mapping hand written queries results to structs and updating the DB yourself on the basis of what is in structs is not at all an ORM.
      - deleted 2y ago
        [deleted]
      - orf 2y ago
        > Mapping hand written queries results to structs and updating the DB yourself on the basis of what is in structs is not at all an ORM. You just described a bad, home-grown Object Relational Mapper.
        RandomThoughts3 2y ago
        No, absolutely not. You don’t even try to manipulate the DB using object-oriented concept in this case. That’s just a good old I/O layer used on a need to basis. This is not in any way an ORM by any sane definition of what an ORM is.
    - ndriscoll 2y ago
      Not in most modern web application servers. ORMs seem to solve the problem of synchronizing some persistent application state (like a desktop GUI app) to your database state, but web application servers are usually relatively stateless. It's better to think of the application's job as taking a request, parsing it, compiling it to SQL, handing that to a database, and serializing the results. Through that lens, the parts where you load and save object state are redundant. You're going to throw those objects away after the request anyway. Just take your request and build an UPDATE, etc. Use record types merely as a way to define your schema.
  - tekkk 2y ago
    No type safety & writing manual SQL is slower. I get your point but the bottleneck is often developement speed, not query efficiency. I know and hate how stupid the ORM is underneath but I have to admit it's a blessing that I dont have to think about SQL at all (until I do).
JodieBenitez 2y ago
It's not a black or white thing. Good ORMs let you use plain old SQL when needed.
- OtomotO 2y ago
  As said, they have cost me too much time and money already, moreso as other devs on the team(s) lent heavily into certain features and I had to rewrite a lot of code.
  - 0x457 2y ago
    Why are you rewritting? 80%[1] of queries most users do can be efficiently handled by ORM. I might need to use hand-written query a few times either because this particular query is faster to write by hand or because ORM builds a a bad query. That is it, no need to throw away entire ORM because of that. When I was in RoR world, pretty much every N+1 query I saw was due to lack of RTFM. [1]: I made this up
    - OtomotO 2y ago
      I need to rewrite the parts that are broken and without going into too much details: it's a lot of code where we had no problems with hundreds of rows but now with thousands (so nothing, lol, I've worked on projects with hundreds of millions of rows) we get severe performance problems. Because it's half a dozen joins and hence no N+1 query but actually N*6+1 queries... And yes, RTFM is nice, problem is: it's my fucking partners that should've done this before we shipped it to the customer which they abandoned and I did not.
      - orf 2y ago
        You’re describing issues with a specific ORM, not with all ORMs. And not even that, really. I’m sure you can understand that it is possible to call functions that execute SQL statements inside a potentially nested loop? What’s that got to do with an ORM.
jamil7 2y ago
This is pretty much where I landed as well, I also love being able to quickly copy and run SQL queries to test and modify them somewhere else.
p0w3n3d 2y ago
What do you recommend? Do the mapping manually? Tbh I tried that while learning rust and it was awful. On the other hand an async orm sounds like (n+1)(n+2)+...+(n+m) Problem

srik 2y ago

Yes, plain sql is indeed the bees knees but there are good ORMs like django/ecto etc. that let you consider N+1 query issues ahead of time. Most ORMs these days have escape hatches anyway. Patience might be needed to keep it all tidy but they don't necessarily have to be a mess.

s6af7ygt 2y ago
I don't get why to use an ORM in the first place. Just define a bunch of structs, run a query, map results to structs. It's a few lines of simple code. You're in control of everything (the SQL, the running, the mapping). It's transparent. With any ORM, you give away control and make everything more complex, only to make it slightly easier to run a query and map some results.
- JodieBenitez 2y ago
  > Just define a bunch of structs, run a query, map results to structs Congrats, you now have your own little ORM.
  - RandomThoughts3 2y ago
    No, absolutely not. Op is never implying they intend to maintain one to one correspondence between the DB and objects and do that through manipulating objects only. Mapping hand written queries results to structs and updating the DB yourself on the basis of what is in structs is not at all an ORM.
    - deleted 2y ago
      [deleted]
    - orf 2y ago
      > Mapping hand written queries results to structs and updating the DB yourself on the basis of what is in structs is not at all an ORM. You just described a bad, home-grown Object Relational Mapper.
      - RandomThoughts3 2y ago
        No, absolutely not. You don’t even try to manipulate the DB using object-oriented concept in this case. That’s just a good old I/O layer used on a need to basis. This is not in any way an ORM by any sane definition of what an ORM is.
  - ndriscoll 2y ago
    Not in most modern web application servers. ORMs seem to solve the problem of synchronizing some persistent application state (like a desktop GUI app) to your database state, but web application servers are usually relatively stateless. It's better to think of the application's job as taking a request, parsing it, compiling it to SQL, handing that to a database, and serializing the results. Through that lens, the parts where you load and save object state are redundant. You're going to throw those objects away after the request anyway. Just take your request and build an UPDATE, etc. Use record types merely as a way to define your schema.
- tekkk 2y ago
  No type safety & writing manual SQL is slower. I get your point but the bottleneck is often developement speed, not query efficiency. I know and hate how stupid the ORM is underneath but I have to admit it's a blessing that I dont have to think about SQL at all (until I do).

JodieBenitez 2y ago

It's not a black or white thing. Good ORMs let you use plain old SQL when needed.

OtomotO 2y ago
As said, they have cost me too much time and money already, moreso as other devs on the team(s) lent heavily into certain features and I had to rewrite a lot of code.
- 0x457 2y ago
  Why are you rewritting? 80%[1] of queries most users do can be efficiently handled by ORM. I might need to use hand-written query a few times either because this particular query is faster to write by hand or because ORM builds a a bad query. That is it, no need to throw away entire ORM because of that. When I was in RoR world, pretty much every N+1 query I saw was due to lack of RTFM. [1]: I made this up
  - OtomotO 2y ago
    I need to rewrite the parts that are broken and without going into too much details: it's a lot of code where we had no problems with hundreds of rows but now with thousands (so nothing, lol, I've worked on projects with hundreds of millions of rows) we get severe performance problems. Because it's half a dozen joins and hence no N+1 query but actually N*6+1 queries... And yes, RTFM is nice, problem is: it's my fucking partners that should've done this before we shipped it to the customer which they abandoned and I did not.
    - orf 2y ago
      You’re describing issues with a specific ORM, not with all ORMs. And not even that, really. I’m sure you can understand that it is possible to call functions that execute SQL statements inside a potentially nested loop? What’s that got to do with an ORM.

jamil7 2y ago

This is pretty much where I landed as well, I also love being able to quickly copy and run SQL queries to test and modify them somewhere else.

p0w3n3d 2y ago

What do you recommend? Do the mapping manually? Tbh I tried that while learning rust and it was awful. On the other hand an async orm sounds like (n+1)(n+2)+...+(n+m) Problem