The pendulum between 'use an ORM for everything' and 'raw SQL only' has been swinging for 20 years. In practice, the sweet spot I've found is using an ORM for simple CRUD (saves hours of boilerplate) but dropping to raw SQL the moment you need joins across 3+ tables, CTEs, or window functions. The real danger isn't ORMs themselves - it's developers who never learn SQL because the ORM shields them, then they can't debug the N+1 query that's killing production.
Absolutely, “ORM == bad” viewpoint strikes me as highly ignorant of all the benefits they provide, particularly in statically typed languages.
People like me don’t choose an ORM to save me from having to learn SQL (which you’ll still need to know), it’s because 99% of the time it’s a no brainer as it vastly increases productivity and reduces bugs.
In a language like C#, EF Core can easily cover 95% (likely more) of your SQL needs with performance as good as raw SQL, for the small percentage of use cases its performance is lacking, you fall back to raw SQL.
But if saving you from writing 95%+ of SQL queries was not compelling enough, it’s just one benefit of EF Core. Another major time saving benefit is not having to manually map a SQL result to objects.
But an often super underrated and incredibly valuable benefit, especially on substantial sized code bases, is the type safety aspect. The queries written using LINQ are checked for free at compile time against mistakes in column or table names.
Want to refactor your schema because your business domain has shifted or you just understand it better than before? No problem. Use standard refactoring tools on your C# code base, have EF Core generate the migration and you’re done in 10s of minutes, including fixing all your “SQL queries” (which were in LINQ).
EF Core is almost always a no brainer for any team who wants high quality and velocity.
Totally. ORMs are not evil. They help to reduce the boilerplate and improve the code maintainability.
We have a complex project with a custom back-end framework that also includes a custom ORM. The ORM covers about 95% of the use cases just fine. It reduces a lot of boilerplate and also makes it possible to find the logic much easier (for example, setting and reading some column from a specific table). Although I have to say, it works so well also thanks to good integration with the framework (fronted, included) and advanced features, like calculated fields with automatic recalculation triggering, integration with user permissions, ability to define custom user permission validation logic before / after saving rows, etc. If we really need an unusual operation where the ORM doesn't suffice (most of the time complicated search queries or some aggregation), we can write raw SQL and, if possible, encapsulate it at repository / service level. But 95% of use cases are covered by the ORM (including the management of parent / child relationships).
I think some "old school" folks like to bash ORMs and view them as very limited and inefficient compared to raw SQL. But you can use them both: primarily ORM and in specific cases switching to raw SQL when ORM doesn't suffice.
It’s been swinging for at least 30 years, Toplink came out in 1994, and I have worked on systems that were written half a decade earlier that contained things we’d all call ORMs.
Agreed, ORMs are OK to start with if you like and then optimize the queries where needed.
There are cases where older ORMS might be more optimized for some cases compared to new ones, and vice versa.
Avoiding learning SQL is the biggest gap. Selecting a NoSQL database because it seems easier, and then spending so much time trying to make a NoSQL database into a relational database is usually not too pretty.
Gavin King, creator of hibernate has said many times that you don’t have to use it for everything. Indeed your code can use the entity manager to execute sql. The problem is sql databases are hard, data modeling is hard, it requires a lot of care discipline and even experience (gasp!) to get right.
But it’s a bot writing the above. Look at the user's comment history. "Not x, but y", poorly formatted listicles, mid-paragraph questions to the reader, and em dashes galore.
The day where they'll be hard to tell apart from humans is close. My alarms didn't ring on this on already, I'll be taken out by the first wave of impostors :(
I agree after a closer look though. the pattern is so strong, you can identify it visually. comments 2, 3, and 4 are all three paragraphs, the next three are all one longer paragraph, and all are of very similar length.
> And if you need real SQL intervals, Django pushes you towards raw expressions or `Func()` wrappers.
It's possible to use a very similar construct to SQL Alchemy here by using the `Now` [function](https://docs.djangoproject.com/en/6.0/ref/models/database-fu...) (it uses `STATEMENT_TIMESTAMP` which is likely more correct than `NOW()` here alternatively there is `TransactionNow`) by doing `Now() - timedelta(days=30)`.
Though the article mentions the distinction between ORMs and query builders, it doesn't really make a case against query builders being bad. After all, it wraps up by building a kinda crappy one-off query builder.
Porsager’s Postgres package does a great job of letting you feel like you’re writing raw sql, but avoids the attack vectors.
Anyway, I agree that ORMs are pretty terrible. I like writing SQL or using a lightweight builder like Kysely. Was a huge Dapper fan back in my C# days.
There are plenty of reasonable alternatives to ORMs that don’t open you to SQL injection attacks.
Parameterized queries have been a thing for decades, which mitigate SQL injection attacks.[1] This is true of the examples in the post too, they used this:
query = """
SELECT * from tasks
WHERE id = $1
AND state = $2
FOR UPDATE SKIP LOCKED
"""
rec = await self.db.fetchone(query=query, args=[task_id, TaskState.PENDING], connection=connection)
I’m glad you’re head-over-heels in love with Postgres— it’s really cool, and I’ve occasionally had projects that really benefited from it… but most of those incredible features just aren’t useful for run-of-the-mill projects. Learning how to profile your ORM queries is a lot easier than maintaining a bunch of code from a different language embedded into your code base. If you’re writing articles about Postgres, you probably have no idea how much of a PITA that context switch is in practice. It’s funny how getting expertise in something can make it more difficult to understand why it’s useful to other people, and how they use it.
There are projects, like SQLC, that cover most of the perceived advantages of ORMs, without the downsides.
One of these downsides is, in my opinion, the fact that they hide the very details of the implementation one necessarily needs to understand, in order to debug it.
Let me just say that I wrote my first (professional) SQL queries about 25 years ago and at various post points have worked extensively with Postgres, a bit less so with Oracle, and occasionally with MySQL and MSSQL. (And also some of the JSON object store databases before switching to Postgres for that stuff.) The only ones I’ve used ORMs with are Postgres and MySQL.
SQLC does not address most of the perceived advantages to ORMs. Sure it addresses some of the concerns of hand-writing and sending SQL to databases from various languages, but that’s not what most people I’ve spoken to in the past couple of decades most valued about ORMs. What most projects really need databases for is some place to essentially store context-sensitive variable values. Like what email address to send something to if the user ID is 12345. I’ve never, ever had to debug ORM’s SQL when doing things like that. Rarely have I needed to with more complex chains of filters or whatnot, and that usually involved taking a slightly different approach with the given ORM tools rather than modifying them or writing my own SQL. When I’ve had more complex needs that required using some of the more exotic Postgres features, writing my own queries has been trivial. It’s of paramount importance for developers to understand the frameworks and libraries, such as ORMs, they’re using because those implementation details touch everything in your code. Once you understand that, the code your ORM composes to make your queries is an IDE-click away.
Not having to context switch between writing SQL and whatever native language you’re working in, especially for simple tasks, has yielded so so so much more to my time and mental space than being exactly 100% sure that my code is using that left join in exactly the way I want it to.
Second, an ORM is just a translation layer, i.e. it does not compile to any binary format the database understands, and instead it gets translated to SQL, which is the standard, minus extensions. SQL is ubiquitous. It’s the closest to a lingua franca that we have in the context of software engineering. And I’m going to be blunt here and say that purposefully avoid learning and understanding SQL if it is part of the job, should disqualify anyone from it.
I’ve been around for some decades too, and to me, ORMs haven’t worked out. They are vastly different one from another and they often create issues that are clear as day when the query is written as SQL. If I go from a Typescript codebase to Python to Java, then, according to you, I should learn the intricacies of Sequelize, SQLAlchemy, and JPA/Hibernate, instead of just SQL. And granted, different SQL dialects have different quirks, but more often than not, the pitfalls are more apparent than when switching between ORMs.
And I can guarantee that someone equipped with a good foundation in SQL will be more successful debugging a Sequelize based application, than someone who has relied on SQLAlchemy.
What most people I know and worked with need, is types. Types help glue SQL and any other language together. If I can run any SQL query and the result comes back as an object, I’m good.
Now, if your point is that ORMs are OK for toying around, I may agree, but still, why would I go through that trouble when I know SQL.
Mostly agreed with the author about ORMs. The provided querying abstraction works against developers when queries reach a certain level of complexity, and at the end of the day, understanding these complexities is not optional.
But I would caution against adding too much business logic to the database, and tying message passing to your database doesn’t sound like the best of ideas.
But there's also value gained in it, isn't there? I very much like doctrine's query builders and being able to analyze and manipulate queries programmatically, e.g. dynamically add a filter to a query and a join if needed. That's pretty simple with a query builder once you've gotten comfortable with the concept and the ORM itself, but it's pretty hard to do with plain sql unless you write plenty of specific code to handle all the known things you might care about.
When someone says that X is bad and not to use it, what I really hear is, “I’m ignorant to some use cases but that won’t stop me from having a loud opinion.”
I oscillate on being tired or amused by just how common tech people make this basic error. But I don’t believe it’s ever in bad faith. I think people in general suffer from perceiving their context as the context even though they’ve experienced maybe 1% of what there is out there.
Considering anybody with a noggin is going to be separating the SQL into it’s own module or whatever rather than just throwing straight inline SQL at your database wherever you it, you’re hardly less likely to have things like accidental writes, anyway. This is clearly someone who fell in love with Postgres, felt ORM abstractions that diluted the Postgres goodness were bad, and then did some mental experiments to consider all of the theoretical ways ORMs suck.
Yeah ORMs help when they're appropriate but ya gotta learn how they work and where the footguns are plus you still really want to know how a database server works. Given the articles title, I doubt the prerequisites were met.
While there is a compelling case for leveraging the full power of Postgres (especially features like SKIP LOCKED and pg_notify), this approach feels like a classic trade-off between fine-grained control and long-term maintainability.
Relying solely on raw SQL and manual mapping certainly eliminates "ORM magic," but it replaces it with a significant maintenance burden.
For specialized, high-performance systems like video transcoding, this level of hand-tuning is a superpower; however, for the average CRUD-heavy SaaS app, the "boilerplate tax" of writing eighty lines of repository code for a simple related insert might eventually cost more in development velocity than the performance gains are worth.
> for the average CRUD-heavy SaaS app, the "boilerplate tax" of writing eighty lines of repository code for a simple related insert might eventually cost more in development velocity than the performance gains are worth.
Perhaps, but IME this kind of thing is much more often the cause of poor performance in CRUD apps than the frontend frameworks that are usually blamed. I have been able to make very snappy SaaS apps by minimizing the number of queries that my API endpoints need to perform.
I've also found that the ORM mainly reduces boilerplate for Insert/Update operations, but often adds a very significant amount of boilerplate for read queries. We ended up using a very lightweight orms for simple inserts / upserts and writing raw SQL for anything else.
No, just because raw SQL queries work great for your toy blog/todo app with 3 tables and simple relationships, doesn't mean they work great for real world business applications with 100 tables and complex networks of relationships. Try maintaining the latter before you make blanket claims like "ORM bad".
If you're asking yourself "do I need an ORM?", then you should probably default to using one, unless you understand your complete use case well enough to know you'd be better off without one.
It's also important to note that not all ORMs are created equal. Some are more restrictive than others, and that should also be taken into account.
Maybe if you add a new variable to a table and need it in a query five views down? But honestly I much prefer using assisted queries like the supabase package, but leaving the tables alone. ORM can be very unwieldy in an unstable environment.
I’ve once tried a "type-safe" SQL extension and it was pretty neat.
Imho something like this is much more useful than a lot of ORM-overhead.
The pendulum between 'use an ORM for everything' and 'raw SQL only' has been swinging for 20 years. In practice, the sweet spot I've found is using an ORM for simple CRUD (saves hours of boilerplate) but dropping to raw SQL the moment you need joins across 3+ tables, CTEs, or window functions. The real danger isn't ORMs themselves - it's developers who never learn SQL because the ORM shields them, then they can't debug the N+1 query that's killing production.
Absolutely, “ORM == bad” viewpoint strikes me as highly ignorant of all the benefits they provide, particularly in statically typed languages.
People like me don’t choose an ORM to save me from having to learn SQL (which you’ll still need to know), it’s because 99% of the time it’s a no brainer as it vastly increases productivity and reduces bugs.
In a language like C#, EF Core can easily cover 95% (likely more) of your SQL needs with performance as good as raw SQL, for the small percentage of use cases its performance is lacking, you fall back to raw SQL.
But if saving you from writing 95%+ of SQL queries was not compelling enough, it’s just one benefit of EF Core. Another major time saving benefit is not having to manually map a SQL result to objects.
But an often super underrated and incredibly valuable benefit, especially on substantial sized code bases, is the type safety aspect. The queries written using LINQ are checked for free at compile time against mistakes in column or table names.
Want to refactor your schema because your business domain has shifted or you just understand it better than before? No problem. Use standard refactoring tools on your C# code base, have EF Core generate the migration and you’re done in 10s of minutes, including fixing all your “SQL queries” (which were in LINQ).
EF Core is almost always a no brainer for any team who wants high quality and velocity.
Totally. ORMs are not evil. They help to reduce the boilerplate and improve the code maintainability.
We have a complex project with a custom back-end framework that also includes a custom ORM. The ORM covers about 95% of the use cases just fine. It reduces a lot of boilerplate and also makes it possible to find the logic much easier (for example, setting and reading some column from a specific table). Although I have to say, it works so well also thanks to good integration with the framework (fronted, included) and advanced features, like calculated fields with automatic recalculation triggering, integration with user permissions, ability to define custom user permission validation logic before / after saving rows, etc. If we really need an unusual operation where the ORM doesn't suffice (most of the time complicated search queries or some aggregation), we can write raw SQL and, if possible, encapsulate it at repository / service level. But 95% of use cases are covered by the ORM (including the management of parent / child relationships).
I think some "old school" folks like to bash ORMs and view them as very limited and inefficient compared to raw SQL. But you can use them both: primarily ORM and in specific cases switching to raw SQL when ORM doesn't suffice.
It’s been swinging for at least 30 years, Toplink came out in 1994, and I have worked on systems that were written half a decade earlier that contained things we’d all call ORMs.
Agreed, ORMs are OK to start with if you like and then optimize the queries where needed.
There are cases where older ORMS might be more optimized for some cases compared to new ones, and vice versa.
Avoiding learning SQL is the biggest gap. Selecting a NoSQL database because it seems easier, and then spending so much time trying to make a NoSQL database into a relational database is usually not too pretty.
Gavin King, creator of hibernate has said many times that you don’t have to use it for everything. Indeed your code can use the entity manager to execute sql. The problem is sql databases are hard, data modeling is hard, it requires a lot of care discipline and even experience (gasp!) to get right.
Edit: Thanks for the merciless downvotes. :)
But it’s a bot writing the above. Look at the user's comment history. "Not x, but y", poorly formatted listicles, mid-paragraph questions to the reader, and em dashes galore.
The day where they'll be hard to tell apart from humans is close. My alarms didn't ring on this on already, I'll be taken out by the first wave of impostors :(
I agree after a closer look though. the pattern is so strong, you can identify it visually. comments 2, 3, and 4 are all three paragraphs, the next three are all one longer paragraph, and all are of very similar length.
Ah, we haven't had an "ORMs bad" post for at least three days.
Everyone too busy writing and posting their "How I use Think for me Saas" / BrEaK It InTo SmAlLeR TaSkS blog posts.
There are valid reasons for not using an ORM but the points made in this article are plain false.
>“Roughly” because Django ORM doesn’t support the JSONB `?` operator.
The `has_key` [lookup](https://docs.djangoproject.com/en/6.0/topics/db/queries/#has...) does exactly that.
> And if you need real SQL intervals, Django pushes you towards raw expressions or `Func()` wrappers.
It's possible to use a very similar construct to SQL Alchemy here by using the `Now` [function](https://docs.djangoproject.com/en/6.0/ref/models/database-fu...) (it uses `STATEMENT_TIMESTAMP` which is likely more correct than `NOW()` here alternatively there is `TransactionNow`) by doing `Now() - timedelta(days=30)`.
The result is the following `filter` call
which translates to the following SQL which can be confirmed in [this playground](https://dryorm.xterm.info/hn-47110310)Postgres is amazing and ORM is your friend. Migrations alone is a good reason why using ORMs is a good idea.
Migrations aren’t necessarily tied to ORMs. There are tons of tools out there to run migrations and nothing else.
Agreed, in many Spring projects I worked migrations were handled by flyway or liquibase while the ORM was always Hibernate.
Though the article mentions the distinction between ORMs and query builders, it doesn't really make a case against query builders being bad. After all, it wraps up by building a kinda crappy one-off query builder.
Agreed, write raw SQL, this has never had any security impact whatsoever[1]
- Your friendly local pentester
[1] - https://en.wikipedia.org/wiki/SQL_injection
Porsager’s Postgres package does a great job of letting you feel like you’re writing raw sql, but avoids the attack vectors.
Anyway, I agree that ORMs are pretty terrible. I like writing SQL or using a lightweight builder like Kysely. Was a huge Dapper fan back in my C# days.
There are plenty of reasonable alternatives to ORMs that don’t open you to SQL injection attacks.
Parameterized queries have been a thing for decades, which mitigate SQL injection attacks.[1] This is true of the examples in the post too, they used this:
[1] https://en.wikipedia.org/wiki/SQL_injection#Parameterized_st...I’m glad you’re head-over-heels in love with Postgres— it’s really cool, and I’ve occasionally had projects that really benefited from it… but most of those incredible features just aren’t useful for run-of-the-mill projects. Learning how to profile your ORM queries is a lot easier than maintaining a bunch of code from a different language embedded into your code base. If you’re writing articles about Postgres, you probably have no idea how much of a PITA that context switch is in practice. It’s funny how getting expertise in something can make it more difficult to understand why it’s useful to other people, and how they use it.
There are projects, like SQLC, that cover most of the perceived advantages of ORMs, without the downsides.
One of these downsides is, in my opinion, the fact that they hide the very details of the implementation one necessarily needs to understand, in order to debug it.
Let me just say that I wrote my first (professional) SQL queries about 25 years ago and at various post points have worked extensively with Postgres, a bit less so with Oracle, and occasionally with MySQL and MSSQL. (And also some of the JSON object store databases before switching to Postgres for that stuff.) The only ones I’ve used ORMs with are Postgres and MySQL.
SQLC does not address most of the perceived advantages to ORMs. Sure it addresses some of the concerns of hand-writing and sending SQL to databases from various languages, but that’s not what most people I’ve spoken to in the past couple of decades most valued about ORMs. What most projects really need databases for is some place to essentially store context-sensitive variable values. Like what email address to send something to if the user ID is 12345. I’ve never, ever had to debug ORM’s SQL when doing things like that. Rarely have I needed to with more complex chains of filters or whatnot, and that usually involved taking a slightly different approach with the given ORM tools rather than modifying them or writing my own SQL. When I’ve had more complex needs that required using some of the more exotic Postgres features, writing my own queries has been trivial. It’s of paramount importance for developers to understand the frameworks and libraries, such as ORMs, they’re using because those implementation details touch everything in your code. Once you understand that, the code your ORM composes to make your queries is an IDE-click away.
Not having to context switch between writing SQL and whatever native language you’re working in, especially for simple tasks, has yielded so so so much more to my time and mental space than being exactly 100% sure that my code is using that left join in exactly the way I want it to.
First of all, congrats on your career.
Second, an ORM is just a translation layer, i.e. it does not compile to any binary format the database understands, and instead it gets translated to SQL, which is the standard, minus extensions. SQL is ubiquitous. It’s the closest to a lingua franca that we have in the context of software engineering. And I’m going to be blunt here and say that purposefully avoid learning and understanding SQL if it is part of the job, should disqualify anyone from it.
I’ve been around for some decades too, and to me, ORMs haven’t worked out. They are vastly different one from another and they often create issues that are clear as day when the query is written as SQL. If I go from a Typescript codebase to Python to Java, then, according to you, I should learn the intricacies of Sequelize, SQLAlchemy, and JPA/Hibernate, instead of just SQL. And granted, different SQL dialects have different quirks, but more often than not, the pitfalls are more apparent than when switching between ORMs.
And I can guarantee that someone equipped with a good foundation in SQL will be more successful debugging a Sequelize based application, than someone who has relied on SQLAlchemy.
What most people I know and worked with need, is types. Types help glue SQL and any other language together. If I can run any SQL query and the result comes back as an object, I’m good.
Now, if your point is that ORMs are OK for toying around, I may agree, but still, why would I go through that trouble when I know SQL.
I sure loved delving into this blog post.
Yup, sloppy slop-slop
Is there a platform convention for this yet? Can we flag obvious slop?
Mostly agreed with the author about ORMs. The provided querying abstraction works against developers when queries reach a certain level of complexity, and at the end of the day, understanding these complexities is not optional.
But I would caution against adding too much business logic to the database, and tying message passing to your database doesn’t sound like the best of ideas.
But there's also value gained in it, isn't there? I very much like doctrine's query builders and being able to analyze and manipulate queries programmatically, e.g. dynamically add a filter to a query and a join if needed. That's pretty simple with a query builder once you've gotten comfortable with the concept and the ORM itself, but it's pretty hard to do with plain sql unless you write plenty of specific code to handle all the known things you might care about.
It sounds to me that you saying that SQL is hard because you’d rather learn the intricacies of an ORM.
Also, this whole point predicates upon the assumption that ORMs are infallible when translating queries into SQL, which most definitely are not.
When someone says that X is bad and not to use it, what I really hear is, “I’m ignorant to some use cases but that won’t stop me from having a loud opinion.”
I oscillate on being tired or amused by just how common tech people make this basic error. But I don’t believe it’s ever in bad faith. I think people in general suffer from perceiving their context as the context even though they’ve experienced maybe 1% of what there is out there.
This straw-mans ORMs by listing out what crappy ones do. I mean, accidental writes? You've either got a terrible ORM, no tests, or both.
Considering anybody with a noggin is going to be separating the SQL into it’s own module or whatever rather than just throwing straight inline SQL at your database wherever you it, you’re hardly less likely to have things like accidental writes, anyway. This is clearly someone who fell in love with Postgres, felt ORM abstractions that diluted the Postgres goodness were bad, and then did some mental experiments to consider all of the theoretical ways ORMs suck.
I gotta agree with you, total straw man. I haven't seen any of the issues this guy has with ORMs.
Yeah ORMs help when they're appropriate but ya gotta learn how they work and where the footguns are plus you still really want to know how a database server works. Given the articles title, I doubt the prerequisites were met.
While there is a compelling case for leveraging the full power of Postgres (especially features like SKIP LOCKED and pg_notify), this approach feels like a classic trade-off between fine-grained control and long-term maintainability.
Relying solely on raw SQL and manual mapping certainly eliminates "ORM magic," but it replaces it with a significant maintenance burden.
For specialized, high-performance systems like video transcoding, this level of hand-tuning is a superpower; however, for the average CRUD-heavy SaaS app, the "boilerplate tax" of writing eighty lines of repository code for a simple related insert might eventually cost more in development velocity than the performance gains are worth.
> for the average CRUD-heavy SaaS app, the "boilerplate tax" of writing eighty lines of repository code for a simple related insert might eventually cost more in development velocity than the performance gains are worth.
Perhaps, but IME this kind of thing is much more often the cause of poor performance in CRUD apps than the frontend frameworks that are usually blamed. I have been able to make very snappy SaaS apps by minimizing the number of queries that my API endpoints need to perform.
I've also found that the ORM mainly reduces boilerplate for Insert/Update operations, but often adds a very significant amount of boilerplate for read queries. We ended up using a very lightweight orms for simple inserts / upserts and writing raw SQL for anything else.
This again...
No, just because raw SQL queries work great for your toy blog/todo app with 3 tables and simple relationships, doesn't mean they work great for real world business applications with 100 tables and complex networks of relationships. Try maintaining the latter before you make blanket claims like "ORM bad".
I guess I'll bite. How/When do you know you actually need an ORM?
If you're asking yourself "do I need an ORM?", then you should probably default to using one, unless you understand your complete use case well enough to know you'd be better off without one.
It's also important to note that not all ORMs are created equal. Some are more restrictive than others, and that should also be taken into account.
Maybe if you add a new variable to a table and need it in a query five views down? But honestly I much prefer using assisted queries like the supabase package, but leaving the tables alone. ORM can be very unwieldy in an unstable environment.
I’ve once tried a "type-safe" SQL extension and it was pretty neat.
Imho something like this is much more useful than a lot of ORM-overhead.
[dead]
[dead]