Things I want in a modern relational query language | Hacker News Reader

Things I want in a modern relational query language

sporks.space

116 pointszdw120 comments

Some overlap here with some of my favorite essays on why SQL is lacking:

https://www.scattered-thoughts.net/writing/against-sql

That particular post ends with a wish-list of items so it's the most similar to the OP. But there are others on the site that I quite enjoy (click on the home icon and search "SQL" on the page).

My personal take is that SQL will continue to reign for a long time because of the how monumental the task of replacing it is due to the inherent complexity of databases. LLMs make this worse because they're really good at translating prose to SQL. Now that it matters less how annoying SQL is to programmers, SQL will become more like assembly over time: something mostly computers write because it's complicated for humans to deal with directly. This is deeply ironic given that SQL was ostensibly designed to read like prose, i.e. to be easy for humans.

As a meta comment, I can handle code blocks without syntax highlighting, and I can handle code blocks that wrap. But both together with long comments just turn into line noise. There's no longer any useful visual signal for how to read them. On my phone the code blocks are simply impossible to meaningfully parse.

https://www.geldata.com/blog/we-can-do-better-than-sql

(https://news.ycombinator.com/item?id=24106608, https://news.ycombinator.com/item?id=19871051)

My ask is 15 years old[1], a live SQL extension. Allow a query to be a subscription to a database, so any updates get streamed as deltas to a listening client. There were a ton of times in my time using SQL where the same query is run over and over, just to get/handle that delta.

Wouldn't it be a lot more efficient to just work that way in the first place?

[1] http://livesql.org/ <--- just a few paragraphs of text from 2011

BTW, Oracle supports this under the name "continuous query notification".

https://docs.oracle.com/en/database/oracle/oracle-database/2...

You can get callbacks from the driver as query results change, or have notifications be sent to stored procedures, or posted to a message queue (and from there turned into web hooks etc). The notification comes with info about the deltas.

The main issue with it is that the queries it can monitor live are a subset of all queries. It's really more like using SQL to select database cells to watch, than propagating changes through arbitrary query plans. For example, it can't handle a SELECT COUNT(*) FROM statement. Obviously you can use it as a trigger for re-running more advanced queries though.

Snowflake has STREAM which can be crated on a view. It also has Dynamic Tables, from which you read a delta using STREAM or using row timestamp.

SQL server has Query Notification.

You can also read from debezium or other cdc, but thats more like table change than query result change.

And Bigquery has continuous queries: https://docs.cloud.google.com/bigquery/docs/continuous-queri...

And many databases have triggers. And with postgres you can combine triggers with NOTIFY to push changes downstream.

But yeah, I feel like most databases are way behind Oracle on this feature and it's so so useful.

https://github.com/dataphor/Dataphor

The problem with alternative query languages is that the people who have the most knowledge about creating queries and of the relational domains underlying their businesses are all experts in SQL. Introducing something else, then, means your most natural user base must migrate away from something they understand how to use well, and that's a hard sell.

So, until the ultimate query language is developed, I'll take SQL with pipes. It's an easy sell and good enough to eliminate 90% of my gripes about SQL.

What I want from a query language is for it to not be a language, but some kind of specification of components that can then be transmitted to the engine in a machine-readable way. The bizarre thing about SQL is that it has to be written in SQL. What if instead a JSON representation of the query (something akin to the AST, with a bit of processing done to boil it down) is what the engine took in? Then each language could specify the query in whatever syntax is most idiomatic for that language. Right now we do this with ORMs, but it's a huge pain because the ORMs are like a transpiler with a really free-form and messy language as the target. All the ORMs are just pasting strings together. If the "native" query format were more structured, we could have a lot more cool approaches for wrapping that on the programmer-facing side.

This would have been interesting about a decade ago, but today AIs all know SQL, and I haven't written it myself in a while.

Since it seems like the quantity of training data dominates AI performance, and AI doesn't yet internalize experience with new tools, it seems like a bad idea to stray from the training set.

Obsessing over a language's syntax and semantics feels a little like debating whether you write assembly using AT&T or Intel syntax.

I've tend to use both SQL and a dataframe API library. I guess it is sort of a a best of both worlds. SQL for the high level, large aggregations, dataframe library for when the equivalent SQL statement would be very tedious or hard to debug.

This is an ultra compact relational query language designed for LLMs. https://memelang.net/11/

Database engineers have to be the change they want to see and add support for newer query languages.

Reply on news.ycombinator.com