Ask HN: Have you found ORMs help more than they hinder? | Hacker News Reader

Ask HN: Have you found ORMs help more than they hinder?

16 pointsxupybd19 comments

I’ve not seen a case when using an ORM was helpful. Often the added complexity isn’t worth it. Plain SQL is great, once you learn it and most ORMs have a query language that means you just have to learn another query language.

That said these exist in droves so they must serve a purpose. I can see the case for database portability but I’ve not worked with a project that needed to shit databases. So are there other cases when an ORM pays off?

View on HN

1. Good luck finding all places that access some table if you want to change something, especially if table is called users or products.

2. ORM provide abstraction layer. Otherwise every place needs to know all details of tables it accesses. Migration from db to solr for example is much easier with ORM. Ensuring that you call strip_html or other logic in all relevant places is also easier if you have one entry point you can hook into.

3. Strings compose poorly. Building complex queries from set of parameters sucks.

4. Database portability is crucial, even if you will never migrate. It gives you access to far greater mindshare and toolset.

Is this also a useful tool from an administrative point of view? One of my friends is a db admin and he has not heard of ORMs at all.

Probably not. The only point of contact with an admin I can think of is that orms can manage schema migrations.

Good luck finding all places that access some table if you want to change something

Is it really that hard though? All we need to do is have a class for Users (or products or whatever) and put all queries in that class.

ORMs are annoying, they add another layer of complexity and take the joy out of just writing a simple query. Now I have to learn yet another syntax which is totally unnatural. There is no guarantee these ORMs convert to optimized queries, and not all ORMs support all SQL features either.

Just reading a 20 line ORM query with multiple complex joins vs the same thing written in plain SQL hurts the eyes and brain.

> All we need to do is have a class for Users (or products or whatever) and put all queries in that class.

Yes, and this is very close to doing what orms do.

ORM's are nice for majority of writes and simple reads based off of an object (a model).

It starts to hinder once there's a requirement for complex reports. I find, that's where the distatse of ORMs in an organization starts.

The other way to deal with majority of these complex reports is to create a separate OLAP table (model) to write/update too, and pull aggregates from there. But that's treading on a thin line of over-engineering.

ORM's are supposed to be used for Objects. If a report isn't a well-defined object/model, then it's going to fight against an ORM.

They are useful if you only use them for simple things. Any queries which are non-trivial or aren't cleanly expressed in your object model can lead to absolutely monstrous queries.

I once saw a 1000 line query - which unsurprisingly timed out - generated for getting a single user record from the database! There were several lookups to rate limit failed login attempts etc, however this should be a couple of lines of efficient sql if hand rolled.

I can tolerate something like Rails' ActiveRecord if used with restraint. E.g.

    user.addresses << Address.new(blah)

Is very useful, it doesn't get in the way and does all the boring stuff for me. People just need to learn this isn't the only way to access the database, and you can drop to sql if you need to.

Fortunately I started developing at a time where there were no ORMs, but I fear there is a new generation of devs who see SQL as some esoteric ASM for databases.

Also, I disagree with database portability as an argument for ORMs. All abstractions are leaky, and trying to unify everything will stop you taking full advantage of your data-store of choice

ORMs are good for dealing with the domain by representing abstractions in a clear fashion. The assumption is that you usually have highly normalized tables. If you do not have highly normalized tables due to cruft and technical debt, it is usually easier to use plain SQL.

Perhaps you are suffering from Blub? http://wiki.c2.com/?BlubParadox One reason for wanting to shift databases is testing. The unit tests might be running on the developers own sqlite instances because running them on the company-wide SQL Server installation is prohibitive.

A well-written ORM generally do not introduce much complexity. For example, C# Linq expressions (yes, I know they are used for many other tasks than db access) aren't more complex than the equivalent sql statements.

> One reason for wanting to shift databases is testing. The unit tests might be running on the developers own sqlite instances because running them on the company-wide SQL Server installation is prohibitive.

That doesn't strike me as a shift, though. Presumably, if sqlite (or, say, its heavier weight, also free, cousin PostgreSQL) can mimic enough functionality of the proprietary company-wide db, why isn't bare SQL enough to access those functions?

Why is SQL not a good enough abstraction? Too difficult to ensure one is writing a portable subset of the language?

> A well-written ORM

This runs into the danger of being a "true Scotsman" type argument if it doesn't apply to at least most popular ORMs (or at least one for each popular language).

> aren't more complex than the equivalent sql statements.

They are, by definition, if one already knows the equivalent SQL statement: one has to learn the additional, separate syntax.

> That doesn't strike me as a shift, though. Presumably, if sqlite (or, say, its heavier weight, also free, cousin PostgreSQL) can mimic enough functionality of the proprietary company-wide db, why isn't bare SQL enough to access those functions?

