ORMs are nice but they are the wrong abstraction

Maybe I don't know how to use one properly, but I have used them for years. For me however tech should be learnable quicker to be beneficial.

Now I only use ORM really only for some basic CRUD queries, if that.

Every time I see a post talking about how bad ORMs are, they're using ORMs in an awful way. It's not really their fault: most ORMs seem like they encourage the worst ways to use them. But they're entirely missing the point: SQL doesn't have any form of abstraction at all. It's completely impossible to write reusable, adaptable SQL. All SQL must be specifically written bespoke for its individual use. Sure, you can rely on Views (or Procedures if you really hate your company and want to write the core logic of your application in a terrible, unmaintainable language), but that's relying on a concretion, not an abstraction. Abstraction would mean that you can change the behavior of a query by changing its input views when you call it, from some set of possible input views that conform to the expectations of the query. ORMs let you do that, SQL doesn't. Stop using ORMs badly and start asking how they can support you better organizing your queries by using abstraction.

This would be the right way to go if:

1) Databases had a consistent language for storage procedures

2) It was sane

The problem is that stored procedures work differently in every database, and most have come up with crazy / stupid ways to do them. That's a fixable problem, but it has not been fixed.

ORMs are nice for "Hello World," and hit a brick wall later.

I don't mind them for simple systems, but for more complex systems which need SQL, don't use an ORM. Mixing ORMs with raw SQL generally mixes and breaks layers of abstraction. This leads to a situation where the overall system is more complex than having a sane relational abstraction. For example, ORMs do a lot implicitly. A code change at the language level, with something like the Django ORM, can and will break your SQL code. Your data logic is also split across two places.

The only time I've seen this work is for SQL for one-off analytics done at a command line, where the SQL code is never intended to be reused.

EITHER:

- Use an ORM, if you are building something simple like a todo list or a basic eCommerce web site; or

- Use full SQL if you are doing something more complex (e.g. if you ever expect to do analytics); or

- If your programmers don't understand SQL, consider whether you want an ORM, a nosql, or to find programmers better matched to the problem domain.

(Footnote: Many good programmers don't understand SQL; that's okay. They just shouldn't be designing databases, any more than e.g. database experts should be designing machine learning algorithms, or machine learning experts should be designing front-end user interfaces. They're different skill domains, and they're all equally important. The key thing is people should know their skills. Otherwise, you'll get an unusable UX, a regression as our ML model, and a broken schema. This is especially true for something which looks simple on the surface but has deep theory behind it. ORMs make it look easy.)

TLDR: Use tools like sqlc (GO), dapper (.net) etc to hydrate and check syntax using already validated SQL, instead of being limited to ORM quirks and narrowed down query syntax.

But...

The actual main problem of ORM is not just ORM but a multi-layered translation of target language query code

  - to internal target language database schema modeled from/to a db schema
  - then to SQL syntax
  - then to wire request to db
  - then to SQL validator in db
  - then to internal database query planner targeting relevant schema elements
  - then to raw index/hash/scan executors

You see the picture? If the database schema wire protocol could only be converted to the target language objects without SQL translation sitting in the middle, query would run optimally and with up-to-date statistics to query planner.

Think of WASM-like protocol (as opposed to JS) to which queries are compiled in the client and passed to the db.

It has been done before on a basic key-value wire protocols and to an extent on graph databases with json requests. Structured relational data is making this hard to do as one would need to have up-to-date db stats in the client along with all index details to run an optimal query.

On the other hand, compiling this:

  db.Posts.Get(p => (p.Authors.Includes(a => a.IsBoss))
    ? { Date: p.Date, Bosses: p.Authors = p.Authors.Filter(a => a.IsBoss), Groups: p.Groups.OrderBy(x =>   x.Name).Top(5) }
    : { Date: p.ModifyDate, Bosses: null, Groups: p.EmployeeGroups.First() }
  )

Into SQL would be close to impossible in an optimal way. Alternatively, with schema access in the client it, one could write internal db "assembly language" procedure for looking up using indexes, conditional querying and spitting out binary result for minimal effort hydration.

Just an idea, I am not aware of similar client query planner solutions available.