Hacker News Reader

Comment by bob1029 | Hacker News Reader

This is a big part of what makes ORMs a problem.

Writing raw SQL views/queries per MVC view in SSR arrangements is one of the most elegant and performant ways to build complex web products. Let the RDBMS do the heavy lifting with the data. There are optimizations in play you can't even recall (because there's so many) if you're using something old and enterprisey like MSSQL or Oracle. The web server should be able to directly interpolate sql result sets into corresponding <table>s, etc. without having to round trip for each row or perform additional in memory join operations.

The typical ORM implementation is the exact opposite of this - one strict object model that must be used everywhere. It's about as inflexible as you can get.

View on HN

Most ORMs will happily let you map stored procedures and views to a class, you can have as many models as you want. So your point doesn't really make sense.

The author's said nothing about ORMs. It feels like you're trying to post a personal beef about ORMs that's entirely against the "pragmatic" software design engineering the author's opining. Using ORMs to massively reduce your boiler-plate CRUD code, then using raw SQL (or raw SQL + ORM doing the column mapping) for everything else is a pragmatic design choice.

You might not like them, but using ORMs for CRUD saves a ton of boilerplate, error-prone, code. Yes, you can footgun yourself. But that's what being a senior developer is all about, using the tools you have pragmatically and not foot gunning yourself.

And it's just looking for the patterns, if you see a massive ORM query, you're probably seeing a code smell. A query that should be in raw SQL.

In Go, for example, there is a mixed approach of pgx + sqlc, which is basically a combo of the best Postgres driver + type-safe code generator (based on raw SQL).

https://brandur.org/sqlc

Even though I often use pgx only, for a new project, I would use the approach above.

I did some exploratory analysis on sqlc some time ago and I couldn't for the life of me figure out how to parametrize which column to sort-by and group-by in queries.

It is quite neat, but I don't think it actually replaces a proper ORM for when ORMs are actually useful for. That on top of all the codegen pitfalls.

I personally quite like the Prisma approach which doesn't map database data to objects, but rather just returns an array of tuples based on your query (and no lazy loading of anything ever). With typescript types being dynamically computed based on the queries. It has its own pitfalls as well (like no types when using raw queries).

The way you describe it, it would be ideal if ORMs would only handle very basic CRUD and force you to use raw sql for complex queries. But that's not reality and not how they are used, not always. In my opinion some devs take pride to do everything with their favorite ORM.

I think if an app uses 90% ORM code with the remains as raw queries, a junior is inclined to favor ORM code and is also less exposed to actually writing SQL. He is unlikely to become an SQL expert, but using SQL behind a code facade, he should become one.

And the ORM free code has massive downsides, not limited to if you add/change a column code can break at runtime, not compile time.

The negatives of not using an ORM is far worse than the negatives of not reigning in some developers who shouldn't be making complex queries.

If they don't even know how to check the SQL their complex ORM query produces, that's a training problem, not an ORM problem.

It's one of our great weaknesses as a profession, assuming everyone will figure stuff out on their own.

With an ORM your application code is your views.

You can write reusable plain functions as abstractions, returning QuerySets that allow further filters being chained onto the query, before the actual SQL is materialized and sent to the database.

The result of this doesn’t have to match the original object models you defined, it’s still possible to be flexible with group bys resulting in dictionaries.

But converting a SQL relation to a set of dictionaries already carries a lot of overhead: every cell in the resultset must be converted to a key-value pair. And the normal mechanics of vertical "slicing" a set of dictionaries is much more expensive than doing the same in a 2d relation array. So while you might want to offer a dictionary-like interface for the result set, please don't use a dictionary-like data structure.

There are valid reasons to avoid complex ORM/query result representations, but this isn’t one of them.

I have very rarely seen or even heard of the result representation data structure for an SQL query being a bottleneck. The additional time and space needed to represent a raw tabular result in a more useful way on the client are nearly always rounding errors compared by the time needed to RPC the query itself and the space taken up by the raw bytes returned. Given that, and the engineering time wasted working with (and fixing inevitable bugs in) fully tabular result data structures (arrays, bytes), this is bad advice.

Unpopular opinion. ORM by definition is the gcd of "supported databases" features. It exists only because people doesn't like the aesthetics of SQL but the cost to use them is immense.

