3 ms·

Choosing this depends a lot on your language, its ecosystem, and your application's own ecosystem (if your app shares a database, ORMs aren't necessarily out of

Choosing this depends a lot on your language, its ecosystem, and your application's own ecosystem (if your app shares a database, ORMs aren't necessarily out of the question, but have a lot more hurdles to overcome). In Python using the Django ORM lights up all sorts of great features in the Django Framework because its automated admin and automated CRUD views feed off the ORM's information and it's often silly not to use Django's ORM for how much free productivity it can buy you. But then if you aren't using Django with Python you've got far more choices of ORMs and maybe fewer reasons to use one or another. In C#, I'm a massive fan of LINQ. I think recent versions of EF (Entity Framework) have been rock solid and do everything that I need to do. (Early versions EF that supported LINQ got some (deserved) bad reputations for its query transformer sometimes doing surprising things. As someone who had to debug many of them, I knew that pain directly. Ever since the "reboot" (EF Core 1.0), EF has had a very good, very "no magic" query transformer that doesn't do anything you don't ask it to do. I do mean it greatly when I say that recent versions have been rock solid.) With LINQ (in either of its forms) I almost never feel like I need to write a query directly in SQL (and most of what is left are fun rare things like MERGE statements) and LINQ offers somethings that writing in SQL can't. Type safety is obviously the big one for why you should want to choose an ORM in the first place. But there are also small things where I think LINQ ergonomics are actually better than their raw SQL equivalents (things like WHERE versus HAVING when writing GROUP BY logic). I've also worked on massive shared database "microservice architectures" where everything needed to be "Native SQL" and not just "Native SQL" but hand tuned micro-apps written in T-SQL (or equivalents) to insure data integrity and overall performance of the entire ecosystem. I don't really recommend architecting applications that way, but I do know they exist for all sorts of reasons and have had cases where any ORM involvement made things more difficult. (That said, in C# modern EF support for type safe calls of stored procedures and views is pretty great and I'd probably still use that today rather than building hand-managed ADO.NET wrappers like I used to have to in those days even if most of the actual ORM parts and query transformation tools were left unused.) Ask two developers and you'll sometimes get five different answers. There's a lot of personal preference here, mixed with all sorts of experiences from all sorts of ORMs. There are so many ORMs to choose from out there and most answers about one don't apply to others, and with EF as an obvious example even the same ORM can have massively different reputations in different eras of its lifespan.

hot_gril 4y ago
I find that the ORM is terrible no matter what language, ecosystem, etc. Or which ORM. But the worst is probably CoreData.
- WorldMaker 4y ago
  Just because you've had bad experiences with ORMs doesn't mean that there are no good ORMs. That's why I bring up how much language/ecosystem does matter. Bad experiences with ORMs come not from ORMs themselves as a concept (it is an extremely broad concept) but from specific ORMs in specific ecosystems. I thought it useful to give examples of specific ORMs in specific ecosystems that bring joy, because throughout these threads you see plenty of hate for ORMs, just like you thought to bring up in response to my post. The auto-CRUD of Django is a joy to have for some types of projects. The type safety and power of C#'s LINQ is easy to underestimate and while there have been times when that power seemed "too magic" and bad versions of EF gave off plenty of bad experiences, there are times of great joy in modern EF. If you haven't experienced these specific ecosystems for yourself, it might be worth doing in a side project somewhere just to give them a shot. I can't guarantee you would find joy in that, but I can suggest that there is possible joy that I know exists to find in such places if you try them with an open mind.
  - hot_gril 4y ago
    It's the concept of an ORM itself, which is a mapping from OOP to relational algebra, both concepts that don't change much between implementations. You have a relational DB with a nice powerful interface already. The ORM tries to abstract that way with a not-so-compatible OOP interface that enforces mediocre schema design. On the practical side, it's common for a system to start needing more impl-specific database optimization eventually, at which point the ORM gets in the way even more. The ORM is ok for small projects, but so is SQL. CoreData is only especially bad because it's confusing to use on top of that. I've used the Django auto-CRUD too.
    - WorldMaker 4y ago
      Not all databases use relational algebra. Among other things document databases are increasingly common for a number of types of applications and ORMs can provide good experiences for those types of databases as well. Beyond document databases, I think the "theoretical" mismatch between OOP and Relational is often over-exaggerated/over-stated and most of the mismatch blamed entirely on the wrong direction. For historical reasons, some of which were a love of relational algebra mathematical "purity", relational databases have tended to have problems storing and querying hierarchical and graph data. Hierarchical and graph data are relatively common and somewhat "natural" in OOP: inheritance and polymorphism often rely on hierarchies, and references between objects can easily be complex graphs, including cycles and other complex graph mechanics. Theoretically "pure" relational algebra cannot accommodate that kind of data, it's what keeps the math simple and pretty. There is a clear OOP/Relational mismatch, but it's a lot more to do with that theory problem, and the problem is entirely on the relational side being unable to express practical, common, complex data. If there's something holding things back and enforcing "mediocre schema design", there's a lot of fingers to point at relational algebra itself (and SQL too, for its own ways of trying to be "pure" to relational algebra). In practice, of course, there is no such thing as a "pure" relational database that people actually develop software for and most SQL databases have all sorts of "impure" extensions and tools and some dialects of SQL can surely feel quite powerful but still often have real, interesting holes in the types of data they can describe and query. Though some of those extensions offer escape hatches today, with the increase in JSON support especially in the majority of SQL databases. I know in practice I've anecdotally been seeing an increasing number of cases where ORMs remain relevant and helpful but reliance on SQL-based databases and SQL specifics is diminishing all the more each year. Not that I don't think SQL-based databases are bad at what they do, what they do well they do well, but that there increasingly there's a lot more escape hatches being used to non-SQL data stores (even if just increasing numbers of JSON blobs in an SQL database). There's always more hierarchical and graph data to store and query, and there are better options every year for that kind of data. From my perspective, the best ORMs often don't "abstract" away the relational underpinnings of a relational database: they do their best to help you simplify (if not dumb down) your application's intended data structures to better fit a relational database. I say that as someone with good SQL performance understanding, a love of the theory of pure relational algebra, and a knowledge that when I need to that I can absolutely beat an ORM at hand tuned SQL and "brilliant" relational database schemas. I'm sure our experiences have been vastly different to this point in our careers. I agree with you that there is an ORM/relational mismatch, but I don't expect you to agree with me that my practical experiences continue to tell me that the fault isn't with ORMs in that mismatch.

