20 ms·

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

• 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.
      • re-thc 3y ago
        > Opaque, difficult to debug, high risk to invoke, difficult to version and version-control properly. Traditionally, maybe. With databases like Neon (postgresql) and Planetscale (mysql) supporting easy branching / snapshot it's at least made this a lot nicer.
        • FridgeSeal 3y ago
          That might be true, but that’s like saying “now that my mechanic can repair my car for free, it’s way less worry to replace my radiator by removing it with a chainsaw”. The solution to stored procs being awful isn’t making a whole branch just to see what it does, it’s fixing the problems with stored procedures at the root level.
          • grugagag 3y ago
            Though im on the fence with stored procs and have seen complicated messes that make it depressingly no fun to work with I also had very good experiences with systems based around stored procs, not in an abusive dogmatic way. The usual answer I’m going to invoke here is that it depends how the tool is used. Any tool, methodology, philosophy can be borked in various ways, that experience traumatize people that they prefer to move on. Of course some tools are clearly worse or less useful than others but you can find some consensus if you know where to look.
      • lyu07282 3y ago
        I would add impossible to test to that list. And the postgraphile people think it's the new hot stuff smh
      • noisy_boy 3y ago
        Thanks for echoing my pain. I am paying for the bad decisions taken by a bloke who only knew SQL and used that hammer to bang on every nail he could find. Everytime someone asks why the result of the stored proc is the way it is, I need to manually run the steps one by one until I find the point where an expected row failed a condition and dropped off. And have extra fun wrapping those statements with declarations incase you are heavily using declared variables in those statements. To top it off, if you are troubleshooting in an environment where you don't have insert/update permissions, you day just became amazing. Fuck you to those who use stored procs without strong justification (like high volume data processing on database side where it totally makes sense). By version control, I assume you mean the inability to use different versions simultaneously without hacks, unlike a library built using Java where you get to pick and choose the version you want to use. Because of you mean version control of stored proc sources, that would be just like a normal git asset.
    • paulryanrogers 3y ago
      Stored procedures often increase CPU load of the DB instances, deploys can be more challenging, and security is only better if one takes care how the procedure is authorized or delegated. They can save some network round trips, and unify interfaces if there are a lot of different stacks sharing one data store. Though it's not universally better.
      • JohnBooty 3y ago
        Stored procedures often increase CPU load of the DB instances This is of course true if you're doing a bunch of computational work in your stored procedure (as opposed to just doing pure CRUD stuff) but I'm struggling to think of a real world situation where this might be the case. Can you name some examples you've encountered? (I'm not doubting you, I'm just curious)
        • paulryanrogers 3y ago
          I did some complex expiration calculations in a stored function, it cut down on round trips and kept the app simpler. Though it did cause some modestly higher CPU load. I've also seen MVs cause CPU spikes where there is a lot of SELECT massaging going on. Even without the MVs themselves certain functions like REGEXP stuff can impact performance. Language of the PL matters too, if you've got choices like Python.
          • JohnBooty 3y ago
            Sorry, MVs? I'm probably going to feel like an idiot but I'm not recognizing this acronym. Also thanks for sharing your example, much appreciated
            • noisy_boy 3y ago
              Materialised Views.
              • JohnBooty 3y ago
    • mmcclure 3y ago
      I (softly) disagree with stored procs being definitively the "best way to do the data layer." Stored procs are an extremely powerful tool to have in your belt, but lord have mercy do they have their own tradeoffs.
      • JohnMakin 3y ago
        some of the worst issues I’ve ever dealt with had one of these as a root cause
      • xarope 3y ago
        As usual there's a time and place. What I've found is that devs tend to be able to write "OKish" SQLs, but fall over when trying to optimize them, so the DB side can then take over and write optimized stored procs, which can have a separate cadence of code updates and version control compared to the backend team.
    • preseinger 3y ago
      an application (client) should be free to query a database (server) directly a stored procedure is an implicit dependency between client and server, fine as an optimization, but definitely not what you want to do by default
      • grugagag 3y ago
        For CRUD stuff if you don’t care about execution plan cache and other optimizations it could save you some time sure to query directly. For chunks of code that transactions/procesing large data directly on the server I’d reach out for stored procedures without thinking too much
        • preseinger 3y ago
          it's not like stored procedures are inherently faster than normal queries, right? as long as you're not doing anything dumb like connection-per-request, query caching should work the same
          • JohnBooty 3y ago
            it's not like stored procedures are inherently faster than normal queries, right? They're doing the same amount of "work" with regards to finding/creating/updating/deleting rows. But you (potentially) avoid shuffling all of that data back and forth between your DB server and your app. This can be an orders-of-magnitude benefit if we are describing a multistep process that would involve lots of round trips, and/or involves a lot of rows that would have to be piped over the network from the DB server to the app. Suppose that when I create an order, I want to perform some other actions. I want to update inventory levels, calculate the user's new rewards balance, blah blah blah. I could do all of that in a single stored procedure without bouncing all of that data back and forth between DB and client in a multistep process. That could matter a lot in terms of scalability, because now maybe I only have to hold that transaction lock for 20ms instead of 200ms while I make all of those round trips. There are a lot of obvious downsides to using stored procedures, but they can be very effective as well. I would not use them as a default choice for most things but they can be a valuable optimization.
    • edandersen 3y ago
      If your default stance in 2023 is that you should start with stored procedures, you are doing something very wrong and you are a risk to the survival of your business.
      • grugagag 3y ago
        Really depends on what you need, depends on size, team structure, budget, and so many other things.
      • JohnBooty 3y ago
        I'd say that any kind of absolutist "default stance" is suspect.
    • webprofusion 3y ago
      False. I've developed enormous systems fully using stored procs for data layer and it's absolutely not the "best way to do the data layer". Next you'll be saying triggers are a good idea. Yes they can be fast, and they're controlled by the DBA, that's why you'd choose them. Injection is not an issue in modern data access layers, that's a 1999 problem.
  • kentm 3y ago
    Charitably, they probably meant the process by which the parameterized query string itself is built, in the case where you need a dynamic query structure.
  • geraldwhen 3y ago
    You can’t parameterize a table name and possibly also column name in Postgres.
    • hinkley 3y ago
      Those parts are perfectly fine as string constants.
      • nhumrich 3y ago
        Depends on how dynamic your table names are
        • hinkley 3y ago
          APIs get weird when you gimp the common path in order to support relatively obscure use cases. The common things should be easy.
  • hinkley 3y ago
    I swear to god I'm going to write a SQL library some day that doesn't take strings as arguments. Although LINQ kinda already does what I'm thinking about, so maybe I just wait for someone to port it.
    • intrasight 3y ago
      The updated SqlQuery function of .NET 8 is pretty cool. It takes a string but safely interpolates the parameters. https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-cor... https://timdeschryver.dev/blog/you-can-now-return-unmapped-types-from-raw-sql-select-statements-with-entity-framework-8 https://timdeschryver.dev/blog/you-can-now-return-unmapped-t... "With the new SqlQuery method, you can now write your own SQL queries and map the result to any type that you want. This is useful for tasks that are not directly related to the day-to-day business of your application. For example, this can be used to import data, migrate data, or to retrieve data from a legacy system."
      • throwaway2990 3y ago
        Sooo it’s dapper with string interpolation override to make queries parameterized so you don’t need to manually put the parameters on as 2nd arguments?
        • intrasight 3y ago
          It's not just interpolation. The interpolation handler rewrites the query and makes it safe from SQL injection. There's an add-on to Dapper that does similar: https://github.com/mishael-o/Dapper.SimpleSqlBuilder https://github.com/mishael-o/Dapper.SimpleSqlBuilder
        • useerup 3y ago
          > Sooo it’s dapper with string interpolation override to make queries parameterized so you don’t need to manually put the parameters on as 2nd arguments? Pretty much. C# does some really cool innovations around string interpolation which proves very useful for logging, SQL query etc. You can create your own string interpolation handler, which is what the new SQL query does and what several log libraries do. So basically you can use interpolation safely and remain assured that you do not introduce SQL injection errors. Also, this way string interpolation does not prevent query plan caching, as normal string interpolation would do.
  • wernercd 3y ago
    My first thought is to scream in horror as Bobby Tables says hi to "string interpolation". complaining about evil ORMs and then just asking to get taken to the cleaners...
    • FridgeSeal 3y ago
      I really feel like a good chunk of the discussions around ORM/not ORM and such are because the core method of interacting with our databases sucks. Passing strings around is an awful interface, and maybe if we explored the space to fix that, a good chunk of these subsequent problems would be obviated.
  • kdazzle 3y ago
    Man, you know what you can really just mostly ignore when you’re using an ORM? Injection If i’m building an app or an API, I will ORM till I die (as long as it’s Django). If I need anything so much as a groupby, though, i will drop right into SQL or create an aggregate table that my ORM can just do a basic SELECT from
    • jkaplowitz 3y ago
      If you’re just worried about injection, you just need to use bound parameters instead of string interpolation. Boom, avoided at the database driver level even with plain sql. I admit though that some cases of string interpolation can be harder to catch in your code than when using ORMs.
      • masklinn 3y ago
        Ah yes, the git gut method of software engineering, an endless font of teaching opportunities from being a bottomless well of fuckups. Especially unhelpful when the dynamic sql is legit.
        • jkaplowitz 3y ago
          I didn’t say you had to be magic about it in the way of git gut, no. One can for example hook up a static analyzer to your GitHub and configure it to complain if there are any sql calls that can’t be automatically verified as free from interpolation of potentially unsafe data unless those dynamic calls are marked with a comment annotation as having been individually verified by a human as definitely not interpolating potentially unsafe data. I suspect such an analyzer may already exist as a SaaS offering. If nobody lies about those annotations, a static analyzer can handle the common cases. If you have a human faking such annotations, whichever motivation causes them to do that would create other security issues even with the ORM. And the analyzer could even handle some kinds of dynamic sql depending on how aware it is of your programming language and type system and what markings you’re willing to apply to functions and/or data with potentially unsafe and/or safe origins. Being vague only in the service of generality here since the specifics vary so much by company. If there is no SaaS service for this, maybe one ought to exist.
  • IceSentry 3y ago
    Some libraries can use string interpolation to create parameterized queries automatically.
  • krzyk 3y ago
    Proper string interpolation is not (see JEP 430).
