I don't want a query language. I want to call and profile typed functions like normal data structures, and which use (low-level/non-declarative) RPC where needed
I once worked on a medical records system (with a pretty well designed but necessarily complex schema) where the primary “patient” data object used by most code was fetched by a query that, depending on what associated data you needed, had between 106 and more than 400 relations (across dozens to hundreds of tables) joined together.
And that was CRUDy data-path code. The OLAP/reporting side added zeros to those numbers. Query texts were often hundreds of kilobytes.
I feel like databases effectively (/literally) add a JIT, which can mostly figure out what to do, even has accurate heuristics on the distribution of the data, but in exchange you get a less deterministic system, and less intuition for how to query or structure things. It's like, you know when to use a list/map/queue, but you want to focus on the business logic, so just use a smart collections which guess at runtime.
I think you can get this with FoundationDB, I should experiment rather than hypothesizing, but it feels like it would be nicer
But why bother? If I have a thousand clients that all want to run a query, why compile the plan a thousand times (and build/distribute the local planner to all of the different clients’ platforms) when I could send a query and have the database plan and cache the query once?
Also, what about views? Let’s say I expose my tables in a convenient non-materialized view. You query that view, bake the query plan into an executable, and ship it. Later, I change the backing schema a bunch, adding/removing/changing tables. I update the view so that it behaves the same way it did before. Your pre-compiled plans are going to be invalid now, right?
Same deal for efficiency: if I make an unindexed table and you ship a plan that copes with that by compiling in a hyper-efficient vectorized full table scan, then later I add an index to the table, do I have to rebuild all my client deployments to start using that index?
If the answer to those is “make the client code aware of the schema, indexes included, at build time”, I think that excludes a lot of cases where multiple codebases (some of which don’t contain the ORM or schema info beyond queries) talk to the same database, and reactive database-side schema changes to e.g. add an index by hand during an outage. I don’t particularly like it, but it’s true that a lot of shops don’t use a database migrator at all, or don’t use one that’s integrated with their client application SDLC in any way, and that’s likely to remain the case in a lot of situations.
Both views-as-query-snippets and reactively adding indices are pretty common, so I’m reluctant to consider SQL alternatives that don’t support those patterns.
Non-materialized views wouldn't be part of the schema, but you could still have stored procedures which change with migrations. A new index would not be used until clients were updated to use it, just like a new API method wouldn't. For better and worse this is the point – changes and improvements are made in the place you write the query, rather than in a dynamic general query runner.
It would probably also increase the places you use an 'application layer' which is tightly coupled to the database and provides a more stable, less general view to various clients. So, the place you write queries can itself be centralized towards what owns the data, but either way there's less happening in between the query and the data.