37 ms·

Is ORM still an anti-pattern?

• stickyricky 3y ago
  If your ORM is sqlalchemy its not an anti-pattern.
• sawirricardo 3y ago
  I personally am thankful for ORM in Laravel's Eloquent. I have requirements to always have a changing query which if done with raw sql can increase the maintenance effort drastically. I speak this because I also am maintaining a legacy project which uses raw SQL and it takes a tremendous effort to even add a single feature which I know can be done in minutes with ORM. Overall, I'm with the ORM gang, however, I also respect the other view and I know well enough the freedom they have with using raw sql. But I guess it's fine for me to be constrained with ORM, especially with Laravel's Eloquent ORM. It greatly improves my productivity working as a software engineer, and I also get to know more edge cases of raw SQL by using ORM.
• jupp0r 3y ago
  In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point. ORM is a tool that can make this easier, it's also a tool that can make it easier to shoot yourself in the foot (ie by making it easy to create N+1 queries without knowing). Like all tools, there are tradeoffs that need to be accounted for together with the actual use case to make a decision. Operating on SQL statement strings is not something I'd recommend in any case.
  • PaulHoule 3y ago
    Query Builders are a subject I am thinking about at the moment. The question is whether or not OO is the right model. JooQ in Java is a DSL for writing SQL in Java, it works amazingly well. It’s not guaranteed every JooQ statement compiles to valid SQL but I have pretty good experiences writing crazy complex queries using things like string_agg in pgsql. Here the relational model is primary and the representation as Java objects is secondary, given the SQL is persistent and the Java objects ephemeral this makes sense. My RSS reader uses Arangodb and Python and I’d like to stop a bit and develop a query builder inspired by OWL class axioms which if you look at them the right way look like building blocks for a Scratch-like SQL query builder. I want both ordinary queries (browse my favorites, say an article with the keyword “niantic” that appeared on Tildes) and rules (don’t want read articles from the Guardian that have “- live” in the title)
  • gofreddygo 3y ago
    SQL is an industry standard well understood by legions of senior developers and DBAs alike. SQL skills learned are repeatable, widely useful and build on past experiences. Debugging and optimizing SQL queries is a well understood art, barring some rare DB specific optimizations (which ORMs don't even touch) Getting expert SQL advice and support when you need is very practical and the results show. Putting an ORM as the middle man generating your queries, negates all those benefits for some pithy premature laziness. Operating on raw SQL is something I'd strongly recommend.
  • scarface_74 3y ago
    > In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point. Welcome to LINQ - introduced in 2007 (?). LINQ isn’t exactly an ORM. You create strongly typed LINQ statements that are turned into expression trees that are then turned into a query by a query provider.
    • llm_nerd 3y ago
      In my experience, across a wide variety of applications built wide a number of different teams, LINQ has almost always been an anti-pattern. Whenever you inject a leaky abstraction (as Spolsky would say), things start to go awry. The crimes of grotesque inefficiency I've seen because the magical LINQ is there to shield devs from proper data tiers.
      • tester756 3y ago
        >LINQ has almost always been an anti-pattern You mean "LINQ" as an input for ORM? or LINQ in general? either way this is crazy claim. There's shitton of projects where queries aren't complex and LINQ will make your life saner instead of this stringly typed hell called SQL. Just check what ORM is generating for your queries and if it is a mess then write it manually and you're good to go. This way you get both benefits: sane type system and performance.
        • llm_nerd 3y ago
          LINQ in general is a very bad indicator if it isn't very restricted. I don't think it's a crazy claim whatsoever: When LINQ appears littered through code, it usually mean there has been perilously little data access planning, so instead of planning out and centralizing concise, planned, secure, optimized data access avenues, just pretend that LINQ being available everywhere is a solution. Every LINQ-heavy solution I've had the misfortune of using has always, with zero exceptions, been a performance nightmare. And instead of one big function you can just optimize, it is death by a trillion cuts. I get that there are big LINQ advocates. It's convenient. I'm sure there are projects where it is use spectacularly. I've never seen such a project.
  • mvdtnz 3y ago
    > In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries I don't see this as a given and I don't accept it in my own applications. If you need data, go to the data access layer. If it doesn't provide you what you need, build a new repository / provider / whatever your pattern is. Whether it's an ORM or a home-built query compositor or whatever, one thing I know from experience is that once your application is "sufficiently complex" that you start to (incorrectly) believe you need this, your application has become too complex to use it reliably. You absolutely will be mistakenly evaluating these builders in the wrong layers, iterating results without realising you're generating N+1 queries, etc. You don't need an ORM.
    • markkanof 3y ago
      Why are a data access layer and an ORM mutually exclusive? An ORM is just an abstraction over your database. You might find use in it as a tool to access your database but contain the use of the ORM to within your data access layer.
      • mvdtnz 3y ago
        I never said they are mutually exclusive. I said you don't need the ORM.
        • danem 3y ago
          Mind sharing an example of a large(ish) app that doesn't make use of an ORM? Last time this topic came up, I went looking for one (admittedly not too hard) and I came up empty handed.
    • jakelazaroff 3y ago
      Following this advice too closely and your application logic starts leaking into your data layer. One simple example is when you need to make atomic changes to two different types of entities. In the data layer, this is usually trivial — just run two queries within a transaction — but you need to expose a function that boils down to `updateBothAandB`. Rinse and repeat enough times and your data layer is a soup of business logic.
  • lll-o-lll 3y ago
    > In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point. Right, but is an ORM a good way to achieve this? I would posit no. The problem, correct me if I’m wrong, is one of an in memory representation of working data and a data access layer (how to get/update the data). An ORM provides a framework for both of these, but in my opinion it’s very inefficient to build, maintain, and optimise. You don’t need to turn to raw SQL as the alternative data access layer, but having one that is easier to customise is better in my opinion. Not at all an expert, probably nonsense, please correct me.
    • jupp0r 3y ago
      It depends on what exactly the ORM offers as a feature set. ActiveRecord scopes in Ruby for example do exactly that.
  • neovive 3y ago
    Good points! As someone who uses both ORM and raw SQL (I actually really like SQL as language) I sympathize with both sides of this debate. I prefer the readability of ORM in my models when developing, but closely monitor and optimize for N+1 and other inefficiencies, when needed. "When needed" is never cut-and-dry, but I try not to optimize too early. I actually tend to start new projects from the database and will scaffold with factories, seeders, and raw SQL queries to get a sense of the data model prior to coding. ORM's such as Eloquent in Laravel also have some nice methods to resolve N+1 and perform lazy loading, but it's always tradeoff.
    • kaba0 3y ago
      > As someone who uses both ORM and raw SQL There is no other way — people who don’t know one should not touch the other. Otherwise they are either juniors, or crazies who just like to complain that “the plane is a bad vehicle because I can’t just sit inside and land it properly without years of training”.
  • rcme 3y ago
    There are libraries that flip the concept of an ORM on its head. Instead of a library that allows lazy query composition, you write your queries and the library generates the code for that query at compile time. It’s a much better model in my opinion. E.g. you could write a query like this: getUser: SELECT * FROM users WHERE id = ? And the library would generate a class like: class GetUserQuery { static getUser(id: String): GetUserQueryResult }
    • w7 3y ago
      Some examples for anyone else reading: Go: https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc Rust: https://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia This is my preferred method of interacting with databases now, if available. Very flexible.
  • deleted 3y ago
    [deleted]
  • kgwxd 3y ago
    IME, the accidental inefficiencies aren't a huge problem. What I hate about some ORMs is that they want to be considered the foundation of a system or, worse, multiple systems, rather than just a tool for implementing pesky infrastructure details. LINQ-to-SQL and Entity Framework feel like they want to be at the center of everything, though they don't have to be use that way. Something like Dapper, on the other hand, seems like it just wants to get your app the data it requires, through classes the application defines, and then get out of your way. In short, I don't think ORMs should generate class libraries to be referenced by applications, they should help you get exactly what you need, when you need it, nothing more, nothing less, and no one expect the person that maintains that code should ever need to care how the data got there. If a column gets added to a table that has nothing to do with a specific application, that application shouldn't need to be updated.
    • swader999 3y ago
      I've seen them help with things like dirty checking, that's a lot of work to take on properly. But in the end I think relying on them for significant load and scale is naive.
  • nicoburns 3y ago
    We had a query builder in our app and ended up stripping it out in favour of raw SQL and string interpolation (for dyanmic queries, not for passing in data). We found the raw sql was much more readable. We still used the library for inserts and updates.
  • HumblyTossed 3y ago
    > In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point. Then your devs have way too much time on their hands. Find them some actual problems to solve.
  • kbenson 3y ago
    Different people have wildly different experiences with ORMs as they used them for wildly different levels of integration and tasks, and in wildly different languages with different features that make ORMs more or less useful, yet there's always someone willing to go out of their way and ignore all that and make absolutest statements about how it's good or bad. We should just learn to recognize it for what it is, someone that's being controversial for attention, and move on. Let's save our attention for the ORM article and discussion that starts out along the lines of "ORMs can provide benefit, but it's important to recognize where, and not let the problems of their use outweigh their benefits. Here's what I've found."
    • srcreigh 3y ago
      what have you found?
    • _gabe_ 3y ago
      > Different people have wildly different experiences with ORMs as they used them for wildly different levels of integration and tasks, and in wildly different languages with different features that make ORMs more or less useful, yet there's always someone willing to go out of their way and ignore all that and make absolutest statements about how it's good or bad. I don't have a horse in this race, I could care less if you do or don't use an ORM. But, and maybe this is the cynic in me, there are practices in software development that absolutely, 100%, for certain, have no good reason and are perpetuated in part by this belief that there must have been a good reason for it to exist (Chesterton's fence and all that). Null terminated C strings are a prime example. There is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes, that C strings should be null terminated. Fortran was created in 1954, and passed the length of the string with the string itself. How many countless bugs and CVEs have risen due to errors in handling null terminated C strings (one example[0])? And for what? To save 3 bytes or just because of the authors decision at a whim's notice? Likewise, decisions made at Javascript's inception have burdened it for its entire life. Decisions that were made at a whim's notice, like implicitly converting numbers to strings sometimes, and sometimes implicitly converting strings to numbers! (Tell me what `10 + "10"` is and what `"10" + 10` is without using the inspector). And the million and one ways to define something that's undefined. Anyways, when somebody tells me there's absolutely no good reason for a development practice to exist, sometimes, that is the absolute truth. And I would rather have more people throwing away these crummy practices that lead to unnecessary headaches (or at least questioning them) then people continuing to laud the practice and perpetuate it ad infinitum. [0]: https://defendtheweb.net/article/common-php-attacks-poison-null-byte https://defendtheweb.net/article/common-php-attacks-poison-n...
  • irrational 3y ago
    That has not been my experience. Then again, I’ve written tens of thousands of sql queries over the decades, so sql is as easy as breathing for me. No need for any sort of query builder.
    • fodkodrasz 3y ago
      Query builders are generally not intended for developers to build "static" queries, but to build dynamic queries programmatically while being safe (eg. from SQL injection) based on dynamic data, often with user inputs, the likes from ASTs from parsed user input in query fields.
  • sacnoradhq 3y ago
    Getting inner joins and denormalized data correct are a PITA that should be delegated to a semantic API that already is flexible and proven.
  • bluesnowmonkey 3y ago
    In a sufficiently complex application, the database lives in another service that exposes a fixed set of queries through an API and certainly does not let you compose arbitrary queries.
    • Spivak 3y ago
      How do you do anything with that? Like do you just accept that if some part of your app needs the data from query1 and query2 you make two trips to the database?
      • duckmysick 3y ago
        You file a ticket with the database team so they can add your fixed query to the service API. After a few rounds of exchanging messages and meetings it might be added. If it's a third-party API (say, a weather forecast or market data) the answers will range from "no" to "yes, and it will cost you X".
  • catdog 3y ago
    Why use an ORM if all you need is a query builder? They exist as of the shelve solution, some are even crazy good.
    • dathinab 3y ago
      the line between an ORM and a query builder is a very blurry one your query builder isn't just string based to avoid a lot of potential bugs which are easy to introduce and miss in tests? It also has a simple way to (de)serialize row from/to POD structs? Now you already have a thin ORM.
    • strokirk 3y ago
      Care to mention one or two? All the ones I’ve seen have been dismissed as ORMs by ORM-haters.
  • Zetice 3y ago
    The N+1 problem feels a bit like y2k in my experience; would definitely be a problem if smart folks didn’t come up with clever solutions, but since they did it’s not nearly the issue it’s made out to be for the average implementer.
  • dbingham 3y ago
    I would challenge your assertion that "any sufficiently complex application" will require lazily composed queries. I have worked on quite complex applications that had no such need. Well encapsulated string construction in a Data Access Object pattern worked just fine. If you find yourself needing to lazily compose queries, then by all means, reach for a query builder. But I would encourage you to first examine whether you've split code into services that really should live together and whether that code should live closer to the data layer before you build a whole query builder. And don't reach for a query builder until you need it.
• amluto 3y ago
  A problem that I haven’t seen a good solution to is that most (all?) ORMs are also caches. And once you step outside the object box, cache invalidation is incredibly hard. Taking an example from this post: await postRepository .createQueryBuilder() .update(Post) .set({ status: 'archived' }) .where("authorId IN (SELECT id FROM author WHERE company = :company)", { company: 'Hooli' }) .execute(); What happens to all the pre-existing Post objects when you do this? (Maybe some ORMs try to get this right. It’s a very hard problem. Certainly the ORMs I’ve used don’t, but I admit I haven’t used very many.)
  • c00lio 3y ago
    Also, most ORMs make it hard or ignore the possibility of using database transactions, checkpoints, partial or full rollbacks, etc. Which might be fine for trivial queries, low write load and unimportant data. But for anything moderately important or complex, transactions are essential.
    • antonyt 3y ago
      They do? Every ORM I've used supports explicit transactions and rollbacks. EF, NHibernate, GORM, SQLAlchemy. What major ORMs deny you this functionality?
      • ttfkam 3y ago
        Yep, and each has their own bespoke and often inconsistent API. All to eventually emit plain ole BEGIN/COMMIT/ROLLBACK. JPA is at least a spec rather than just yet another single isolated implementation. Unfortunately it's not a good spec.
      • c00lio 3y ago
        It isn't that there is no such thing as "a way to open/close transactions". It is that transactions usually break all the supposed ORM benefits like composability. Often, you cannot even method-chain transactions like foo.search().begin().insert().insert().commit(), you have to do it in multiple lines. Even worse, rollback handling is often an exception, breaking control flow of your application. And most only support basic transactions, not checkpoints.
• jiggawatts 3y ago
  I’m a big fan of the “middle ground” micro-ORMs like Dapper or Diesel. Write your queries in SQL, but you get to have your strong typing and automatic result set to objects mappings. Productivity combined with the full-featured “real” query language. Also, I’ve become a fan of using the object mappers only for read-only queries. Updates occur via the command pattern — stored procedures. These can be mapped through to look like native methods.
  • gmac 3y ago
    Yes, the middle ground is great. In TypeScript there’s Kysely or Zapatos (mine), for example.
  • mvdtnz 3y ago
    Absolutely. Dapper is easily the most pleasurable data access solution I have worked with. Developers need to stop pretending SQL doesn't exist and just embrace it.
  • zmj 3y ago
    I think this is the sweet spot. Last time I worked with SQLite on .NET I wrote a wrapper library along those lines: https://github.com/zmj/sqlite-fast https://github.com/zmj/sqlite-fast
  • calvinmorrison 3y ago
    Personally I like not having to either write my own model functions which just map back to sql, or having to write a boilerplate php to do parameterized queries correctly. Eloquent abstracts enough of that crap out of my way i can focus on the logic. Sincerely someone rewriting an 'old' php app that is just arrays and queries to something using models and eloquent, what a game changer
    • BafS 3y ago
      Eloquent is one of the worst ORM I worked with. It works until it doesn't, there is too much magic and it mixes SQL/Entity in the same class. Since then I use doctrine and/or repository pattern which makes the code more extensible and testable, it's also trivial to do your own raw SQL request and map it to your entity if you don't want to rely on an ORM.
• kstrauser 3y ago
  That's begging the question: it never was. Some ORMs are awful to work with, but that doesn't mean the genre is a bad idea. I've loved working with SQLAlchemy over the years because it doesn't try to re-invent the whole idea of SQL. Instead, it gives you a convenient layer for building queries, passing them around, etc., while it concentrates on getting the details right so you don't have to futz around with them.
  • evrimoztamur 3y ago
    I also can vouch for SQLAlchemy, in the in the sense that it allows me to write SQL with confidence, where the code matches the query.
  • bellgrove 3y ago
    I haven’t seen someone use the phrase begging the question properly in… forever.
    • jkrubin 3y ago
      What is the proper usage?
      • kstrauser 3y ago
        I don't think I can summarize it concisely, so I'll defer to the wiki: https://en.wikipedia.org/wiki/Begging_the_question https://en.wikipedia.org/wiki/Begging_the_question In this case, the phrasing of the question implies something not at all evident, that ORMs are an anti-pattern.
      • teddyh 3y ago
        https://en.wikipedia.org/w/index.php?title=Begging_the_question&oldid=1157780714 https://en.wikipedia.org/w/index.php?title=Begging_the_quest...
    • nprateem 3y ago
      Including here, since 'it never was' is a statement, not a question. So no question has been begged.
• square_usual 3y ago
  ORMs are all problematic, but some are less problematic than others. Some years ago I started writing more Elixir with Phoenix and Ecto, and I quickly realized just how much better Ecto is when compared to ActiveRecord. I wrote about it back then [1] but the gist of is it: SQL > Query Builders with some ORM elements > ORM qua ORM, and the less you blur the boundary between your application and the database the better. 1: https://blog.shrikanthup.com/2021-12-04-ecto-data/ https://blog.shrikanthup.com/2021-12-04-ecto-data/
  • mmcclure 3y ago
    You say this in your post (and allude to it here), so not trying to be pedantic, but explicitly surfacing it here for other folks that don't click through: Ecto isn't an ORM. I love Ecto. In my opinion, Phoenix has a lot of strengths, but Ecto is the superpower in that ecosystem. That being said, at least _some_ of the brand of "problems" mentioned in the post can exist into the Ecto world. I've seen plenty of folks new to Ecto fall into N+1 queries using `preload` naively, so I'd argue it doesn't completely erase that blurred boundary. I also personally much prefer the level that Ecto sits at and think the tradeoffs are totally worth it.
• imbnwa 3y ago
  God save us from JPA Criteria and its hellspawn
• recursivedoubts 3y ago
  ActiveRecord has always felt great to me: yes there are some corner cases to deal w/ but it hits the 80/20 spot, let's you kick out to SQL when you need to, adds a lot of excellent life-cycle related hooks that clean up domain logic, and is pretty easy to reason about pragmatic ORMs that don't try to hide everything behind a "bundle" or introduce their own query language are great, it's the ones that try to totally "solve" the ORM problem that are hard to work with
  • tarsinge 3y ago
    Yes I can’t imagine going back to doing big CRUD apps without AR, readability and maintenance would take a hit. The catch is you need a prior good knowledge of SQL to use it correctly. Problems happen when people don’t learn SQL and then don’t understand the queries AR is generating and the limits.
  • Alifatisk 3y ago
    You hit the nail with this, ActiveRecord is truly a joy to use, when you stumble upon an edge case, you can always go back to raw sql again! This is how an ORM should be.
  • thinkpad13 3y ago
    yeah i dont get why they complaint about orm, maybe its because the only orm that I use is activerecord
• dustingetz 3y ago
  Also mutability; object-level edits/writes are half the problem with ORM
• adamrezich 3y ago
  when you write code to build a query in an ORM in an interpreted language like Ruby or Python, does it typically go out of its way to actually build the query once? or does the query get "re-built" (or cached?) every time you call the function that contains your query-building code? I've been wondering about this and other things while working on my own mini-ORM thing for SQLite—I have it so queries are built once, at compile-time, type-checked, and stored in read-only memory in the executable. but I'm going down the route of having the user manually write SQL for everything that isn't super basic, instead of making a sort of mini-DSL for query-building. so far it's been going great but there's a lot of prior art in this space, and while I've used both Rails and Django before, forever ago, I've been kind of going into this all blindly (and so far it's been going great!).
  • cjblomqvist 3y ago
    Probably different for different ORMs. I know EF Core (C# /.NET) does it/cache queries (for performance reasons).
  • deleted 3y ago
    [deleted]
  • gndk 3y ago
    In the PHP world, especially when using Symfony, Doctrine ORM is very popular (inspired by Hibernate). It has all kinds of caches, including the query cache that you mentioned: https://www.doctrine-project.org/projects/doctrine-orm/en/2.15/reference/caching.html#query-cache https://www.doctrine-project.org/projects/doctrine-orm/en/2....
  • clintonb 3y ago
    Why bother caching queries that are pretty quick to generate? Most caching around ORMs focuses on caching the results of the query since they require more resources to generate.
    • adamrezich 3y ago
      well if you're using an interpreted language without any sort of build step then I suppose that's all you can do, but if your backend is written in a compiled language with compile-time execution functionality, then it seems useful to do stuff like this then, instead of at run-time, likely multiple times per request—that's a lot of unnecessarily repeated string-building, without any benefit for doing so!
• femiagbabiaka 3y ago
  Yes. > This is mostly false. ORMs are far more efficient than most programmers believe. However, ORMs encourage poor practices because of how easy it is to rely on host language logic (i.e., JavaScript or Ruby) to combine data. The ORM encourages this, unfortunately. > Now don’t get me wrong, ORMs are not as efficient as raw SQL queries. They are often a bit more inefficient, and in some choice cases, very inefficient. Maybe a bit pedantic, but this and what's above contradict each other. The problem is essentially this: you shouldn't use the abstraction until you understand what you're abstracting. But that's not how we write software typically.
  • mathewpregasen 3y ago
    It's more that ORMs are often perceived to be far slower on average, but in reality, they are not that much slower most of the time, and only far slower in choice scenarios. But yes, agreed on your comment on general abstraction.
    • femiagbabiaka 3y ago
      If the performance hit is seen as the overhead of the actual object oriented pattern (assigning fields to objects, caching, etc), then yeah, not that much slower, after all, the allocations would have to happen at some point. Patterns like DataMapper pattern present an alternate path that gives ergonomic advantages while not hiding the hard parts of dealing with state AND being incredibly performant. But the real performance cost of ORMs has always been what typically gets left on the table: well written queries and schema. Poorly written schemas inspire poorly written queries which in turn completes the ouroboros by inspiring poorly written indexes. And ORM's like ActiveRecord encourage this. One easy example of this is the Paranoia gem as an addon to ActiveRecord, which encourages the misguided pattern of keeping around old data in your RDBMS.. There are many more examples.
• deleted 3y ago
  [deleted]
• tomalaci 3y ago
  I feel like if your query ends up being quite large in complexity then maybe it is a time to create a view or stored procedure depending on the use-case and have ORM call that one instead. To me ORM is quite useful to have some data classes that I can use with it to automatically handle various generic operations and maybe some relationships. Many ORM frameworks will also auto-create tables with the relationship constraints, provided you setup your data classes correctly. The moment you start linking up too many ORM operations together makes me want to abstract it away into a view/procedure. It will be easier to debug that view/procedure rather than inspecting what your ORM has generated and how to fix it. I am interested to hear if someone has other insight into how to utilize ORM frameworks properly.
  • ttfkam 3y ago
    Unless you're on Java. Just need three columns out of twenty? That's too bad. The JPA spec mandates you pull all the columns in that table. But fundamentally on any programming language, all popular ORMs treat the database like it's MySQL 5.7—the bare minimum in modern functionality. It's like owning an Audi but being told you have to drive it like a 1971 Chevy Vega—a lowest common denominator.
• andrewstuart 3y ago
  IMO SQL is one case where you should "just program the damn thing". There's no point at all in learning some SQL or ORM library. Learn and write SQL directly. Database access is a fundamental thing you'll be doing for your entire career probably. Best to learn the actual technology directly. This is true for certain other technologies too - CSS for example - don't learn a CSS library - learn and use CSS. In fact that's true really for almost all aspects of front end development - don't use a "forms library", program the damn forms APIs in the browser. Also my personal experience is that SQL is much easier to learn than certain ORMs anyway. Don't waste the cost of learning the wrong thing.
  • wvenable 3y ago
    And then you learn SQL, which you should, you'll know when using an ORM is the right option. You might even be able to evaluate them better. I suppose the same with CSS -- knowing it means you'll have much more flexibility and understanding when picking a CSS library for convenience.
    • systems_glitch 3y ago
      Indeed, my introduction to databases was extracting stuff using Perl's DBI and getting good at pushing the work out to the DB. When I was introduced to ORMs it was instantly clear that they were a game changer for MVC development. I've transitioned a few legacy (large) Rails applications through Arel and often did not understand why we were putting the work in, other than to make it easier on new/future developers who did not understand SQL. In pretty much all of them we ended up having to keep some non-Arel queries -- you know, the ones where `STRAIGHT_JOIN` cuts 5 minutes off a query!
  • homarp 3y ago
    Isn't it more a case of "learn the fundamentals before learning the abstraction" so you know what you are "paying for" (unless it's "zero-cost" abstraction)?
    • TeMPOraL 3y ago
• AdamH12113 3y ago
  For those who didn't know, ORM stands for "object relational mapping".[1][2] It's always good to define acronyms up front, especially if you're trying to attract a larger audience. [1] https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapp... [2] https://stackoverflow.com/questions/1279613/what-is-an-orm-how-does-it-work-and-how-should-i-use-one https://stackoverflow.com/questions/1279613/what-is-an-orm-h...
  • mlatu 3y ago
    fuck. thank you. had to collapse three pages of comments to find this... fucking hell
• jwlake 3y ago
  I'm always confused that ORMS don't standardize on a syntax where N+1 queries are literally illegal in all cases. With `forEach` and callbacks and everything you could easily have a syntax where you can't await until an `run` and curry everything into a single round trip. It lets you write "standard" javascript but disallows N+1 loops always. Then people have to write "bad" code to N+1.
  • ttfkam 3y ago
    If only this were true. ORMs have come up with some very subtle foot guns over the years.
• thatxliner 3y ago
  I’ve asked about ORMs before https://news.ycombinator.com/item?id=35215355 https://news.ycombinator.com/item?id=35215355 For simple projects, I don’t see a need for it.
• flappyeagle 3y ago
  never was
• Mountain_Skies 3y ago
  It's amazing how many SQL Injection vulnerabilities I see in brand new code. At least with an ORM, this is abstracted away unless you try extremely hard to create such a vulnerability.
  • ttfkam 3y ago
    Parameterized queries are a thing in every popular driver in every popular language (except Lua). It's been quite a while since I saw a tutorial using string concatenation. Thank heaven those dark days of horrible PHP/MySQL SQL injection tutorials everywhere came to a close. For the record, I've seen SQL injection attacks in ORMs too, though far more rarely than the "100 separate queries to render this web page" insanity that avoiding SQL knowledge inevitably brings.
    • antod 3y ago
      Heh, I remember lots of "100 separate queries to render this web page" insanity with doing some CMS work ages ago (actually 200+ was sometimes common). Only thing was it didn't have an ORM either :) Someone will probably correctly guess which one it was...
  • ttfkam 3y ago
    Demonstrate how easily and accidentally one can make an SQL injection with these: https://github.com/porsager/postgres https://github.com/porsager/postgres https://github.com/gajus/slonik https://github.com/gajus/slonik
• cies 3y ago
  Imho, ORMs make the easy queries easier (terser) but simply do not cut it for complex queries. So you need some solution for that too. You could drop down to SQL-as-string-queries, but then you loose all type safety on the queries. We use jOOQ (sort of LINQ on the JVM) which brings significant type safety to the database interaction layer. Would I use an ORM if I had to start over? No. Would I use a db + jOOQ if I had to start over? In many cases, yes. To me they are not so much a anti pattern, but usually come with many anti-patterns, like coupling validation to entities to caching to ...
  • drbawb 3y ago
    jOOQ was one of the first times in my career where I (a junior, at the time) went to bat and convinced my company to shell out for a commercially licensed library. Our application was basically nothing but complex queries; getting our ORM, Hibernate 3 back then, to do anything sane & performantly was a nightmare. Those licenses were money well spent for developer productivity alone, but honestly they've probably paid for themselves multiple times over just in terms of "energy usage at runtime" cost. It's not always ORMs, but it's a pattern I see over-and-over again, somebody that doesn't know SQL tries to abstract away all the uncomfortable (to them) parts of SQL. It always ends up as an unmaintainable and non-performant disaster. _It's relations all the way down._ If you're going to fight SQL at every turn, you're better off using something other than SQL to store your data. (I still die a little inside every time I open up a SPROC that's just a giant cursor. Won't somebody _please_ think of the sets?)
• mschuster91 3y ago
  A point I'm missing is: without using an ORM (or however else you're calling your database interface) you will eventually end up writing and maintaining one yourself, at least for CRUD stuff, and it will be filled with weird bugs, edge cases and security holes. > A common complaint about ORMs is that they break two of the SOLID rules. If you aren’t familiar with SOLID, it is an acronym for the principles that are taught in college classes about software design. And that right here is the problem. Academia is teaching people stuff that makes sense in an academic environment where time, maintainability, money and effort aren't of a concern (as long as you have grant money, but that's not relevant here) - and that leads to a very nasty collision when fresh graduates enter the workforce. They're used to shaving yaks and reinvent wheels all day long in the quest for a "perfect" solution, and if the company doesn't have (enough and qualified) senior/lead developers to catch that, you end up with ... very interesting products, budget overruns and/or dumpster fires. Or a combination of all of them. ETA: There's something that can go even worse: when stuff designed by and for academics enters public availability. The best/worst example for that is OpenStack... enough knobs and twists to support the specialized environment of CERN and probably hundreds of universities worldwide, with components modularized to an insane degree, but almost impossible to get started with because it's so byzantine.
  • erik_seaberg 3y ago
    This. Use a good one unless you want to own half of a poor one.
    • wredue 3y ago
      This is just a claim that everyone who advocates for ORMs makes, but it’s not reality.
      • erik_seaberg 3y ago
        The third time I have to figure out whether to write obj.setCreatedAt(rs.getTimestamp("created_at", calendar)) or obj.setCreatedAt(rs.getLong("created_at")) I'm going to write code to look at the schema and the setter and figure it out automatically. And that’s where new ORMs come from.