• lmm 3y ago
  > 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. This is bullshit, IME. I've been doing this for over 10 years, and there's not a single one of those "requires a tweak at the string level" queries that couldn't be handled better by actually spending 5 minutes reading the Hibernate documentation and doing what it says to do in that situation. But the same SQL fanboys who believe every developer ought to memorize tri-state truth tables and COBOL-style function names can somehow never be bothered to do that.
  • j16sdiz 3y ago
    Its bullshit, IME. All DBMS I have worked need some SQL level tuning to squeeze the last ~20% performance.
    • makeitdouble 3y ago
      I interpret parent's point as doing the SQL level tuning through the ORM's dedicated mechanisms, and not through a raw string. Any decent ORM has extra procedures to tweak the resulting query and inject optimizations that it can't arrive at automatically, kicking the ORM away every time there's some tweak to do is counterproductive.
  • afavour 3y ago
    > there's not a single one of those "requires a tweak at the string level" queries that couldn't be handled better by actually spending 5 minutes reading the Hibernate documentation So instead of learning and using widely applicable SQL skills you learn and use Hibernate specific skills? And that’s supposed to be a positive?
    • makeitdouble 3y ago
      That's the same debate as whether you to hand craft xml files or have a library do it for you. You'd better know in details how the format works, but going through a library will help cover most common issues you wouldn't want to deal with by hand.
• jongjong 3y ago
  I always hated ORM and saw it as an anti-pattern since I first used it many years ago for these exact reasons. I remember back then, devs would look down on me for voicing my critiques about it. There are many tools and techniques which I am forced to use today as a dev which are also anti-patterns. The unfortunate reality of our industry is that most of the popular tools which software devs are using suck and are encouraging anti-patterns. The thing about the tech industry is that there are a small number of well-connected developers and ex-developers who call all the shots in this industry and they suck.
  • wernercd 3y ago
    > anti-patterns if it works and it's stupid... it's not stupid. If companies around the world can get stuff done with "anti-patterns" then, by definition, they don't "suck". I'm all for doing better and trying to improve my "craft" but I'm not going to look down on decisions that work.
    • msla 3y ago
      Leaded gasoline worked but it was so stupid it actively made people stupid.
      • jkaplowitz 3y ago
        I think you’ve just identified leaded gasoline as the next greatest AI-powered cloud native blockchain technology.
    • throwanem 3y ago
      You can drive a nail with a brick if you try hard enough. Does that make it not stupid to go looking for a brick while there's a hammer right there in your toolbag?
      • AlchemistCamp 3y ago
        A screwdriver is the tool I know best and therefore it’s the most productive thing for me to use to nail things together with in my upcoming project. I learned this strategy from HN/IH commenters on many, many posts asking about which tools are ideal for various tasks.
• stickfigure 3y ago
  As a tool for mapping query results to objects and doing crud, hibernate is fine. Certainly it is much better than writing your own hibernate on jdbc. Which you do eventually because everyone gets sick of typing the same boilerplate over and over. I think you hit a few key points though - don't try to hide the database flavor and don't use JPQL (just write native SQL).
• bottled_poe 3y ago
  I feel these complaints are going to become a non-issue when we can write the optimal SQL then ask AI to do the conversion to ORM code. Or just ask AI to write the optimal ORM code with known data/performance considerations.
  • FredPret 3y ago
    Or tell it in English what has to happen and have it output a compiled executable
  • babelfish 3y ago
    What’s the point of ORM code if you already have optimal SQL?
    • Uvix 3y ago
      It still needs to turn the query results back into objects.
      • hinkley 3y ago
        More languages are getting destructuring semantics built into them. I think this problem is slowly taking care of itself. You write a query that renames everything to your struct names, or you write a mapping that does it, then let the programming language do its thing, instead of a third party library doing it. Essentially your code for talking to the database starts to look more and more like the code that deals with JSON responses, which all just look like quasi-idiomatic code in the programming language.
    • hinkley 3y ago
      Because you can't put the latest Hype Train item on your resume without it.
• hn_throwaway_99 3y ago
  Couldn't agree with this more. The way I like to put it is "ORMs make the easy parts slightly easier, but they make the hard parts really hard". But I don't care that much about improving the easy parts, because they're already easy! Yet I can remember plenty of times when ORMs made my job a million times more difficult and all I was doing was fighting with the ORM itself (and usually at the worst time, e.g. when a query reached enough scale that it "fell over"). Query builders aren't exactly the same thing, but I love this article, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41..., and it's made me a huge fan of Slonik and similar technologies. It does 90% of what I want a query builder or ORM to do (e.g. automatic binding of results to objects, strong type checking, etc.), but it lets me just write normal SQL. I also never understood this idea that SQL is some mystically difficult language. It has warts from being old, sure, but I'd rather learn SQL than whatever custom-flavor-of-ORM-query-language some ORM tool created.
  • ttfkam 3y ago
    Agreed! First time I used the postgres Node library [0], I was sold! [0] https://github.com/porsager/postgres https://github.com/porsager/postgres
  • B-Con 3y ago
    > but I'd rather learn SQL than whatever custom-flavor-of-ORM-query-language some ORM tool created This point is worth emphasizing! When you application carries non-trivial business logic, it will create non-trivial DB access patterns. The developer will either use SQL or some other language that, pragmatically speaking, translates to SQL. But either way, they will learn some DB expression. I'd rather use a standard. Existing familiarity, easier to find docs, support, and is probably more bug-free, etc.
    • Moru 3y ago
      And since SQL has existed for so long, chances are that someone already had your problem and has posted the solution somewhere.
    • 3y ago
