
Ask HN: What happened to the ORM? - olalonde
After all those years, I sometimes feel like the whole ORM thing hasn&#x27;t made progress or been settled. Which ORMs do you like and what do you like about them? What is the sweet spot? I&#x27;m asking this because database I&#x2F;O has always been my biggest frustration when doing application programming. These days, I do more functional programming and tend to side with the ORM haters but I do occasionally wish I could just abstract out the database more. Anyways, I&#x27;d just like to hear HN&#x27;s thoughts on this.
======
ciconia
I'm the original author of Sequel [1], an ORM for Ruby. Lately I've been
finding that ORM's actually get in the way of accomplishing stuff. I think
there's a case to be made for less abstraction in programming in general, and
access to data stores is a major part of that.

I believe in most cases the efficiencies that can be gained from using an ORM
is quickly offset by a substantial dependency on third-party code, limited
control of performance, and inability to express more complex SQL expressions,
such as CTE's, lateral expressions, JSONB functions and operators etc.

ORM's also tend to pile on lots of functionality, of which most projects will
normally only use a small fraction.

For an in-production system I've been maintaining for the last 10 years, I've
recently ripped out the ORM code, replacing it with raw SQL queries, and a bit
of DRY glue code. Results: less code, better performing queries, and less
dependencies.

[1]
[https://github.com/jeremyevans/sequel](https://github.com/jeremyevans/sequel)

~~~
TeMPOraL
> _I think there 's a case to be made for less abstraction in programming in
> general, and access to data stores is a major part of that._

I'd like to hear more of your thoughts on that.

My experiences with ORMs are less about too much abstraction, and more about
them encouraging _wrong_ abstractions. I'm yet to work with an ORM-using
project that doesn't employ ORM objects as business model objects. The ones
I've worked with always exploded due to the fact that even with ORM, database
model doesn't map 1:1 to how the business logic would like to see the world -
and the tension of trying to use one set of objects for both purposes at the
same time is what messed up the codebase.

~~~
ciconia
> I'd like to hear more of your thoughts on that.

These days it seems that even for the most rudimentary programming tasks
everybody reaches straightaway for their favorite framework/lib without too
much thinking.

I think in a way all these frameworks and libraries have had a negative impact
on the general quality of coding. Programmers end up with limited knowledge of
how the underlying technologies work: the HTTP protocol, SQL, the DOM...

And because these frameworks tend to grow over time, in an effort to be
comprehensive and serve everyone's needs, they introduce more and more
dependencies, more and more tooling, more and more code, which you'll need to
get to know sooner or later.

Programmers who use said frameworks and libs become more and more "hooked",
start using more features than actually needed (do you really need that auto
generated REST API for that 3-page dry cleaning website?), and in the process
become, well, dumber.

You can of course take this argument to absurdity by claiming that everybody
should just write machine code. That's of course not what I mean. I just think
that for many programming tasks, programmers would be better off - and no less
productive - just by attacking the problem head on, with a good understanding
of the technologies involved.

Sometimes a simple handsaw is more efficient and less hassle to use than that
fancy sophisticated table saw you got in the corner.

------
tony
ORM's are good form, why?

\- Avoids mistakes when dealing with writing raw SQL queries (SQL is quite
repetitive in practice)

\- The declarative nature of classes maps well to types and relationships

\- The declarative nature of classes maps out well to tables, even with
polymorphism [1]

\- Keeping "Models" in an ORM often maps out well to migration utility
(Alembic, Django Migrations)

\- Object-chaining map very well to queries

\- ORM objects can be reused and composed

\- They can abstract out intricacies across SQL dialects

\- They can potentially make it easier to migrate to different SQL servers if
no specialized features were used

\- Can help avoid common security vulnerabilities like SQL injections

\- When something can't be expressed via ORM relationships, they tend to allow
the dev to drop down to raw SQL. In the case of SQLAlchemy, there is a core
query language [2], too.

\- In the case of Django, QuerySet is used as a standard throughout extensions
that power a whole community. Plugins that don't even know each other (e.g.
django-filter and django-tables2) can operate on the same django queryset to
filter/search and sort/display data.

I mention QuerySet/Django ORM quite a bit in a recent blog post at
[https://www.git-pull.com/code_explorer/django-vs-flask.html](https://www.git-
pull.com/code_explorer/django-vs-flask.html).