• oneplane 3y ago
  It never was. Using ORM made the pool of candidates to work on a codebase bigger, and it made applications better on average (not because of some ultimate performance or security metric but because it evens out all the horrible hacks). In a vacuum, on a super specialised team or in an academic setting it might be something one could write such an article about, but the average CRUD-app-programmer tends to not have the bandwidth or specialisation to spend effort on manual data wrangling. The same applies the other way as well: as soon as saving object-level details or fetching related data becomes something that requires thinking, plenty of junior and medior engineers end up forcing loads and saves by hand. Not because it's the best way or the only thing they could do, but because it works, it delivers the features and makes the company money, and everyone else also working on the code understands what is happening. In a way, 'bad' code that works and can be worked with is not all that 'bad'...
  • ttfkam 3y ago
    If you're using JS or Python, the SQL call just returns an array of objects/records. No muss. No fuss. If you're using something like Sqlx in Rust, the SQL is not just checked for validity, it seamlessly assigns the result set to the exact type you define for that use case. ORMs make basic CRUD fast and easy while making everything else massively more complex. If all you ever need is CRUD, I guess that's alright. Many of us work with projects that far exceed it though. And for that, you need to know SQL and not just some half-baked SQL punch through the ORM deigns to afford to you.
    • swader999 3y ago
      You can use cqrs to just build simple reporting views that orms get and set and for your real concurrency boundaries build aggregates that hydrate their state from all the events that have happened to it. Handles complexity to the nines and gets your reports and screens wicked fast at any scale you can imagine. You won't need to join much or optimize queries. Maybe figure out a few non obvious indexes.
      • ttfkam 3y ago
