Hacker News Reader

Comment by leetrout | Hacker News Reader

I never get the ORM hate for CRUD apps.

It gives a centralized place to understand what data is in play in the application and adding a new column to the database means updating a class / struct / type in one location and checking the use of that within the code base.

Without an ORM and with poor discipline I end up having to go find every query in the app referencing that table and check if I need to update the query which usually means changing the columns, the ? value placeholder and putting the correct value in the associated values container.

It all seems like a lot of tedium and ripe for simple typos in the queries that is easily avoided by using beneficial tooling.

View on HN

My distaste is simple: The abstraction that is an ORM is leaky AF. Optimizing an automatic query is a PITA. Doing more than simple joins across tables is hard. Falling back to pure SQL quickly becomes untenable across an application (because each instance quickly becomes an ad-hoc workaround).

And to be frank, CRUD apps created with an ORM that remain as simple CRUD apps are the vast minority of the work that I’ve done in my career. Everything else evolves into something where we inevitably end up working around the ORM more than using it as-is.

The solution in my opinion: SQL generators (like linq and such). ORMs often include these as an after thought, but pulling in a full ORM for the sql generation is like pulling in a semi truck for its generator; it’s too easy to start experimenting with the toys stored in the trailer.

> The abstraction that is an ORM is leaky AF

Add to that, as someone mentioned above ORMs tend to be "common least set of features". And if you for some reason want to use a specific feature of your RDBMS you have to jump through hoops and the ORM "illusion" ends.

There is a tradeoff here, as usual.

The hate for ORMs comes from more advanced applications, not simple CRUD apps. When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution.

The problem comes when you have 25 tables, complex relations, complex updates and need to optimize your queries for better performance. You now need to know the inner workings of you ORM, and pray that there are sane ways of configuring these things. Knowing the inner workings of these enormous beasts are not always easy – at least not easier than writing the SQL yourself.

This and the infamous question "how do I write this complex SQL query in ${ORM}?"

I ended up using raw queries in many of the projects I worked on for more than 2 or 3 years. It's more cost effective than trying to find the right combination of ORM statements.

Yes, but as I say in the sibling comment, could this not be the best of both worlds?

Almost. The best of both worlds would be an ORM that can understand the SQL query and populate its objects/structs/whatever with the result. I have to manually process the results now.

If I had an ORM like that maybe I would write all SELECTs directly in SQL.

What makes that impractical in projects with multiple developers is that about half of them don't know SQL nowadays. They know how to use the ORM and the language to write CRUD queries and that's all. If they see SELECT * FROM invoices WHERE customer_id = ? they probably understand it. Nested queries, HAVING, GROUP BY... oops! CTEs... OMG! This means that sometimes they write three or four queries when one would be enough.

For complex data models, I find the ORM provides me with a lot of help in the form of IDE hints. I find the ORM schemas also provide documenting/referential value with less code than the raw SQL equivalent.

For the scenario you're describing, you could still use a mix of an ORM and raw SQL as needed.

But any ORM I have ever used supports raw queries. So, for boilerplate (which is often a significant portion of queries) the ORM does the heavy lifting, and for everything else you have the power to write the SQL yourself. This is something commonly buried in ORM discussions and I never understand why.

I think it comes from experiences of coming in to codebases and seeing that people are using the ORM for everything, even when it's obviously suboptimal and raw SQL would be better.

Compromise, best tool for the job, nuance, etc - all tend to get tossed out when people make tech decisions. Someone using an ORM may not even be aware that raw SQL is possible.

I wouldn‘t say its buried, at least not in the discussions I have been in, but the problem is that even with custom queries you are still in this weird place between fully managed ORM queries and raw SQL queries. How does the result map to data structures? How does it handle colliding column names in joins? Does the ORM handle over complete control when using custom SQL? There are tons of questions like these that are not uniformly answered by different ORMs.

I am not saying that ORMs never work. They sure do in a lot of cases, and some ORMs more than others. But an ORM is not a silver bullet – far from it. There are gotchas, and you need to invest time in understanding how they work. Just as you need to understand how SQL works.

It's not just 'simple CRUD apps' vs 'advanced apps', I find it's also language/ecosystem specific. I've worked on Java and .NET projects (typed languages) where ORM's are a fact of life, and actually make it a lot easier to navigate & refactor your codebase. I've also worked on Python and Node.js projects, where ORM's often did not significantly improve the developer workflow (navigation, refactoring etc.) because of the lack of strict types in these languages, and ORM's and their usage were therefore a point of daily team discussion.

Exactly this.

In Python/Node: I'd never use an ORM for handling 5 tables. I'm just fine writing/maintaining my 5-10 queries in plain SQL. Centralize all the queries in a single file and that's it.

In Java/C#: Do I even have the choice? Even for 1 table it's probably easier to let the ORM do its job than fighting the typing system.

> When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution.

Perfect solution to what problem?

Yeah, I'll will say plain SQL is even simpler here.

If all you do is CRUD then fine. However databases are meant to do so much more than that. SQL means structured query language, it supports and enables complex queries. You could argue all those queries can be done in code and you'd be right, while paying the price of inefficient big data transfers and duplicating logic in possibly quite slower ways.

Have you ever seen what EntityFramework produces for queries? I wish I could share some of the beautiful disasters that it has produced for my company.