[1]
[http://docs.sqlalchemy.org/en/latest/orm/inheritance.html](http://docs.sqlalchemy.org/en/latest/orm/inheritance.html)
[2]
[http://docs.sqlalchemy.org/en/latest/core/](http://docs.sqlalchemy.org/en/latest/core/)

~~~
barrkel
> Object-chaining map very well to queries

This isn't remotely true; it turns what looks like an in-memory access into a
network round trip. Navigating your database using an idiom of lists and
member accesses is a fast path to n+1 queries all over the place, or ORM tuned
to eagerly fetch the whole world when you only touch a tiny piece of it.

The closer an ORM's API is to a monad, the happier you'll be. Fundamentally,
accessing the database is executing remote code; the more you can package up
into the query before it goes off and does anything, the better performance
you'll see.

IMO trying to shoehorn objects (in the OO sense, with polymorphism, data
hiding and behaviour) into a database is wrong-headed. Data hiding in
particular is a wrong mental model for thinking about facts in the database,
and the more is hidden, the harder it will be to reason about performance and
bulk remote operations generally.

~~~
bildung
_> This isn't remotely true; it turns what looks like an in-memory access into
a network round trip. Navigating your database using an idiom of lists and
member accesses is a fast path to n+1 queries all over the place, or ORM tuned
to eagerly fetch the whole world when you only touch a tiny piece of it._

That isn't true for e.g. Django's ORM, which lazily evaluates the query and
only actually accesses the db after that, with a single query, and filtering
done in SQL.

~~~
spookylukey
This is not the case - with Django's ORM you are still susceptible to the N+1
problem. It can be largely mitigated through `select_related` and
`prefetch_related`, but the fundamental issue is there - that simply by
accessing an attribute for an object that wasn't already fetched, you can do
another database query without it being at all clear in the code that this
will happen.

~~~
bildung
That's true, though select_related is mostly the default, and that behaviour
is documented well. In practice, I very seldomly find N+1 cases with Djangos
ORM, even when doing relatively complex data analysis.

------
slackingoff2017
They're still alive and well, just not sexy anymore. The most popular one is
probably still Hibernate which is helped by Java being the top high level
language.

I never understood the ORM hate. Every place I worked we intermingled raw SQL
when needed. Hibernate has a way to clear query caches so you can use raw SQL
when you need to. You can just write raw SQL exclusively if you want within
hibernate so I don't get how you could lose anything :) .

Still, my experience is mostly with Hibernate. It's extremely mature, meaning
reliable, feature complete, and only 0-30% slower than raw queries in most
cases. It makes adding support for things like multitenency and audited tables
a breeze without losing database agnostic behavior. It makes database upgrades
a no-brainer 95% of the time too. It has a built in memory caches that help
enormously with common queries. Probably the biggest thing is it makes the
database strongly typed so it's possible to refactor. Code refactoring in Java
is easy but raw stringified SQL is nearly impossible to fix in any language.

I think the biggest counterpoint to ORM is shitty ORM. Things like SQLAlchemy
generate downright horrific SQL slowing everything to a crawl and causing
deadlocks. Another honest counterpoint to ORM is the learning curve. Everyone
is taught SQL but the ORM is more abstract and harder to reason about, not a
fun thing to learn.

TBH I think most ORM's are just poorly done. Putting an object abstraction on
a relational database is hard. The only ones I've enjoyed for completeness and
performance are Hibernate and to some extent Entity Framework. EF being the
easiest to use but a bit slower with less features.

I have heard good things about Dapper but never used it. I like the idea of
injecting a SQL DSL into the language itself, wish it was more prevalent.

~~~
wiz21c
>> Things like SQLAlchemy generate downright horrific SQL slowing everything
to a crawl and causing deadlocks.

Not my experience... Would you care to explain ? SQLA works pretty well for
me, as long as I stay in rather simple queries. For example, data analysis
queries are next to impossible to express with SQLA, but that doesn't matter
much.

For the simple update queries, it just works fine for me. It also allows for
very good control on relationships, etc.

~~~
majewsky
I've always had success with ORMs when I let them write the 95% of trivial
queries (SELECT * FROM table, INSERT INTO table, UPDATE table SET ... WHERE id
= x). The complex queries I'd always rather write by hand to take advantage of
the expressiveness of SQL, and to make sure that queries hit my indexes
correctly.

~~~
thedudemabry
I totally agree. Mr. Fowler's article lays out a good case for ORMs being a
good, leaky abstraction with manholes for diving deeper when necessary. That
implies that we can't, as application developers, use it as a black box,
because underlying knowledge is important. Until someone comes up with a magic
algebra for paving over the details SQL, we're stuck mitigating or dealing
with its realities.

One of the things I've recently prioritized is the ability to run arbitrary
queries (generally read-only) at some sort of REPL prompt, for debugging and
investigative purposes. ORMs and their offshoots offer a great deal of support
in that area because you can converse with an ORM in a domain-specific way
without dropping into raw SQL. That can be a big advantage.

------
fzaninotto
It's now called GraphQL. Let me explain.

First, the need for data has moved from the server to the client side in API-
centric architectures. The backend is often just a dumb HTTP layer on top of a
(sometimes relational, sometimes not) data persistence. Heck, I even built
apps without server, using PostgREST.

Second, on the client side, we don't need an object-oriented interface to the
datastore, because JavaScript. But we do need a powerful declarative query
builder, and static data types to build up on. GraphQL provides that.

Third, if the purpose of the ORM was ever to abstract the SQL dialect of the
database, GraphQL has resolvers, which turn a GraphQL request into a request
for the database of your choice. So it does that job, too.

Last, for the data crunching jobs that you decide to do on the backend, you
often need specialized data stores (event store, document store, full-text
search index), and ORMs don't address that. And since these jobs are often
resource intensive, you need the best performance - therefore falling back to
raw SQL in most cases.

ORMs were a good solution for the kind of software architectures that were
used for the web in the 00s. Since then, we have different needs, which
require different solutions.

Disclaimer: I was the maintainer of a popular open-source PHP ORM [1] for 3
years. I no longer use ORMs.

[1] [http://propelorm.org/](http://propelorm.org/)

~~~
ruslan_talpa
What have you used PostgREST for?

~~~
fzaninotto
Public and private web apps for a French media company. Dumb backend, most of
the logic was in the frontend (React) and in async workers (Node.js, with
direct access to the database without ORM).

~~~
Svenstaro
Serious question, if you do most logic in the frontend then where do you do
the server side input validation and where do you do the business logic? If I
fumble around with your client side code and you don't check that then I could
theoretically do whatever I want in your database.

------
coldtea
What happened to the ORM?

To my opinion, it was a bad solution to the wrong problem.

For one, we're not that enamoured with objects anymore (what with functional
programming, immutability, etc).

Second, SQL and DDL, being declarative, is both a higher abstraction that (at
least) most ORMs, and offers more fine level control to the DB at the same
time!

Third, people don't really switch databases that often, for the abstraction
between different SQL syntaxes to matter.

~~~
Scarblac
> Third, people don't really switch databases that often, for the abstraction
> between different SQL syntaxes to matter.

But third-party libraries shouldn't assume any specific SQL database, so if
you want libraries that can do database things, ORMs are very useful.

~~~
TeMPOraL
They can always do SQL abstraction _without_ doing the object-relational
mapping.

------
codeulike
An old article, but still somewhat relevant I think:

"ORM is the Vietnam of Computer Science" (2006)

[https://blog.codinghorror.com/object-relational-mapping-
is-t...](https://blog.codinghorror.com/object-relational-mapping-is-the-
vietnam-of-computer-science/)

------
_Codemonkeyism
I thought we've been through :-)

"ORMs are a thing of the past" (2009)
[http://codemonkeyism.com/orms/](http://codemonkeyism.com/orms/)

"Be careful with magical code" (2010) "[...] ORMs and in particular Hibernate
[...]" [http://codemonkeyism.com/beware-magical-
code/](http://codemonkeyism.com/beware-magical-code/)

------
barrkel
My experience is strongly flavoured by developing and optimizing on the edge
of what the database is able to do; and my performance condition isn't scaling
up millions of tiny trivial queries, but getting big hairy dynamically
generated queries to execute efficiently within a deadline.

No ORM comes remotely close to being able to solve this kind of problem. The
kinds of things I need to consider:

\- analyzing select / predicate / order / etc. for tables used and selectively
including tables

\- forcing query evaluation order by creating a subtable query with
conditions, projecting out an id, and rejoining in the next query up

\- analyzing predicates and rewriting predicate trees to push predicates down
into subtable queries, or convert predicates into joins, or replace references
to columns on other tables with denormalized columns

\- gathering heuristics about query set size and changing query generation
technique

\- splitting queries into parallelizable queries and composing result set
client-side

An ORM doesn't really help me. Even a library tuned for symbolic manipulation
of SQL doesn't help hugely; my life is made much simpler when I can freely
extend the SQL AST classes with my own analytic and rewrite methods.

~~~
joncrocks
Isn't this the type of thing that 'the' (or a) query optimiser is meant to do?

With a suitably tweaked ORM, you can pass platform-specific hints down to the
DB. The platform's query optimiser should be (obviously up for debate!) the
one that's taking a query and working out the most efficient way of executing
it.

Not saying it always will always pick the best execution, but if you're
getting to the point of supplanting the optimiser you're starting to replace
the database itself.

At which point you're probably beyonds the realms of what an ORM can and
should reasonably doing, you're starting to writing your own custom
persistence platform.

~~~
barrkel
No, query planners are generally hopeless; for my use cases, it's trivial to
get orders of magnitude better performance with different query structures.

If you have just a predicate, or a single join, most query planners are just
about ok; with multiple tables and predicates touching those tables, there's
ever increasing risk it will start the join on the wrong table and produce too
many rows somewhere in the plan. Engines like PostgreSQL are unpredictably
stupid (but sometimes very smart), while MySQL is predictably stupid (but
consistently fast when you keep it simple and avoid specific constructs).

When designing for interactive use, you want to design for a maximum run time
on the query. If that means using a query which is often not the fastest, but
is never slower than X, it's the query to use. Smart planners are more of a
hindrance than a help, because they reduce predictability.

The worst case is when a planner starts with a small row set (planners usually
like starting with small row sets) and joins against a big table expecting a
similarly small joined set, but ends up with an enormous result set because of
the cardinality of the join. Estimating join cardinality is a tough problem
for database vendors to solve, and it's critical to figuring out the correct
join order - particularly since the number of join strategies grows with the
factorial of the number of tables to join, so the planner can't spend much
time on it. Meanwhile, the application developer may very well know the
designed cardinalities and a join sequence that minimizes the chance of a
blowup, the most important thing for interactive use.

~~~
joncrocks
Well yes, this is exactly what a query optimiser is meant to do.

What you're meant to do is add a few indexes, maybe sprinkle some hints into
your query (platform specific), based on your knowledge based on your rough
knowledge of the statistics of your data structures and let the
optimiser/planner work out the best way to execute it.

Now yes, the optimiser may sometimes get it hilariously wrong, but I guess
that's the price you pay for a relatively portable (across DB platforms, with
some tweaking of the hints) solution.

The risk of going too far down the rabbit hole of dynamically re-writing the
SQL is that you're essentially trying to second-guess any query
optimiser/planner and for most of people making software they're going to do a
poor job building a 'shadow optimiser' based on 'experience' of a specific
platform and how it behaves in observed scenarios. If the people building
databases are finding it hard, there's probably a reasonably good reason.

I'd also note that this isn't really a problem with ORMs per-se, but with
relational DB platforms and their performance when running arbitrarily complex
SQL.

~~~
barrkel
You can concretely measure how much time it takes to scan X million rows of a
table; how much slower it is for every join you add; how much slower an index
is to scan for every column that's added to it; how much slower it gets for
every column you add to the select clause; etc. And determining optimal join
order in my case isn't particularly hard (it's a lot like a star schema; the
key thing is not to bring in the whole of the big table in the middle).

I was a compiler engineer in my previous job; transforming and optimizing
expression trees are my bread and butter. I'd give myself good odds of
improving MySQL for my particular use case - I'd know where to look, what
stats to collect and how to approach things differently. But likely my
optimizations would be pretty specific to my situation, and not everybody
would be happy with more predictable, sometimes slower performance.

------
flukus
They still have their uses, particularly with largish LOB/enterprise apps. For
me they are no longer the goto solution for apps I write though, if I started
building one today I would start with an "ORM lite" (never liked the term)
like dapper. If it ever got big and complicated enough that an ORM was
justified then I would introduce one.

I find an ORM really excels when you've got complicated business logic, the
sort of thing where there are dozens of business rules that may or may not be
involved in any given installation. Something like this is extremely stateful
and and ORM does a great job of tracking this state. In fact, this is what I'd
define an ORM as, a state tracker for database modifications. If you don't
have much state then you probably won't get much out of an ORM.

Even when using an ORM does make sense, it's important to remember that they
don't make sense everywhere. For a performance critical or particularly
complicated query then SQL should still be a fallback.

------
madiathomas
Not sure in other platforms, but in the .NET world, ORM(read Entity Framework)
usage skyrocketed and improvements are made with each version of .NET
released. I use Entity Framework. Both at work and when doing my home
projects. Code First. I started with Database First and only recently moved to
Code First. It feels so good modeling my database using C#.NET classes instead
of doing it in SQL. Don't get me wrong. I do like SQL and I am very good with
it, but I prefer C# better.

Speed of development greatly improved since I switched to EF. Speed of
development is what matters most for me when building first version of an
application.

------
TurboHaskal
It could be that most haters are simply using the wrong one? If JPA was my
sole experience with ORM I would side with the haters as well, fortunately I
built enough successful projects using DBIx::Class and SQLAlchemy to
understand the advantages of using an ORM to the point that I refuse working
without one.

------
realusername
I still use ActiveRecord a lot (Rails) and I would not write SQL queries
manually since the ORM works so well. But I used ORMs in other languages and
they were not as convenient so maybe it depends on the ORM itself. The time it
saves compared to standard queries is massive.

ORM haters would likely point out that complicated queries are hard to write
with an ORM but these complicated queries are not a massive part of the work
and can still be abstracted away in a method using SQL if needed.

Another good point of the ORM: validation of data. There are plenty of
validation you cannot enforce in SQL (regex, postcode...).

~~~
empthought
> Another good point of the ORM: validation of data. There are plenty of
> validation you cannot enforce in SQL (regex, postcode...).

Those particular examples are trivial to enforce in every DBMS I am aware
of...

~~~
tarr11
Dbms constraints are a usually a subset of constraints that are supported by
ORMs.

------
bendermon
The big question is when to use ORMs/raw SQL.

SQL is the most concise and perfect fit for RDBMS.

However, at the application level there are benefits of using ORM.

\- The application itself is usually imperative style as against the
declarative nature of SQL.

\- Chaining is sometimes more readable and concise. One can chain dynamic
filters.

\- Abstract the underlying data model with higher level names. SQL eq. of
table views.

\- Hides the underlying relational model. Which can sometimes be helpful in a
large code base. And sometimes a curse.

I normally opt for ORM in Rails/Django web apps. But SQL in

\- Performance critical \- Report generation, where it might be complex and
declarative nature of SQL shines.

------
falcolas
I have the same opinion now that I did at the beginning. They provide a decent
abstraction of the database. But that abstraction leaks.

Just a week ago, I ran into an issue where SQLAlchemy wouldn't support a "in"
clause against a list of Model objects. There's a leak that I had to patch by
using a list comprehension to generate a list of integer IDs from those
objects.

Then I ran into a "you're doing a check against an empty list; we are pretty
sure this will come back with no results, but we're going to run the query
anyways." Another leak (and it's a valid one), but I have to manually work
around it if I don't want that extra query run (I didn't).