• ttfkam 3y ago
  My dislike of ORMs mainly stems from the tendency to treat modern SQL engines as glorified dumb bit buckets. Where a CTE or LATERAL join or RETURNING clause would simplify processing immensely or (better yet) remove the possibility of inconsistent data making its way into the data set, ORMs are largely limited to simplistic mappings between basic tables and views to predefined object definitions. Even worse when the ORM is creating the tables. SQL is at its heart a transformation language as well as a data extraction tool. ORMs largely ignore these facets to the point where most developers don't even realize anything exists in SQL beyond the basic INSERT/SELECT/UPDATE/DELETE. Pivot tables. Temporal queries. CUBE/ROLLUP. Window functions. Set-returning functions. Materialized views. Foreign tables. JSON processing. Date processing. Exclusion constraints. Types like ranges, intervals, domains. Row-level security. MERGE. It's like owning a full working tool shed but hiring someone to hand you just the one hammer, screwdriver, and hacksaw and convincing you it's enough. Folks go their whole careers without knowing they had a full size table saw, router, sander, and array of wedges just a few meters away.
  • marginalia_nu 3y ago
    To be fair, a lot of those tools have that one weird caveat you need to know, and are also strongly dependent on your dialect. For example there's an aggregate function in DB/2, LISTAGG, that joins strings... the caveat being that if they get too long, the query blows up. It's in OracleSQL too, which has a syntax where you can tell it to truncate the string. SELECT, INSERT, UPDATE, DELETE work more or less the same whether you're on MariaDB, Postgres, DB/2, OracleSQL, etc.
    • ttfkam 3y ago
      As opposed to the dizzying array of ORMs that all have multiple caveats you need to know and utterly incompatible in fundamental ways between each other? Folks go on about how they're "stuck" with a single dialect of SQL but completely ignore how utterly impossible it is to switch ORMs without a full app rewrite. And ORMs are all language-specific, so if you have two different app clients in different languages, you can't always share even basic API access patterns. Got a Java Spring app alongside a Django app? Good luck!
• mpetrovich 3y ago
  In my career, the biggest problem with ORMs I’ve used is something the author touched on: They can be terribly leaky abstractions. It seems that many ORMs end up re-implementing SQL in their own domain-specific language. However, an understanding of SQL is often needed to debug and optimize ORM queries. So why not just use SQL directly then?
• bourneavent 3y ago
  [dead]
• gorjusborg 3y ago
  To me, the article doesn't make very compelling observations or arguments. There are some interesting things to note about object-relational mapping: * They map data to objects (so they are really tied to OO languages) * They generally trade control for convenience * Over the maturity of projects that use them, the convenience gets eroded, due emergence of system issues that require control to be wielded. Honestly, if you are debating the use of an ORM, I would ask why. What is it that you are trying to get out of introducing an opaque abstraction between you and your data? It better be worth it, now and in the future, because it will probably take a good amount of effort to move away from it later. The trade of control for convenience is usually a good trade at first (during prototyping and early stages of feature development). Later, there is usually an inflection point where it becomes more of a hinderance than good. The trouble is, at that point, migration away can be too risky/costly to justify. Finally, I have noticed that, generally, mature, well-functioning code bases tend to move away from traditional OO-patterns. Specifically, code and data tend to separate as code bases grow and age because it is easier to reason about. This tends to diminish the value of ORM further. Overall, I've moved away from ORM on the whole, in favor of query -> struct mapping.
• marginalia_nu 3y ago
  I think that if you want the most out of a RDBMS, then ORM is never the way to go. The problem being that objects and database rows are not the same thing. Not even a little bit. If you squeeze that square peg through that round hole, you shave off 80% of the point in doing so. Object Relational Mapping is basically a contradiction. If you are only using the subset where the cost of using an ORM is negligible, you're probably not really using the RDBMS to its full extent and maybe you should consider a document oriented database instead. Relational databases are stupidly powerful if you let them do their thing. Kind of a pity to reduce them to a k/v store. That said, there's totally a segment of CRUD:y non-performance-critical applications where ORM is a non-terrible choice.
  • jakelazaroff 3y ago
    Could you expand on some benefits of seeing your data as "rows" rather than "objects"? I'm not clear on what that means in practice.
    • marginalia_nu 3y ago
      They have operations that objects do not, such as joins and projections. It's a completely different paradigm for dealing dealing with data. You can take an "object", pick parts of that, take a few parts of another related "object", perform some manipulations on those selected "fields" and view that grab bag that as though it was a whole new "object". There is really nothing like that in OOP.
• jacobsenscott 3y ago
  "My big complex application is big and complex! It must be my (orm|monlith|OO|dynamic typing|database)!" No, it is just a big complex app is complex. You can shuffle the complexity around, but you can't magic it away. You can change a pattern here or a tool there and make some subset easier to work on, but you've probably just made some other subset harder to work on.
• cdumler 3y ago
  Overall, I think the article is pretty good. ORMs fall under the same category as garbage collection or try/catch exceptions: it is a tool. Tools solve a lot of common cases, but that doesn't relieve the developer from understanding what is actually happening under the hood. * Garbage collection solves pointer problems, but the developer has to know what is being allocated and when to prevent churning memory. * Try/catch exceptions solves breaking the tie between where exceptions can occur and where exceptions can be handled, but the developer still has to determine if all of the exceptions are being handled. * An ORM solves relating objects to data persistence, but the developer still has to know what relations weren't covered in the original query after the objects are used. > Active Record struggles with this, and that’s why we refactored our billing > subscription query. Whenever we got results we didn’t expect, we’d have to > inspect the rendered SQL query, rerun it, and then translate the SQL error > into Active Record changes. This back-and-forth process undermined the > original purpose of using Active Record, which was to avoid interfacing > directly with the SQL database. This is where I disagree. ActiveRecord was meant to map objects you have to your relational database, hence Object Relational Mapper. It is not a reporting interface. But it is reasonable to get around this. One way is to get a lot more specific in building the query with `arel`. Alternatively, open the raw connection and execute SQL with `ActiveRecord::Base.connection.execute`. The disconnect here is believing that ActiveRecord solves billing/reporting SQL statements. It does not. It solves mapping objects. If your access pattern doesn't match (reports, N+1, GraphQL) then you have to understand the tool well enough to create your own mapping solution (custom SQL, preloading/scoping, data loader). I remember when Java was considered really, really slow. The guy used a code example to prove a point. I rewrote the code to stop relying on the GC allocating/freeing data structures over and over and sped the program up by 400% on that alone.
• nijave 3y ago
  Martin Fowler's Patterns of Enterprise Application Architecture has a section called "Data Source Architectural Patterns" with some data access patterns. It doesn't explicitly call out "ORMs" but they tend to combine multiple patterns. It also has a section on "Query Objects" which cover some of the method -> SQL magic ORMs do. It also lists pros and cons of these patterns As with many things, ORMs come with pros and cons. Imo they can be especially productive for simple CRUD applications and prototyping but may quickly increase complexity with object or data models that use more complex query patterns. https://martinfowler.com/eaaCatalog/ https://martinfowler.com/eaaCatalog/
• rektide 3y ago
  Better visualization/viewability of what's happening again seems like the best thing. If we could have a uml diagram of the orm's model, then see the retrieval access patterns flowing across that model, the risks/badsides of ORMs would be much reduced. Making clear what's happening is as usual a win.
• lovasoa 3y ago
  SQL is much more powerful than most people think, and taking an afternoon to learn everything you can do with it is a great investment. After you do that, you will probably not WANT an ORM anymore, finding that it makes your code less readable than plain sql, not simpler. Actually, you'll even realize that you may not need anything BUT sql to write your application, using something like (shameless plug) SQLPage [1] [1] https://sql.ophir.dev https://sql.ophir.dev
  • wpollock 3y ago
    >SQL is much more powerful than most people think, and taking an afternoon to learn everything you can do with it is a great investment. SQL is indeed powerful but how can anyone learn SQL well in an afternoon?
    • lovasoa 3y ago
      Maybe I'm mistaken, but I think someone who is already using a relational database through an ORM can learn enough to make powerful queries in an afternoon. Let's see what we need: - SELECT a,b,c FROM table WHERE condition : 1 hour - INNER JOIN, LEFT JOIN : 1 hour - GROUP BY, HAVING, aggregation functions: 1 hour - subqueries, EXISTS : 1 hour - recursive queries : 1,5 hours - end of the afternoon You'll probably need to practice a little after that intense afternoon, but I do think that after learning just the five points above, you can become the most knowledgeable person about SQL in your team.
      • antod 3y ago
        You originally said "everything you can do with it" rather than just the basics though. Those goal posts moved pretty far. I agree with learning most of your examples in an afternoon, but really knowing SQL takes a hell of a lot longer than that. In fact even the most SQL shy ORM preferring devs I've worked with still know those basics.
        • lovasoa 3y ago
          Where do you work? That must be a great place! Most devs I know, even talented ones, have never done a recursive query, and a lot of them don't even know it's possible.
• mey 3y ago
  My prior/current experiences with most ORMs are that developers feel like they need to know less about how a relational database functions than they should because they can focus on the abstraction. This leads to schema designs that aren't ideal or not understanding where it is efficient to perform an action (should this be filtered in the database or application, when does a query need to be optimized, where is caching happening, indexes?). Great for initial productivity, but without monitoring, can run into some serious issues down the line. I've seen lambda expressions hitting ORM interfaces where it wasn't immediately obvious how much data was being grabbed, cached, and pulled back. Then when the database was moved just a few milliseconds further away (network hops) from the application server, it caused significant issues because of the number of round trips happening magnified that small latency increase. This is ORM specific, but not all of them are great at handling transaction blocks. In such cases dropping into a database stored procedure may be ideal, but then again you are no longer using the ORM and having to take your object and do mapping into a different structure (the stored procedure). My personal preference is for just enough quality of life tools around the database access. In Java land, jdbi for quality of life plus flywaydb for migration (written in raw SQL) is my current preference. jdbi fixes many basic mapping/binding issues JDBC doesn't handle and flywaydb provides a good foundation for keeping environments in a consistent state.
• time0ut 3y ago
  ORMs are like any category of tool. Some are good. Some are bad. Either way, you need to master it (in additional to SQL) to get the most of out it. These days, my approach is to start with some repository interface/abstraction that exposes well defined access patterns/operations and models. Then I hand code the implementation with just normal SQL that I hand code. You can get really far with this. Eventually, if it starts feeling unwieldy, I'll look at reimplementing it with an ORM. This rarely happens. In fact, its only really happened twice that I can think of. That said, if I joined a team that was set on using a particular ORM and they had experience with it, I wouldn't think twice. The only thing that is a deal breaker for me is using any ORM feature like Hibernate's auto flush insanity (something that figures out which objects you've dirtied and automatically generates updates and inserts when you close the session). It will be very difficult to ever untangle that if you need to. Far better to explicitly have to persist things than rely on some auto-magic like that.
