5 ms·

Pardon me for the tangent (just a general comment not directed to OP). What I have learned over the years is that the only way to properly use ORM is as a fanc

Pardon me for the tangent (just a general comment not directed to OP). What I have learned over the years is that the only way to properly use ORM is as a fancy query tool. Build the query, fetch/update data, MOVE THE DATA to separate business objects. Don't leave ORM entities shared across the sea of objects! Phew, thanks, I got that off my chest.

rafaelmn 2y ago
That doesn't really help you with EF because there's plenty of stuff shared at context level. So depending on the order of queries in the context the same query can return different data. I hate EF and everything it stands for. :)
- nercury 2y ago
  Yeah, in a web app, one context per request. In desktop app... I have never used EF there.
noduerme 2y ago
adding an off the shelf ORM layer creates so much more opacity and tech debt than writing queries I don't understand why anyone would willingly put one into their stack. Sure, they're neat although I don't even know if they save time. There's something very satisfying about well-crafted queries. And is it ever really well crafted if you can't tweak them to improve their their execution plan? I've never had a client or boss who asked to use an ORM framework. I suspect it's something people think looks cool - treating SQL as OOP - until they run into a problem it can't solve. [edit] for instance, I have a case where I use a few dozen custom queries on timers to trawl through massive live data and reduce it into a separate analytics DB. Using everything from window functions to cron timers to janky PHP code that just slams results from separate DBs together to provide relatively accurate real-time results. At the end from that drastically reduced set in the analytics DB... sure, I'm happy to let the client summarize whatever they want with Metabase. But those things just couldn't be done with an ORM, and why would I want to?
- bad_username 2y ago
  For me the biggest reason is automated database initialization and migration. After defining or updating the ORM model, I don't have to worry about manually CREATing and ALTERing tables as the model evolves. This is compatible with the OC suggestion of using ORMs as a "fancy query builder" and nothing more, which I strongly support.
  - noduerme 2y ago
    I'm not sure if you're talking about creating and altering model tables or if you mean ORMs provide safety in case underlying tables are modified. I'd argue that well-built queries should be resistant to alteration of the underlying tables, and that views and functions and stored procedures already exist to both red flag breaking changes and also to combine and reduce whatever you need without relying on third party code in another language layer to do the lifting.
  - blksv 2y ago
    Doesn't it also mean that any non-trivial migration (e.g. which requires data transformation or which needs to be structured to minimize locking) has to be defined elsewhere, thus leaving you with two different sources for migrations, plus some (ad-hoc) means to coordinate the two? (I would say that it is conceptually perverse for a client of a system to have authority over it. Specifically, for a database client to define its schema.)
  - devjab 2y ago
    You always have to worry about your model changes if you run at any sort of scale. Some ORMs will get it right most of the time, but the few times they don’t will really bite you in the ass down the line. Especially with the more “magical” ORMs like EF where you might not necessarily know how it build your tables unless you specifically designed them yourself. This is where migrations also become sort of annoying. Because if you use them. Then it is harder to fix the mistakes since you can’t just change your DB without using the ORM or you’ll typically break your migration stream or at least run into a lot of troubles with it. And what is the plus side of having a code-first DB really? You can fairly easily store those “alter table” changes as you go along and have full availability of history in a very readable way that anyone, including people not using C#, Java, Python. Which is the other issue with ORMs. If you have multiple consumers of your data. Then an ORM most likely won’t consider that as it alters your “models”. For a lot of projects this is a non-issue, especially at first. Then 10 years down the line, it becomes a full blown nightmare and you eventually stop using the ORM. After spending a lot of resources cleaning up your technical debt.
marcus_holmes 2y ago
Agree completely, as does most of the Go community :) Newbie gophers are regularly told to learn some SQL and stop trying to rebuild ActiveRecord in Go ;) But in .Net, EF is still the most common way of accessing data (I have heard, because I stopped using it over a decade ago).
- deleted 2y ago
  [deleted]
- lomase 2y ago
  EF is the common way of saving data.
arrowsmith 2y ago
I wouldn't have believed you until I moved from ActiveRecord (Rails's ORM) to Ecto (Elixir/Phoenix's data mapping library which is decidedly not an ORM.) It's a million times better and I'm never going back.
- cultofmetatron 2y ago
  same, I wish more libraries would go the ecto design route. my ecto queries map pretty close to 1:1 with the sql counterpart. no guessing what the output is going to look like. I spend my time debugging the query and not trying to get the orm to output he query I want.
  - arrowsmith 2y ago
    Yeah, I hear some people say that they find Ecto.Query confusing, and I think it's because they never learned SQL properly. That's understandable because it's possible to use something like ActiveRecord for years without ever learning to write even a simple SQL query. But if you have a good grasp of SQL then Ecto.Query is trivial to learn - it's basically just SQL in Elixir syntax.
    - cultofmetatron 2y ago
      > it's basically just SQL in Elixir syntax. its sql in elixir syntax with a bunch of QOL improvements. for one thing, I can seperate my subqueries into separate variables ``` sub_q = from(l in Like) |> where([l], l.user_id == ^user_id) |> select([l], %{ user_id: l.user_id, likes_count: count(l.id) }) |> group_by([l], l.user_id) main_query = from(u in User) |> join(:left, [u], likes_count in ^subquery(sub_q), on: likes_count.user_id == u.id, as: :likes_count) |> select([u, likes_count: l], %{ name: u.name, likes: l.likes_count, }) |> where([u], u.id == ^user_id) user = main_query |> Repo.one() ``` Being able to think directly in sql lets you perform optimal queries once you understand sql. and imho, this much cleaner than tha equivalen sql to write. it also takes care of input sanatization and bindings.
  - freedomben 2y ago
specialist 2y ago
> use ORM is as a fancy query tool As an alternative to query-by-example (QBE)? https://en.wikipedia.org/wiki/Query_by_Example https://en.wikipedia.org/wiki/Query_by_Example

rafaelmn 2y ago

That doesn't really help you with EF because there's plenty of stuff shared at context level. So depending on the order of queries in the context the same query can return different data. I hate EF and everything it stands for. :)

