> ORMs produce objects. (Duh! That’s what the O stands for.) Objects are like directed graphs—nodes that point to other nodes but not necessarily to each other. Conversely, database relational tables contain data that are always linked bidirectionally via shared keys, aka an undirected graph.

> The second issue is that ORMs sometimes make multiple roundtrips to a database by looping through a one-to-many or many-to-many relationship. This is known as the N+1 problem (1 original query + N subqueries). For instance, the following Prisma query will make a new database request for every single comment!

I'll throw in another: combinatorial blowout between cross-joins is another problem. If you inner-join collections directly, you will end up with every possible permutation of each of the collection values. This is M x N or even M x N x O x P ...

Another similar problem is the idea of having "dictionary" tables which have surrogate keys/enums/etc and you want to put the keys in other tables but have a frontend show the display value. And really the display text is not part of the object, it's part of the dictionary object. And while you can manually write a query which pulls these back/hydrates them, it's not automatic and it's not performant unless you write something manually, you are individually loading N different rows with N queries!

This problem is real, but it's actually not a problem with ORMs but rather a problem with JDBC-style/row-oriented transfer paradigms. It's an impedance mismatch with the way we query the database, not with using ORMs.

What programmers intuitively want is to pull over some pool of objects from a given query. And that doesn't necessarily map cleanly onto a single SQL query - in fact they may be different types of objects in different tables etc! And you can't just inner join and pull them back in a single row - because then you get combinatorial blowout if you have more than one collection.

Obviously you need to define what pool of objects you want, because like the dictionary example, you could potentially keep hydrating deeper and deeper into the structure. You have the query string, but you also need to have some "hydration" string. And it turns out JSON:API has already defined a nice little standard for how to define what parts of the objects you want hydrated.

https://jsonapi.org/format/#fetching-includes

So what shakes out of this is "JOPT" - Java Object Pool Transfer. You can probably build it on top of JDBC, just be aware that it probably involves multiple queries, one for each type of object. The ORM loads all objects of all types, puts them into a Map<keytype,MyObj> for each type of object in the pool, and then assembles the object graph using the foreign keys.

You can do the data pulls relatively efficiently with temporary tables containing the pkeys that you want for that object type, that you populate for each query and then inner join against the tables. "SELECT * FROM table INNER JOIN tempTbl.pkey" will run much more efficiently than a "SELECT * FROM table WHERE pkey IN (:1, :2, :3)" because every time you want to pull a different number of objects you get a different literal SQL string which blows up the query planner.

It might also be desirable to have some better SQL support for maintaining these pools as a cached result rather than having to run a separate query for each type. You could pull back the primary result and have the pools stored as an aggregation column, then turn it around and pull the other object types, but it's just clunky in general, SQL is not expressive in the ways that is ideal for object pools. It would be nice to have a "cursor" that can traverse across multiple object types, or cache the included results between multiple queries in a transaction, etc.

--

The other thing is that the "collections" model is fundamentally bad in SQL too. Really this is the fundamental problem that originally drove practical adoption of NoSQL, I think. At the end of the day everyone's data is relational, the examples of "non-relational" applications ("netflix movie reviews"/IMDB isn't relational, really?) are always extremely contrived and obviously one feature-request away from being relational, that's been bullshit since the start. But it's obviously desirable to just put the IDs of the collection members into a text field/etc and that's why people like NoSQL - collections are literally so painful in SQL that people just choose to not use SQL.

With JSONB being very advanced on Postgres you can probably do this quite easily, or you could use arrays, etc (I think arrays might be built on the text?). JSONB can have foreign keys, indexes, etc, and TOAST can store up to 5GB of text (or something like that) per item if you need it to. Practically speaking, I think you could build a very successful application without using a "join table" or a one-to-many schema, and the object transfer protocol will just pull those uuids for you. Postgres can dynamically un-aggregate text fields back into rows for inner join/etc too, if you want.

Alternatively, you can map the one-to-many or join-table into an aggregation query that turns the actual collection table into a string_agg() function or similar.

A fully general model might require these to be implemented via recursive CTEs that aggregate all of the collection object uuids into a pool for the ORM to pull back, because the relations you're filtering might be many layers deep.

But these technical details are the type of dumb technical query building that ORMs are great at. Yes, you have to come up with the code that unrolls a collection text column into a couple rows, does the WHERE query, and aggregates back out, but that's just annoying to write, not technically difficult, and ORMs don't care about annoying code.

--

Anyway that's what I think people want from ORMs. They want to write a query in HQL or an object-based/programmatic query builder, and just pull back a pool of objects that's hydrated according to the include-string. People don't actually care about the relations and it's actively tedious to manage that aspect of ORMs because there's so many ways for it to bite you without you doing anything that's obviously wrong. It works, especially on toy problems, it just runs like shit, or pulls back M x N permutations of collection members, etc. Having to janitor that is the biggest headache of ORMs and honestly SQL in general.

It's functionally insane that we still interact with ORMs in SQL-like and row-style syntax. It's an object persistence layer, the tables are an implementation detail, the entire point is decoupling that but instead we just end up with even more complex logic to make the ORM behave. That is the incongruity that I think people are searching for here. Stop making people manually build the parts of the query that handle the administrivia to hydrate the object, and just make the querying and object mapping work properly, fix the combinatorial blowouts, etc. And do it without falling back to hammering out object requests one at a time like lazy loading tends to be.

If you're pulling back records one by one, or using the dynamic/lazy-loading function for relations between objects - yeah that's gonna be super slow and you're gonna hate it. In my experience that's the #1 reason people don't like ORMs, they're not running one query, they're running 1000 queries and hammering the database hard. The lazy-loading functionality might as well not exist and tbh the default should probably be to have that getter throw a "you fucked up the query, this object hasn't been joined!" exception unless it's specifically configured to allow lazy-loading.