• ajhurliman 3y ago
  I may be in the minority here (based off of other comments), but using raw SQL commands has been more performant, easier to write, easier to understand, easier to learn, and easier to build complex queries/ commands than an ORM has ever provided. Updating some records in a CTE and using the results in a subsequent SELECT? It may take a minute or so on stack overflow, but significantly less than it would with SQLAlchemy. Want to make a composite primary key? I have no idea how you would do it with most ORMs, but it’s relatively straightforward with SQL. A lot of times the stuff you want to do won’t even be available in your ORM, but the person lobbying for using it always pleads “it’s okay, it still allows you to make raw queries! We have an escape hatch!” Then when you start writing raw queries, your PRs get rejected: “Please stick to the ORM”. I don’t understand people’s love of ORMs instead of simply learning SQL, it seems to bring on myriad headaches for nearly zero benefit.
  • c_crank 3y ago
    Seconding this. Nothing less fun than trying to untangle insidious Hibernate bugs.
  • meitham 3y ago
    I don’t use ORM, but depending on the complexity of the SQL statement I might build it using SQL alchemy core. I hate to deal with all these rules about the right way of escaping SQL Colum names with “, ‘ and [. I find it sqlalchemy core to be the right balance where I am still in charge of the transaction management around the statement but rely on a library to build the textual SQL statement, without it I would probably have to reinvent some parts of sqlalchemy.
  • Spivak 3y ago
    > I don’t understand people’s love of ORMs instead of simply learning SQL > Want to make a composite primary key? I have no idea how you would do it with most ORMs, Since you mentioned SQLAlchemy class MyTable(Base): col1 = sa.Column(sa.Integer, primary_key=True) col2 = sa.Column(sa.Integer, primary_key=True) ORMs aren't an alternative to learning SQL. * Type safety on all SQL operations. * All the benefits of a query builder. * Reverse relationships that read better in code `parent.children`. * External file management that is generic, works on all your tables the same way, and supports multiple simultaneous back-ends making migrations easy. * Object de/serialization and transformations letting you use native types like datetime. * Typed/parsed JSON columns, I use Pydantic for this. * Handling single/multi table inheritance for you and giving you type safety.
• freetime2 3y ago
  The “automagic” nature of ORM frameworks makes it easy for developers to inadvertently implement “N+1 selects” access patterns. Most ORM frameworks will also offer a number of ways to mitigate this issue, though, and if you are going to use an ORM you must thoroughly read the documentation to understand how it works and what options are available to accessing data. That being said, the worst codebase I ever worked on was created by a team who felt that ORMs were “evil”. It was littered with N+1 select issues from developers trying to reuse snippets of code without thinking through the performance implications. So I’d say it really comes down to the skill/experience of the engineers to use tools correctly rather than what tools you use.
• andrewstuart 3y ago
  Modern databases offer incredibly useful and powerful functionality that abstraction layers typically don't implement. Postgres has so many useful features and functions that ORMs just don't usually surface - that's not what ORMs are about. So it's not just about "learning SQL", it's about learning the database itself and what it has to offer. Search engines, JSON in/JSON out, features like SKIP LOCKED for queueing, arrays, the list goes on and on, and if your view of the database is through an abstraction library you'll likely never even know what's in there. So don't just learn SQL, learn the database itself.
• mattbillenstein 3y ago
  ORM, Python GIL, need for k8s, mongo is webscale - pick your summertime bikeshedding topic instead of going outside and re-evaluating your life friends!
• javajosh 3y ago
  I've been doing ORM on Java since Hibernate was new, and it has always sucked. One of the selling points, which is now understood to be garbage, is that you can use different databases. But no-one uses different databases. Another selling point which is "you don't need to know SQL", is also garbage. Every non-trivial long-lived application will require tweaks to individual queries at the string level. The proper way to build a data layer is one query at a time, as a string, with string interpolation. The closer you are to raw JDBC the better. Oh yeah, another bad reason for ORM: to support the "Domain Model". Which is always, and I mean always, devoid of any logic. The so-called "anemic domain model" anti-pattern. How many man-hours have been wasted on ORM, XML, annotations, debugging generated SQL, and so on? It makes me cry.
  • bsuvc 3y ago
    > The proper way to build a data layer is one query at a time, as a string, with string interpolation. Surely you mean a parameterized query string and not string interpolation, which is susceptible to sql injection.
    • javajosh 3y ago
      Details. But sure. In truth the best way to do the data layer is to use stored procs with a generated binding at the application layer. This is absolutely safe from injection, and is wicked fast as well.
      • FridgeSeal 3y ago
        Having been on the team that inherited a project full on stored procedures (more than once): no thank you, not ever again. Opaque, difficult to debug, high risk to invoke, difficult to version and version-control properly. I’m sure they could be done “less worse”, but I doubt they can be done well.
        • afavour 3y ago
          Holy crap yes. This thread has triggered a long lost memory from over a decade ago where everything, absolutely everything was done through stored procedure. Such a wild, wild waste of time.
• revskill 3y ago
  ORM is the wrong name for what we see for now. Its name should be OSM - Object SQL Mapper translator. So, the main reason to use an ORM (and also the correct way), is to help translate your business logic into SQL without writing sql string.
• bsuvc 3y ago
  I use ORMs to create custom line of business apps, mostly CRUD-ish and it works fine. The ORMs I've worked with have a way to write your own queries if the need arises.
• Buttons840 3y ago
  It's common to isolate all database calls to a specific part of the code. Usually each potential database operation has its own function, such as "update_user", or "get_spending_report", etc. This is good. However, I think it's an anti-pattern to use both an ORM and still isolate your database code. It's like a double-abstraction where only one is needed. You have a write a bunch of CRUD functions and you have to use the ORM inside those functions. ORMs are complex, but they can be convenient to use, but if you're isolating all the database calls then the convenience of the ORM is wasted inside those half-baked CRUD functions. You end up getting the worst of both worlds. So I say either (1) use raw SQL isolated inside CRUD functions, or (2) use an ORM and use the ORM abstraction freely throughout your code.
  • deleted 3y ago
    [deleted]
• ketralnis 3y ago
  You don't have to listen to what other people "consider harmful" or think are "anti-patterns" or "clean code". You don't Gotta Catch 'em All the design patterns or figure out if coffeescript is the flavour of the week or plug in event-sourcing because it was on HN yestreday. You can just solve the problem you have. Nobody will know. I don't why programming is so vulnerable to these memes and false consensus and blog fad driven architecture. I have some styles I like and I use them. Some of them aren't compatible with each other so I don't use them all at the same time. I don't wait for John Carmack or Bob Martin to tell me if it's okay. You shouldn't either.
  • bodge5000 3y ago
    On the front-end you have a new javascript framework every 15 minutes, on the backend you have this. But I'm with you, I'll use what works for me until it no longer works for me
    • henry2023 3y ago
      Thankfully everyone decided that react is the final one. Pretty stable over 10+ years. The backend had a lot of this 3 years ago when everything absolutely everything needed to be k8 orchestrated dockerized Microservices
      • aniforprez 3y ago
        There's the new brouhaha over the Server Components thing but it's safe to ignore it completely when even the documentation for it is so poor
    • meowtimemania 3y ago
      Meh, it's still 80%+ react and has been for the last 5 years.
      • alex_lav 3y ago
        React Router? React Router Dom? Redux? Rematch? Jotai? MobX? Hooks? Classes? Create react app? Not create react app? Next? Gatsby? Preact?
• Waterluvian 3y ago
  Been using ORM for a decade. It’s been amazing for my use case. Absolutely amazing. Tools are tools. Blanket statements about tools say a lot more about who’s saying it than about the tool. Don’t be afraid to conclude a given popular tool is wrong for your needs, or an unpopular one is right.
• nodamage 3y ago
  Seems like most people are commenting in the context of web apps but I find ORMs are most useful on the client side (mobile/desktop apps) where objects can be long lived and you want to take advantage of ORM specific patterns like identity maps (https://martinfowler.com/eaaCatalog/identityMap.html https://martinfowler.com/eaaCatalog/identityMap.html) so you don't have multiple conflicting versions of an object in memory. If you're writing a web app and just pulling data from a database and dumping it to HTML or JSON then yeah it matters way less whether those database rows get materialized into objects before getting rendered to output, because you're throwing the objects out at the end of the request anyway.
• ravenstine 3y ago
  ORMs are fine so long as they are used exactly what they are intended for, which is to make your job easier. As soon as the ORM [inevitably] gets in the way, just bypass it and write straight SQL. What drives me nuts is when I work with engineers who insist that we do all database queries through the ORM, no exceptions, just in case (TM) we someday switch from MySQL to PostgreSQL or whatever. That day has never come anywhere I've worked. It's generally a fantasy. If you're going to invest in a tool, invest in the database, not the ORM. You're more likely to save time writing complex queries in SQL than in the highly unlikely circumstance you need to suddenly switch database technologies.
  • Waterluvian 3y ago
    Yup. And I love when there’s a well-built escape hatch for this in whatever ORM you use. In Django you can just write your own SQL for a query and then say, “trust me, this query will populate this structure.” So the moment you measure performance and it’s a problem, you can just opt to DIY without having to rewrite much of anything. And of course if you need to go further, go nuts. There’s nothing locking you in.
• xwdv 3y ago
  ORM is a crutch. One of the things that has always separated good developers from great developers, is that the great developers are masters of database querying.
  • mb_72 3y ago
    In that case, I'm happy to be 'just' a good developer who has used an ORM to create a product that runs an entire company, and has been doing so reliably for 12 years and counting now. OTOH, I took over a role years ago where the 'great developer' before me left behind masses of crazily complicated and hard to understand SQL queries. As long as you can deliver reliable and maintainable software in a relatively timely manner, it doesn't matter how it's done, and clients sure don't know or care either.
    • xwdv 3y ago
      Well you’re free to have that opinion. But I wish for a day I could show you the raw power of pure SQL, you would never go back.
  • Capricorn2481 3y ago
    > One of the things that has always separated good developers from great developers, is that the great developers are masters of database querying. What an arbitrary thing to say. I guess the developers that don't interact with the database are ass.
• ElFitz 3y ago
  My favorite ORM is TypeORM. A client insisted we use that, mostly because they were already using it. Somehow, a few left joins led to ridiculous (talking tens of gigabytes of ram when the whole database itself was at most a hundred megabytes) memory leaks, blowing up the instances. Turns out, it is a known issue [0], and you’re better off joining by hand. I won’t complain though. I got a nice blog post out of it. Now, this wont’ be specific to ORMs, and any dependency could blow up your service by leaking all over the place. But so far it’s been the only one to do this to me. [0]: https://github.com/typeorm/typeorm/issues/4499#issuecomment-852649915 https://github.com/typeorm/typeorm/issues/4499#issuecomment-...
  • knocte 3y ago
    You say "known issue", and add a link to it, but that's not a link to a bug, it's a bug to a comment from someone that has implemented a workaround to some sort of problem. Then it's not clear if it's really a TypeORM bug or not (if it was, there would be clear aim from the maintainers to fix it).
    • ElFitz 3y ago
      It is a link to a specific comment on a GitHub issue [0] titled "Excessive memory usage when loading models with relations" where multiple users chime in and report facing the same issue. Someone honestly trying to refute my comment in good faith could have looked around and easily understood this. They would then have found another comment [1], mentioning a pull request [2] supposedly fixing this exact issue, with the explicit aim to fix "performance issues". If that was not a bug, then I don’t know what is. But then, I understand that this can be a lot to ask. Personally, I believe the only valid criticism that could stand here would be that, not having bothered to check if it still was an issue, I perhaps should have instead used the past tense, out of precaution, instead of assuming the bug had been fixed two to three years after being initially reported. And I shall try to remember that. So thank you, I guess. [0]: https://github.com/typeorm/typeorm/issues/4499 https://github.com/typeorm/typeorm/issues/4499 [1]: https://github.com/typeorm/typeorm/issues/4499#issuecomment-1046775521 https://github.com/typeorm/typeorm/issues/4499#issuecomment-... [2]: https://github.com/typeorm/typeorm/pull/8169 https://github.com/typeorm/typeorm/pull/8169
• gedy 3y ago
  It's interesting that in general I hear a lot of hate for ORMs, but the Rails devs I know all seem to like ActiveRecord and can't imagine working without it.
  • wilsonnb3 3y ago
    Ditto for entity framework in the .NET world, in my experience. Of course everybody has a horror story about some abomination of a query that they had to fix some time but the productivity gains are worth it.
• roblh 3y ago
  I can’t speak for all ORMs but I’ve found over the last couple years that I really like Mongoose. It’s proven to consistently be one of the least troublesome parts of our stack, and once you get to know the ins and outs, it provides a ton of useful things that are organized in a very logical way. Custom query helpers in particular were a revelation for me. Being able to reliably compose different pieces of a query to standardize how you access things across different parts of an application is really cool and incredibly useful. I didn’t choose to use Mongo, but having Mongoose has made it mostly pleasant, at least after taking the time to really understand the library.
• api 3y ago
  What really needs to exist and doesn’t is languages with first class support for direct relational queries in the language. We have functional, procedural, and OO programming languages but no relational programming languages. The fact that a database is this weird thing bolted onto your code with a bunch of glue is asinine. The data is the whole point. It should be in the center with code around it not off to the side. The database should be the runtime with the data as the center of the universe.
• elzbardico 3y ago
  Frankly, I am cynical. ORMs produce bad queries, but the SQL queries I've seen in some code bases are also nothing to write home about. If the choice is between terrible spaghetti-string-concatenation-manual-sql-from-hell and sometimes-inefficient-but-fixable-when-needed ORM code, I prefer that people stay with their ORMS.
• deleted 3y ago
  [deleted]
• jumpkick 3y ago
  SQLAlchemy is one of my favorite libraries of all time. I actively choose Python for projects because I get to use it. If you’ve never used this ORM you don’t know what you’re missing. And I say this as someone who for years actively campaigned against the pattern.
• WatchDog 3y ago
  I had an irrational hatred of ORM's for a long time until I used them for a while. They are great for CRUD applications, they simplify a lot of your application boilerplate. Yes, you still should know and learn SQL, and know how your database works, and yes for any kind of complex query you will need to reach for raw queries, but that's fine.
• post-it 3y ago
  I love ORMs. I recently wrote a little script to scrape a website and put some data in a database. I used Sequelize and it's excellent. I don't want to write an INSERT INTO blah (A, B, C, D, E, F, G, ...) VALUES (a, b, c, d, f, e, g, ...) statement where, whoops, I transposed two values! Will SQL throw a cryptic error because the types don't match, or will it silently accept my awful query because the types do match? Perhaps I'll find out after a frustrating night of querying data that doesn't seem to make sense. "But post-it, why don't you write a loop that builds a query string?" I could! But if I'm already abstracting away the misery that is SQL, I may as well use a library. "But post-it, with an ORM I can't do <X thing to speed up my query>!" I'm never going to need that. 99% of people using SQL are never going to need it. They're going to INSERT < 100 million things and then they're going to do a bunch of SELECTS and some JOINs and they won't care if the query takes 150 ms or 50 ms. And if they do, they'll optimize and write that one thing in raw SQL.
  • superasn 3y ago
    You can always insert into blah set A=a, B=b, .. in SQL too
    • psnehanshu 3y ago
      What? Never knew this.
      • 331c8c71 3y ago
        Me neither... Must be an sql dialect.
        • post-it 3y ago
          It's MySQL specific, though Postgres has a similar `INSERT INTO blah a as A, b as B` syntax apparently.
    • post-it 3y ago
      Not in SQLite I can't.
• talkingtab 3y ago
  There is a common problem - some fundamental technical problem is complex and has a steep learning curve. In comes the ORM or Library or Plugin or ... These things are all layers on top of the fundamental technology that simplifies the underlying thin. Or that provides a layer that hides the complexity of multiple implementations of a technology. So you adopt it. And you get running. Then you run into a problem with the layer. It does not implement a fundamental part. Or it has a bug, or the implementation you need is not covered. At this point you try to fix the ORM or adapt to it or fix it or something. Because you still don't know the underlying technology. I'm not saying this is a mistake. Sometimes the ORM works and sometimes it does not. It depends on how fundamental this technology is to your product and a host of other issues. BUT! It is always worthwhile to learn and understand the fundamental technology. SQL is not that hard. It is worth understanding. Even if you want and can use and ORM, not knowing SQL will hurt you. And knowing an ORM is just an extra thing to learn. I actually like SQL. I find it hard, a different way to think, but fun. I use React a lot. It helps except when it doesn't - and then being able to work in html, css and POJ saves the day. Even though I never code in assembly language (ever!) knowing how registers, CPU's and instructions work makes me a better coder. So when faced with the choice of a simpler way to do something that provides a layer over a fundamental technology, I am now extremely skeptical. Sometimes it works out, but most often it is not a good choice for me.
• jrockway 3y ago
  Someone should tell me how they think relations map to objects. At runtime, you submit a query like "SELECT foo, bar FROM baz". At compile time, you make an object that can hold foo and bar. This, unfortunately, is backwards. Runtime happens after compile time, but runtime is when you know what objects you would have needed to compile to understand the result. I have a feeling that ORMs have most of their popularity in dynamic languages where there is no type system. You get whatever the database thinks you should get today, and defensively code around that (or crash hard when a query starts returning an integer instead of a string). I feel like the maximum amount of syntax sugar you can put around a database is something like sqlx.ScanStruct. If you have a query that returns "foo int32, bar string", then sure, an API like `result := struct{ Foo int32, Bar string }; row.ScanStruct(&result)` seems fair to me. Anything more than that, I think you're just creating problems for yourself. P.S. I have no idea what the link has to do with ORMs.
• bob1029 3y ago
  My experience is that ORMs are like training wheels for fledgling teams/developers. They're a fantastic way to make sure everyone is following the same pattern/rules and that changes will more-or-less work over time without too much headache. If you have a team with developers who are actually skilled in SQL and use things like Dapper when writing code, you will actively detest the notion that the raw SQL should be abstracted away. I also believe that you are more likely to completely go off rails if constrained at domain modeling time into an ORM-compatible schema. The real world usually has some cranky things like circular dependencies and self-joins. Modeling these effectively with an ORM can be a nightmare (in my experience). If you have access to SQL, determining how to manage a circular dependency is much more obvious & direct. The effect is that you are free to model you domain in such a manner that it more closely resembles actual reality. In turn, this makes it easier to talk about and write the SQL. There is 100% a virtuous cycle here. The more you force yourself to endure the SQL, the better every aspect around it will become. You will feel the fire of a bad schema the moment you see if it you have been spending thousands of hours troubleshooting queries over bad schemas. Knowing when an arrangement of tables is "smelly" before they see any data can save you so much frustration. I think a lot of people who hide from SQL aren't hiding from SQL. They're hiding from someone else's horrible interpretation of a problem domain.
  • figassis 3y ago
    I still don't get it, none of what you mention is impossible or even difficult in many ORMs (at least none of the ones I've used), and using ORMs != being unskilled in SQL. I've had many cases where I start with the SQL, optimize it and then convert it to ORM to take advantage of not having hardcoded columns in my code for example. I've also done the reverse, converting ORM syntax into raw SQL (still sing the ORM), and I've also written ORM free apps. In my experience, using ORM makes collaboration much easier, especially if it helps take advantage of statically types languages. I think there is this obsession by engineers to signal skills/experience, where everything that looks like it may also make it easier for less experienced developers must be bad or a result of some useless trend. However, we don't need to litter our app with SQL literals to show how skilled we are. I was part of a team once where one of the developers made it a point to do this because ORMs are for losers, and surprise, most of the bugs were things like changing column names and not updating related queries, plus insufficient testing because "we're moving fast!". Even if it did not cause bugs, isn't it a lot more effort than having one place you need to change? I think we're hired to build products and solve problems, our egos should really get out of our way.
• HomeDeLaPot 3y ago
  Was the article removed? Link just takes me to the project README, which says nothing about ORMs.
• jbmsf 3y ago
  I want a few things for database support libraries: - Elimination of boilerplate to load data from a table into a type - A reasonable query mechanism that avoids SQL injection - A migration tool Every ORM I've used solves the first problem (for which I'm grateful). Some do the others. Most do way too much more. I still use an ORM in every non-trivial project because I don't want to do these things by hand. But it could be much better.
  • pilotdeveloper 3y ago
    1) I'm querying JSONb for that from postgresql and then the conversion to Ruby types is trivial. 2) Parameterized queries and you don't have SQL injection 3) The migration tool is a script of a few lines (bash in my case). It has a "migrations" table, it reads a list of migration files in a migration dir, check which ones don't exist in the migrations table and runs them in order. A migration file is just a .sql file My whole setup is a few lines of code. A very small library covering the first two, a small script for the third one.
    • jbmsf 3y ago
      I get it, but I want to use SQL. It's a lingua franca and has a lot of power for writing migrations that I'd rather use than inventing my own. That said, it's awesome to hear from someone using jsonb with migrations. Most places I've seen using jsonb push responsibility for handling different schemas onto the application, which <shudder>.
      • pilotdeveloper 3y ago
        I think I expressed myself in a confusing manner. I write _SQL_ for migrations and nothing else. What I meant was that in order to _run_ the migrations it's enough to have a simple bash script of a few lines (which basically calls psql, ls and feeds the sql migration files in order). I don't use jsonb with migrations. My migration files look like migrations/1645698230_create_orders.sql create table orders (.... ); migrations/164595623_setup_some_trigger.sql create trigger... -------- In response to "Elimination of boilerplate to load data from a table into a type" Unrelatedly to migrations, when working with postgres, I use postgres'json capabilites to get nice nested structures that I can convert to any other language easily (or even send it to the browser if serving a webpage). Instead of using tabular responses, I get json structures _directly_ from postgresql. Very often I just write SQL queries that return a single row with a single json/jsonb field that has the whole JSON response to be served to a web endpoint. Basically I write most if not all my webservice in SQL. And I just have a very thin layer of a server language to work as a proxy between a frontend application and my database application (written in SQL).
