6 ms·
Always use ORMs and then spend the next year debugging N+1 queries, bloated joins, and mysterious performance issues that only show up in prod. Migrations rand
by superasn 1y ago
Always use ORMs and then spend the next year debugging N+1 queries, bloated joins, and mysterious performance issues that only show up in prod.
Migrations randomly fail, schema changes are a nightmare, and your team forgets how SQL works.
ORMs promise to abstract the database but end up being just another layer you have to fight when things go wrong.
- martin82 1y agoSkill issue. In the hand of a good team, ORMs and migrations are an unbeatable productivity boost. Django is best in class.
- tossandthrow 1y agoFunny, I use prisma and pothos, with p99 at below 50ms - no N+1 (when it is not lower, then it is because there are sec framework and other fields that might not be mapped directly do the prisma schema)
- porridgeraisin 1y agoDoesn't prisma do many sql features like distinct... In memory?
- dattasmasher 1y agoYes, but you can use the `nativeDistinct` preview feature rely on the DB to perform the operation. You can see the related issue with more info: https://github.com/prisma/prisma/issues/23846 https://github.com/prisma/prisma/issues/23846
- amonith 1y agoThat sounds plausible in theory, but I've been developing big ol' LOB apps for more than 10 years now and it happens very very sporadically. I mean bloated joins is maybe the most common, but never near enough bloated to be an actual problem. And schema changes and migrations? With ORMs those are a breeze, what are you're on about. It's like 80% of the reason why we want to use ORMs. A data type change or a typo would be immediately caught during compilation making refactoring super easy. It's like a free test of all queries in the entire system. I assume that we're talking about decent ORMs where schema is also managed in code and a statically typed language, otherwise what's the point. We're on .NET 8+ and using EF Core.
- Tade0 1y agoA couple of years ago I had an opportunity to fill a fullstack role for the first time in several years. First thing I noticed was that I couldn't roll an SQL statement by hand even though I had a distinct memory of being able to do so in the past. I went with an ORM and eventually regretted it because it caused insurmountable performance issues. And that, to me, is the definition of a senior engineer: someone who realised that they've already forgotten some things and that their pool of knowledge is limited.
- mattmanser 1y agoORMs are absolutely fantastic at getting rid of the need for CRUD queries and then boilerplate code for translating a result set to a POCO and vide versa. They also allow you to essentially have a strongly typed database definition. It allows you to trivialise db migrations and versioning, though you must learn the idiosyncrasies. What they are not for is crafting high performance query code. It literally cannot result in insurmountable performance issues if you use it for CRUD. It's impossible because the resulting SQL is virtually identical to what you'd write natively. If you try to create complex queries with ORMs then yes, you're in for a world of hurt and only have yourself to blame. I don't really understand people who still write basic INSERT statements. To me, it's a complete waste of time and money. And why would you write such basic, fiddly, code yourself? It's a nightmare to maintain that sort of code too whenever you add more properties.
- FridgeSeal 1y agoPlenty of tools out here doing plain sql migrations with zero issues. At my day job everyone gave up on attempting to use the awkward ORM dsl to do migrations and just writes the sql. It’s easier, and faster, and about a dozen times clearer. > I don't really understand people who still write basic INSERT statements Because it’s literally 1 minute, and it’s refreshingly simple. It’s like a little treat! An after dinner mint! I jest, I’m not out here hand rolling all my stuff. I do often have semi-involved table designs that uphold quite a few constraints and “plain inserts” aren’t super common. Doing it in sql is only marginally more complex than the plain-inserts, but doing them with the ORM was nightmarish.
- Scarblac 1y agoI always see this sentiment here but I just havent experienced any of it in 14 years with the Django ORM.
- rahimnathwani 1y agoYou've never had to use .extra() ?
- DangitBobby 1y agoDjango has SQL logging so you can see what your queries will do! It's wild.
- ormsaregreat 1y agoHitting the database should be avoided in a web application, and use keys as much as possible. All heavy objects should be previously cached in disk.
- listenallyall 1y agoThat sounds like an awesome idea for a new, post-React web framework. Instead of simply packaging up an entire web SPA "application" and sending it to the client on first load, let's package the SPA app AND the entire database and send it all - eliminating the need for any server calls entirely. I like how you think!
- about3fitty 1y agoI can unironically imagine legitimate use cases for this idea. I’d wager that many DBs could fit unnoticed into the data footprint of a modern SPA load.
- listenallyall 1y agoYes, probably a lot of storefronts could package up their entire inventory database in a relatively small (comparatively) JSON file, and avoid a lot of pagination and reloads. Regardless, my comment was, of course, intended as sarcasm.
- tonyhart7 1y agowe have AI that scans for any potential query N+1 right now people forget how sql works??? people literally try to forget on how to program more and more programmer use markdown to "write" code
- pjc50 1y agoEh, nobody wants to transfer rows to DTOs by hand. My personal opinion is that ORMs are absolutely fine for read provided you periodically check query performance, which you need to do anyway. For write it's a lot murkier. It helps that EF+LINQ works so very well for me. You can even write something very close to SQL directly in C#, but I prefer the function syntax.
- pier25 1y agoYeah EF is amazing
- ormsaregreat 1y agoPro tip. Don't use Django migrations. Manage the database first and mirror it in orm later.
- rick1290 1y agoWhy? Isn't this easier to screw up the prod db?
- npteljes 1y agoThis fully matches my experience, and my conclusions as well. I'd add that I often don't get to pick whether the logic will be more on the ORM side, or on the DB side. I end up not caring either - just pick a side. Either the DB be dumb and the code be smart, or the other way around. I don't like it when both are trying to be smart - that's just extra work, and usually one of them fighting the other.
- FridgeSeal 1y agoBut think of how much time you’ll save needing to map entities to tables!!!! Better to reinvest that time trying to make the ORM do a worse job, automatically instead!!
- skinkestek 1y agoPeople love to rant about ORMs. But as someone who writes both raw SQL and uses ORMs regularly, I treat a business project that doesn’t use an ORM as a bit of a red flag. Here’s what I often see in those setups (sometimes just one or two, but usually at least one): - SQL queries strung together with user-controllable variables — wide open to SQL injection. (Not even surprised anymore when form fields go straight into the query.) - No clear separation of concerns — data access logic scattered everywhere like confetti. - Some homegrown “SQL helper” that saves you from writing SELECT *, but now makes it a puzzle to reconstruct a basic query in a database - Bonus points if the half-baked data access layer is buried under layers of “magic” and is next to impossible to find. In short: I’m not anti-SQL, but I am vary of people who think they need hand-write everything in every application including small ones with a 5 - 50 simultaneous users.
- tetha 1y agoI'd say, pure SQL gives you a higher performance ceiling and a lower performance and security floor. It's one of these features / design decisions that require diligence and discipline to use well. Which usually does not scale well beyond small team sizes. Personally, from the database-ops side, I know how to read quite a few ORMs by now and what queries they result in. I'd rather point out a missing annotation in some Spring Data Repository or suggest a better access pattern (because I've seen a lot of those, and how those are fixed) than dig through what you describe.
- reactordev 1y agoThe best is when you use an orm in standard ways throughout your project and can drop down to raw sql for edge things and performance critical sections… mmmmm. :chefs kiss:
- strken 1y agoI'm wary of people who are against query builders in addition to ORMs. I don't think it's possible to build complicated search (multiple joins, searching by aggregates, chaining conditions together) without a query builder of some sort, whether it's homegrown or imported. Better to pull in a tool when it's needed than to leave your junior devs blindly mashing SQL together by hand. On the other hand, I agree that mapping SQL results to instances of shared models is not always desirable. Why do you need to load a whole user object when you want to display someone's initials and/or profile picture? And if you're not loading the whole thing, then why should this limited data be an instance of a class with methods that let you send a password reset email or request a GDPR deletion?
- vimto 1y agoThis is a common sentiment because so many people use ORMs, and because people are using them so often they take the upsides for granted and emphasise the negatives. I've worked with devs who hated on ORMs for performance issues and opted for custom queries that in time became just as much a maintenance and performance burden as the ORM code they replaced. My suspicion is the issues, like with most tools, are a case of devs not taking the time to understand the limits and inner workings of what they're using.
- cluckindan 1y agoThere is a solution engineered specifically for avoiding N+1 queries and overfetching: GraphQL. More specifically a GraphQL-native columnar database such as Dgraph, which can leverage the query to optimize fetching and joining. Or, you could simply use a CRUD model 1:1 with your database schema and optimize top-level resolvers yourself where actually needed. Prisma can also work, but is more susceptible to N+1 if the db adapter layer does separate queries instead of joining.
- kiliancs 1y agoI like Ecto's approach in Elixir. Bring SQL to the language to handle security, and then build opt-in solutions to real problems in app-land like schema structs and changesets. Underneath, everything is simple (e.g. queries are structs, remain composable), and at the driver layer it taks full advantage of the BEAM. It's hard to find similarly mature and complete solutions. In the JS/TS world, I like where Drizzle is going, but there is an unavoidable baseline complexity level from the runtime and the type system (not to criticize type systems, but TS was not initially built with this level of sophistication in mind, and it shows in complexity, even if it is capable).
- OkayPhysicist 1y agoEcto is a gold-standard ORM, in no small part because it doesn't eat your database, nor your codebase. It lives right at the intersection, and does it's job well.
- CafeRacer 1y agoThe reason why I dislike ORMs is that you always have to learn a custom DSL and live in documentation to remember stuff. I think AI has more context than my brain. Sql does not really needs fixing. And something like sqlc provides a good middle ground between orms and pure sql.
- DangitBobby 1y agoORM hate might as well be a free square on "HN web development blog post Bingo".
- PaulHoule 1y agoYou really want something that lets you write table=db.table("table1") table.insert({"col1": val1, "col2": val2}) at the very least, if you are really writing lots of INSERTs by hand I bet you are either not quoting properly or you are writing queries with 15 placeholders and someday you'll put one in the wrong place. ORMs and related toolkits have come a long way since they were called the "Vietnam of Computer Science". I am a big fan of JooQ in Java https://www.jooq.org/ https://www.jooq.org/ and SQLAlchemy in Python https://www.sqlalchemy.org/ https://www.sqlalchemy.org/ Note both of these support both an object <-> SQL mapper (usually with generated objects) that covers the case of my code sample above, and a DSL for SQL inside the host language which is delightful if you want to do code generation to make query builders and stuff like that. I work on a very complex search interface which builds out joins, subqueries, recursive CTEs, you name it, and the code is pretty easy to maintain.
- avgDev 1y agoWhy can't one use ORM and then flag queries which are slow? This is trivial. Inspect the actual SQL query generated, and if needed modify ORM code or write a SQL query from scratch.
- at-fates-hands 1y agoJust in case: Object-relational mapping (ORM) is a key concept in the field of Database Management Systems (DBMS), addressing the bridge between the object-oriented programming approach and relational databases. ORM is critical in data interaction simplification, code optimization, and smooth blending of applications and databases. The purpose of this article is to explain ORM, covering its basic principles, benefits, and importance in modern software development.
- benoau 1y agoThe only time I've seen migrations randomly fail was when others were manually-creating views that prevented modifications to tables. Using the migrations yourself for local dev environments is a good mitigation, except for that.
- jjice 1y agoPrisma has shown me that anything is possible with an ORM. I think they may have changed this now, but at least within the last year, distincts were done IN MEMORY. They had a reason, an I'm sure it had some merit, but we found this out while tracking down an OOM. On the bright side, my co worker and I got a good joke to bring up on occasion out of it.
- lmm 1y agoWeeks of handwriting SQL queries can save you hours of profiling and adding query hints. If you want a maintainable system enforce that everything goes through the ORM. Migrations autogenerated from the ORM classes - have a check that the ORM representation and the deployed schema are in sync as part of your build. Block direct SQL access methods in your linter. Do that and maintainability is a breeze.
- sandeepkd 1y agoAt the end of day its a trade off. It would be an exception if anyone can remember their own code/customization after 3 months. ORMs or frameworks are more or less conventions which are easier to remember cause you iterate on them multiple times. They are bloated for a good reason, to be able to server much larger population than specific use cases and yes that does brings its own problems.
- mdavid626 1y agoOr just use MongoDB. No ORM needed.
- skinkestek 1y agoVery practical, like a credit card. Let's you do what you want here and now and then pay dearly for it afterwards :-)
- Too 1y agoBusiness opportunity: Invent a type system that prevents N+1 queries.