I have not. I assumed it was analogous to Django / SQLAlchemy where you could always pull the escape handle use raw SQL when it was truly necessary.

You can do raw SQL with EF, but you lose a lot in the process.

EF Core -can- be better than the older, much more reviled EF as far as generated SQL, but in EF core 3 they did a pretty big breaking change on joins, bringing behavior closer to EF6's tendency to do a cartesian asplosion

It produces what you tell it. The thing is Linq is a more general language than SQL. If you tell EF to generate something that would be really hard to translate to SQL then of course it's going to do a bad job. Just by taking a few seconds to think how the query you are writing will translate into SQL goes a long way towards helping EF produce good, clean queries. EF still provides many benefits on top of this so imo it's worth it but at the end of the day whether you are writing SQL or EF you have to understand the basics of how databases work.

I have. Recently I was looking at an Azure SQL DB and a long running query caught my eye. "Beautiful disaster" is an apt description for the 123 line query I saw.

ORMs are great for the simple CRUD case. That's how people get lured in.

It's when things get complicated and performance is important that they start to feel like a hindrance. I've had to wrestle with ORMs only to figure out that it's generating bad queries or lots of queries when it could do it with one query. I've usually had to end up subverting the ORM or throwing it away entirely in almost every case.

> I end up having to go find every query in the app referencing that table

This sounds like bad design, why would you have SQL queries for the same table littered across a project, rather than in context with each other in the same file/class/interface/etc.?

Most of the CRUD experience I have is in Java, and I've preferred using JDBI over ORM's like Hibernate. You write a CRUD interface for the table and annotate the methods with (something very close to) plain old SQL. I can define how a SQL row gets mapped to a POJO, though in many cases the default mapping logic is fine. I know exactly what SQL will be executed, at compile time. I use Liquibase to manage the database schema. It's about as painless as it gets; I've had many obtuse errors to resolve attempting to use an ORM. I think most importantly, I'm familiar enough with the schema that if and when I need to do an ad-hoc query to give some data to an analyst, or debug an issue, I know what I'm working with.

Yes, it is bad design, because you have one Sql.RunQuery method returning a DataTable and it's used every-freaking-where. Then you have to convert the data in every place back to the proper objects, paying special attention to the difference between DBNull and regular null.

I've used both ORMs and direct ADO queries (in C#), and while ORMs can trip you in special cases like the N+1 SELECT problem, the raw queries are a pain everywhere. It's just to easy to say "ah, I need a quick SELECT here, I'll just return this bunch of records and I know that record[5] is a string with the value I need". Hello, versioning hell.

The hate for CRUD apps stems from it being too simple to be called a "high-tech" solution, that's all.

Many global apps start as CRUD apps, and only become high-tech with scale. And even at hyperscale companies a lot of software engineering work is probably "basic CRUD" - reading from one system and writing into another, without applying a radical transformation to the data in the process. And even doing that well is quite challenging, which is why the average software developer gets paid well.

It's great if your talent allows you to do much more than that, but I wouldn't deride it, and much less hate it, when your own success is likely going to have to rely on competent people around you doing that job extremely well.

Yeah, as long as it's well designed, like LINQ.

ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.

There is so much code out there to help you do typed params and to stop you having to concat strings to make queries. You don't need an orm for this.

Here's the root of my problem: ORMs always force their world view onto the database, which is completely backwards from how things should work. The database is the most important part of the application, it will probably outlive your current code base. Yet your data model ends up getting warped to support a single ORMs worldview.

New table columns should never cause problems for existing code because you should never use `SELECT *` and you should never use positional semantics in your SQL, put a linter on it.

As for typos, that's what SQL builder libraries are for, they allow you to work with your own language constructs instead of mashing together strings manually.

ORMs are this thing that developers seem to think is a life preserver but as time goes on morphs into an anchor.

> ORMs always force their world view onto the database, which is completely backwards from how things should work.

I have different experience. There are situations where you work with database given of course, but in many projects ORM enforces good and unified practices (rows have unique id, constraints and indexes are properly named, relationships are done in always the same way), and generally database is just a servant to developer idea of data model, not a god to worship and bend to.

> you should never use positional semantics in your SQL

If I'm understanding you, that along with your mention of a SQL builder means, for example, in Python, you'd expect

CreateUser(name="blah")

As opposed to something like

db.Exec("INSERT INTO users (name) values (?)", "blah")

Is that correct? I think we're agreeing on reducing the amount of raw SQL being tossed around within the code.

SQL columns can be referred to either by name or by index. Avoiding positional semantics means using "ORDER BY transaction_date ASC" rather than "ORDER BY 3 ASC". On the calling code, your python library may return either a tuple or a dictionary. The dictionary is the right way to do it, because then you access parameters by name, and reordering or adding parameters to the query output doesn't break your script.

Less about the exact syntax and more about the tool, for example: https://github.com/sqlkata/querybuilder. I just chose that since it was on top of a search but the idea is the same. Your code generates raw SQL, so it's 100% interchangeable with writing SQL yourself however the builder library deals with the syntax, proper ordering, quoting, full attribute names, etc. Some such libraries even let you define your schema in code to make your SQL generation type safe.

Reply on news.ycombinator.com