SQL (in strings) is not composable at all. Your app rarely has fixed set of queries and almost always need to be able to construct 'where' dynamically, add joins, etc. SQL syntax is especially non-composable, because there once was hype to make programming languages imitate natural languages. In this case, ORM is inevitable if you don't want to have rube goldberg machines of string concatenation everywhere.

Non-OO ORMs may be not called "ORM"s but idea is the same. Moreover, Sqlalchemy Core mentioned in article is great example of non-OO ORM: it composes queries and returns result in tuples[1].

[1] http://docs.sqlalchemy.org/en/rel_1_1/core/tutorial.html

The article makes a good point; I never think of an ORM as a replacement for SQL -- it can work in concert with it. A lot of my code is much simpler with an ORM than it would be worth out it and in other places it's much simpler with direct SQL than without it. Both can live together harmoniously.

Another example of this would be Pandas for Python, the code the developer writes looks like Python, but the DSL provided actually translate to Fortran & C. There's also a project called Dask which reimplements the backend for Pandas' DSL to run distributed. I wish there was an equivalent for pyspark :)

1. They isolate the "magic strings" where your code knows about your database schema in one place (the mappings or conventions). Database changes break your app in only one predictable place.

2. They keep junior developers from constructing queries in ways that are vulnerable to SQL injections.

I put part of that blame on languages. When the wrong thing is so much more natural than the right thing, it's bound to happen.

We use relate (https://github.com/lucidsoftware/relate), a Scala lib.

    sql"SELECT * FROM users WHERE email = $email"

A decent ORM can help manage the impedance mismatch between SQL and the app language. ORMs are very leaky abstractions and definitely restrict design, but metaprogramming SQL in an app language has its own drawbacks.

[1] https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule

Inherited a product that used an ORM. The application was a java application running on multiple hosts and non-sticky sessions. It used session caching, which round-robin load balancing broke silently. Hah, no I'm kidding! It used global caching! Then it was replete with transaction annotations, but it was the wrong transactional level and the transactional annotations were not configured to be used anyway. Then the schema was hard-coded (which I'd usually say is a good thing) because they'd had the database fall over when the ORM attempted to update the schema. Of course the hard-coded schema was missing things and only fixed when the, now manual, updates to the DB caused the queries to outright fail. While the ORM prevented SQL injection, the belief that the ORM somehow handled any kind of injections, meant that the outputs weren't sanitized and XSS injections were not handled at all.

So perhaps the problem isn't SQL or ORMs, its that some programmers don't know what the fuck they are doing, and ORMs make it easier to postpone that enlightenment, sometimes indefinitely.

1. Type safety instead of magic strings

2. Composibiity of queries.

3. Testability - I can mock out the dbcontext in unit test and use an in memory List<T> to test queries without having a database dependencies.

But these days, for any new project, my default is to avoid relational databases unless there is good reason for them. Even for BI, my live database is more than likely going to be NoSql and use a combination of CQRS, Event Sourcing, and messaging.