nercury 2y ago
Yeah, in a web app, one context per request. In desktop app... I have never used EF there.

noduerme 2y ago

adding an off the shelf ORM layer creates so much more opacity and tech debt than writing queries I don't understand why anyone would willingly put one into their stack. Sure, they're neat although I don't even know if they save time. There's something very satisfying about well-crafted queries. And is it ever really well crafted if you can't tweak them to improve their their execution plan? I've never had a client or boss who asked to use an ORM framework. I suspect it's something people think looks cool - treating SQL as OOP - until they run into a problem it can't solve. [edit] for instance, I have a case where I use a few dozen custom queries on timers to trawl through massive live data and reduce it into a separate analytics DB. Using everything from window functions to cron timers to janky PHP code that just slams results from separate DBs together to provide relatively accurate real-time results. At the end from that drastically reduced set in the analytics DB... sure, I'm happy to let the client summarize whatever they want with Metabase. But those things just couldn't be done with an ORM, and why would I want to?

bad_username 2y ago
For me the biggest reason is automated database initialization and migration. After defining or updating the ORM model, I don't have to worry about manually CREATing and ALTERing tables as the model evolves. This is compatible with the OC suggestion of using ORMs as a "fancy query builder" and nothing more, which I strongly support.
- noduerme 2y ago
  I'm not sure if you're talking about creating and altering model tables or if you mean ORMs provide safety in case underlying tables are modified. I'd argue that well-built queries should be resistant to alteration of the underlying tables, and that views and functions and stored procedures already exist to both red flag breaking changes and also to combine and reduce whatever you need without relying on third party code in another language layer to do the lifting.
- blksv 2y ago
  Doesn't it also mean that any non-trivial migration (e.g. which requires data transformation or which needs to be structured to minimize locking) has to be defined elsewhere, thus leaving you with two different sources for migrations, plus some (ad-hoc) means to coordinate the two? (I would say that it is conceptually perverse for a client of a system to have authority over it. Specifically, for a database client to define its schema.)
- devjab 2y ago
  You always have to worry about your model changes if you run at any sort of scale. Some ORMs will get it right most of the time, but the few times they don’t will really bite you in the ass down the line. Especially with the more “magical” ORMs like EF where you might not necessarily know how it build your tables unless you specifically designed them yourself. This is where migrations also become sort of annoying. Because if you use them. Then it is harder to fix the mistakes since you can’t just change your DB without using the ORM or you’ll typically break your migration stream or at least run into a lot of troubles with it. And what is the plus side of having a code-first DB really? You can fairly easily store those “alter table” changes as you go along and have full availability of history in a very readable way that anyone, including people not using C#, Java, Python. Which is the other issue with ORMs. If you have multiple consumers of your data. Then an ORM most likely won’t consider that as it alters your “models”. For a lot of projects this is a non-issue, especially at first. Then 10 years down the line, it becomes a full blown nightmare and you eventually stop using the ORM. After spending a lot of resources cleaning up your technical debt.

marcus_holmes 2y ago

Agree completely, as does most of the Go community :) Newbie gophers are regularly told to learn some SQL and stop trying to rebuild ActiveRecord in Go ;) But in .Net, EF is still the most common way of accessing data (I have heard, because I stopped using it over a decade ago).

deleted 2y ago
[deleted]
lomase 2y ago
EF is the common way of saving data.

arrowsmith 2y ago

I wouldn't have believed you until I moved from ActiveRecord (Rails's ORM) to Ecto (Elixir/Phoenix's data mapping library which is decidedly not an ORM.) It's a million times better and I'm never going back.

cultofmetatron 2y ago
same, I wish more libraries would go the ecto design route. my ecto queries map pretty close to 1:1 with the sql counterpart. no guessing what the output is going to look like. I spend my time debugging the query and not trying to get the orm to output he query I want.
- arrowsmith 2y ago
  Yeah, I hear some people say that they find Ecto.Query confusing, and I think it's because they never learned SQL properly. That's understandable because it's possible to use something like ActiveRecord for years without ever learning to write even a simple SQL query. But if you have a good grasp of SQL then Ecto.Query is trivial to learn - it's basically just SQL in Elixir syntax.
  - cultofmetatron 2y ago
    > it's basically just SQL in Elixir syntax. its sql in elixir syntax with a bunch of QOL improvements. for one thing, I can seperate my subqueries into separate variables ``` sub_q = from(l in Like) |> where([l], l.user_id == ^user_id) |> select([l], %{ user_id: l.user_id, likes_count: count(l.id) }) |> group_by([l], l.user_id) main_query = from(u in User) |> join(:left, [u], likes_count in ^subquery(sub_q), on: likes_count.user_id == u.id, as: :likes_count) |> select([u, likes_count: l], %{ name: u.name, likes: l.likes_count, }) |> where([u], u.id == ^user_id) user = main_query |> Repo.one() ``` Being able to think directly in sql lets you perform optimal queries once you understand sql. and imho, this much cleaner than tha equivalen sql to write. it also takes care of input sanatization and bindings.
- freedomben 2y ago

specialist 2y ago

> use ORM is as a fancy query tool As an alternative to query-by-example (QBE)? https://en.wikipedia.org/wiki/Query_by_Example https://en.wikipedia.org/wiki/Query_by_Example