Not unpopular. ORM hate is real. I like SQL Alchemy and Drizzle in projects for the features they give you for free (such as Alembic migrations and instant GraphQL server), but I still write SQL for most stuff.

Even in the article the solution wasn’t to abandon the ORM in favor of raw SQL but knowing how to write the code so it doesn’t have to run 100 extra queries when it doesn’t need to.

> Particularly if you’re using an ORM, beware accidentally making queries in an inner loop. That’s an easy way to turn a select id, name from table to a select id from table and a hundred select name from table where id = ?.

If your ORM is going to the DB per row you're using it wrong. N+1 queries are a performance killer. They are easy to spot in any modern APM.

Rails makes this easy to avoid. Using `find_each` batches the queries (by 1,000 records at a time by default).

Reading through the comment section on this has been interesting. Either lots of people using half baked ORMs, people who have little experience with an ORM, or both.

I mean Rails also makes it easy to accidentally nest further queries inside your `find_each` block and end up with the same problem.

Your team can have rules and patterns in place to mitigate it but I'd never say "Rails makes this easy to avoid".

This is true with any any interaction with the DB, ORM or otherwise. Regardless of the layer of abstraction you choose to operate at you still need to understand the underlying complexity.

What Rails gives you is easy to use (and understand) abstractions that enable you to directly address performance issues.

Easy is highly contextual here, because none of this is trivial.

I think the real value in frameworks like rails and Django is that it makes it easier to collaborate. When you do it from scratch people inevitably write their own abstractions and then you can't share code so easily.

>The typical ORM implementation is the exact opposite of this - one strict object model that must be used everywhere. It's about as inflexible as you can get.

I can't respond to the "typical" part as most of my experience is using EF Core, but it's far from inflexible.

Most of my read-heavy, search queries are views I've hand written that integrate with EF core. This allows me to get the benefit of raw SQL, but also be able to use LINQ to do sorting/paging/filtering.

Have you ever build a complex app like this?

In particular, have you have to do testing, security (eg. row level security), manage migrations, change management (eg. for SOC2 or other security frameworks), cache offloads (Redis, and friends), support for microservices, etc.

Comments like this give me a vibe of young developers trying out Supabase for the first time feeling like that approach can scale indefinitely.

> Comments like this give me a vibe of young developers

I don’t think so. The context is about avoiding joining in memory, which is fairly awful to do in a application, and should be avoided, along with uninformed use of ORMs, which often just add a layer of unwarranted complexity leading to things like the dreaded N+1 problem that most inexperienced Rails developers had when dealing with ActiveRecord.

If anything, what you’re talking about sounds like development hell. I can understand a database developer having to bake in support for that level of security, but developing an app that actually uses it gets you so far in the weeds that you can barely make progress trying to do normal development.

A developer with several years of experience or equivalent will have pride in developing complexity and using cool features that make them feel important.

After a developer has maybe twice that many years experience or equivalent, they may develop frameworks with the intent to make code easier to develop and manage.

And beyond that level of experience, developers just want code that’s easy to maintain and doesn’t make stupid decisions like excessive complexity. But, they know they have to let the younger devs make mistakes, because they don’t listen, so there is no choice but to watch hell burn.

Then you retire or get a different job.

I don't know what I am talking about that sounds like hell?

I am merely talking about properties of developing complex web applications that have traditionally not been easy to work with in SQL.

I am in particular not proposing any frameworks.

How can that sound like hell?

Not the person you replied to, but I have! A java project I worked on a couple years ago used a thin persistence layer called JOOQ (java library). It basically helps you safely write sql in java, without ORM abstractions. Worked just fine for our complex enterprise app.

Sql migrations? This is a solved problem: https://github.com/flyway/flyway

What about micro services? You write some terraform to provision a sql database (e.g. aws aurora) just like you would with dynamo db or similar. What does that have to do with ORMs?

What about redis? Suddenly we need an ORM to query redis, to check if a key exists in the cache before hitting our DB? That’s difficult code to write?

I’m confused reading your comment. It has “you don’t do things my way so you must be dumb and playing with toy projects” vibes.

As a previous user of alembic I was surprised that flyway's migrations only go forward by default and that reversing them is a premium feature. That's like having the luxury trim being the one with seatbelts.

it’s been a while since I used flyway. is there a better option in 2025? Just curious.