That's the sales pitch. In essence, they are rarely ever a good idea. As database drivers have become more simple to manage, and ORM's have tried to become more complex, the value proposition (which I'm loath to suggest existed in the first place) dissipated without any trace.

I'm not sure I agree with this. There is a massive benefit to having not just your entire engineering org thinking in an object oriented fashion, but also your entire product org. Designers and PMs having a deep understanding of the data model is extremely useful, and to the extent that everyone is already speaking in OO terms (ie: your records are already going to be structured in this fashion), an ORM makes a ton of sense.

Not only does it give you abstracted and simplified syntax for common access patterns, it gives you a shim to add in all kinds of stuff like caching, analytics etc., that would be extremely difficult without an ORM.

Of course there are pitfalls, mostly poorly constructed queries that don't perform well, but any ORM will let you write raw SQL when you need to.

If your models map 1:1 to database tables it's hard to see why you wouldn't want an ORM, and I think there are a lot of great arguments for structuring your data like that (and of course some against it as well).

Not only does it give you abstracted and simplified syntax for common access patterns, it gives you a shim to add in all kinds of stuff like caching, analytics etc., that would be extremely difficult without an ORM.<<

Whether you use an ORM or not, your data layer can (and generally should) be designed to present a logical data model that may differ from your physical model. While some ORMs offer easy hooks for the addition of caching and analytics, the same is true for a well-designed data layer. I've also seen naive ORM implementations of those kinds of features cause more trouble than they were worth.

>>Of course there are pitfalls, mostly poorly constructed queries that don't perform well, but any ORM will let you write raw SQL when you need to.<<

While this is technically true, many ORMs force you into database access patterns such a multiple synchronous roundtrips to the database that do not perform well and cannot be resolved simply by dropping to raw SQL. A highly performant data layer is going to interact with the database in ways that ORMs generally cannot.

No, lack of thought in using the tools isn't the same as the tool forcing you to do it. Put the complex and efficient query into a view and map the view with the ORM, problem solved and SQL used appropriately and the ORM used appropriately.

> A highly performant data layer is going to interact with the database in ways that ORMs generally cannot.

This is not true, unless using the data layer instead of ORM allows you to structure your data differently. In which case, sure.

> MyBatis is a Java persistence framework that couples objects with stored procedures or SQL statements using an XML descriptor or annotations

IOW an ORM.

Not remotely correct; the purpose of an ORM is to map result sets into objects to avoid repetitive hand coding of those mappings for programs that work with objects. It isn't' about a dev's comfort zone, it's about eliminating the need to hand write repetitive mapping code.

> In essence, they are rarely ever a good idea. As

Also wrong, they are nearly always a good idea as they drastically improve productivity by eliminating tons of manual code that simply isn't necessary to write.

You don't seem to understand the value proposition of an ORM enough to be able to critique it.

There are two sides to this, illustrated by the parent post and you post. Ultimately, it comes down to style preference and his little actual tangible TECHNICAL benefit. The benefits to team process/organization can definitely be argued for ( or even against ) but, that is why the debate will rage on for eternity, IMO.

The funny thing about the anti-ORM-ists position is that they are either:

1) Exclusively embedding raw SQL in their code, and accessing each query result by also hard-coding column names and value types in map look-ups. Maybe this is a "pure" approach, but it is also very brittle as models evolve.

Or,

2) They develop their own naive ORM implementation without the self-awareness to realize that is what they are doing. As zzzeek said, "If your application has objects, and talks to a relational database to marshal result rows into instances of those objects, you are using an ORM."

Look, I don't have a horse in this race, so let me tell you that, from the outside, it's not so clear which side is being more unreasonable. Or maybe it is.

Edit: just to add something tangible, you're misrepresenting others' position too much.

Suggesting that ORMs are awful and no one should use them is effectively premature optimization, and we all know the consequences of that.

It is also actively ignoring the history of ORM development. Django's ORM, I know for instance, was developed at a newspaper so that you could go from "feature writer has giant data set and interesting idea about how to visualize it" to "production interactive newspaper article up and allowing people to see their community from different vantage point" within the scope of a day or two. And it worked. And it continues to work.

Ignoring the functional aspects of the world or proscribing premature optimization with your opinions is effectively soapbox ranting. You can do it, but the solutions and equity being built on the backs of shitty SQL generated by ORMs are going to just walk right on by.

People who bitch about ORMs usually complain for the wrong reasons. Many ORMs are highly opinionated about how to organize their tables, and if you don't adhere to that your code complexity can climb exponentially. If you're constantly going against the grain, maybe you're using the wrong ORM.

Keep in mind even ActiveRecord, as ornery as it can be, still has a "revert to manual query" mode. Any well-designed ORM should support that as well.

For example, Laravel's Eloquent database layer is awesome for the 95% use case. When stuff gets complicated I can drop down to the query builder or raw SQL if things get real hairy.

I work with Django's ORM on a daily basis, and I think being aware of the fact that I can't completely ignore how I interact with my database (e.g. "Well I have an ORM so I shouldn't care" <- this is bad) is enough. The idea that I throw out the proverbial baby here feels like an overcorrection, and while I get how complicated an ORM can become, I think that's more of a result of relying too much on the abstraction.

I'm sure most folks here are familiar with Joel Spolsky's "Leaky Abstractions" article. The way I see it, the ORM abstraction is leaky, and that's okay, because the alternatives are all so involved and complex. For some higher-than-fifty percentage of the time however, the abstraction works. That has value.

... but I'm still getting good use out of an ORM right now. I use Entity Framework for the write operations (with lazy-loading) but hand-written SQL for the reads. It works better than anything else I've tried in the past.

edit: these databases are relational, acid and competitive with postgres. Strong consistency is achieved by threading through a notion of time (like git)

i wrote about it on reddit once, here: https://twitter.com/dustingetz/status/690374292027117568

Trying to make these decisions based on the merits of the techs themselves doesn't derisk the business, it does the opposite.

It's true, you can have a junior person write up a view simply using the ORM as it appears, but then during code review it'll be valuable for the senior person to come in and say things like, "That query is going to suck, here's how you'd get that data without locking so many tables."