• randomNumber7 3y ago
  Isn't the root issue, that relational model is superior for the job. And everyone was using it since codd wrote this famous paper in 1970? (I'm not that old btw^^) And if you would read only the first two pages, you would see that ORM (and also NO-SQL) has some of the problems he describes in systems used prior!! to 1970!! I think 90% of users must not understand the relational model, because it's impossible, that the other models are the right tool for the job so often.
• geodel 3y ago
  In the world of Java enterprise applications it is not anti-pattern but a best practice. It is not just encouraged but often mandated by Enterprise Architects. At least in my experience of 18 years it brings the developers who are neither good in SQL nor in Java and result in stunningly crappy applications.
• darepublic 3y ago
  Im imagining a build tool to take raw SQL statements and validate them and produce typescript code from them. So ie const data = query(select * from table_a where pk = x) is a promise of the appropriate type etc. I currently use prisma and it is kind of good but can definitely be leaky and is rough around the edges. I like doing raw SQL statements but making it type safe is annoying.
• ghiculescu 3y ago
  > ORMs are an anti-pattern. They are a toy for startups, but eventually hurt more than help. Does anyone actually say this?
• fourseventy 3y ago
  I think ORMs are fine for small queries. For example: User.find_by(email: "xyz"). But as the queries get more complex ORMs fall apart and simply using raw SQL is better imo.
• burntcaramel 3y ago
  I don’t mind query builders. They help you fall into the pit of success and reuse useful queries. The thing I don’t like about ORMs is how you get back a graph of objects, and you can mutate any of them and call `.save()` on any of them. They pretend to be a live representation of what‘s in the database, but they are a proxy. And so when saving, the ORM has to sync the alterations you made to your objects with what it believes is in the database. It treats database tables as just a bag of attributes. I prefer to work with values and be explicit about what’s been read and what needs updating. If you need optimistic concurrency control be explicit about that too.
