I've come to believe that relational is the wrong choice for most apps.

Most people choose a relational db without even realizing what relational means and what the tradeoffs are.

Almost every app dev would prefer that their app reacts to changes in the entities, and updates it UI accordingly. Incrementally computing new query results. This is non-trivial in the relational model.

The relational model is a bunch of constraints such that relational algebra can be used by the system to re-arrange the query plan and get the same results.

Your queries are optimized, yes, but if you want to stream changes, then you probably want a different query plan altogether. And its not a matter of just making SQL cacheable and streamable like with incremental view maintenance - you probably want different queries to run altogether. Most people aren't building analytical reporting apps with SQL. The database size for a single user or team in most apps is such a small size that aggregations can just be done client-side even.

I think a better way would be to have a database that knows about all queries in your app, and optimizes collectively. And instead of relying on the database to do it, give users the tools to build query plans themselves. And also, throw away the idea of relations and just make all query results reference the underlying entities. Instead of complaining about the "ORM mismatch" with relations, get rid of the relations, and embrace objects.

There's a bunch of work being done on making relational systems work this way. Some interesting reading:

- https://www.scattered-thoughts.net/writing/an-opinionated-ma...

- https://materialize.com/ which is built on https://timelydataflow.github.io/differential-dataflow/, which has a lot of research behind it

- Which also can be a compilation target for Datalog: https://github.com/vmware/differential-datalog

- Some prototype work on building UI systems in exactly the way you describe using a relational approach: https://riffle.systems/essays/prelude/ (and HN discussion: https://news.ycombinator.com/item?id=30530120)

(There's a lot more too -- I have a hobby interest in this space, so I have a small collection of links)