It is, but then developers have to make a conscious effort to stick with a portable subset of SQL. It is much easier to have an ORM do that job for you.

> Too difficult to ensure one is writing a portable subset of the language?

For complex applications, that is very difficult, yes.

> This runs into the danger of being a "true Scotsman" type argument if it doesn't apply to at least most popular ORMs (or at least one for each popular language).

Some of the earlier Java EE ORM:s were truly garbage. They included way to much convenient magic. For example, it was often hard to predict whether a someObj.getFoo() call would trigger a database lookup or not. OP might have cut his teeth on one of those and therefore believe they all introduce extra complexity.

> They are, by definition, if one already knows the equivalent SQL statement: one has to learn the additional, separate syntax.

For example, in C# Linq is already used for functional list processing. Using them for SQL is just a change of backend.

> a conscious effort to stick with a portable subset of SQL.

Or perhaps just a validation tool. Since you mentioned testing, it might even be enough to use a stripped-down parser from something like SQLite. Granted, integration could be tough if these don't already exist.

> It is much easier to have an ORM do that job for you.

> For complex applications, that is very difficult, yes.

I was hoping for more than the bare assertion. What makes it so difficult? What makes an application "complex" in this context?

> Some of the earlier Java EE ORM:s were truly garbage.

So modern ones are all (or mostly) well-written?

> For example, in C# Linq is already used for functional list processing. Using them for SQL is just a change of backend.

This sounds very much like an application developer treating the database as merely a dumb datastore, which I suspect (and I admit I'm speculating here) the OP would consider an anti-pattern.

Perhaps that's the real source of the OP's question, the perspective of someone who's well-versed enough with RDBMSes and their ability to manipulate/process data before it even reaches the application, even with only portable[1] SQL.

Hopefully the OP, or someone who responded with a "me too" to the OP can chime in to support or refute my speculation.

[1] I would expect the intersection of MSSQL, Oracle, and Postgres to be suitably powerful

op here. I'm not sure about using the database as a dumb database. The only times I've seen that done the code was so bad that the datbase structure was not really the main concern. I will say that often there are a lot of missing indexes and foreign keys in those situations. I get my understanding of the data model from the database. Foreign keys not only help constrain things to valid data but they provide a nice map to navigate the database with.

> Perhaps that's the real source of the OP's question, the perspective of someone who's well-versed enough with RDBMSes and their ability to manipulate/process data before it even reaches the application, even with only portable[1] SQL.

Yeah you are totally correct here. I find I can significantly reduce the complexity of the code by putting a lot of logic into the SQL. SQL is a very good domain specific language. Additionally the database is very efficient at it's job. I've never been able to replicate this in an ORM.

In ORMs I have found the large number of database queries can make it difficult to move the database to a different machine to the application.

But even this aside I have to learn a lot to use a tool such as hibernate. All that learning and added complexity has to pay for itself somewhere. I'm not sure I've seen that pay off. But I honestly think I'm missing something.

> But I honestly think I'm missing something.

Since you confirmed my presumption, I don't think you're missing anything. Put another way, I think you're mostly missing the perspective of the typical ORM proponent.

From that perspective, even if the datastore isn't necessarily dumb, as such, it's more like an afterthought, conceptually subservient/inferior to the application, perhaps even a necessary evil. The job of the ORM is to release the data from that terrible (relational) paradigm and bring it into enlightened (object) world of whatever language the application is written in. This is, of course, a bit of a caricature for effect.

There was a fairly extensive discussion of a database-first paradigm a few weeks ago here on HN:

https://news.ycombinator.com/item?id=17247088 https://blog.jooq.org/2018/06/06/truth-first-or-why-you-shou...

It's worth noting that even that author didn't suggest entirely avoiding an ORM, but, rather, using code-generation (in one that offers it).

I don't know I personally find them quite useful and time saving, esp Eloquent ORM. I don't think I have written a direct sql query in a very long time unless it's an exceptional case like below.

But the best part is if I ever needed to, the ORM isn't stopping me.. like recently I wanted to `insert ignore` thousands of values in the table using a single sql query. Afaik that isn't supported by Eloquent so that I did using query. Rest the ORM is able to handle almost everything and keeps the code much more readable and easier to write.

An ORM is like any other tool, you can either use it properly or misuse it. I've seen terrible hand written SQL code, and I've seen terrible LINQ queries. But with EF for example, once you learn LINQ and it's power, it becomes a very invaluable tool.

Also, the argument of it getting in the way is moot too, as you can still call a stored proc via an ORM as well.

There's been more than a few times where I'm working on a smaller project and start looking at ORM's, the time it would take to learn the ins and outs and getting it set up, and then just write a few SQL statements instead and call it done.

Nor me. A possible smart ORM should take the DB as it is and shouldn't generate static code (ok at runtime).

Micro orms are the sweet spot for me.

Reply on news.ycombinator.com