Finally, we get the script to the point where it's passing all of the unit
tests, and package it up for the integration tests. Those integration tests
fail, because SQLAlchemy dynamically imports DB connecters based on the DB
being used. The unit tests were running in SQLite in-memory DBs, the
production Aurora, requiring a connector to be installed. Another leak.

Ultimately, ORMs can be useful, but they are an abstraction which leaks all
over the place. So long as you're OK with the tradeoffs (the rest of my team
was; I'm ambivalent, though I was the one who ended up fixing all of these
leaks), they're another good tool for your programming toolkit.

------
lz400
I think part of the reason they are not talked about anymore is that people
like to talk about the hot and sexy and not the mature and incrementally
improving. The hot and sexy now is full of Javascript and noSQL so ORMs aren't
as relevant as before. ie: schemaless JSON + dynamic languages have eaten a
big piece of the cake.

That said, people seem to still use ORMs in Ruby, Java, C#, etc. and
frameworks look fairly mature.

------
pastaelle
The point of an ORM is not to be some magic 1:1 powerhouse, but to make it
easy to abstract your storage layer. If I had the time and resources, I would
make storage plugins with testing interfaces and dependency injections that
precisely match the query patterns of my application. ORMs are almost never an
ideal fit but they give me that for free out of the box.

------
mwnorman2
I was part of the team that developed TopLink, later EclipseLink that became
the reference implementation of JPA. After I left Oracle, almost all of the
original 30+ Engineers/QA/Managers were moved to a different project. The git-
repo is infrequently updated by only a few support Engineers. From my point-
of-view this means that the de-facto Java ORM is Hibernate (even though we
were first!). It is very mature, well-tested yet still getting some 'love'
(c.f. recent changes for Java 8 streams).

