
Ask HN: Have you found ORMs help more than they hinder? - xupybd
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.<p>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?
======
fiedzia
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.

~~~
akudha
_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.

~~~
fiedzia
> 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.

------
acesubido
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.

------
amarraja
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

------
tmaly
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.

------
bjourne
Perhaps you are suffering from Blub?
[http://wiki.c2.com/?BlubParadox](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.

~~~
mmt
> 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.

~~~
bjourne
> 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.

~~~
mmt
> 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

~~~
xupybd
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.

~~~
mmt
> 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://news.ycombinator.com/item?id=17247088)
[https://blog.jooq.org/2018/06/06/truth-first-or-why-you-
shou...](https://blog.jooq.org/2018/06/06/truth-first-or-why-you-should-
mostly-implement-database-first-designs/)

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).

------
superasn
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.

------
ed_at_work
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.

------
neilsimp1
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.

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

------
UK-Al05
Micro orms are the sweet spot for me.

