Things I want in a modern relational query language
17 points by calvin
17 points by calvin
Less opaque query planners
This needs some more elaboration; PostgreSQL's EXPLAIN (like most other EXPLAINs I've seen) has their own terminology, but well, I don't think there's any existing terminology to reuse. I find EXPLAIN understandable once you learn the terminology.
What I want is a dual syntax. Besides a way to write a query as a text string, I want to be able to write queries as structured data, with libraries to manipulate queries in all languages (perhaps by using protobuf or any other similar thing). I want to be able to write:
students = _.select(_.all).from("students")
foreign_students = students.where("foreign", _.eq(True))
to_display = foreign_students.range(30, 40)
result = to_display.execute()
Many ORMs and other libraries to get data out of RDBMSs have something like this, but I want the new query language to be designed so that structured queries are a first-class citizen.
This is quite useful to implement stuff such as CRUD frameworks; you can define things such as "what needs to be displayed in this list view" and then the CRUD framework can manipulate the query to implement pagination and similar things. This is nowadays done through ORMs (which means you often don't have all of the RDBMS features) or by string manipulation (ugly).
Of course, I would also like the classic abbreviated join format inferred from foreign keys (e.g. select * from students.student_address.addresses, where student_address is a foreign key on the addresses table to students).
And it's not really a query language feature, but I want incremental queries. I should be able to subscribe to the results of a query and get streaming updates to it. Real-time UIs are useful and it's really hard to implement them efficiently in current OSS RDBMSs.
I think your description of queries as structured data seems really similar to the Ecto framework in Elixir, specifically the Ecto.Query module (where the query DSL is implemented). I'm pretty ignorant of the capabilities of ORMs in other languages, but I know that in Ecto:
Flop is a library for implementing pagination automatically (one of many), and it has a companion library for integrating with the de facto Elixir web framework. Ecto also has abbreviated joins with assoc(), since you can define table associations upfront.
There's not a great story around streaming query results, but I've used Phoenix Channels for implementing real-time features. That requires a sort-of event system where every modification of a record needs to be broadcasted rather than just defining a query for the client. Streaming queries sounds like a lovely approach, but my guess is that it would need to be backed by a streaming database.
None of this is implemented at the DB level (I've only used Ecto with Postgres), but some of the things you described sounded like a wishlist for Ecto functionality.
Yes, most ORMs already work like this, or would be easy to adapt to behave like this.
The problem is that I'm not aware of any ORM that can handle all of PostgreSQL's features in a "structured" way without passing in SQL text fragments.
Also, I'm not aware of any language-independent implementation. Having this on just a single language is a bit downer.
Phoenix is something I've looked at as a basis for real-time apps, However, yes, you really need low-level database support.
(PostgreSQL logical replication is a bit close to this, but it's not really designed to be user-facing. But it has some neat features; for example you can define a subscription for a subset of a table based on the value of a column, such as foo = 3. Then if a query updates foo from 3 to 4, the subscription will send a delete, and an update from 2 to 3 will look as an insert!)
Right now you have to roll most of the logic yourself, even in frameworks that help you significantly. It seems extremely resource-intensive to implement a streaming relational database, but I expect that would represent a huge step forward for the development of many kinds of apps.
Re the dual syntax thing, Spark has exactly that (though it's not used to query a database): one can either use plain text SQL queries, or use structured queries that look roughly like this:
df = spark.table("user")
.groupBy("org_id", "date_of_birth")
.agg(f.count(), f.mean(col("age")))
This is a vague recollection from roughly 5 years ago so it probably does not look exactly like that, but that's the general idea. And while Spark has a lot of clumsiness in other areas, using these structured queries was a breeze and something I've missed a lot since then.
I think what would help is if databases adopt the following ideas for their query languages:
SELECT -> WHERE or WHERE -> SELECT, both being fine)The combination of these additions would make it much easier to dynamically generate complex queries at runtime as you can essentially generate a single linear list of binary instructions, instead of the usual complex tree structures most ORMs generate, taking a giant dump on the allocator in the process.
The downside of such a format is that in-place updates of already generated instructions becomes really difficult due to the linear nature. However, in all my years I don't think I've seen an actual use-case for taking an existing dynamically generated query and then updating something deeply nested inside that data structure; instead it's always been cases such as "Take query A and wrap query B around it", i.e. composing queries together is far more common than mutating them in-place. Even if you were to need to mutate a query, any form of mutation can be replaced by composition, and using a linear format such as the above it wouldn't even be all that expensive (certainly not as expensive as working with large tree structures).
Hmm, that's interesting. I normally wouldn't optimize so much, but likely for a general purpose RDBMS it would be interesting to find an "optimal" "efficient" representation. Something stack based might be nice, yes.
You may find Substrait relevant. The best way to describe it is as a common interchange format for describing operations on relational data, though it only describes the common core of relational operators, with system-specific features using extensions. Some data systems accept Substrait plans.
Are there any SQL alternatives one can practically use today?
https://github.com/evgskv/logica apparently compiles to SQL. https://prql-lang.org/ sounded interesting, but they've taken a turn I don't like in their development process.