Other posters have mentioned the 'Vietnam of CS' post from many years ago -
the main message I would like to highlight is that ORM's are deceptive: it
_appears easy_ to create one, but once you spend some time with how people
actually use databases, the real-world starts to drag you down (the 'leaky
abstraction' meme thrown around). An example of this is: how many ways are
there to map a One-to-One relationship (hint: greater than 5)

~~~
mwnorman2
Answer: nine ways to map 1:1

See slides 20, 21 and 22:
[http://images.slideplayer.com/9/2645248/slides/slide_20.jpg](http://images.slideplayer.com/9/2645248/slides/slide_20.jpg)

------
DanielBMarkham
I currently think ORMs might be an anti-pattern, but I'm interested in what
other folks think. It's the same kind of anti-pattern that led us down the
path of taking Object-Oriented Analysis and generating an entire stack using
MDD.

The thing is, you should really only want your database to do a small number
of things, and those things you should pay special attention to. You don't
want a database that does everything and all runs on autopilot. Then who the
heck is coding the system?

I think most any app has a reasonable number of features which map to an also-
reasonable-number of function calls back to persistent storage. Identify those
and make them first-class citizens in your coding. After all, aside from UI
dazzle, that's where all the magic happens.

------
manigandham
Nothing happened. 99% of scenarios are a great fit for a _tool_ that maps and
moves data from the object-oriented programming language to relational
databases. They decrease errors, increase productivity, and for the most part
well.

Most of the complaints seem to be from using exceptionally bad ORMs or using
them in the 1% of scenarios where performance is critical or very complex
queries must be produced.

There really needs to be less blind hate and more understanding of when and
where tools should be used (as well as the general quality of the tool
itself). That's one of the basic tenets of programming so it's rather
unfortunate to see so many misguided arguments here.

------
richardknop
One of the issues is that programming languages are evolving so fast it is
hard for ORM libraries to keep up.

A good ORM library needs to be well designed, battle tested and mature. Then
it provides great benefits over writing just raw SQL. That takes years of
development effort.

The problem is that with new languages (Node, Golang, Rust) there hasn't been
enough time and effort put into ORM yet to produce something good enough to be
usable.

When developing in Java or Python there are great, mature and battle tested
ORMs I would use but with these new languages I had bad experience with ORMs
as they still seem experimental and have issues therefor stick to raw SQL when
working in newer languages.

------
baq
there's raw SQL, there are ORM and then there's the mid layer of DSL-ish query
builders like sqlalchemy.core, which are amazing if you've ever been
dissapointed by not being able to compose queries in a sane way in SQL.

~~~
okket
SQL is getting better though, see

[http://modern-sql.com/blog/2017-06/whats-new-in-sql-2016](http://modern-
sql.com/blog/2017-06/whats-new-in-sql-2016)

[http://modern-sql.com/use-case/literate-sql](http://modern-sql.com/use-
case/literate-sql)

------
alex_hitchins
I come from a heavy C# background. I can say that MS has been revising their
Entity Framework platform and it's a perfectly good ORM tool to use.

Being stubborn and old-school, I prefer the lower level roll your own approach
for speed and efficiency. That said, if dealing with an enterprise application
where ease of code mendability/adaptability is key, I'd happily go with
something like Entity Framework.

It would be interesting to see if code generation tools have progressed in
this domain at all. I remember several data layer class generators which
although not exactly an ORM, could provide many of the same benefits.

------
vram22
Using just SQL or using an ORM is not a binary choice (and I'm not talking
about the ability to drop down to SQL in an ORM). There is a middle or third
option: libraries that wrap SQL (with a thin or thick layer) and provide a
somewhat higher level of abstraction. Often called Data Access Layers, or
similar names. SQLAlchemy Core is one such, and the pyDAL of web2py is another
[1].

[1] I had blogged briefly about pyDAL here: pyDAL, a pure Python Database
Abstraction Layer:

[https://jugad2.blogspot.in/2014/12/pydal-pure-python-
databas...](https://jugad2.blogspot.in/2014/12/pydal-pure-python-database-
abstraction_30.html)

I'd worked as the team leader on one such database middleware product, written
in C for Windows, some years ago. It was successful, and somewhat widely used
in projects for clients at the company where we built it.

Such products can enable you to write less boilerplate SQL code, and also to
work at a somewhat higher level of abstraction, thereby providing some
productivity.

I agree with some of the other comments here, that 1) such abstractions can
sometimes be leaky, and 2) SQL is not hard to learn (for the basics and a bit
more - hell, it used to be considered bread-and-butter tech), but for some
reason there seems to be an impression that it is tough, from the last few
years - sometime after Web 2.0, maybe.