• gwbas1c 3y ago
  One thing I really like is using Entity Framework inside of LINQPad (C#) for ad-hoc sysadmin-style queries. They are much, much easier to write; and because most sysadmin-style queries aren't performance bound, it hits a great sweet spot where the ergonomics of an ORM are a win. I'd really like a universal ORM, though. I'd like to be able to write queries in a SQL-like language and have my classes automatically generated for me, no matter what programming language I'm using. I'd like to write joins in a SQL-like language and have the data structures automatically populated, no matter what programming language I'm using. I think this exposes the real flaw in ORMs and SQL: ORMs are too tied to the programming language, and SQL is too tied to its own domain. For example, if I could write a set of joins that returned structured JSON, and a JSON schema, perhaps some of the ORMs could be replaced by a simple JSON parser?
  • kkoste 3y ago
    > I'd like to be able to write queries in a SQL-like language You can do this using Entity Framework Core. var query = from obj in context.Objects join objChild in context.ObjectChildren on obj.id equals objChild.ObjId where obj.Name = "SomeName" select new {obj, objChild} var res = await query.ToListAsync() This will give you a list of anonymous objects. But you can also just have the query create non-anonymous objects.
    • shortrounddev2 3y ago
      Personally I don't prefer the SQL syntax in Linq. I like using EF Core, but with regular C# style statements
      • kkoste 3y ago
        I really think this depends on the situation. If you have an objet with foreign key relations to a lot of other objects then using lambda syntax is most likely preferred: var res = context.Objects.Include(x=> x.ChildObjects) But if you have to do joins then definitely query syntax is much clearer. For example the join above in lambda syntax looks like this: var query = await context.Objects .Join(context.ObjectChildren, obj => obj.Id, objChild => objChild.ObjId, (obj, objChild) => new { Obj = obj, ObjChild = objChild }) .Where(objAndObjChild => objAndObjChild.Obj.Name == "SomeName") .ToListAsync();
• jononomo 3y ago
  I'm working with the Django ORM and I absolutely love it. Maybe it'll bite me in the ass five years from now, but I'll have made millions by then.
• agentultra 3y ago
  It’s not an abstraction, it’s an indirection, and that is why so many people get frustrated with using them. Stick to the conventions and patterns and you’re happy: you don’t have to bother yourself with making thousands of decisions about schemas, relations, indexes, constraints, naming conventions for all of these things, patterns and so on. They wouldn’t be so frustrating if they were a proper abstraction: if they had a denotational meaning and simple, elegant theorems about them could be established. But they don’t have that: you get a layer of indirection between a parser and some structures in your code. You get an evaluator that builds strings from some definitions and expressions. Nothing about them is formalized and gall of it is convention and hand-waving. So the way each library does it is different and how it works is left as an exercise to the reader. So is it an anti-pattern? I think I agree with the article: it depends. If your data follows common, repeatable patterns then it might be best to take advantage of an ORM to generate and manage a bunch of code. They’re not bad-by-default. But I’ve been around since the 90s and people have been writing ORMs, swearing off ORMs, and writing new ORMs in waves. It’s never going to end I suspect until someone formalizes it and builds a proper abstraction by giving a denotational meaning and proving theorems (which some folks have been working on by using category theory as the underlying formalism). Until then I tend to avoid them myself as I find that many applications I work on have access patterns and structures that don’t fit the mould.
• deleted 3y ago
  [deleted]
• locusofself 3y ago
  django + postgres seems to be an outlier in terms of being high quality.
• moomoo11 3y ago
  Using an ORM is not an anti-pattern. It is a tool. For 95% of my use cases, ORM have sufficed. When it hasn't, just use SQL...
• stcroixx 3y ago
  They’re still valuable in larger enterprises where applications are maintained for decades without rewriting. I’ve seen db vendors get switched more than once in contexts like this. Code gen tools are nice, saves a ton of boilerplate. Everyman type programmers still get a lot of mileage out of them. Majority of queries HQL handles fine and you can always drop into SQL to handle the ugly ones.
• zackees 3y ago
  I use sql alchemy in all my python microservice. It’s great.
• issung 3y ago
  I purely use EFCore for managing migrations easily, almost all queries end up complicated enough or need atomicity so I write them as raw, the automatic object mapping is just a nice bonus.
• dools 3y ago
  The thing I always disliked about ActiveRecord ORMs is that you need to learn "their way" of writing SQL too much, because everything beyond the "Getting Started" tutorial is too complicated to use the built-in query building features. The thing I like about ActiveRecord ORMs is the way you can iterate over a data set. The thing I dislike about how this works in practise a lot of the time is that it takes up quite a bit of memory because the relationships are built and held as objects. Another thing I like about ActiveRecord ORMs is that I can have classes that have functionality, but get their attributes from the database. The thing I dislike about ActiveRecord ORMs is having to create a class for each of my database entities even if I don't need them (and/or having classes automatically created for them, still just as bad if you ask me). I created PluSQL, a non-ActiveRecord ORM because I prefer to write SQL most of the time, but want to minimise the amount of boilerplate, and also because I like iterating using objects but don't like holding the entire object tree in memory and only want to create classes to represent a database entity when I need one: https://github.com/iaindooley/PluSQL https://github.com/iaindooley/PluSQL
  • JohnBooty 3y ago
    The thing I always disliked about ActiveRecord ORMs is that you need to learn "their way" of writing SQL too much Yeah. I see people straining to write complex queries in their ORM and I think it's just insane. At some point you're going to have to understand and debug the generated SQL anyway. I think Rails' ActiveRecord implementation is pretty ideal because one of the explicit goals was to get out of the developer's way and allow them to write raw SQL when needed. I don't know how well other ActiveRecord implementations fare with this. I created PluSQL, a non-ActiveRecord ORM because I prefer to write SQL Cool! I'm not a PHP dev but it looks neat. Even though I just defended ActiveRecord, I kind of miss the days of thinner data mappers / ORMs.
• rukuu001 3y ago
  ORMs work great early on, but you’ll always end up fighting them.
• parentheses 3y ago
  Setup cost is non trivial. Portability is typically poor. Custom SQL or optimizing bad SQL can sometimes be very difficult to do without completely breaking open the ORM. The promises of ORMs are typically not kept. I have always found a thin wrapper around JDBC or some other SQL driver is always sufficient in avoiding the code explosion of reading from/writing to SQL databases.
• jaimex2 3y ago
  Well, theyre the old anti-pattern. You should be using the new anti pattern - GraphQL It'll keep your other anti-pattern happy. Consultants.
• ookblah 3y ago
  i feel like i'm old enough to see every new generation of people come in talking about how ORM is the devil lol. balance in everything, i maintain that ORM for 90% of the people out there is the right solution (basic or complex CRUD apps). is it the most efficient? a big no, but at most people's scale it's fine and nothing that a caching layer can't solve if you really need it. you can always drive down to the raw SQL as well. i can't tell you how many times in my career i thought i was a purist for writing raw queries or using even a query builder and ended up creating some frankenstein "ORM" in the end. if you're app has any sort of complexity involving relationships you are in for a bad time trying to cobble this shit together yourself.
• pulse7 3y ago
  People will try to reinvent the same thing over-and-over - mostly because of missing core knowledge. So the cycle will go this way: discard ORM and use native SQL queries. Make your own management of what has changed and what not. Make your own (object) relationship/collection management. Make your own caches of loaded objects. And - the most non-obvious but the real deal-breaker: deal with complex updates where MANY objects are involved. Then they will come and start praising ORM. Because they learned the core knowledge...
  • laeri 3y ago
    The problem is that there are different kinds of ORMS. One where the model is created and either the tables are generated from the model or some mapping exists. The other kind takes the database as a source of truth and from sql queries generates type safe wrappers and models. I believe most people only have experience with the first kind which has some problems but never really experienced the second kind which is very near to the database.
• wokwokwok 3y ago
  Hot take: ORM is dynamic runtime code gen. Sometimes, you need dynamic code gen and you dont want to manually do it. …but mostly, it’s a bad idea. You can’t easily debug the generated code, you can’t easily tweak the behaviour, and writing the templates for it is hard. Mostly you can replace it with a hand written or pre generated (“compiles to sql”) solution… but I’d like to point out you can’t always and if you do need dynamic SQL please, use an ORM, don’t template your own half baked solution.
• miked85 3y ago
  ORM doesn't seem like an anti-pattern in my experience; sometimes they are very useful for certain applications, other times they aren't. This applies to all tools.
• webprofusion 3y ago
  As with anything, there are situations where it works well and where it doesn't, and the tech can be easily abused or misused. ORMs (in e.g. dotnet Entity Framework), particularly when working database-first, can be a great asset. If you are working in DBs with hundreds of tables and all setup with proper foreign key relationships, an ORM can help navigate a complex schema and provide compile time sense checking when things change. Embedded SQL is a double edged swords with perceived performance benefits but with very difficult to maintain "hard coded" verbose SQL particularly for joins. I'm talking about when there are hundreds of tables, not when you have 5-10 tables. I've no ambition to convince anyone one way or the other, but don't believe everything your read on the internet about best practices especially since people may be talking about something quite different to what you're looking at. Some people will be pretty shocking developers regardless of which tech is being used.
• deleted 3y ago
  [deleted]
• beders 3y ago
  The article leaves out a few weaknesses of ORMs 1. inability to represent arbitrary query results. Results get mapped back into 'domain-objects'. Now you've removed yourself from the extreme expressiveness of the SELECT statement as well as GROUP BY, pivot tables etc. etc. The best you can hope for is domain objects that are only partially initialized with values. 2. lazy loading of attributes - certainly configurable, but if you rely on SELECT statements being generated while you are navigating a 'persistent' object, you will run into unexpected performance issues. To solve those, you then start to eagerly load domain data for certain access patterns. More annotations, more configurations and more ... stuff - where a nice SELECT query would have sufficed to get the data you need. 3. Troubleshooting is a pain. Especially if you rely on dynamically building queries. Have fun getting the actual SQL that was run and then translate back and forth between SQL and the query builder syntax to optimize it - if you can even optimize it. 4. Doesn't treat data as data. First of all data coming from SQL queries is stale the moment it arrives in your objects. Trying to pretend otherwise and having an object graph that pretends to be the living counterpart of the state in your DB is foolishness. 5. No/limited control over writes/updates. The moment you rely on your ORM to create UPDATEs and INSERTS to reflect the state of your object graph back into the DB, good luck with your chosen locking strategy and good luck optimizing those. Pessimistic locking is the best worst you can hope for. Stay away from ORMs (as in Hibernate and its ilk) and treat data as data. Use structs/records.
  • jbverschoor 3y ago
    5x simply not true
    • DandyDev 3y ago
      That is a cheap comment. Could you elaborate why you disagree with the comment you respond to?
      • isbvhodnvemrwvn 3y ago
        Not the original responder - hibernate has had projections to solve #1 for ages, the rest are on point IMO.
• cjdell 3y ago
  It took me a long time to realise this but I discovered the only thing I really needed from an ORM was a type safe query builder. Essentially a drop in replacement for trivial SQL queries, which is most of them. This article sums up the difference between old and new quite nicely. Prisma is fantastic for rapid development and type safety but doesn't require you to completely change the way you write code as it is still basically just a query generator, unlike traditional ORMs which use hidden magic to try to do everything for you with surprising consequences. https://www.prisma.io/docs/concepts/more/comparisons/prisma-and-typeorm https://www.prisma.io/docs/concepts/more/comparisons/prisma-...
  • erikpukinskis 3y ago
    I don’t think you can call Prisma a “query builder”… It introduces its own global DSL for describing relationships between tables, and then you query that. So if you need a relationship for any query, it’s now present in the model for every query. That doesn’t feel like a query builder to me. A query builder should still look like the SQL if you blur your eyes. Prisma does not.
    • sorenbs 3y ago
      A different view on this is that relationships is a property of the data, not of the query. So it makes sense to represent that in a single place instead of repeating it for every query. Now, if multiple teams have differing understandings of what relationships are present in the data, they can go ahead and use separate Prisma Schemas.
• yankput 3y ago
  In my view, ORM replace some type of bugs (SQL injections, wrong table/column names) with different type of bugs (inefficient queries, hard to debug). One of the things I really do hate about ORMs is that they usually try to be DB-agnostic, thus preventing you from actually leveraging powers of the underlying database. And that they always end up being really complex. On the other hand, I hate string manipulation (that you sometimes need to do if you do some types of queries) and in general that by dropping to SQL, you are switching to a different language, that kind of sucks (nobody sane actually enjoys SQL syntax, I think).
• nprateem 3y ago
  It never was
• daitangio 3y ago
  ORMs major selling point is solving the n+1 queries problem. They can extract the tree of objects very well. But for fast batch processing they have many drawbacks.
• monkellipse 3y ago
  “At lago we…” Hmmm… as a forced user of Lago’s product at work, I’m sorry but I stopped reading at that point. Terrible software, terrible support, enjoy your ORM hellscape I’ve flipped the bozo bit.
• truth_seeker 3y ago
  If we really wish to dig deeper, you only need an general purpose languages to host HTTP Server and perhaps HTTP client to connect to other external services. Everything else including business logic can be done in RDBMS.
• thatjoeoverthr 3y ago
  Part of the madness is domain modeling. One imagines their software’s domain model includes things like accounts and persons and try to build those as objects. This is a lie. The domain has been modeled in the database already with constraints. The domain of the backend is not to manipulate persons or accounts but rather to do things requiring secrets, like call the database. As such the actual meaningful structures are things like SQL clients, transactions, JSON decoders, situation specific POCOs and secrets.
• Scarblac 3y ago
  I love the Django ORM. It's concise, generates good queries and just works. However, there's a danger - for complex applications, you want neatly separated domain objects and data layer. Django makes it really easy to make your model your domain object, and at some point that starts to hurt. But a large part of any app is just objects backed 1-on-1 by a database table, and CRUD actions on them. It's fine. Just watch out for those parts where the separation is needed, and separate them.
  • robben1234 3y ago
    I feel that ORM might be an antipattern depending on a language. Every Java developer I interacted in my career with wanted to avoid ORMs and instead come up with a bunch of pattern-abstracted ways to write raw SQL. Every Python developer I interacted in my career with loved their ORMs. Django or SQLAlchemy.
    • Scarblac 3y ago
      Probably depends on the typical type of application, team size etc then? Or maybe Hibernate is just horrible :-) (I haven't used it)
• Mawr 3y ago
  > But after some meditation on the matter, we’ve reasoned that ORMs do not suck. They are simply an abstraction with a quintessential pro and con—they abstract away some visibility and occasionally incur some performance hits. That’s about it. This isn't an insight. Yes, everything is a tradeoff, but that doesn't mean all tradeoffs are equal. The higher the ratio of abstracted away complexity to the simplicity of an interface, the better the abstraction. For ORMs, this ratio is low. They abstract away very little and present a huge interface.
• pmontra 3y ago
  I recently discovered https://github.com/porsager/postgres https://github.com/porsager/postgres I think this is how an ORM for SQL databases should be. Unfortunately I don't use Node on the backend (Rails, Django, Phoenix) but I wish there would be ORMs like that for those frameworks. TLDR: write SQL with interpolated parameters, get the results parsed into objects.
• earthnail 3y ago
  There are fantastic ORMs out there. ActiveRecord is stellar. GRDB for Swift is fantastic. As usual it depends on your task and the ORM you choose.
• JSavageOne 3y ago
  I use TypeORM for Node.js, and it's extremely frustrating and littered with bugs, but even then it still saves me time vs. using SQL for everything. Automatic table synchronizations are a huge time saver. Even if one doesn't use an ORM, I think one will eventually end up rolling their own, so why waste that time? Anyways ORMs have query builders, so one can always write SQL queries when necessary. I just wish TypeORM's query builder was just knex because knex is far superior to the TypeORM's bug-ridden querybuilder.
• runeks 3y ago
  I use an ORM for two things, in prioritized order: 1. Composability — making it easy to compose smaller queries together into larger queries 2. Type safety — so I can use the compiler to tell me when I've made a mistake
• fedeb95 3y ago
  saying the ORMs violate SRP is like saying REST services violate SRP because you have different endpoints.
• vbezhenar 3y ago
  I don't agree to ORM being anti-pattern. I'm using Java for many years and I'm using Hibernate for many years. It's awesome and incredible productivity booster. Though ORM requires extremely high qualification, that I would admit. It's definitely not a tool for unexperienced developers. SQL is much easier to use and brings less surprises.
• ckdot2 3y ago
  There are ORMs (like Doctrine) that don't break SRP. I think ORMs have a bad reputation because of the ActiveRecord pattern. Not every ORM has to implement that pattern.
• EGreg 3y ago
  Love how the tech community keeps veering from “ORMs are awesome!” (rails era) and “blockchains are the future” (2016) to “they suck!” No, ORMs don’t suck. We used them for over a decade in our projects. There is no downside because you need middleware that writes your SQL queries for you anyway, so the app layer can reason about sharding and other data, and to avoid SQL injection. So you may as well make it object-oriented. Just make sure your ORM can handle primary keys with more than one column. What sucks is using relational databases when you could be using graph databases. Because graph databases don’t waste memory duplicating primary and foreign keys across tables, and don’t waste time doing O(log n) lookups in an index for each row, 99% of the time you can directly get the ENTIRE array of Foos that are related to a given Moo in essentially O(1). https://neo4j.com/blog/data-modeling-basics/ https://neo4j.com/blog/data-modeling-basics/ I would have used them but MySQL was ubiquitous so we went with RDBMS. Many people chose RDBMS because they were mature and very widespread. But graph databases have been catching up.
• MrDresden 3y ago
  One of the only places I've found ORMs to function well is on mobile where, usually, neither the schemas nor queries are that complex. In any other setting they have just been a limiting abstraction layer.
• BulgarianIdiot 3y ago
  The abstract concept of ORM is fine, unfortunately the particular ORMs we use suffer from pretending all objects and their relationships exist in RAM, there's a single instance of every entity and it's always up to date. An abstraction that isn't merely wrong, but falls apart in your face at every opportunity. This problem is not unique to mapping objects to SQL databases, but mapping objects to anything remote at all, say a GraphQL or a REST API. OOP as we presently interpret it is an inherently synchronous, reference (or handle, rather) based paradigm, that only works when all your state is local and in RAM. This is why distributed object protocols keep failing. They'll keep failing until OOP programming reorients to use values for messages and makes references explicit, so their impact is seen and felt (and it's especially seen and felt when you reference an entity on another machine halfway around the world, in terms of lag, fragility, eventual consistency and everything). We see hints of this with value types in Swift and .NET, unfortunately I'd say rather rudimentary so far. But it's coming. The ideal example of such a set up is Erlang. A language that Joe Armstrong has called "probably the only OOP language in the world". A statement Alan Kay agrees with (source: https://www.quora.com/What-does-Alan-Kay-think-about-Joe-Armstrong-claiming-that-Erlang-might-be-the-only-object-oriented-language-and-also-his-thesis-supervisor-s-claim-that-Erlang-is-extremely-object-oriented https://www.quora.com/What-does-Alan-Kay-think-about-Joe-Arm... ).
• tiku 3y ago
  In Symfony the ORM (Doctrine) layer has a normal relational database in the back. Performance can take a hit, but you can always build custom SQL for faster querys of larger sets. Or am I missing the point?
• rowanG077 3y ago
  I only have experience with EF from C# + LINQ and I was very, very happy using it. The query language is insanely good. Just to have statically typed queries was a total godsend. Ad-hoc projections which lead to optimized SQL. It literally has saved me days, maybe even weeks of work. Sure sometimes you need to fallback on raw SQL. But that was less then 1% of cases for me.
• bbsimonbb 3y ago
  This thread only underscores the total mindshare dominance of ORMs. The implicit alternative to ORMs in almost every comment is raw sql in string literals in app code. For 6 years now, we've had tools that let you use sql as a language, then generate the wrapper exposing your query to your app as a method. (queryfirst, pgtyped, pugsql, sqlc) This is (or could be) a paradigm shift. It's clearly, demonstrably a superior way of working, but it exists on the fringes because ORMs (and this unchanging discussion) have sucked all the oxygen.
  • kubanczyk 3y ago
    Yes, this. Also sqlc in Go. SQL is a language, an extremely high-level one at that. Trying to stitch it together using a lower-level language is a prime example of "if you have a hammer, every problem looks like a nail". An illustration. Let's say I'm coding in assembly (low-level) and I interact with a large system. The system's docs say that the input can only be formulated in Java Script (higher-level than assembly). So there's the choice: I can either import an arcane library that allows me to stitch something in pure assembly, without ever committing xxx.js file to repo. Or, I can express myself in .js files, using the power of a high-level language.
  • bruce343434 3y ago
    Which tools then?
    • laeri 3y ago
      They specifically mentioned them (queryfirst, pgtyped, pugsql, sqlc)
      • bbsimonbb 3y ago
        also zapatos, scala-slick (see type-checked sql statements) and yesql
        • bbsimonbb 3y ago
          datagrip in pycharm seems to be on a similar trip. The SQL stays in string literals, but datagrip looks inside the string literal to validate and propose auto-completion.
• liampulles 3y ago
  We have LLMs which are pretty good at SQL now, this seems like a good usecase. Rather than using ORMs, have an AI tool to generate an SQL query to join xyz objects and generate a test. You get to fine tune your SQL and speedily make queries.
• vogon_laureate 3y ago
  If you’re not migrating from one database schema to the next database hotness at least once a month, are you even alive?
• dathinab 3y ago
  Where they ever an anti-patter? I would say no. But most implementations have issues of varying degrees of servility. And the important part is understanding this issues and deciding weather or not this issues are worth it compared to the benefit a _specific_ ORM might provide.
• bartq 3y ago
  The obvious solution is to use nor ORMs or raw SQL generated with various helpers in your language, but to open up DB engine to execute low level bytecode. Think: DB=GPU, SQL=GLSL, what's actually executed -> compiled code.
• redditor98654 3y ago
  I love the DynamoDB mapper model of the ORM. It only handles object serialization and deserialization but everything else you write code as if you are working with the native DynamoDB API. I want an RDBMS ORM that does only that - translate a ResultSet to a Java POJO and convert a Java POJO to an SQL insert/update statement. I would gladly handle everything myself.
• gv83 3y ago
  all the absolutism I read in the comments speaks miles about the problem not being in ORMs (which are a tool that is very useful given the right scenario) but existing between chair and keyboard. I'd rather have an ORM than having to deal with some of the garbage code I've seen over the years (specially around many-to-many tables or relations in general) to handle the deserialization from db "manually, hand crafted, artisanal, more maintainable", specially for very basic CRUD or almost CRUD scenario where a decent ORM pulls most of the project with little hassle. Hand crafted queries have their places just like ORMs.
• figassis 3y ago
  I don’t really understand the N+1 issue. Most ORMs I’ve used make it very easy to generate the exact query you’d generate without it, with the added benefit that you define the relationships at the model level. This helps a lot when you need to make changes like renaming a field without having to worry about propagating these changes in every query and migration in your app. When you build a statement with an ORM, you can also output it and tweak it to be what you would write as raw sql. You can look at generated migrations and tweak your models until migrations are efficient, etc. if you’re generating N+1 queries I don’t know if you would do better with raw sql. Besides, most ORMs today let you switch between 3 levels, default ORM, query builder and raw sql. You also get to choose if it converts the results into your models or if it just returns raw results. Maybe I’m missing something but so far it seems like the problem with ORMs lie on not understanding when and how to use them?.
• vaughan 3y ago
  We are definitely going to look back on this era and wonder why we stuck to archaic tech for so long. It's clear that the database needs to be more integrated in our applications. SQL is not a good fit for most apps. The reporting/analytics features are great, but that's not what most of our apps are doing these days. This kind of stuff is moved off into other databases most of the time anyway. The core idea of the "relational model" is not a good fit for most apps. Interactive user interfaces are about listening to changes in single models...we don't really care about "relations". Relations are forced upon us to allow automatically optimizing query execution in the database. But this optimization doesn't take into account what other queries we run, so we have to handle caching ourselves, and completely breaks reactivity. People fight against the query planner all the time. I think the solution is to have control of the query plan. Without a database, people write code that is similar to what a database is doing, often without realizing it. Take: `users.map(user => ({user, posts: postsByUserId[user.id]})`. We have created an index, and we're doing an apparent hash join. Now this usually turns into a tangled mess because of all these adhoc hashmaps we create and pass around. You will see this kind of spaghetti all through modern web apps. The more of these adhoc transformations you have, the more complexity you have because we don't have good tools to visually trace these transformations throughout your app. Your app becomes hard to change. But it also gives us a lot more flexibility when it comes to caching query results. What would be nice is if our sql dbs were broken into layers, and we had access to the query planner so we can write reactive and cacheable queries. And also that our sql dbs could be run in the same memory space as our app code...which means running in a web browser.
  • parasubvert 3y ago
    You're making a lot of assumptions in this post. If you want to persist and query data structures, there are LOTS of non-relational databases that do that (e.g. Mongo). The "relational model being not a good fit for most apps" is a 40 year old argument, and was wrong then and wrong now, it's like saying "mathematics is not a good fit for X". In practice, fighting the query planner abstraction via query hints can be needed, but that's a pretty minor pain IMO. Inevitably for any long lived app, future requirements lead to unpredictable patterns of access or update occur and this is where the relational model has always shined.
• dagurp 3y ago
  I have inly used Django's ORM and I don't like writing queries in it but I think it's fantastic because * It generates the sql to create the DB very competently. * It creates migration scripts * Let's me use SQLite on my dev machine but something else on my server and this means that * Version control is very easy * Testing and bisecting older versions is not a problem That makes it all worth it for me.
  • Spivak 3y ago
    > Let's me use SQLite on my dev machine but something else on my server. This is my favorite feature too and forces me into better SQL hygiene because sqlite is just weird enough that supporting two databases keeps me on the beaten path.
• jillesvangurp 3y ago
  The anti pattern is not the tools but how the tools are used. ORM is abused a lot by people that don't necessarily understand databases very well. This abuse leads to overly complicated table structures based on an illusion that the ORM tries to keep up that internal domain models map 1 to 1 to the database. This is basically self inflicted pain. If you've never heard about the object impedance mismatch, that's basically the problem with modern object oriented application architectures and trying to shoehorn that into a database. Complex table structures have all sorts of issues related to complex database migrations and operational overhead related to that, additional complexity in the form of lots of joins, a need for indices to support those, all sorts of cascading behavior when things are updated or deleted, transactionality issues, and so on. That complexity tends to leak back into applications. If you know what you are doing, you can avoid all of that of course. But there are a lot of examples of people using ORMs where things get a bit messy. I've fixed a fair few projects where hibernate usage got out of hand a bit. These are not fun projects to join usually. Performance issues, flaky behavior, wonky test failures (and slow tests), etc. Usually a good way out is to cut down on the number of tables. My mantra is that if you aren't querying on it, maybe it can just be a json blob instead of a lot of silly tables and columns that need to be joined together. I like document stores. And you can easily adapt sql databases to build one that has nice transactional behavior. A few years ago I consulted a company to help them build them a search engine. They had completely over-engineered their data model, spent months obsessing on their domain model, and there were 20 or so tables and all the usual issues related to that (hard to extend, issues with complex transactions, stupidly complicated joins, etc.). Basically, I sat their tech lead down and went over the requirements: - get a thing by id - do some CRUD on things - put all the things in the search index (for each thing do X) - use the search index to find things That's it. The whole point of the system was to act as a single source of truth for the search index. So, I told them: that sounds like you need 1 table with a json blob to represent your thing. We had a few other columns for things like timestamps, userIds, etc. That vastly simplified the application architecture. We could add features without requiring a lot of database migrations. Etc. All good things.
  • DougBTX 3y ago
    For more on this approach, see docs on single table design principles: https://aws.amazon.com/blogs/compute/creating-a-single-table-design-with-amazon-dynamodb/ https://aws.amazon.com/blogs/compute/creating-a-single-table...
• valevino 3y ago
  This word brings me nightmares: EAGER
• hn_user2 3y ago
  I’m an ex-ORM advocate. In short, you are very very unlikely to ever change your database. DBs are full of useful features that an ORM hides. Instead, I now pick a DB that has the features I want and then use the hell out of the features. I remember a pre-ORM time of so many manually created db side configs, stored procs, views, triggers, etc. that a DBA would have to manage. I think these unmanageable messes were one of the catalysts to getting people on board with ORMs. To avoid going back that far. I make sure all config is part of code. Managed by migrations. Or otherwise checked into a repo and managed by a CI/CD pipeline. Now I get to use a full featured DB again. Actually use all its features. And things don’t turn into unmanageable messes.
• azlev 3y ago
  I remember the world before ORM (all queries inside data access objects) and it was not a great time to programming. While I understand that ORMs have problems, I don't want to craft all SQL by hand and make myself a SQL builder because I need some optional filters.
• bl4ckm0r3 3y ago
  I really like how Ecto does things in Elixir by separating schema and functions that modify the data.
• rbanffy 3y ago
  As a fun exercise, I suggest trying to build a mapper that gets a URL with the `type1/id1/type2/id2/type3` pattern and spits out a set of objects of type3 that have a parent of type2 and id2, under a type1 with id1. Now do that with arbitrary types and depth. With a good ORM, this is so trivial to write that, most of the time, you don’t even need to: you can rely on decorations or any similar mechanism. I have codebases that have barely any code devoted to implementing that, and that will happily build the query on the fly for any type in the model it’s allowed to use. Some problems fit ORMs really well. Some don’t, of course, and it’s fine - knowing when to use a tool and when not to is key with any tool.
• fifticon 3y ago
  I can see that people have already heaped plenty of fuel on the fire, but.. The amount of time I have wasted babysitting Microsoft's EntityFramework stuff :-/. That is stackoverflow-googling 'why does EF suddenly behave this way, and how to I get it to stop?'. In my most recent adventure, it had suddenly started to fight with some of our foreign-key delete-cascades. That is, even though EF had itself generated migrations that told SQL-Server to use delete-cascade, this would catch EF off guard, and it whould suddenly scream 'there used to be rows in this table but now they are gone!' Stackoverflowing suggested to bypass it by specifying an alternate delete-cascade policy (presumably one where db was allowed to cascade and client would pretend everything was OK.) That .. sort of worked, but now our unit-tests were failing, because they didn't like that new cascade-rule.. So now we have conditional compiles, where our unit tests compile with the rule set one way, and production code sets it the other way.. I have no idea what our tests now claim about our code, relative to production.. Hooray for ORMs. Not.
  • fifticon 3y ago
    Another favorite of mine: EF will sometimes generate constraints on your tables or columns, that can't really work. But the generated migrations are generated with drop-reversers that don't work (ie if you try to execute them, you get SQL-errors.) BUT, EF also generates random names for these constraints, so you can't just drop them by hand - you don't know what they are called, and they will have different random names on different db instances! So you (that is, I) end up having to write convoluted stored procedures, that query the db for 'what kinds of constraints do you have that satisfy these requirements, what are they called?', THEN you can remove them in an order that makes sense (instead of the autogenerated nonsense that won't execute..) Oh, and EF's code for handling its own migration-table used to be buggy; they used to have a column they later got rid of, and some of their code would fail on this column either being or not being there. My final gripe (for today) is that their migration-table doesn't include a timestamp for when the migration was executed. This has caused me headaches, because sometimes bugs in client data would depend on, whether a migration had happened early or late, since it would determine against which version of your db model client data was processed. sigh.
• nrawe 3y ago
  The issue of pattern/anti-pattern is, I think, redundant. It's a little bit like calling something an "ism" to shutdown discussion. The problem is one of use case: is using an ORM the best decision for my use case? I've seen both sides of that decision play out, and success and failure for each of those decisions. I've built ORMs in support of those decisions (1/10 would not recommend). If the problem you have in front of you is a simple CRUD use case with Fat Client then it makes a tonne of sense to use an ORM. As other people mention, you may find yourself with a non-trivial problem created by the ORM; there are well defined approaches to those problems. But, if the model you are dealing with is inherently complex, and there are a good number of business rules working across objects, consider not using an ORM and going down an abstraction layer so you have greater control over the system. It's more code; you may see some duplicate query logic here and there; IDE support isn't as good. But you get control. The big issue I have when talking with engineers is an understanding of the trade offs and taking a business-rules/domain model first approach. Often it's just "I know SQLAlchemy so that's what I'm using". One additional point of note: it is also very easy to break encapsulation with an ORM, meaning logic gets sharded between controllers, views, models, services etc. This is a personal bug bear of mine. A bad tradesperson blames their tools and all that, but it's something I see a lot, where engineers treat the objects as Records and manipulate state outside of the object boundary.
• bazoom42 3y ago
  Anyone who eschews an ORM just end up writing their own half-baked badly documented ad-hoc ORM. “Jusr use SQL” is not an alternative since you still need to map the result into a data structure.
• DrScientist 3y ago
  My main issue with ORM's is it tends to treat the persistence of data as an after thought rather than being the really important thing. The data is often much more significant that the app - in two ways - the value of the data outlives the life time of the app, and often the data has connections or uses beyond the app. ORM's tend to result in an app centric, rather than data-centric view of data persistence. Now you don't have to use it like that - but even if you design the schema and mappings yourself, you still have the problem of the ORM assuming it soley owns the access to the data - and if you turn off that assumption performance is often terrible. If I had an app with modest performance requirements, where the data and the app where one - then that's probably the ORM sweet spot. But if the data is important, has multiple uses ( and so you might want multiple views in terms of objects ), and will likely outlast the application then I'd stay well clear. SQL is already a powerful abstraction - learn it instead.
• AugustoCAS 3y ago
  It's not an antipattern and it will never be. Like many solutions there are scenarios in which using an ORM is the simplest thing to do. Also there are many different implementations of ORMs each with different features (Martin Fowler has a list from his Patterns of Enterprise Application Architecture: https://www.martinfowler.com/eaaCatalog/index.html https://www.martinfowler.com/eaaCatalog/index.html). For example in the JVM world, jOOQ and Hibernate provide very different solutions. * jOOQ provides a table data gateway (when used without records) * Hibernate: unit of Work, Lazy load, multiple inheritance strategies, Query object, Versioning, lifecycle events, and much more. Lot of the grief about Hibernate comes mainly from people who never bothered RTFM. Having said the above, if one needs flexibility and high performance (with complex queries), using vanilla SQL is invariably the best way.
• chromanoid 3y ago
  This is a classic silver bullet syndrome topic... https://www.youtube.com/watch?v=qamzvLfX-Zo https://www.youtube.com/watch?v=qamzvLfX-Zo
• wiradikusuma 3y ago
  For those who don't use ORM, how do you map DB result sets to strongly typed model and subsequently JSON?
• qudat 3y ago
  Anti-pattern is an anti-pattern.
• laeri 3y ago
  There seem to be two kinds of ORM's. One is based on the domain models and will either generate tables and columns or has some kind of mapping from domain model -> database. The second type does the inverse approach and generates domain models from an existing database. I vastly prefer the second version as the database is and should be the source of truth and generating types from the existing database as well as queries (you write sql and it generates type safe wrappers for your language). The first approach has been done quite a lot and is very error prone and there will be drift from your domain model compared to the database which can lead to subtle errors. The second approach is very flexible and you can still write SQL as you would have otherwise done but you can integrate it easily in the code as the type safe wrappers are generated automatically. Also your domain model (database -> model) is always up to date.
  • rco8786 3y ago
    I’ve used both but never experienced the issues you describe about the first approach, though your description is pretty vague so maybe I’m not understanding what you’re saying. My biggest fear with the first approach is that I make a change to my model which in turns causes some sort of unwanted change to my production database. It’s never happened to me, but I worry about it enough to steer away. My favorite ORM is still ActiveRecord, which falls somewhere in the middle of those two schemas. You define the models (tables), but the columns/types are inferred from the database structure.
• caseymarquis 3y ago
  Comparing a JavaScript ORM to a C# ORM like Entity Framework feels like comparing C macros to Lisp macros. You can't really have a reasonable conversation about ORMs without defining the feature set your ORM has. If an ORM doesn't apply type safety to SQL queries and translate typed code expressions to equivalent SQL expressions, it's not performing two of the key functions I associate with ORMs. If I didn't take these features for granted, I would probably question how useful ORMs are too! I think the reason it's so hard to have a conversation about ORMs in general is that we're often lumping radically different systems together and then making a blanket judgement. Again, it's like working with C macros, getting annoyed with them, and then judging all macro systems to be bad.
• myspy 3y ago
  Laravel Eloquent is nice to use, I've heard criticism before, but not that it is an anti-pattern.
• andix 3y ago
  No, good ORMs are awesome. As their name says, the primarily map objects and object-trees to relational database tables both ways. They provide a standardized and type safe way to do so. They often include a query builder that generated SQL. An important feature for an ORM is, that you can inspect the generated SQL and that you are also able to use them with SQL queries. Some ORMs even let you mix SQL into their generated queries. Generating code or generating database schemas from code is also a common feature, including migrations. It may satisfy your need, or it may not. So use it, or don't use it. It should be easy to change the strategy at any point. The big mistakes only happen if the developers think they don't have to know or care about SQL anymore. That's just wrong, because an ORM is just a tool to work with a SQL database. Like the Tesla Autopilot, it helps you drive in many situations, but you still need to know how to drive, supervise it all the time and correct it in many situations.
• classified 3y ago
  I don't see any reason why ORM would cease to be an anti-pattern.
• otabdeveloper4 3y ago
  No. It's needed when you're doing something high-performance and are listening to a database change stream.
• BLanen 3y ago
  This is saying that if you use your ORM as a typed query builder, its fine! But then just use a typed query builder?
• qwerty456127 3y ago
  To me using ORM instead of SQL seems like a healthy person using Stephen Hawking's speech generation device instead of just speaking.
• Arch-TK 3y ago
  So, in summary, ORMs are only bad when you use the ORM parts and not when you mostly ignore the ORM parts and only primarily use the query generator and basic abstraction parts. Well yes, that's because whenever things get hairy, you're no longer dealing with an ORM, you're dealing with the query generator. You don't have to translate your complex graph queries into their relational variants by manipulating the output of an ORM. And I have to agree that query generators have a hell of a lot more value and are way less likely cause trouble than ORMs. So yes, ORMs are no longer an anti-pattern, because most modern ORMs aren't really ORMs anymore, and when they are, they either swallow the performance hit and resulting issues when trying to compensate or don't encourage you use the ORM parts except for very specific cases, and instead encourage you to use the far more powerful query generator parts and all the random mild abstraction parts. The question left is, is it better to just learn SQL including the specific variant of SQL you're targeting, or is it better to instead learn a specific query generator and its database specific parts? Addendum: Really, at this point I don't think it matters what people use, the number of people who actually know how to design a database is insignificant compared to the people who are tasked with designing them. I am now all but convinced that the reason ORMs are popular is because they make it slightly quicker and easier to deal with a fucked up database design than having to do the same with pure SQL or a query generator.
• danielovichdk 3y ago
  I believe the underlying truth and cause one uses an ORM is the implicit mapping from tables to models/types and the near hidden exposure to the database. There is, I believe, a positive gain with the use of a slim ORM which at the same time exposes an API for querying with real SQL. Dapper/Linq/Fluentmigrator is my favorite stack for interacting with a database from system code. It's slim, fast and very near the database. Whatever the arguments are around SQL and hiding it, I believe that to be the real anti pattern. If you must interact with a database in your system, make the use of SQL as absolutely easy as possible. It's a great nd immensely powerful language which no ORM can substitute in a nice manner.
• wholinator2 3y ago
  Very interesting how the page defines every acronym _except_ ORM
• kkoste 3y ago
  I am going to play the devil's advocate in this sea of ORM-haters. First of I am an avid EF-Core developer. But I feel like I have a pretty good grasp on when I should let the datbase do the stuff the database is good at (cascading deletes). And I have experienced times where I had to use an SP because EF-Core was slow and would lock up a table or two. This article shows an example of an ORM system that makes use of raw sql to construct its final queries. I can't be the only one that thinks that that is weird. Why use an ORM that doesn't abstract from the database - that's half the point of an ORM. And I think that is the key point of ORM... Abstraction. Some of the advantages I can think of when it comes to ORM. * ORM speeds up the development time. Now you don't have to write long queries. In EF-Core for example we just use LINQ either with method or query syntax. Query syntax already looks a lot like sql.* * More tools. EF-Core Migration is great. Now I can spin up an mssql container. Quickly create a bunch of tables. Then go ahead and run integration tests.* * Because the database is abstracted. I can now run unit test on an in-memory database where I can have whatever data I want and I am not depended on queries being only compatible with MsSql.* * If anyone has ever worked with ASP. Not ASP.NET. Just ASP. Legacy technologies appears irregardless of age. When writing queries you might just try to convert a bool to a text string. Works fine on your computer. Shit's the bed on the server because the server's language is something other than English. Argueably this could have been prevented if ASP weren't just so ASP. But also an ORM could have prevented it.* * I don't have to worry about the dialect of the database I communicate with. Because the ORM takes care of that. This also incldues the formatting of my DateTime variables.* * Error handling next. I can specify in EF-Core how big many characters can be in my column. And do error handling accordingly if the amount of characters are exceeded. This is the power of code-first approach - something I believe is a strong approach to designing your database scheme.* * Last small advantage is that I can combine it with GraphQL and then I wont have to make a new endpoint for every new "edge-case" request.* Disadvantages I can think of. * Slow. Naturally when you add an abstraction layer it becomes a bit slower than if you were to use raw sql. So if you work with a a lot of data at once you have to rethink your approach. And adding GraphQL into the mix is another abstraction layer that is just going to make it slower.* * Implementing the database scheme is definitely faster using db management system rather than a code first approach.* * Merging in EF-Core is for me still a lost cause. You first have to grab all the rows to see which matches and then update this whilst inserting the rest. You can probably pay your way out of this one using BulkExtensions at the fantastic price of $1000/year. Currently I solve this using a temp table and a stored procedure.* * Duplicate and unnecessary operations has been apparent until EF-Core 7.0. Before when I had to delete or update an entity I would have to first fetch it up the database in order to track it before I can do either. This is two db-transactions that can be reduced to one in EF-Core 7.0 now. But before that was an absolutely pain.* * Similarly to previous point. Adding an entity to a database is slow and can result in deadlocks if you have a lot of inserts happening rapidly. I've cirumvented this using EFCore.BulkExtensions which does an optimized insert for each dataprovider. But using a third party library even when is free is a pain... Especially since the repo owner now have gone the way of paid licensing of the library.* Complex queries such as partion by and pivots is not possible in EF. Probably never will be. But in regards to partion by I think it should be implemented at some point mostly because I have experienced I can make a query many times faster by using it. Summary ORMs are great. Especially in REST services when your queries are small. But don't use ORMs when you have to handle a lot of big data.
• shivekkhurana 3y ago
  I hope to see a similar thread on OOP one day.
• shakura 3y ago
  I never understood the rant on ORMs. From my point of view the main problem they solve is get rid of overinflated data access objects. In big project with complex data structure in database you can get hundreds of tables and you end up with data access objects with more than hundred methods like: "getOrderById", "getOrderByUser", "getOrderByIdWithItems", "getOrdersBySupplierWithOrderItemsAndUserAddressIAmTiredOfMakingNewNamesForMethodsPleaseKillMe" ORMs elegantly get rid of most of these stuff and you and up with code like user.getOrders().with("item") or something like that. The downside of that is it becomes a bit harder to profile if some offender method is doing hundreds thousands queries to a database but still possible with a little bit of more work. There are situations when you need something more complex than "SELECT ... JOIN ... WHERE ...". For example you might want to force server to use some particular index or you use subqueries or CTEs. In that case you can use native query functionality when you pass a raw SQL query to ORM and it just populates data objects with the results of a that raw SQL. Most of ORMs have such functionality. But it's very rare when it's needed, usually you have several occasions per service if it's complicated enough.
  • airmax95rarri 3y ago
    right, ORM is great for most scale and complexity out there. lots of folks that can’t stand it have been bit by it being used for heavier analytical use cases. example: a dev writes a loop that will call a method that runs “user.PutOrders(list) when user.Name != Fred” and your database crashes because every row is scanned by that query and its running that query in a loop. often it’s really difficult to show that this query caused the issue. ORM makes it way easier to do that, but the other things it makes easier still make it worth using. except for rare scenarios unique to specific ORMs themselves, usually with problems attributed to ORM are just bad (and poorly tested) code
• AtNightWeCode 3y ago
  If the code is handwritten or not does not really matter when you are looking at the generated execution plans and so on for troubleshooting. I worked with several systems not using ORMs that still have the N+1 problem. And if a schema generator also is used it won't forget to add indexes. My main issue with ORMs is that schema changes over time becomes more and more scary. Another thing is that many libs comes with a bit of magic. A seemingly innocent change can move things from SQL to the client or the other way around. Ending up with large performance impacts. EDIT: And probably 100% of all the security related issues I have fixed in SQL would have been avoided with an ORM framework.
• zilti 3y ago
  Regarding the topic of ORM being an antipattern, I can also highly recommend this methology by Hettie Dombrovskaya and Boris Novikov: https://github.com/hettie-d/NORM/ https://github.com/hettie-d/NORM/
• 40yearoldman 3y ago
  ORMs will always be bad. They take control away and lock you into a system where fewer people are using than the underlying tech being hidden. SQL is not hard, and I today's world most people should be using indexed documents, vs trying to do inner and outer joins to save some bytes. And in the cases where all the advanced SQL stuff matters you probably are not going to want to use a ORM anyway.
• h_mirin 3y ago
  Are you all discussing web development? Then, I assumed that most web developers use ORM. So, I'm purely surprised by the number of people here who are against it. I agree that abstraction can be leaky, and it's true that you often end up writing some SQL-like code. However, isn't writing raw SQL potentially dangerous, considering aspects like security, access control etc? I want to learn your thoughts!
• shortrounddev2 3y ago
  EF Core is my favorite ORM. Some Java ones are alright, and Javascript ORMs all suck because of the lack of RTTI. I try to keep my webapps as cruddy as possible and do complicated things in application logic with transactions for atomicity. I don't like relying on SQL
• 3cats-in-a-coat 3y ago
  ORM isn't the anti-pattern, but rather the current way we do OOP. Object orientation is a pervasive, powerful paradigm, but our current approach only works for in-process, highly interlinked, synchronous objects. We need to move to OOP more similar to Erlang. Message based, where the messages are values, not references to references of references, which is the case with Java-style objects (you work with an object via a handle, which is in fact a reference to the object, allowing mutable shared state).
• mikl 3y ago
  Yes. What the author is describing with the fancy TypeScript query builder is strictly worse than plain SQL. You still have to understand how SQL works, but on top, you all so have to understand this weird DSL on top of it.
• chucke 3y ago
  Ruby sequel (http://sequel.jeremyevans.net/ http://sequel.jeremyevans.net/) is the only library where you can combine classic ORM Model bases usage, with a more raw query builder "just get me all the data into plain objects". You'll never need anything again in your career life.
• readline_prompt 3y ago
  Though not considered ORM, I enjoy using Ecto (elixir).
• paulmd 3y ago
  > ORMs produce objects. (Duh! That’s what the O stands for.) Objects are like directed graphs—nodes that point to other nodes but not necessarily to each other. Conversely, database relational tables contain data that are always linked bidirectionally via shared keys, aka an undirected graph. > The second issue is that ORMs sometimes make multiple roundtrips to a database by looping through a one-to-many or many-to-many relationship. This is known as the N+1 problem (1 original query + N subqueries). For instance, the following Prisma query will make a new database request for every single comment! I'll throw in another: combinatorial blowout between cross-joins is another problem. If you inner-join collections directly, you will end up with every possible permutation of each of the collection values. This is M x N or even M x N x O x P ... Another similar problem is the idea of having "dictionary" tables which have surrogate keys/enums/etc and you want to put the keys in other tables but have a frontend show the display value. And really the display text is not part of the object, it's part of the dictionary object. And while you can manually write a query which pulls these back/hydrates them, it's not automatic and it's not performant unless you write something manually, you are individually loading N different rows with N queries! This problem is real, but it's actually not a problem with ORMs but rather a problem with JDBC-style/row-oriented transfer paradigms. It's an impedance mismatch with the way we query the database, not with using ORMs. What programmers intuitively want is to pull over some pool of objects from a given query. And that doesn't necessarily map cleanly onto a single SQL query - in fact they may be different types of objects in different tables etc! And you can't just inner join and pull them back in a single row - because then you get combinatorial blowout if you have more than one collection. Obviously you need to define what pool of objects you want, because like the dictionary example, you could potentially keep hydrating deeper and deeper into the structure. You have the query string, but you also need to have some "hydration" string. And it turns out JSON:API has already defined a nice little standard for how to define what parts of the objects you want hydrated. https://jsonapi.org/format/#fetching-includes https://jsonapi.org/format/#fetching-includes So what shakes out of this is "JOPT" - Java Object Pool Transfer. You can probably build it on top of JDBC, just be aware that it probably involves multiple queries, one for each type of object. The ORM loads all objects of all types, puts them into a Map<keytype,MyObj> for each type of object in the pool, and then assembles the object graph using the foreign keys. You can do the data pulls relatively efficiently with temporary tables containing the pkeys that you want for that object type, that you populate for each query and then inner join against the tables. "SELECT * FROM table INNER JOIN tempTbl.pkey" will run much more efficiently than a "SELECT * FROM table WHERE pkey IN (:1, :2, :3)" because every time you want to pull a different number of objects you get a different literal SQL string which blows up the query planner. It might also be desirable to have some better SQL support for maintaining these pools as a cached result rather than having to run a separate query for each type. You could pull back the primary result and have the pools stored as an aggregation column, then turn it around and pull the other object types, but it's just clunky in general, SQL is not expressive in the ways that is ideal for object pools. It would be nice to have a "cursor" that can traverse across multiple object types, or cache the included results between multiple queries in a transaction, etc. -- The other thing is that the "collections" model is fundamentally bad in SQL too. Really this is the fundamental problem that originally drove practical adoption of NoSQL, I think. At the end of the day everyone's data is relational, the examples of "non-relational" applications ("netflix movie reviews"/IMDB isn't relational, really?) are always extremely contrived and obviously one feature-request away from being relational, that's been bullshit since the start. But it's obviously desirable to just put the IDs of the collection members into a text field/etc and that's why people like NoSQL - collections are literally so painful in SQL that people just choose to not use SQL. With JSONB being very advanced on Postgres you can probably do this quite easily, or you could use arrays, etc (I think arrays might be built on the text?). JSONB can have foreign keys, indexes, etc, and TOAST can store up to 5GB of text (or something like that) per item if you need it to. Practically speaking, I think you could build a very successful application without using a "join table" or a one-to-many schema, and the object transfer protocol will just pull those uuids for you. Postgres can dynamically un-aggregate text fields back into rows for inner join/etc too, if you want. Alternatively, you can map the one-to-many or join-table into an aggregation query that turns the actual collection table into a string_agg() function or similar. A fully general model might require these to be implemented via recursive CTEs that aggregate all of the collection object uuids into a pool for the ORM to pull back, because the relations you're filtering might be many layers deep. But these technical details are the type of dumb technical query building that ORMs are great at. Yes, you have to come up with the code that unrolls a collection text column into a couple rows, does the WHERE query, and aggregates back out, but that's just annoying to write, not technically difficult, and ORMs don't care about annoying code. -- Anyway that's what I think people want from ORMs. They want to write a query in HQL or an object-based/programmatic query builder, and just pull back a pool of objects that's hydrated according to the include-string. People don't actually care about the relations and it's actively tedious to manage that aspect of ORMs because there's so many ways for it to bite you without you doing anything that's obviously wrong. It works, especially on toy problems, it just runs like shit, or pulls back M x N permutations of collection members, etc. Having to janitor that is the biggest headache of ORMs and honestly SQL in general. It's functionally insane that we still interact with ORMs in SQL-like and row-style syntax. It's an object persistence layer, the tables are an implementation detail, the entire point is decoupling that but instead we just end up with even more complex logic to make the ORM behave. That is the incongruity that I think people are searching for here. Stop making people manually build the parts of the query that handle the administrivia to hydrate the object, and just make the querying and object mapping work properly, fix the combinatorial blowouts, etc. And do it without falling back to hammering out object requests one at a time like lazy loading tends to be. If you're pulling back records one by one, or using the dynamic/lazy-loading function for relations between objects - yeah that's gonna be super slow and you're gonna hate it. In my experience that's the #1 reason people don't like ORMs, they're not running one query, they're running 1000 queries and hammering the database hard. The lazy-loading functionality might as well not exist and tbh the default should probably be to have that getter throw a "you fucked up the query, this object hasn't been joined!" exception unless it's specifically configured to allow lazy-loading.
• marviel 3y ago
  postgres -> pg_graphl -> graphql_codegen ^ my preferred alternative to ORMs
• ShadowBanThis01 3y ago
  I’m about to launch a mobile app that requires not-very-complicated back-end support. I was planning to use Laravel & PHP. Should I shun its ORM?