The modern app simply wants to query/update data, show real-time updates to this data, and then have a nice normalized cache so one piece of data is the same everywhere it's rendered.
I've been fixated for a while on the thought that any solution needs to be built on top of an existing db like Postgres or SQLite, with SQL as the primary interface (like every other DB has these days), but I feel like this is holding progress back.
Data schemas these days are less fixed, they change often, and involve interacting with third-party data sources (usually large nested and unstructured JSON), as well as being more graph-like, and tree-like. SQL becomes unwieldy under these conditions. For example, try figuring out the best way to store a tree in SQL, or querying 2 or more M-M joins, or storing a json response from a thirty-party service in a normalized way, or trying to implement materialized view maintenance.
We need more flexibility than SQL and the relational model offers.
I also think apps should be built such that they can run entirely in the browser, and then the backend is just a thin interface to the data. Most apps end up implementing the entirety of the backend in the client anyway in order to have a snappy experience.
I feel perhaps there is liberation in a more hackable/modular database that can run on the frontend and backend.
On the frontend, the focus would be optimizing the amount of data we need to pull from the backend to be able to run as many queries as possible without fetching, and then efficiently checking for freshness of the local dataset.
On the backend, with AssemblyScript (TS syntax -> WASM), we could provide an accessible and efficient way to implement the underlying layers of the database engine and could also expose the query planner and executors to the user.
I feel like viewing a web page as nothing more than a "database skin" covers 95% of use cases and obviously SQL is a large part of that. Not sure what your struggle is with tree structures in SQL, that seems like the easiest case. More complicated, as you mention, is multiple M2M rels. Denormalizing these into database views or temp tables can go far, as well as materialized views. Outside of that, populating denormalized dimensional tables via triggers (and other means) goes far as well. This is just kind of how things end up being as data grows and analytics come into play. A good DBA can do wonders, especially as SQL becomes more of a black art due in part to so many software stacks trying to hide so much from the DB layer. So with all that said, I just don't see how trying to do away with so much on the backend and shove it into the browser will in any way "simplify" things.