------
teilo
I think the answer is to take a hybrid approach. Use an ORM where it makes
sense. You shouldn't need to write SQL to do simple CRUD and elementary
queries with a join or two. But when things get more complex, you should be
able to easily drop to SQL, and have the results mapped back to an object.
Perhaps also a DSL that allows you to write queries in code. I have yet to
find an ORM that can elegantly accomplish this, although SQLAlchemy comes
close.

------
watwut
I never seen orm as abstracting database out. More like easier to maintain way
to access it and have features like caching/consistency available without much
work.

------
out_of_protocol
Take a look at Ecto (kind-of-orm for elixir), didn't even hear about anything
comparable for other languages (which is sad)

* No impicit N+1 and stuff like that (elixir is functional with immutable variables)

* Whole "orm" is really fast, thanks to macros (compile-time metaprogramming)

* Can express very complex queries, allowing raw sql then necessary

[https://hexdocs.pm/ecto/Ecto.html](https://hexdocs.pm/ecto/Ecto.html)

~~~
odammit
I agree, Ecto is a great.

Ive always appreciated ORMs, but disliked how people tend to forget about the
database behind them.

Ecto gives you a lot of power and flexibility, and its very easy to remember
there is SQL behind there.

Compose-able queries are awesome.

Schemaless queries are awesome.

Not tying your "model" or "context" directly to a table or letting it be a
portion of a table is awesome.

Backing "validations" with database constraints is awesome.

That Ecto team kills it.

------
TheAceOfHearts
It depends a lot on the project. I think learning SQL and basic database
theory are a strict requirement. ORMs are a great tools to help make life
easier, but you'll need to reach in sometimes in order to fix performance
bugs. I've learned to love both.

ActiveRecord is amazing. It makes it really easy to get stuff up and running,
and it works for a wide range of problems. There's nothing better for typical
CRUD operations, especially when you models map mostly 1-to-1 to your tables.
You start to encounter problems later on as the application grows, especially
when you have lots of associations, complicated queries, or your models grow
too fat.

Maybe your problem isn't really with ORMs, but with MVC or whatever
application architecture you're using. For example, Rails has been criticized
because of its fat models and its overall "flavor" of MVC.

Trailblazer [0] was created by a Rails critic as an alternative approach for
structuring your web application. I won't go into the details, since the
website already does a good job explaining. But he effectively kills the
traditional model and shifts the persistence layer around, which is a fairly
common pattern.

I remember reading book that discussed ASP.NET's approach to MVC. I don't
quite remember all the details, but it mostly amounted to having four primary
layers (presentation, application, domain, infrastructure) which were further
divided and given specific responsibilities. This approach is overly verbose
and complicated for some cases, but the abstractions appear to make more sense
as the application increases in complexity. A small example is an application
in which you have to break up a model's data between multiple storage services
like Redis and the RDBMS.

You'll probably like Ecto [1]. It's an ORM for Elixir. After the 2.0 rewrite
it's a lightweight embodiment of some of the concepts mentioned above. One of
the key insights is that you'll want to separate business logic from data and
presentation. But it really depends on the project! It might help or it might
just add more boilerplate.

[0] [http://trailblazer.to](http://trailblazer.to)

[1] [https://hexdocs.pm/ecto/getting-
started.html](https://hexdocs.pm/ecto/getting-started.html)

------
gt565k
I never understood the ORM hate/bashing. It's like the framework fanboy
paradigm. "I love framework X, and framework Y sucks!"

They are tools built to help solve a particular problem.

ORMs are great when dealing with CRUD operations on entities and remove the
need to write tedious boilerplate code every time. On the other hand, ORMs can
be misused and cause all kinds of performance related problems. Most obvious
use-case is developers that don't have much experience with databases, and
instead of choosing to perform processing of data in the database run-time,
they fetch data through the ORM and write poor performing code riddled with
business rules in the application layer.

I think it's good to take a step back and understand that the ORM is a tool,
which when used for the purpose it was designed for, works well.

Complex joins and queries can still take advantage of an ORM. One example is
using materialized or non-materialized views, which can then map to an Object
using the ORM to query it.

That way the bulk of the processing is done in the database runtime, and
simply accessing the object is done through the ORM.

There's also the separation of concerns. A seasoned developer will opt for
creating a well defined schema, and baking business rules into the database
layer, because he knows that a well designed, and highly normalized data model
will outlive any application where the business rules are housed in the
application layer.

All things considered, I do love the ActiveRecord ORM design-pattern. I've
worked with hibernate, JPA, and some other ones in the past, but ActiveRecord
as employed by Rails and Django, is still my favorite.

------
jasonkester
Nothing. They're just as bad an idea today. I think perhaps that message had
begun to sink in.

Developers have this unreasonable fear of the SQL language, and I can't really
understand why. Sure, it's different. But then so is CoffeeScript and
everybody seemed fine learning to draw little arrows in their code for the six
weeks that was popular.

For me, SQL is the perfect language for describing and working with databases.
"Computer, give me a list of Students who are taking Algebra 101". But
developers really seem to prefer taking that whole Student list and spinning
through it themselves, looking up every class everybody is taking and building
their own list thank you very much.

And instead of using a simple, concise way of defining data tables (that
follows that same elegant data retreival and manipulation language), devs
would much prefer something with angle brackets.

So they never bother learning the easy way of doing things and spend several
years inventing something more complex and opaque.

Eventually, I guess enough people got fed up of dealing with the silliness and
picked up a book on SQL.

~~~
viraptor
I'm not sure which ORMs you worked with (any?), but that's not how they work.
You don't pull the whole table to filter it in your code. Any non-toy ORM is
perfectly capable of filtering on a known attribute or relation of a class on
the db side.

There's some irony in the fact you're making fun of devs who don't want to
learn SQL, while you don't understand ORM...

------
stuaxo
SqlAlchemy and the Django ORM are good - recently worked on a C# project and
the devs are very anti-orm.

I think, the REPL makes a huge difference.

In a python project, getting a shell at any point - or experimenting in the
shell Jupyter is really straightforward.

The brackety languages have options for this sort of thing now, but it is
still just a lot more hassle - the easiest way to get something similar is to
just write SQL.

------
samblr
ORM gets in the way of programming.

ORM abstracts which something developers know as his/her fundamentals : SQL
statements. For simple operations this abstraction doesn't hurt. But this
abstraction becomes obfuscation when we write complex queries or statements.

First a developer has to figure out complex SQL query to solve a business
problem. Then has to figure ORM equivalent to it. Then ,ofcourse one should,
write original SQL query in comment section above ORM API query to help figure
whilst debugging.

Once into debugging, if original SQL query is missing in comment section or
hasn't been updated. Spend extra time on what is wrong with both queries.
Which one is right/wrong and then how to solve the problem.

Although, I do see some value in ORM in migrating to another DB or abstracted
objects of tables-and-properties.

But for simple reason of learning additional set of apis to run SQL statements
hurts!

------
Scarblac
We write science and GIS related interactive web applications. All our
backends are Django because every application has some CRUD components
somewhere and in general Python and Django have fantastic libraries available.

The Django ORM just works, I love it, it's never in our way and does what we
need.

------
neverminder
Nowadays I'd say there's something better than ORMs, take Slick (Scala) for
instance, it's an FRM (Functional Relational Mapping) or JOOQ (Java). Both of
those provide pretty much 1:1 DSL to SQL while at the same time granting a lot
of flexibility and type safety.

------
tanilama
ORM is a leaky abstraction. It does provide benefits, like avoiding SQL
injection, however, it still don't have the level of flexibility as SQL, and
probably will never do.

It is still useful though, but hardly stands on its own term.

------
ericclemmons
In Node, I consistently reach for Knex, which is less of an ORM (like
Bookshelf) and more like a query builder.

This simplifies binding, mapping, and most queries.

There have been rare situations to use ".raw", but the escape hatch id there.

------
bullen
I made my own ORM:
[https://code.google.com/archive/p/memory/](https://code.google.com/archive/p/memory/)

It had a pretty nifty graphical editor:
[http://rupy.se/logic.jar](http://rupy.se/logic.jar)

But now since I need a distributed database I built my own async-to-async
HTTP/JSON thing:
[https://github.com/tinspin/rupy/wiki/Storage](https://github.com/tinspin/rupy/wiki/Storage)

------
luord
I've used several. Liked some, hated some. My favorite is SQAlchemy, can't say
I have any real complaints about it.

The few times I've had to strain the object model, SQAlchemy has degraded
nicely and lets me use raw SQL. It's a great compromise.

I feel like, just like using an ORM can turn into an "all-solving" hammer, so
can functional programming. If a project one's working on feels like it would
benefit from being represented as mapped objects, maybe one should... Do that.

Then again, I don't have much experience nor interest in FP. Haven't needed
it, I guess.

------
Ciantic
There are ORM's in functional languages too, but they too have problems.
Mainly the problems arises from SQL being structurally typed, meaning you can
JOIN totally unrelated tables as long as they have columns with same types.

Practically there are no programming languages that allow to do all set
operations on properties of records. Though Elm comes close. If the language
has this peculiar feature, it should be possible to write a "ORM" that just
works for all queries (given that the language has similar flexibility).

------
jzwinck
I mostly use this now: [https://pandas.pydata.org/pandas-
docs/stable/generated/panda...](https://pandas.pydata.org/pandas-
docs/stable/generated/pandas.read_sql_query.html)

Which is basically an easy to use API that turns a SQL query into an efficient
columnar table in memory.

Yes, this still requires writing actual SQL. And yes it does not help you
insert or update data. But for 95% of uses it works well.

------
oliwarner
Django user here. I feel like it got settled a while ago.

I think that probably encapsulates the whole issue here. ORM isn't one thing,
it's a whole range of implementations and use-cases. For the sorts of
annotations and reports I need to generate, Django has really started to come
into its own in the past couple of years. Well on top of schema migration,
caching and things like that.

So I wouldn't go looking for one answer. It's always going to be contextual.

------
circlefavshape
I work someplace where the primary db schema is very poorly structured, and
the one attempt we (or rather a single dev that was under-supervised) made to
use an ORM was disastrous - a single http request would generate literally
hundreds of queries. I don't know whether the problem was the dev himself or
the ORM or the db, but either way we're having to replace everything and I've
been put off ORMs for a very long time to come

------
forgottenacc57
I used to use an ORM but I discovered in fact that SQL gives me precision,
control and performance and indeed I like it. I'll never go back to the ORM.

------
forgottenpass
_I sometimes feel like the whole ORM thing hasn 't made progress or been
settled._

The object and relation impedance mismatch is not a new concept. The feeling
of progress in ORM was just the act of bringing ORMs to ecosystems that hadn't
had good libraries for it, introducing ORM to a generation of developres that
had never used it, and improving the libraries.

------
reitanqild
_Which ORMs do you like and what do you like about them? What is the sweet
spot?_

I've worked with and liked

\- JPA, mostly Hibernate (although we were mostly sticking to the standard
parts)

\- a few months of Entity Framework which was one of my favourite parts of my
.Net experience (2.place I think after Asp.Net MVC which I really liked.)

\- SQLAlchemy (a few years ago) at least I think I liked it.

------
ux-app
ORMs aren't very good. They make simple things slightly simpler and get in the
way the rest of the time.

------
COil
The problem with ORM? I always forget DQL "like" syntaxes while I never forget
raw SQL.

------
Sakes
There are a number of things you can do to improve performance of queries
generated by ORMs.

* Make sure you are indexing properly

* limit the number of items returned by introducing paging

* use lazy loading where applicable

I'm sure there are other techniques, but these are my gotos.

------
INTPenis
What I see are often custom ORMs implemented for a specific library or
purpose. Many of the APIs I work with daily are in fact a type of ORM.

That said, I never use an ORM myself unless it's one I implemented as stated
above.

------
chicocode
My point against ORM are that will always, no matter what you try, will limit
your domain logic. But it will help you to get things done. What I'm doing is
use ORM only as a mapping for database layer.

------
repomies691
It is a good tool, people use it, however it turns out it was not a silver
bullet (tm). I think it is pretty much settled for what things it makes sense
or not.

------
douche
I'm still using Linq-to-SQL in the .net world. For 99% of what I need it to
do, it works golden. Once in a while I've got to go in and profile and rewrite
a query when somebody that doesn't know what they are doing has introduced an
n+1 query, or done something overly "clever" that's resulted in gnarly SQL
being generated.

------
darkblackcorner
What's wrong with ORMs? I tend to use Dapper. I've found that it can solve
most of my problems, and it's easy to drop back to raw SQL as needed for the
last 5% that need finer control. It's also super easy to extend and customize
based on my requirements, and it's almost as fast as raw SQL.

