[nercury]

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.

[arrowsmith]

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.

[atonse]

Ecto is hands down my favorite part of the elixir ecosystem.

It’s so elegant and the Lego blocks (query, schema, change set, repo) can be mixed and matched in different ways.

I’ve even used schemas and change sets to validate API requests and trivially provide very nice, clean, and specific errors, while getting perfectly typed structs when things are validated.

[cultofmetatron]

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.

[noduerme]

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?

[nercury]

Yes, I would not put it just anywhere. But I have few rules about ORMs:

- Proper DB design first. You should be able to remove the ORM and DB should still function as intended. This means application-side cascade operations or application-side inheritance is banned.

- No entities with magical collections pointing to each other. In other words, no n to n relations handled by ORM layer. Create in-between table, for gods sake. Otherwise it becomes incredibly confusing and barely maintainable.

- Prefer fetching data in a way that does not populate collections. In other words, fetch the most fine-grained entity and join related data. Best if you craft special record entities to fetch data into (easy with EF or Doctrine).

- Most ORMs allow you to inspect what kind of queries you create. Use it as query building tool. Inspect queries often, don't do insane join chains and other silly stuff.

I would use ORM in one kind of app: where I would work with data that shares records that might need to be either inserted or updated, and there is several nesting levels of this kind of fun. You know, you need to either insert or update entity, if it exists, you should update, and then assign related entities to it, if it does not, then you should insert, and assign related entities to the newly created id. The ORM can easily deal with that, and on top of that it can do efficient batched queries, which would be really annoying and error-prone to hand-craft.

If the app does not require this kind of database with these kind of relations, I would not use ORM.

[LaGrange]

> 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.

Simple: because if I don't, I'm going to spend the rest of my career explaining why I didn't to people extremely skeptical of that decision. Meanwhile even people like me tend to just shrug and quietly go "oh, an ORM? Well, that's the price of doing the job."

Also, ORMs are an endless source of well-paid jobs for people who actually learned relational algebra at some point in their lives, and that's not a compliment to ORMs.

[lozenge]

ORM is not for writing analytics queries. It's for your CRUD operations. Something like Django Admin would be impossible without an ORM. You create tables for your business logic and customer support or whoever can just browse and populate them.

[DanielHB]

Most queries are pretty trivial, ORMs are great for 90% of queries. As long as you don't try to bend the ORM query system to do very complicated queries it is fine. Most (all?) ORMs allow raw queries as well so you can mix both.

On top of that most ORMs have migrations, connection management, transaction management, schema management and type-generation built-in.

Some ORMs have inherently bad design choices though, like lazy loading or implicit transaction sharing between different parts of the code. Most modern ORMs don't really have that stuff anymore.

[kaba0]

How do you map rows to objects? How do you insert into/update rows in your databases? These are the basic problems ORMs solve splendidly. They are for OLTP workloads, and have deliberate escape hatches to SQL (or some abstraction over it, like JPQL in java-land).

I just fail to see what else would you do, besides implementing a bug-ridden, half-ORM yourself.

[bad_username]

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.

[marcus_holmes]

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).

[lomase]

EF is the common way of saving data.

[rafaelmn]

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]

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

[specialist]

> use ORM is as a fancy query tool

As an alternative to query-by-example (QBE)?

https://en.wikipedia.org/wiki/Query_by_Example