• dheera 3y ago
  > very non-trivial long-lived application will require tweaks to individual queries at the string level. It's possible we can have LLMs tweak queries to perfection given ORM-style input. Obviously ORMs of the past don't do this, but it's not impossible we couldn't do it in the future. For example filter(lambda user:user.username.startswith("foo"), db.users) is an anti-pattern because it fetches all of db.users and then does the filtering in Python, which is horrible. But an LLM could optimize this code to an SQL query like SELECT * FROM users where username LIKE "foo%"
  • roflyear 3y ago
    I don't think this is a good example of where ORMs fall apart. If you have devs doing the first thing you have issues. ORMs fall apart when they either do unexpected things, or "expected" but often terrible things, like lazy-loading data that causes a bagillion queries.
    • kaba0 3y ago
      Both of these things are only a matter of knowing the tool.
      • roflyear 3y ago
        It's true. I use ORMs in my projects.
  • sk0g 3y ago
    You don't need an LLM to be able to write SQL though?! Aside from attempting to shove LLM into every conversation, I'm failing to see its relevance here. You still have to learn the ORM syntax and quirks as well, since they may be neither intuitive nor consistent. Why not just focus learning efforts on the (more) global skill of SQL queries instead of the leaky abstraction that is ORMs generating SQL queries?
    • tcfhgj 3y ago
      Save time?
• srcreigh 3y ago
  Not to mention the 8 second startup time penalty to generate 150 models worth of Hibernate code at runtime (every time, with no caching...) Over the course of a month this cost me as much as 2 hours of time.
  • equalsione 3y ago
    You _might_ be able to use AppCDS[0] to tackle this issue [0] https://wiki.openjdk.org/display/HotSpot/Application+Class+Data+Sharing+-+AppCDS https://wiki.openjdk.org/display/HotSpot/Application+Class+D...
• hinkley 3y ago
  > is that you can use different databases. But no-one uses different databases. The main selling point to me was that you could write a lot of wicked fast integration tests on top of H2 and exercise your ORM. But that was already at a point where "the testing pyramid" was a diagram that some of your coworkers had seen and were trying to figure out how to explain to everybody else. So my desire to write fast backend integration tests never lined up with my job responsibilities + opinions on relative test counts.
  • catdog 3y ago
    > The main selling point to me was that you could write a lot of wicked fast integration tests on top of H2 and exercise your ORM Nowadays in the age of containers you can spin up the database you are using in production easily in a few seconds. Unless you are stuck with using one of the old commercial monsters like Oracle this is not really worth doing anymore.
    • masklinn 3y ago
      > Nowadays in the age of containers you can spin up the database you are using in production easily in a few seconds. Is that a database for ants? At $dayjob “a few seconds” isn’t even enough to send a dump over the network. Which is a moot point anyway because there’s absolutely no way random j dev will get access to all the PII that’s in the production db.
      • ragnese 3y ago
        I think the parent pretty clearly meant that you can spin up an instance of the same kind of database used in production. E.g., if you use MySQL in prod, you can spin up a local MySQL docker instance for tests rather than an in-memory not-MySQL option like H2 or SQLite.
• 0x445442 3y ago
  Yeah, I always thought iBatis was a better solution.
