The idea that ORMs are a net-positive is definitely an open question. After 20+ years I'm certainly of the opinion they're not. I recently discovered Slonik (only for postgres, hope there is eventually a port for MySQL and others) and I'm a huge fan of the overall approach and the API. This blog post from the creator explains: https://medium.com/@gajus/stop-using-knex-js-and-earn-30-bf4...

While I agree that there are certainly places where ORMs are net negative for the developer, for a language ecosystem having one is net positive.

Lots of app developers need simple and easy to setup database access so that they can focus on the parts of their app that matters. Not having an ORM means that a decent chunk of them will move on to another language/ecosystem that has the libraries they want.

> The idea that ORMs are a net-positive is definitely an open question.

If it's still being debated, isn't that a good indication that there's no perfect solution for every use case?

ORMs are likely more straightforward when you know you're not going to need to do anything advanced, but get in the way when you need fine grained control for example.

The problem is that easy queries are easy, who cares about orm, and hard ones are too hard for orm. I haven't been able to see the benefits in either easy or hard projects.

you nailed it: In simple cases the abstraction doesn't help enough to justify including it and in complex ones it actually makes things much more complicated because it just doesn't work right.

Ultimately it's useless for both.

I definitely agree there is no perfect solution for every use case. What I think is a mistake, however, is using a technology that makes stuff easier when you are small and have little load, but then become absolute burdens once you become successful and need to scale. I've worked on many a project where the ORM became the primary piece of "tech debt" that was hindering productivity.

I contrast that with technologies that are easy to use when you're small, but then let you layer additional pieces on later when you need to scale, without needing to redo everything.

I guess I mean it's never going to be answered because there is no short answer. It's like asking: what's the best programming language?

I disagree with this sentiment. The use-cases for an ORM are straight-forward. It's more like asking, "which tool is best for getting this nail into this piece of wood?"

What most people discover to be the greatest benefit of using an ORM is the "mapper" bit (converting tabulated data into an object graph and visa versa) and, to a lesser degree, change-tracking.

Somewhat ironically, the overwhelming majority of the time criticism of ORMs is directed at neither of the above, instead pointing to query performance.

You can have data mapping, you can have change-tracking, you can even have schema migrations without opting-in to the pain points many ORMs introduce because these are all somewhat orthogonal concerns.

At the end of the day there is very little to be saved between writing:

  users->where(u => u.name === "John")

and

  SELECT * FROM users WHERE [name] = 'John'

Often, as queries become more complex, the SQL is actually a shorter expression than whatever query DSL comes with the ORM.

A big benefit of the `users->where(...)` approach is being able to reuse and compose. An example would be conditionally adding a WHERE clause based on some parameters. Using the raw query approach you end up having to do some string concatenation versus managing the state of the some query builder object.

I think that the "query builders" though are just one piece of the ORM that you mention, alongside the change-tracking, data mapping, etc. Having a decent query builder that isn't abstracting away too much of the underlying sql (essentially just mapping 1-to-1) plus data mapping are the sweet spot for me personally.

You are correct.

Interestingly, it is this exact property (composition) that creates the most common problems when using an ORM.

Composition is often at odds with optimization.

Static type safety is one difference.

It'd be more accurate to say the illusion of type safety.

Under the hood many[0] ORMs simply construct a query similar to my example above and then convert result set of tabulated strings to the appropriate types (usually using reflection).

This means two things:

First, that the "type-safety" portions of an ORM are really located in the "mapping" code, so not really related to querying.

And second: you don't really have type safety. A database schema could change at any time and break the code even if static analysis seems to think it should work.

[0] Notable exceptions are languages that offer type providers (e.g. F#) but I digress

This particular illusion of safety actually provides some non-zero degree of safety. For instance, it prevents trivial typos in property names.

Sometimes all you really need are simple CRUD operations against a data store for a particular app. Most ORM fill that roll quite well in my opinion. They are also great for rapid prototyping. I think where people run into problems is trying to shoehorn every data operation through the ORM layer, especially for more complicated data storage requirements. That being said, working with knex was a miserable experience.

if it's simple, why do you need to include a new dependency? Especially one as complex as an ORM?

I just think that at the end of the day, for any moderately complex system under non-trivial load, the idea that you want to "hide the complexity of SQL" from the service developer is an absolutely flawed premise.

I disagree. I've used many ORMs in my ~20 years, and I've architected and designed several hugely complex systems under non-trivial load.

There are really two things I want to put out there:

1) My opinion is that about 90% of your standard, day-to-day queries work just fine in a good ORM. The developer _should_ know enough about the DB schema and SQL to handle the other 10%. (In our 10 y/o enterprise software, the only queries we really drop down into SQL for are complex windowed reporting queries.)

2) Eloquent ORM is... different. It's probably the best I've seen. I wish it existed in other languages. Sequelize, which may be the "best" in the JS ecosystem, doesn't hold a candle to Eloquent, IMO.

I skimmed the Eloquent docs[0] but I couldn't see anything that sets it apart from other ORMs. What makes it special?

[0]: https://laravel.com/docs/7.x/eloquent

Well, the biggest thing is that it writes the SQL I would hope it to write in most cases, reliably.

In particular, I think relations are great to work with in Eloquent.

That's not really the point of ORMs though. It's map your data objects in code to your entitled in your relational database. That it comes with a SQL builder is just a necessity in order for the mapping to be done by the ORM. You can still write raw sql with most ORMs, but then you won't get the mapping which is the point. In some cases you will need to do that and that's fine. Hiding complex SQL isn't the goal

> That it comes with a SQL builder is just a necessity in order for the mapping to be done by the ORM.

That's only true if you need the "relational" part of an ORM - mapping a flat list of values onto a structure of related objects. If you only need to map a flat list of values onto a single object's fields, you don't need a query builder.

I wrote a C# SQLite library along those lines: raw SQL and simple object mapping. https://github.com/zmj/sqlite-fast

It's not just about hiding the complexity of SQL. You also get flexibility you don't have when writing SQL directly. For example, our laravel app uses pgsql in production environments but uses sqlite in our CI environment. We also migrated off of mysql which consisted only of changing a configuration value and verifying no issues by running tests

I agree. But I do like query builders (Knex for Node). I still write and think in SQL, but don't have to write queries as strings.

> I still write and think in SQL, but don't have to write queries as strings.

Why is that a benefit though? Why would I rather learn some custom query builder-specific DSL when SQL is at least mostly standardized pretty much everywhere. The use of template strings in JS can get rid of all the problems of just plain string concatenation for SQL (e.g. it can prevent SQL injection, enable safe dynamic queries, etc.)

I've used lots of ORMs too. I have mostly positive experiences with them, but a lot of mixed experiences too.

However, Eloquent absolutely is a class above the rest. Honestly one of the best I've ever worked with.

Are ORMs even that great? In my experience they invariably end up just being a messy abstraction to work around.

Since the underlying DB driver exists, you could probably fork one existing node one, or even submit a pull-request for demo support in your favorite node ORM.

Curious, Why do you think it will be "notoriously" difficult?