From what I can se jooq is only really type safe with pojo mappings, to what point it is an orm with an expressive query dsl.

Alternatively you use record style outputs, but that is prone to errors if positions are changed.

Regardless, even with jooq you still accept that there is a sizable application layer to take responsibility of the requirements I listed.

i guess it’s semantics, but i agree with you actually. After all ORM = object relational mapping. However it’s certainly the most lightweight ORM i’ve used in the java and c# world. With JOOQ you are in complete control of what the SQL statements look like and when those queries happen (avoids the common N + 1 risk). _Most_ ORMs i’ve seen attempt to abstract the query from the library user.

In our project we generated pojo’s in a CI pipeline, corresponding to a new flyway migration script. The pojos were pushed to a dedicated maven library. This ensured our object mappings were always up to date. And then we wrote sql almost like the old fashioned way…but with a typesafe java DSL.

I don't understand why all these problems should be easier handled with an ORM then with raw sql?

Why is it so hard to believe that well tested, typed code is better than manual string concatenation?

Before you tell me about how you just use a Query Builder/DSL and a object mapper for convenience: That's a freaking ORM!

It is a granluarity tradeoff.

With SQL you need to explicitly test all queries where the shape granularity is down to field level.

When you map data onto an object model (in the dto sense, not oop sense) you have bigger building blocks.

This gives a simpler application that is more reliable.

Obviously you need to pick a performant orm - and it seems a lot of people in these threads have been traumatized.

Personally, I run a complex application where developers freely use a graphql schema and requests are below 50ms p99 - gql in translated into joins by the orm, so we do not have any n+1 issues, etc.

The issue with GraphQL tends to be unoptimized joins instead. Is your GraphQL API available for public consumers? How do you manage them issuing inefficient queries?

I've most often seen this countered through data loaders (batched queries that are merged in code) instead of joins, or query whitelists.

While this api in particular is not publicly exposed, that would not be a concern.

The key is to hold the same schema on the database as on the graphql and use tooling that can translate a gql query into a single query.

The issue I've seen with GraphQL isn't necessarily the count of queries run, but rather the performance or said queries (i.e. most SQL queries are not performant without proper indexes for the specific use case, but GraphQL allows lots of flexibility in what queries users can run.)

Yes - one needs to ensure that the data is well indexed - that is reasonable.

But indices does not need to yield a single result. It is OK that indices reduce the result set to tens or couple of hundreds of result. That is well within the performance requirements (... of our app)

In my ears that's just neglect? You assume your ORM does the basic data mapping right and don't verify it?

> You assume your ORM does the basic data mapping right

You know, it should. There's no good reason for an ORM to ever fail at runtime due to mapping problems instead of compile time or start time. (Except, of course if you change it during the software's execution.)

Why should a raw query fail?

No? The difference is to verify it ones for the orm VS ones for every single place your query.

I have to respond here as I seemingly the depth limit is reached.

As you've mentioned graphql you probably comparing ORM in that sense to an traditional custom API with backed by raw sql. In a fair comparison both version would do the exactly same, require the same essential tests. Assuming more variations for the raw sql version is just assuming it does more or somehow does it badly in terms of architecture. Which is not a fair comparison.

The orm represents deferred organization. Ie someone else is testing mapping and query generation for you.

An example is prisma. Prisma has a team og engineers that work on optimizing query generation and provide a simple and intuitive api.

Not using an orm forces you to take over that organization and test that extra complexity that goes into you code base.

It might be merited if you get substantiel performance boosts - but I have not seen any reasonably modern orm where performance is the issue.

A raw query doesn't has to be repeated in every place it's required. Not sure what your point is.

You will have a bigger variety of queries hwne you don't use an orm - this puts a higher load on software testing to get the same level of reliability.

> 50 ms p99

You realize that’s abysmally bad performance for any reasonable OLTP query, right? Sub-msec (as measured by the DB, not including RTT etc.) is very achievable, even at scale. 2-3 msec for complex queries.

The is the response time for the server, not the database - which is appears that everyone but you understood clearly from the context.

Guessing you are a Rails dev?

C#’s Linq based ORMs have always been - type safe built into the OS feature -> run time generation of an agnostic expression tree -> database provider converts it into SQL. It does database joins (unless you do something stupid like get out of IQuery land).

Reply on news.ycombinator.com