• aidenn0 3y ago
  Counterpoint: We are in the process of switching from Oracle to Postgres and the ORM (is definitely helping. It's otherwise a pain. We've literally written a query in SQL and asked ChatGPT to give us the equivalent in the ORM (then checked it was right, of course).
  • grugagag 3y ago
    Haha, that’s funny. I wouldn’t trust ChatGPT but it’s something I may change my mind in the future of course and would give it a go too if I had to go anywhere near oracle, to me it’s quite unapealling and am sure the project it lends itself to is also ‘fun’
    • aidenn0 3y ago
      For this use case, I don't have to trust ChatGPT, since I can just read the SQL that the ORM outputs. The project isn't terrible, it's just old. Oracle could very well have been the right choice 25+ years ago.
• jay_kyburz 3y ago
  In my view, the root of the problem is Object Oriented Programming itself. The older I get the more I like having having separate data and functions. Serialization is easier, network communication is easier, testing is easier. You can put your functions in modules grouped roughly by what kind of data they operate on, but you don't have to be creating "objects".
  • Spivak 3y ago
    > The older I get the more I like having having separate data and functions. Am I missing something or is this just OO but with func(obj) instead of obj.func()? Like the data is your instance but you have to track the types and remember what functions work on what. The coupling is just implicit now.
    • jay_kyburz 3y ago
      yes but, when you think some more about that means a lot. There is no such thing as inheritance, you route your data through different functions. There is no such thing as private data, its all public by default. You can still have strict types I guess, to make sure you are passing the right kind of data into the right function. But most of my experience coding this way is js and lua. I only code like this for my home projects, I have no idea what it would be like working in a team of 10 programmers all rushing to jam new features into a giant code base.
      • za3faran 3y ago
        You don't have to use inheritance, but it's there when you need it. Private data are also very useful.
      • lelanthran 3y ago
        > There is no such thing as private data, its all public by default Not in C, put the strict declaration in the h after and the definition in an implementation source file. Callers of the implementation functions can pass the strict around without seeing any of the fields. Only functions in the implementation can access the fields which results in all acce to the fields going through the implementation only. Cleaner and safer than letting the caller see everything while only accessing some things.
• heluser 3y ago
  Big +1 to this. I’ve been using JDBI lately and could not be happier. It is not an ORM, only a lightweight mapper. And it supports all modern libraries and paradigms (Vavr, Immutables, immutable “value classes” …)
• roflyear 3y ago
  ORMs have other selling points. Primarily, it looks like code and works with tooling designed for code. So changing a column name or type, for example, is pretty easy to refactor, which may not be true for SQL queries and may take significant testing. Additionally, ORMs allow you to easily hook into things (post-commit, etc) that is often useful, and to define custom things, like JSON serialization with custom types, without a massive amount of work. But yes, you make great points I think.
  • toomanydoubts 3y ago
    >So changing a column name or type, for example, is pretty easy to refactor, which may not be true for SQL queries and may take significant testing. You can also get that with raw sql on the right ecosystem. I once developed a simple application using hasql and hasql-th(on Haskell). Very close to raw sql strings but with type checking at compile time. Feels like you're driving a lambo.
    • roflyear 3y ago
      I'll have to check that out. Languages I use don't have that type of tooling that I'm aware of :) Have used Haskell before, but... far from extensively!
• re-thc 3y ago
  > One of the selling points, which is now understood to be garbage, is that you can use different databases. It was a major selling point back in the days. You can say that's now a legacy but it was definitely a thing pre-cloud / SAAS. Lots of software used ORM to offer multi-database support, which was required when you sell a license and users installed it on-premise. Some organizations strictly only allowed a certain brand of database. You couldn't spin up a random database of flavor in AWS, Azure or GCP. There were in-house DBAs and you were stuck with what they supported.
  • hn_throwaway_99 3y ago
    Lord I can't say enough how glad I am those days are over. Even with ORMs the RDBMS of the early/mid 00s had plenty of differences so that it was a collosal undertaking getting apps to run on multiple DBs. Sales team: We've got a huge potential client, but they require DB2. Eng team: <spends 6 months getting shit to work on the insanity that is DB2> Sales team: Damn, after all that, the potential client went with another vendor. Eng team: Please pour gasoline on us, we'll light the match.
  • threeseed 3y ago
    This idea that everyone is going to move to SaaS / Serverless has ended. With the growth in Kubernetes companies are much more willing to either (a) run platforms on-premise or (b) run them in their own cloud. In both cases you can't just force them to use your choice in database.
• hcayless 3y ago
  My favorite thing about Hibernate is that it’s sophisticated enough that it has its own query language, which is very, very similar but not identical to SQL. Makes me want to take a drink just thinking about it.
• danmur 3y ago
  I've used ORMs quite a bit and I agree with you, but the advantage for me has been getting the database objects into your application, and to a lesser degree good ORMs providing a schema for other code to integrate with. It's not super popular but I've been using Scala's Quill quite a bit lately. I don't think it's an ORM exactly, it's more like LINQ and automatic mapping between the database and case classes, but it's really nice and does everything I want from an ORM.
• Izkata 3y ago
  > 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. In Django world (python web framework) we do all the time: in-memory sqlite for tests for speed, anything else for the actual app.
  • fastball 3y ago
    You never use DB-specific features? We use postgres' JSONB column type which precludes using something like sqlite in tests.
    • Izkata 3y ago
      Most of our projects are mysql, and in those we don't use any db-specific features. Those all use in-memory sqlite for tests. Only in one of our projects do we use jsonb with postgres, that one does use postgres for the tests as well. Though, that project probably didn't need it - we're just sort of stuffing semi-regular data from another system into it. It would have worked just as well as a text field that was parsed as-needed, since as far as I remember we don't query it directly, only query based on other columns in that table.
      • anticodon 3y ago
        You can run tests in MySQL in memory almost as easily. database-autotests: volumes: - mysql-autotests-volume:/var/lib/mysql volumes: mysql-autotests-volume: driver_opts: type: tmpfs o: "size=2G,mode=777" device: tmpfs It's also very fast and allows to test the code on the same database, especially if you have something MySQL-specific.
        • deleted 3y ago
          [deleted]
        • Izkata 3y ago
          Not sure what that is, docker? None of the several dozen projects I'm referring to use it.
• desiderantes 3y ago
  This is why I moved to jOOQ years ago. Nice lean abstraction over SQL, to the point where it's just a typesafe SQL builder and also does the mapping to domain objects if you want. Abstracts and polyfills SQL capabilities just enough for you to be able to port your queries between DBs if desired.
  • illamint 3y ago
    I've only had a brief stint in Java in my career, but I got to learn about and use jOOQ, and I think it's such a fantastic option in this space. I'm still a diehard SQLAlchemy fan, and I'd use it in Python-land. For Go, I think sqlc is a decent option, but it's no jOOQ. I'd love jOOQ for Go.
  • jpgvm 3y ago
    jOOQ is far and away the best library in this class for any ecosystem. It's so good you would need to think long and hard not to choose the JVM for a SQL heavy application because it's just that damn good and most other library requirements are relatively interchangeable.
    • peterisdirsa 3y ago
      100% agree!
  • jmartrican 3y ago
    I'm a big fan of jOOQ. I've been able to implement the most complicated SQL expressions in jOOQ. Though there is one particular use case that it cannot do, but my memory fails me. There is an open issue in Github for it that's been open for years. Luke, the creator of jOOQ, is not able to figure out the solution to implement it. But no worries, it's still my favorite way to access my DB.
    • lukaseder 3y ago
      What, you can't just say that and not link to the issue!
• Justsignedup 3y ago
  I dislike ORMs, but good ORMs are significantly better than none at all. They act as a fairly simple layer between the database and the application, and are great for inexperienced engineers to get started with an experienced engineer helping out occasionally for performance hints. Furthermore they are a good adapter in general. Converting the way the world looks to a DB and the way the world looks to a web-app is always a time consuming effort. I would have to say that overall ORMs have saved me more hours than they wasted over the years, with AREL in ruby being pretty much 80% time savings minimum. Sure there are occasions where I just drop down to SQL, but most of the time it is just a win. It is the perfect library: Make simple things simple, and complex things possible. Which includes AMAZING debug logging in rails 7. Like game-changer style. I've used Hibernate in the past, and I feel like it probably saved me 20% of the time, but required me to learn a complex system. It did have some nice features like caching and such. The ORM itself matters, I strongly do not believe ORMs are an anti-pattern. As far as the original selling points... meh - use any database, never worth even thinking about this. If you switch DBs you will have issues. - you don't need to know sql. True, but eventually you will need to learn it. But I've seen Jr. engineers get far and use this as a learning experience. I think thinking of the problem from both the Sr. level and the Jr. level is important. I happen to have a TON of db experience and am often the guy debugging sql, even if I'm on the front-end team, but many people simply aren't there yet. Edit: To everyone saying "just let me write normal sql". I want to challenge you to think of how to extract knowledge of relationships and query patterns into a codebase without constantly repeating the same damn joins. SQL is a bad language, but it is the one we have. And so ORMs solve a lot of SQL's weaknesses. They are _terrible_ for writing custom reports, but are excellent for making structured backends. In the end, that knowledge abstraction you'd make if you wanted to abstract the knowledge of data relationships into a codebase ends up being... an ORM.
  • freetime2 3y ago
    We switched database providers and our ORM saved us a ton of effort. Sure we ran into issues due to differences between the DBs, but every single insert, update and JPQL query in our app ported over without issue. The few places where we used native SQL, on the other hand, needed to be tweaked and rewritten. If we were making heavy use of native SQL and stored procedures, it would have been an order of magnitude more time consuming. I used to be of the same opinion that swapping DB providers isn’t worth worrying about, because you’re screwed regardless. But our company was bought, and forced to make the change, and the ORM made a huge difference.
• sverhagen 3y ago
  > "you don't need to know SQL" Who is making those claims? Really? I've used Hibernate probably about as long as you have, and to me: it's fine. Is it perfect? Of course not, but if I have to make the trade-off between Hibernate (or nowadays Spring Data) or rolling my own abstraction to interact with the database, for any kind of non-trivial application, I would not roll my own. When you roll your own, you know what you're doing: still ORM! And like with many things: date and time zone math, encryption, writing databases, and ORM, if you can pull in a specialized tool, you can focus on your core business, and don't have to become an expert in a field that's not your core business.
  • john-radio 3y ago
    This year happens to be the first year when I worked with SQL enough to say that I know my ass from a hole in the ground in the language, and my job title is Senior Backend Developer and I have 11 years in tech and 5 as a developer. My journey with SQL is basically the GIF of Colin Farrell running in circles avoiding the violent American tourist in In Bruges; I have some good qualities as a developer but diligence in learning SQL is not one of them.
    • antonvs 3y ago
      I learned SQL many many years ago using Microsoft Access. It has (or at least had, I don’t know how it’s changed) a great visual query builder which would generate and run the SQL for you. Seeing what SQL it generated, and the data it produced from the diagram I had created, was really helpful in learning interactively. Any tool that does this (well enough) should help a lot.
  • freetime2 3y ago
    It’s frustrating to see “not needing to know SQL” as supposed reason for using an ORM. If you don’t have a decent foundational knowledge of SQL and relational DBs, of course you are going to have a bad time with ORMs. ORMs can help you to write less SQL, and less boilerplate for getting data in and out of the database. But pretty soon you’re going to need to debug something, and without a solid understanding of DBs and SQL, you’re going to be hopelessly lost.
• tynpeddler 3y ago
  I've done many database migrations from db2 to MySQL. It's a thing that happens quite a lot. ORMs and Java's usage of database access layers over the underlying sql drivers vastly simplified the process.
  • RexM 3y ago
    Same, but SQL Server to MySQL (Aurora)
• seer 3y ago
  I used to agree 100% with this sentiment, as dissatisfaction with available ORMs at the time (early days of doctrine in PHP) drove me to actually write my own. Turned out an amazing exercise in why orms are hard. Anyway a few years later I was in a position to start things fresh with a new project so thought to myself, great lets try to do things right this time - so went all the way in the other direction - raw sql everywhere, with some great sql analyzer lib (https://github.com/ivank/potygen https://github.com/ivank/potygen) that would strictly type and format with prettier all the queries - kinda plugged all the possible disadvantages of raw query usage and was a breeze to work with … for me. What I learned was that ORMs have other purposes - they kinda force you to think about the data model (even if giving you fewer tools to do so) With the amount of docs and tutorials out there it allows even junior members of the team to feel confident about building the system. I’m pretty used to sql, and thinking in it and its abstractions is easy for me, but its a skill a lot of modern devs have not acquired with all of our document dbs and orms so it was really hard on them to switch from thinking in objects and the few ways orms allows you to link them, to thinking in tables and the vast amounts of operations and dependencies you can build with them. Indexable json fields, views, CTEs, window functions all that on top of the usual relation theory … it was quite a lot to learn. And the thing is while you can solve a lot of problems with raw sql, orms usually have plugins and extensions that solve common problems, things like soft delete, i18n, logs and audit, etc. Its easy even if its far from simple. With raw sql you have to deal with all that yourself, and while it can be done and done cleanly, still require intuition about performance characteristics that a lot of new devs just don’t possess yet. You need to be an sql expert to solve those in a reasonable manner while, a mid dev could easily string along a few plugins and call it a day. Would it have great performance? Probably not. Would it hold some future pitfalls because they did not understand the underlying sql? Absolutely! But hay it will work, at least for a while. And to be fair they would easily do those mistakes with raw sql as well, but with far few resources to understand why it would fail, because orms fail in predictable ways and there is usually tons of relevant blog posts and such about how to fix it. It just allows for an better learning curve - learn a bit, build, fail, learn more, fix, repeat. Whereas raw sql requires a big upfront “learn” cost, while still going through the “fail” step more often than not. Now I’m trying out a fp query builder / ORM - elixir’s ecto with the hopes that it gives me the best of both worlds … time will tell.
• catdog 3y ago
  > 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. Or use a query builder if a good one exists for your language, e.g. jOOQ for the JVM. After suffering through years of Hibernate I could not have been happier.
• recroad 3y ago
  As a TypeORM user, I am abstracted away from 95% of SQL. It’s great.
• x-shadowban 3y ago
  Thanks man I really feel the same when what should be 2 joins, becomes an all day hellscape of inspecting orm emitted sql, to debug why its blizzard of exists-subqueries is absolutley jacked and why fixing that breaks other code depending on such bugs
• rileymat2 3y ago
  “ One of the selling points, which is now understood to be garbage, is that you can use different databases.” Of course there are many cases where it fails, but years ago we /successfully/ used the django orm to write test fixtures that used an in memory sqlite db for quick tests with a mysql production db. It is not automatic and not free, but it is absolutely not garbage.
  • ragnese 3y ago
    SQLite behaves differently than MySQL. IMO, your ORM allowed you to write tests that were of dubious value.
    • rileymat2 3y ago
      That is irrelevant if the ORM behaves the same and db engine is an implementation detail.
• knome 3y ago
  >But no-one uses different databases I'll often use different databases, since it allows me to test everything but the database via unit testing, and I've moved projects from one backend to another with relative ease thanks to it. But I abstract it out to a class filled with database functions that create specific transactions around whatever application I'm working on needs to do. Not via an ORM.
• rendaw 3y ago
  This sounds like a rant about Hibernate, not against ORMs.
• mulmen 3y ago
  > 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. This fits in my head a lot better than an ORM would but I'm a database guy so my brain is already wired different. How do you handle CRUD here? Like if I have a dozen columns in a table and I want to update two of them is that one bespoke query? Or do you always update the full record? Or do you do some dynamic SQL with the skeleton of an update? This feels like a basic question but it's still one I don't grasp all these years later.
  • vishnugupta 3y ago
    I always make sure to update only those columns they’ve changed if I know in advance. This is to minimize my data getting polluted with bad data should a bug creep in application logic. That said, there are often times it’s difficult to known in advance. For instance user profile update which is a web form with like 10 fields all of which can be edited. Even if you can figure out exact fields that have changed to write that bespoke query will be intractable as it will lead to combinatorial explosion of update queries.
    • mulmen 3y ago
      > This is to minimize my data getting polluted with bad data should a bug creep in application logic. Yeah makes sense, seems like begging for a race condition. In Postgres it also generates vacuum churn. So if you do know that I updated a few fields how does that work? Do you run a few single-field updates in a single transaction or do you generate dynamic SQL?
      • vishnugupta 3y ago
        In the case I described I just update all the fields. Anything else is just way too tedious and big prone. I hear that ORMs come in handy in such case but then they have their own set of bigger problems as others have pointed out here. The good part though is such cases to update all the fields are very few. In 90-95% updates I exactly know which fields are getting updates and for what reasons.
• saghm 3y ago
  I'm not sure if you're speaking specifically about Hibernate, but if you are making the argument against ORMS generally, I feel like there are some counterarguments to the points you mention: > Every non-trivial long-lived application will require tweaks to individual queries at the string level I suspect that the difference in expectations here is about how many of the queries will need that, and whether the number of less sensitive queries is large enough that it's worth having less boilerplate for them even if some other queries do need to be able to drop into something lower-level. People will quibble about where this boundary is, and maybe the real disagreement is about where to drawn the line between "trivial" and "non-trivial" applications, but I don't think it's quite as obvious a conclusion that ORMS aren't useful in the general case as it sounds like you're arguing. > The proper way to build a data layer is one query at a time, as a string, with string interpolation This also isn't obvious to me. I can understand the advantages it provides, but it only lets you move the problem of conversion to native types in your language to outside the query layer, not eliminate it. I think there's a solid argument that splitting up the problem that way is better, but again, it seems more nuanced to me than there being one obvious answer. > The closer you are to raw JDBC the better How are you sure that it's impossible to provide any sort of useful abstraction here? There are plenty of other cases where using the lowest-level language possible isn't the obvious correct choice all the time; it doesn't sound like you're writing your code in raw assembly either, so there's obviously some utility in using higher-level abstractions.
• kaba0 3y ago
  > Another selling point which is "you don't need to know SQL" It has never ever been a selling point by anyone with an ounce of brain. ORMs are made for OLTP workloads, not OLAPs (one of the creators of Hibernate have also said so, but I won’t look it up now), and their primary utility is to save you from writing long and error prone insert and update queries, plus they automatically map an sql row to an object. That’s it. You will reimplement plenty of parts of it if you go the raw way, so it is again, not a zero sum game.
  • jjav 3y ago
    > > Another selling point which is "you don't need to know SQL" > It has never ever been a selling point by anyone with an ounce of brain. Every project I've been involved with that got saddled with an ORM was originally justified "so junior developers don't need to know SQL". Which is a great way to paint yourself into a very painful corner, but that's what teams do.
    • ivan_gammel 3y ago
      Sorry to hear that you encountered such teams. It tells a lot about their professionalism and experience. There are teams which do not use this kind of argumentation.
    • mejutoco 3y ago
      That sounds like Human Resources or non-technical management taking technical decisions.
    • Sakos 3y ago
      Honestly, where are they even getting junior developers that don't know some basic amount of SQL or can't learn it in an afternoon? It's such a non-issue. What a great way to ensure juniors never gain proper SQL experience/expertise, and they'll be the later senior developers too, which just propogates the problem.
    • scott_w 3y ago
      > Every project I've been involved with that got saddled with an ORM was originally justified "so junior developers don't need to know SQL". The people who made "not knowing SQL" the main reason to use an ORM should not be making the decision to use an ORM. They can only use it incorrectly. If there are ORMs that document "not needing to know SQL" as a key selling point of their ORM, they should remove those sentences because it's not. As someone with 15 years experience using ORMs, you absolutely do need to know how your database works to be effective at using an ORM well. Once you do, and you understand how your ORM works, I still think the benefits of a well-integrated ORM into your codebase make it worthwhile. Generally the code becomes easier to understand, modify and reuse if you're using an industry-standard ORM. Like with most good libraries or frameworks, the number of use cases the maintainers encounter usually means you'll find something pre-prepared when you come across a new problem.
• theshrike79 3y ago
  Every non-trivial project that uses an ORM will, at one point, fetch the whole database. Either by someone accidentally creating mappings that do it without noticing or someone not understanding that there's a whole-ass database in there and they just fetch everything local and filter with a for-loop.
  • ivan_gammel 3y ago
    I have been coding on Java since v1 and wrote basic ORMs before Hibernate was a thing (yes, in EJB world). There were pretty big and complex projects in fintech and healthcare, but I have never encountered the situation you describe. Maybe I was lucky, but logically you cannot say “every non-trivial project”.
    • spyke112 3y ago
      Some years ago Entity Framework followed navigational properties (foreign keys) by default, which quickly escalates. The scenario could be a user table referencing a organisation table, so that when quering for an org, you’d get every user. Fell into this one personally, it sucked!
      • ivan_gammel 3y ago
        This must be a mistake that you do once as a junior and then never repeat. When you declare a field with collection, would not it be natural to ask how it works?
        • spyke112 3y ago
          Definitely! The problem though, is that this issue creeps up on you over time, and maybe only i production, because the performance is directly correlated to the amount of data in the database. But I guess issues like these are what transition you from junior developer into whatever comes next.
          • 3y ago
• jjav 3y ago
  > How many man-hours have been wasted on ORM, XML, annotations, debugging generated SQL, and so on? It makes me cry. Agreed. All my greatest successes with ORMs have been ripping them out of projects I've inherited. The results have been more maintainable code that's faster and less resource intensive.
• never_inline 3y ago
  What's your opinion on primarily SQL based toolkits which mainly serve to serialize / deserialize results? For example Android Room (you define DAOs with methods, and annotate each method with SQL query. `Room` takes care of deserializing results into your domain types etc..). Or Sqlc in Go: you write SQL with annotations, and sqlc will generate Go code corresponding to them. There's also libraries like JDBI which lend to less structured usage while still avoiding most boilerplate. I am optimistic that we are converging on the good parts of the ORMs without the baggage that hibernate brings.
  • zimpenfish 3y ago
    > What's your opinion on primarily SQL based toolkits which mainly serve to serialize / deserialize results? Without `crud` or some other interface, I would probably have yeeted Go into the sea for all my db-related work because `sql` is horrible to work with.
• zimpenfish 3y ago
  > But no-one uses different databases. Within the same installation, that's true. Within different installations, not necessarily. GotoSocial uses `bun` as its ORM which allows you to use the same code for a Postgres, MySQL, SQLite or MSSQL backend[1]. Which is extremely handy if you're writing something for other people to use in a system you (obviously) don't control. [1] Although I believe GTS only officially supports PG or SQLite.
• jimbob45 3y ago
  Like most 10x technologies such as ORMs, AWS, and high-level languages, the goal should be to gain the advantage they provide early on but have a plan to move toward technologies with better long-term outlooks (ideally home-grown alternatives).
• friendzis 3y ago
  I have always thought that ORM with overrides could be the answer. At the call site objects to be extracted/inserted are defined. The implementation checks whether there is DBA written query and executes that or tries to generate something. A fancy DAO with auto-generation if you will. If you want any sort of database maintainability you just cannot have queries concatenated from strings scattered around code, especially in environments with code hot loading. Otherwise, database migrations quickly start requiring shims for old interface. So ORMs/DAOs are absolutely necessary in any larger application just to maintain (hehe) maintainability. At this point why not have the abstraction layer auto generate queries? DBA time is much better spent optimizing those "few" queries that do matter for performance than writing thousands of straightforward CRUD queries. > Another selling point which is "you don't need to know SQL", is also garbage On one hand, programmers do not need to know SQL beyond basic data extraction techniques. DBA is going to be better than them at the job anyway, even if for the reason that DBAs have access to (and to look at) performance metrics. On the other hand, auto-generator is going to be worse than a DBA too, therefore "you don't need to know SQL" is garbage. We already have to fight SQL engine, now we have ORM layer to fight on top.
• devjab 3y ago
  > But no-one uses different databases. I've never worked in a place that didn't use different database. Maybe we're not talking about the same thing, but how are you developing new things if you don't have separate databases? Are you making changes directly on your production database while you're creating new things? How do you test things? Do you keep everything in the same database, or do you have multiple and then collect data from different APIs? Not that you need an ORM to have several or indeed different databases. I'd argue that it doesn't even make it that much easier.
  • other_herbert 3y ago
    It’s unlikely that you would be using different db vendors.. not different databases for different environments, that you absolutely should be doing … but pgsql in one and mssql in another is super unlikely
  • miahi 3y ago
    They mean database agnostic - that you can easily/magically migrate your application from a DBMS to another and the ORM will handle it for you. That usually works only for trivial databases and does not include data migration.
    • devjab 3y ago
      Ah ok. Yeah, nobody does that.
• YmiYugy 3y ago
  I don’t get how people jump from “it doesn’t work sometimes” to “never use it”. You can write raw sql when the ORM fails. But for many queries the ORM does work, so why not use it?
  • funcDropShadow 3y ago
    Because, then you have to integrate your few handwritten queries into the caching layer of your orm.
  • ragnese 3y ago
    There are several reasons in this case: You don't always know before-hand when, where, or how it's going to "fail". SQL doesn't fail. Sure, *I*, the programmer, could use either incorrectly, but that's not what I mean- I mean that the ORM library is always a bad abstraction that either hides the features of the DB you're actually using, and/or has gotchas and workarounds. If you use both, now you have to deal with integrating them both into your code in some semi-consistent way. Most (some? just me?) of us would balk if someone said they included two different HTTP request libraries into their project because one of them was more convenient most of the time, but the second one was the only one that worked for some of the requests they have to make. ORMs are an investment. They're always complex and leaky abstractions that require a significant amount of time to learn and wrestle with. If they only serve to make the easy queries a little bit easier, but you still need SQL for the actually-hard stuff, then it's hard to imagine a scenario where that investment is actually worth it.
    • Capricorn2481 3y ago
      > If you use both, now you have to deal with integrating them both into your code in some semi-consistent way I have no idea what you mean by this. What does it matter to my business logic whether a repository function got data from an ORM or from Raw SQL? In either case, I would return the same kind of data. The edge of your application, where side-effectful stuff like DB queries sits, is exactly where you don't need to do anything consistently. You're always fetching random stuff. At some point, you need to turn that into a format that your code understands. That doesn't change if you decide to only use raw SQL.
• jsksknennekf 3y ago
  And how many people were rejected in job interviews for saying exactly this? I hate this industry.
• andretti1977 3y ago
  I come from Java and moved to nodejs for backend four years ago. I now use sequelize as orm. Most of your pain is due to hibernate itself. It may be the only really enterprise level orm but it is a pain in the ass. Sequelize is extremely simpler and writing code with it is a joy compared to hibernate. When you say nobody uses different database that’s true 99% of the time but i worked with a company which developed a tool that must be placed within customer infrastructure and this type of customers force you for the db choice since they have highly paid db support teams (financial sector) so they had to support multiple dbs. A year ago i had to develop a big Java application without orm (cto’s choice): i didn’t remember how tedious, error prone and slow is development without orm!!! Never do it again! I think the best approach is to use orm for common crud tasks and add specific sql queries when things get a little bit complicated.
  • BiteCode_dev 3y ago
    Yes, starting with Hibernate as a idea of what ORM provides is terrible. ORM have plenty of flaws, but it's like buying screws on wish and saying using a screwdriver is a terrible experience.
    • bzzzt 3y ago
      Hibernate is not that bad. The problem with ORMs in the Java web space is lots of people used them as an 'extreme DRY' solution to link database 'objects' to the front-end which breaks separation of concerns and forces you to use the same model or resort to ugly hacks for further mapping. In a properly separated application it works fine.
  • ragnese 3y ago
    > A year ago i had to develop a big Java application without orm (cto’s choice): i didn’t remember how tedious, error prone and slow is development without orm!!! Never do it again! To be fair, JDBC is an awful, awful, API. In a sane language, with a sane SQL API/library, there's really no reason you shouldn't be able to just pull out a statically typed tuple (with proper handling of NULL, unlike JDBC) from a query result in one line.
• haspok 3y ago
  I used to have a similar opinion, but today I'm not so much against it. There was a project where I did not have access to the "official" database (Oracle) and was forced to develop against MySQL instead. Granted, it was a reasonably simple application, but still, most of the queries _just_worked_ on both MySQL and Oracle. I would also argue that for querying you don't need ORM (might be better off with something like Jooq), but for complex updates, ORM can be a godsend.
• pharmakom 3y ago
  I almost entirely agree. However, I think that the domain model should be immutable and devoid of logic.
• BiteCode_dev 3y ago
  You started with the worse ORM in existance. There are ORM with good pros/cons ratios out there. Python has espacially good ones: - Django comes with a clunky and poor performing one, but it's very well integrated, super practical, productive and has nice ergonomics. - Peewee gives you an ORM in a small package, which makes writing those little programs a joy when you don't need anything fancy, but feel lazy. - SQLAlchemy requires a lot more investment, but is very flexible, generates clean SQL and has extremely correct behavior. It also exposes a lower level query builder for when you don't want the OOP paradigm and wish to express idiomatic SQL behavior, but abstracted away with Python. This becomes a standard engineering decision, where you analyze the ROI.
  • heywhatupboys 3y ago
    Ruby on rails with active record is absoutly amazing. and anyone who hasn't tried it, haven't used a great ORM
    • mikrotikker 3y ago
      I love it.
    • cpursley 3y ago
      Elixir’s Ecto smokes AR all day and every day. I never want to go back.
  • winrid 3y ago
    Yeah, the productivity and performance with the Django ORM is amazing. I have trouble using any other web framework now... The thing people are missing is relations. The most important aspect of your application is how data is related to each other. A good ORM makes that easy.
    • thworp 3y ago
      +1 on that. Handling relationships like parent.child.other_children when writing your own SQL is not trivial at all.
• alserio 3y ago
  > But no-one uses different databases. I do. And I use Hibernate to abstract over mysql and oracle, since I want to keep clients that use both. You need a little more work than that, but it make it possible. Hibernate is not the technology I love the most, but it does some non trivial work like managing the unit of work, graph caching and mapping that can be useful. It is also highly prone to be used wrong by devs. You might not need hibernate, sometimes it might help. It should probably be used less than what you see in the wild, though.
• vbezhenar 3y ago
  KeyCloak is one example of many projects which support different databases. Though I'm skeptical if ORM helps that much nowadays. SQL is pretty standard nowadays if you avoid database-specific features.
• KronisLV 3y ago
  > I've been doing ORM on Java since Hibernate was new, and it has always sucked. Have you ever looked at something like myBatis? In particular, the XML mappers: https://mybatis.org/mybatis-3/dynamic-sql.html https://mybatis.org/mybatis-3/dynamic-sql.html Looking back, I actually quite liked it - you had conditionals and ability to build queries dynamically (including snippets, doing loops etc.), while still writing mostly SQL with a bit of XML DSL around it, which didn't suck as much as one might imagine. The only problem was that there was still writing some boilerplate, which I wasn't the biggest fan of. Hibernate always felt like walking across a bridge that might collapse at any moment (one eager fetch away from killing the performance, or having some obscure issue related to the entity mappings), however I liked tooling that let you point towards your database and get a local set of entities mapped automatically, even though codegen also used to have some issues occasionally (e.g. date types). That said, there's also projects like jOOQ which had a more code centric approach, although I recall it being slightly awkward to use in practice: https://www.jooq.org/ https://www.jooq.org/ (and the autocomplete killed the performance in some IDEs because of all the possible method signatures) More recently, when working on a Java project, I opted for JDBI3, which felt reasonably close to what you're describing, at the expense of not being able to build dynamic queries as easily as it was with myBatis: https://jdbi.org/ https://jdbi.org/ With the multi-line string support we have in Java now, it was rather pleasant regardless: https://blog.kronis.dev/tutorials/2-4-pidgeot-a-system-for-millions-of-documents-the-application https://blog.kronis.dev/tutorials/2-4-pidgeot-a-system-for-m... I don't think there's a silver bullet out there, everything from lightweight ORMs, to heavy ORMs like Hibernate, or even writing pure SQL has drawbacks. You just have to make the tradeoffs that will see you being successful in your particular project.
  • namero999 3y ago
    Can't vouch enough for MyBatis, using it since forever and it never let me down, or produced any sort of frustration. It strikes the perfect balance: on code level, you still work with domain objects and the db access sublimates away into single lines of code (select, update, ...), but they are backed by handcrafted queries that can leverage the full potential of the db. What MyBatis does is mapping from an arbitrary result set to your domain object, and it's _really smart_ about it (it knows how to convert most data types and perform arbitrary level of recursion/nesting of objects, or return collections). For all those things that it can't possibly do (like intermediary JSON conversions or things like that) you have an handy TypeHandler abstraction, that you can tuck away in a dedicated package for autodiscovery and doesn't pollute your code or queries. Also, you pay only 3% over raw JDBC in the average case. If you love MyBatis please donate: it can't go away.
• dikei 3y ago
  > One of the selling points, which is now understood to be garbage, is that you can use different databases Yup, and even if the ORM supports multiple databases, once you have committed to one database, it's almost impossible to switch to another due to different convention being used for data mapping.
• javier2 3y ago
  I 100% agree. My goto is «hibernate makes simple things easier and hard things harder». JDBC can be a little bare-bones so I prefer something like JDBI.
  • aembleton 3y ago
    How does hibernate make hard thing harder? Can't you just use the `@SQLSelect` or `@SQLInsert` annotation along with SQL?
    • javier2 3y ago
      I have no idea how those annotations work and how I can map them to the object I want.
• yobbo 3y ago
  All agree, but it can save time for many trivial short-lived things.
• maccard 3y ago
  Years ago, I worked on a project that used an ORM and multiple databases. An ORM made sense at that point, as setting up and running the database properly was an exercise in self restraint at the best of times. Nowadays with RDS and/or docker, there's no excuse. That said, ORM's offer one killer feature - code gen/serialisation.
• AugustoCAS 3y ago
  Sounds like you've have misused hibernate if you had an 'anaemic domain model'. The only reason to use hibernate is to have a full domain model and then let hibernate map it to the DB. I recently worked for a company that was using hibernate in the way you mention and I was surprised as I had never seen it used in that way in the ~10 years I used it. I'm not trying to defend Hibernate, but if used correctly it can be a good tool for a good number of scenarios.
• usrusr 3y ago
  This mirrors my opinion about ORM quite well, but it's missing an important angle that might help understanding why ORM made it that far in the first place: The big selling point, in hindsight, was running document-style persistence on relational. Today we happily dump JSON blobs in there and we accept that we'd have to deal with the consequences if it ever came to querying on some oddball property (chances are consequences won't even be so bad), but back then it was always relational first, and relational everything. All that busywork of spreading things like the change history of the user's favorite ice cream out to glorious relationality, stuff that will never ever be accessed outside its natural tree structure ("the document")? ORM were excellent at that. Now that we have arrived at something that I'd consider close enough to consensus that some data is fine too keep in document blobs (instead of spreading out), the value proposition of ORM is much, much smaller than it used to be.
  • ragnese 3y ago
    But weren't older ORMs especially BAD at JOINs (N+1)? The value prop would've been even worse then.
• nitnelave 3y ago
  As a side note, I actually do use an ORM to support multiple databases with LLDAP, since it's a self-hosted application that can be used with either SQlite (for the most light-weight), or PG/MySQL for a more HA-friendly setup.
• j-a-a-p 3y ago
  Worst mistake I ever made, beside of course the night when we left a fully loaded automatic rifle on the roof of our van, was a move from Tomcat + JDBC to JBoss + Hibernate. It went from fast (JDBC) and stable (Tomcat), to unstable (JBoss) and slow (Hibernate).
  • kstrauser 3y ago
    Uh, I want to hear more about this first story.
    • j-a-a-p 3y ago
      It was that period in my life with involuntary dealings with guns and ammo, called the military. I was commander of a security detail and we often went about in our region with our gear and live ammo. This particular night we had our last stop at about 4AM and went back to base. At base it appeared the UZI belonging to the driver was missing. Headlines like "Boy finds military weapon and empties it at the gas station" is a certain ticket for jail time, so I made sure my concerns resonated with the team. Luckily the driver remembered leaving the UZI on the roof, so we went back to exactly the last spot and spend a good hour searching in that neighbourhood. I think it was then about 6AM and it was getting light. We were getting desperate and I called another commander to assemble his team to help searching. At that point I figured why not replay the whole event with another gun? And so it happened: when that fell off the roof, it landed just a few meters from the UZI.
      • kstrauser 3y ago
        That’s amazing. Most of my stories involve providing healthcare to Marines.
• jefc1111 3y ago
  "But no-one uses different databases." Not true. A client wanted an on-premises instance of our product and specified SQL Server while we had only used MySQL before. Due to the ORM there were only tiny changes required to be up and running very quickly.
• prng2021 3y ago
  > But no-one uses different databases. There are tons of scenarios where this is simply not true and an ORM is a huge time saver. You could be building internal tooling that needs to be flexible enough to work with different databases. You could work for a massive multinational consulting firm like Accenture or professional services division at a company like Microsoft and build applications that any customer with a general relational database should be able to use.
  • ragnese 3y ago
    This is an honest question. Have you worked on such systems, and which ORMs have you used with multiple databases? I ask this because (of course) every database has different features, and some of the ORM stuff might work differently depending on what the underlying RDBMS actually is. For example, many ORMs have an API where updating a table row will also return the new, updated, entity. In my experience, there's usually no alternative (first class) API to update without returning the updated entity. With PostgreSQL, that's not a big deal- you've mostly just wasted a few bytes of memory allocating an object in your application that you didn't need. With MySQL, though, there's no functionality to get the row(s) that were just inserted or updated as part of the same query, so the ORM always ends up doing two queries when the underlying RDBMS is MySQL; now you're doing twice as many real database queries as you actually want/need. Another example is pretty much all of SQLite. Lots of ORMs "support" SQLite, but SQLite is sufficiently different from your MySQL, PostgreSQL, MSSQL, etc, that the documentation often ends up listing plenty of "gotchas" and caveats for what things actually work with SQLite, specifically. There are other small things, but those are the main ones I remember encountering frequently. If you're working on one of these systems, how much can you actually get away with pretending the underlying RDBMS doesn't matter?
    • prng2021 3y ago
      Yes I’ve typically used Sequelize. There are tons of small differences between databases that add up to a big and annoying waste of time. Quick example: https://www.w3schools.com/sql/sql_unique.asp https://www.w3schools.com/sql/sql_unique.asp I agree ORMs are not a silver bullet and I’ve had my share of pain with Sequelize, most recent being broken migrations specifically with MSSQL when it involved dropping a column with a Unique constraint. But all in all, it’s still been a big convenience. Part of my career has been in consulting and there, you notice lots of companies are trying to solve similar problems or automate similar business processes. For example, contract approvals that integrate Dropbox, docusign, Salesforce, and Slack. Something like Zapier may not cut it so you develop a custom app, but it requires storing state and audit records in a database. The app has to work with different databases to cater to different client’s requirements and the application itself is relatively simple. ORMs are great to use in this case.
• gofreddygo 3y ago
  I agree with this sentiment, been working against ORMs just as long. ORMs universally suck in all languages, all projects I've worked on (java, c#, python). You lose productivity, lose performance, lose visibility of what you're application code is actually doing vs. what it should. I eventually learned how every ORM was implemented differently, spit out different queries even for the simplest select queries and to tweak them is a wild trip around ORM's limitations reading decompiled ORM code. As an aside, java ORMs present the distinct displeasure of poorly thought out, incompatible opaque annotations. You gain ORM knowledge that does not apply across different ORMs and also subverted SQL knowledge and insights that are immensely useful as a developer. When my queries are simple, I don't see any reason to use it. When my queries are complex, I see strong reasons to not use it. I actively work to remove or not use ORMs in any project I've worked with. The hardest person on the team to convince is usually the newbie or the manager/lead who never has written a single SQL query.
• commandlinefan 3y ago
  Well, in ORM's defense, it auto-generates the "data transfer objects" from the schema for you, so you don't have to. You can save minutes of typing in exchange for years of painful maintenance.
• pritambarhate 3y ago
  > But no-one uses different databases. If you sell on premise self-hosted software, sometimes you actually need it. A big client will say, your software uses only PostgreSQL but our admin team can only support MySQL. Can your software use MySQL instead of PostgreSQL. Saying "yes" can help you close a contract worth hundreds of thousands of dollars.
• michaelcampbell 3y ago
  I've been developing professionally for companies for over 30 years now, and I've _once_ seen a DB changed (in the context of the above). And it required a lot of ORM re-tooling, so as you say, that promise is based on a false hypothesis, and is false even when the hypothesis holds. n=1 and all that, but yeah, I have felt this deeply.
• eru 3y ago
  > The proper way to build a data layer is one query at a time, as a string, with string interpolation. That seems like a false dilemma? Yes, ORM is garbage. But that doesn't mean you can't represent SQL in your host language by eg at least it's AST or something else more sophisticated than strings. If your host language has a rich enough type system, you can even type your tables in a way that's understood by both SQL and your host language. (But that's not too easy. Even mighty Haskell struggles a bit to give JOIN a proper generic type.)