hot_gril 4y ago

I find that the ORM is terrible no matter what language, ecosystem, etc. Or which ORM. But the worst is probably CoreData.

WorldMaker 4y ago
Just because you've had bad experiences with ORMs doesn't mean that there are no good ORMs. That's why I bring up how much language/ecosystem does matter. Bad experiences with ORMs come not from ORMs themselves as a concept (it is an extremely broad concept) but from specific ORMs in specific ecosystems. I thought it useful to give examples of specific ORMs in specific ecosystems that bring joy, because throughout these threads you see plenty of hate for ORMs, just like you thought to bring up in response to my post. The auto-CRUD of Django is a joy to have for some types of projects. The type safety and power of C#'s LINQ is easy to underestimate and while there have been times when that power seemed "too magic" and bad versions of EF gave off plenty of bad experiences, there are times of great joy in modern EF. If you haven't experienced these specific ecosystems for yourself, it might be worth doing in a side project somewhere just to give them a shot. I can't guarantee you would find joy in that, but I can suggest that there is possible joy that I know exists to find in such places if you try them with an open mind.
- hot_gril 4y ago
  It's the concept of an ORM itself, which is a mapping from OOP to relational algebra, both concepts that don't change much between implementations. You have a relational DB with a nice powerful interface already. The ORM tries to abstract that way with a not-so-compatible OOP interface that enforces mediocre schema design. On the practical side, it's common for a system to start needing more impl-specific database optimization eventually, at which point the ORM gets in the way even more. The ORM is ok for small projects, but so is SQL. CoreData is only especially bad because it's confusing to use on top of that. I've used the Django auto-CRUD too.
  - WorldMaker 4y ago
    Not all databases use relational algebra. Among other things document databases are increasingly common for a number of types of applications and ORMs can provide good experiences for those types of databases as well. Beyond document databases, I think the "theoretical" mismatch between OOP and Relational is often over-exaggerated/over-stated and most of the mismatch blamed entirely on the wrong direction. For historical reasons, some of which were a love of relational algebra mathematical "purity", relational databases have tended to have problems storing and querying hierarchical and graph data. Hierarchical and graph data are relatively common and somewhat "natural" in OOP: inheritance and polymorphism often rely on hierarchies, and references between objects can easily be complex graphs, including cycles and other complex graph mechanics. Theoretically "pure" relational algebra cannot accommodate that kind of data, it's what keeps the math simple and pretty. There is a clear OOP/Relational mismatch, but it's a lot more to do with that theory problem, and the problem is entirely on the relational side being unable to express practical, common, complex data. If there's something holding things back and enforcing "mediocre schema design", there's a lot of fingers to point at relational algebra itself (and SQL too, for its own ways of trying to be "pure" to relational algebra). In practice, of course, there is no such thing as a "pure" relational database that people actually develop software for and most SQL databases have all sorts of "impure" extensions and tools and some dialects of SQL can surely feel quite powerful but still often have real, interesting holes in the types of data they can describe and query. Though some of those extensions offer escape hatches today, with the increase in JSON support especially in the majority of SQL databases. I know in practice I've anecdotally been seeing an increasing number of cases where ORMs remain relevant and helpful but reliance on SQL-based databases and SQL specifics is diminishing all the more each year. Not that I don't think SQL-based databases are bad at what they do, what they do well they do well, but that there increasingly there's a lot more escape hatches being used to non-SQL data stores (even if just increasing numbers of JSON blobs in an SQL database). There's always more hierarchical and graph data to store and query, and there are better options every year for that kind of data. From my perspective, the best ORMs often don't "abstract" away the relational underpinnings of a relational database: they do their best to help you simplify (if not dumb down) your application's intended data structures to better fit a relational database. I say that as someone with good SQL performance understanding, a love of the theory of pure relational algebra, and a knowledge that when I need to that I can absolutely beat an ORM at hand tuned SQL and "brilliant" relational database schemas. I'm sure our experiences have been vastly different to this point in our careers. I agree with you that there is an ORM/relational mismatch, but I don't expect you to agree with me that my practical experiences continue to tell me that the fault isn't with ORMs in that mismatch.