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.

Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult.

Most people reach out towards an ORM or query building engine and otherwise don't really go far beyond the basic CRUD, joins, and some simple aggregations with groups. Since they try to be DB agnostic you'll rarely get an adaptor over CTEs or window functions or partitioning.

An LLM is great at exposing what a database is capable of doing with SQL and might even manage to navigate the most poorly designed of schemas. And it might even manage to design one to an acceptable standard if it has enough domain knowledge in its context.

The problem in my view is that there aren't good tools to debug advanced SQL stuff within the context of the whole system which is usually written in a higher level language. I just spent a few weeks modifying some code where the original dev put a lot of logic into stored procedures. That's in principle fine but it's really hard to figure the actual business logic when it's spread out over C# and then also SQL. It doesn't help that the SQL code looks like FORTRAN code from 1985.

Personally I think we need ORMs that allow expressing advanced SQL stuff with other high level languages. Or even better: The ORM detects where advanced SQL makes sense and uses it.

If I had to pick, I'd try to make the ORM redundant by making 'lower level' SQL easier to deploy rather than depending on sending strings of SQL queries and mutations over the wire.

I haven't worked in a single setup where raw SQL has been encouraged, because it always requires DB migrations and not all of them are safe. Nobody dares touch the DB server's resources by setting up stored procedures, materialised views, etc. etc. and instead people are blowing money on Redis instances and caching and shit.

I don't have an answer to this but I've hit a lot of issues in my career where I think, "this could have been solved months ago by pivoting a couple of tables or creating a new function." You have been able to 'script' the DB for decades but you lose a lot of what you gain from the traditional SDLC at the app layer.

For me, one of the primary benefits of ORMs is that they can parameterize requests which then prevents SQL injection attacks.

Passing raw SQL to the database needs very careful attention to the dynamic parts, and it's too easy for user-generated data to be included.

Yes, it's possible to pass user generated text through a sanitizer but now you just have an arms race between the sanitizer and "clever" users.

It’s not that hard to pass values as query parameters of a manually written query. With inferior databases that don’t support array parameters it’s a bit more work to construct the correct number of $ parameters in the query, but still not that hard

"Can it be done?" is one question. "Is it done dilligently by all the programmers on the team?" is quite another.

Dapper in .net is fantastic to deal with raw sql, to the point I think I’m delusional because it’s so damn simple to send outrageous queries to the database and have those multiple mixed results turned into objects very simply.

I’ve never had an issue of raw SQL requiring migrations? Unless you’re talking of changing database engine? In which case I think it’s a bit of folly to imagine changing the database engine will not mean changes to your stack higher up the chain.

> Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult.

Isn't that true of most languages? SQL has pretty simple syntax; I think the only reason it's sometimes seen as arcane is that fewer and fewer people bother to learn it.

dba here and I really don't get why SQL is so feared... I get that it requires very different way to think about data but it is quite simple in terms of you tell it what to do, and if it does it badly you probably told it wrong so just try something different...

Because:

1. SQL isn't composable (you can't assign fragments to variables except for CTEs) so you can't easily test out subparts and build them up incrementally without just copy/pasting stuff around.

2. Joins are an unnatural way to dereference pointers.

3. SQL is more than SELECT. Once you get into updates you encounter lots of scary edge cases and traps. How many engineers really understand isolation levels? Why doesn't skipping the column list in an INSERT substitute nulls for the nullable columns that aren't provided? What changes can you make to a schema that are 'safe' for your environment (won't take table locks)? What locks are being taken by the RDBMS behind your back - sometimes it matters!

4. Site outages caused by optimizer plan shifts are scary because people don't feel in control.

Good databases have features to ameliorate these issues, but most people's experience is of databases that are merely OK and not good.

very interesting thanks for the insight, I feel like there are broadly two camps of developers, one like yourself probably wants to know every last detail, and the other that are somewhat prefer to stay naive and taking the "declarative" part very broadly...

there's a bit of cyclic relationship at play here, the data changes the query plan, and the query plan affects the performance, while the performance is being optimised by the engine. if the architecture is bad then sooner or later the engine runs out of tricks and performance suffers but then nobody never is quite sure whether the current architecture's good enough.

I totally get the whole nosql that was the rage for a while... quick to prototype but I do believe once it's somewhat settled, moving the consolidated parts back to SQL is much easier to manage and optimise

> 2. Joins are an unnatural way to dereference pointers.

There's gotta be a simple & clear alternative to this obstruction. Maybe it just hasn't been invented yet.

Most query languages do fix that. GraphQL is one example.

GraphQL joins are entirely up to the underlying resolvers, it has no syntax for expressing different kinds of joins or filter conditions on values from multiple joined relations

1. Can be done using views. Creating named views is really not that different than building named functions or methods.

They aren't the same. Views are persistent objects, not like local variables that exist only for the scope of an operation. CTEs are the closest equivalent but you can't factor out predicates that way.

Why does it matter? It’s like a named operation, and if you want local scope you can create CTEs, which is like an anonymous local operation. So you have both options.

> How many engineers really understand isolation levels?

I feel like there’s no excuse for this one. You need to know how your data store will interact with your query and others.

The problem, I think, is what the tail end of that is, and is what you hinted at when discussing locks: RDBMS interaction. I have come around on this recently (quite recently - after reading and re-reading this article, and the comments), so forgive me if any past comments in my history indicate otherwise.

It is unreasonable to expect a developer to administer an RDBMS. If you're a small startup, you kind of have to out of necessity; maybe if you're lucky, you hire a dev who's also done infra work, and if the stars align, they've specifically administered an RDBMS at scale. But what counts as administration? Let's look at adding a secondary index, possibly the most common DDL.

AFAIK, no ORMs / frameworks (I am assuming here that most devs are using some kind of abstraction for RDBMS access) default to "safe" builds - no `CONCURRENTLY` for Postgres, and no reducing `lock_wait_timeout` to something sane for MySQL (I've no idea about MSSQL nor Oracle, though I also assume that if you're running one of those, you probably have a DB team). So already, there is an implicit assumption that they've read the pertinent manual section[s] for their RDBMS, which seems unlikely. Even if they did, there's a chance they would also need to have read and understood the paragraphs on handling invalid index builds (Postgres), or the impact that foreign key constraints can have on metadata locks (MySQL).

Let's say the line gets drawn at "devs should be able to understand that they [probably] need secondary indices," with implementing those being entirely on another team or service. OK - how much do they need to understand? I think it's reasonable to expect a developer to understand B+trees; after all, they're just a data structure. Should they need to be able to internalize that such that they can understand why doing a range scan on a column in the middle of a multi-column index removes everything to the right of it from B+tree filtering? Probably, but now we're significantly deeper into specifics. Should they know that there are different kinds of indices, like GIN? Maybe. What about different operator classes (Postgres) for them? Maybe, maybe not. What about knowing about its `fastupdate` option, and the related `gin_pending_list_limit` configuration item? I'd love to say no, those are squarely in the world of ops, but then why should they be allowed to create the index at all if it's going to increase someone else's operational burden?

For all these reasons, I don't think it's prudent to have dev teams managing their own DBs. But then, you get into the fight that most places seem to be in, where the devs want to do something to the DB that the ops team knows will be a headache later, they push back, product gets mad that they aren't shipping, ops capitulates, and then the headache predictably becomes real months down the road. Rinse and repeat.

I have no clue how to fix this while maintaining the modern trend of velocity dominating everything else.

Some of it can be fixed by automation, but yes it's a problem. The NoSQL trend was partly I think a reaction against the complex feature sets and quirks of SQL databases. But then those features existed for good reasons, and just saying "we do less" isn't really a simplification, it's just ... doing less. MongoDB also distinguishes between concurrent and non-concurrent index builds, requiring you to pick up front. And it's not fully concurrent anyway.

> Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult.

This is kind of a hot take. Most devs I know know PostGreSQL well. They know how to write complex queries with CTAS, joins, etc, know how to create indexes, views, and add user defined functions.

The end of that article ends with a pertinent quote [1] by Michael Stonebraker [2]. I've included more of the original quote here:

> My biggest complaint about System R is that the team never stopped to clean up SQL. [...] All the annoying features of the language have endured to this day. SQL will be the COBOL of 2020, a language we are stuck with that everybody will complain about.

> My second biggest complaint is that System R used a subroutine call interface (now ODBC) to couple a client application to the DBMS. I consider ODBC among the worst interfaces on the planet. To issue a single query, one has to open a data base, open a cursor, bind it to a query and then issue individual fetches for data records. It takes a page of fairly inscrutable code just to run one query. [...] Only recently with the advent of Linq and Ruby on Rails are we seeing a resurgence of cleaner language-specific enbeddings (sic).

1. http://www.redbook.io/ch2-importantdbms.html

2. https://en.wikipedia.org/wiki/Michael_Stonebraker

I was with him until he mentioned Ruby on Rails, is he talking about something other than the fairly ugly activerecord pattern?

LLMs are also very good at writing code for newly invented languages, especially if they can execute it and iterate. I strongly believe the barrier to switch languages is lowered in a post LLM world.

Ten years ago I was at a startup where we used Datomic, and it was okay, but six months in the sales team was like “ok how do I run SQL queries so I can triage leads”. We had no answer of course.

Today it would simply be: type what you want in natural language and we’ll generate the query with Claude.

I just tried one representative query from that startup against a hypothetical datalog query tool in Rust and it did just fine.

I did this with our team: I created a schema.md document with an LLM-friendly explanation of the schema, and walked the whole team through how to create a Grafana query using an LLM and this document.

Within a couple of weeks we have totally non-technical folks with very sophisticated queries in their dashboards. It works fine.

It was very cut-and-paste, though, and I'm working (when I get the chance) on doing this via a chat interface where the LLM can interact directly with the database and Grafana to make it smoother.

So I think the answer is not necessarily new languages, just better integration with the final interface. In an ideal world we should be able to ask in chat "what were the sales numbers for last quarter for APAC excluding the three largest customers?" and get an answer near-instantly, and then we don't really need to deal with queries or languages at all.

did you use llm to create the llm-friendly schema.md? This sounds like a brilliant idea I might need to steal it... thanks!

Yeah, but it was a lot of questioning and refining to get it to understand the wrinkles and edge cases. I think we're on about version 5 of the doc now

I agree that an LLM could also generate the code for a new query language. But my point is that fewer people will attempt to author a new query language in the first place because an LLM will be writing the queries either way. So the effort would be less impactful.

You're right, I was talking past you.

I have an implicit belief that SQL isn't the most effective low level language we could have and LLMs will free us up to explore that space, similar to asm.js -> WASM. But I'm open to being wrong about that.

I think it strikes a good balance between still human-readable and low-ish.. any lower I suspect you'd need to dedicate a lot more documentation else where..

Datalog exists and there are so many implementations.

Agree. Especially when Datalog queries are simpler to write and faster to run than SQL, there is a strong reason to at least try it.

I would be genuinely curious what the OP other and folks here think of Mangle Datalog in this context. The go implementation is here: https://codeberg.org/TauCeti/mangle-go and the Rust implementation is here: https://codeberg.org/TauCeti/mangle-rs

The implementations are not high performance, but if you can fit everything in memory or you can organize your data and integrate it through external queries, you should get something workable for a lot of use cases.

I did not set out to replace SQL, and while I don't mind adoption, that is not why I am sharing it here. The open sourcing was motivated by making datalog more widely known. I did some research and found out that I needed a datalog implementation with particular characteristics, I for sure knew I didn't want to use SQL for what I needed.

There are structured types and recursion and being able to name predicates and compose queries... Mangle has some users and there is a few application that take advantage of the queries-as-logic-programming approach.

I think an insight one can draw in this discussion that a query language and the system (DBMS implementation) that it is part of can hardly be separated when it comes to the inevitable performance requirements one has.

That's a pretty cool pair of repos. I've been working on a C++ Datalog myself [0] for embedded queries over in-memory datasets. It's been surprisingly useful for a bunch of different usecases (e.g. build graphs). My implementation is faster than Souffle on all the benchmarks I've tried, but apparently I still have some work to do optimizing startup time on small graphs.

[0] https://github.com/J-Montgomery/dartfrog

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.

landscape it is not good but okay-ish on my phone display. hth.

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)

Sounds a lot like Spark before it became so enterprise-focused. Back in my day we wrote scala to run our queries, and once we figured out how to get our compiler and runtime set up, we liked it!

I’ve been getting into Postgres recently and I was very surprised how easy it is to introduce new types/operators/etc through C code. I’m not talking about domains. Just write some C and you can have whatever type you want. It really demystified “extensions” for me, I actually think that is an actively harmful name (it sounds clunky, gross, based on my experience dealing with “extension” and “plugins” elsewhere) for what is essentially just custom types/functions. More people should try writing their own postgres extensions. It’s not very difficult at all!

I’ve been cooking in this space for quite a while (HDFS/spark, Apache Pinot, proprietary stuff, an experimental functional ORM over SQLite). The biggest problem, I think, is the interface between the management/admin, application, and “query” layers. I think something like grpc/protoc (or indeed the way Spark used the JVM) is needed to provide non-leaky abstractions and more programmatic/structured interfaces from the DB to its clients. Happy to share more, but basically, the database needs to become capable of general (meta-)parsing with a reflective type system, I think.

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.

The issue is that incremental computation is a non-trivial problem that cannot be solved on a language design level.

There are some new interesting players in the field though, eg. https://github.com/feldera/feldera

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.

pg logical replication is close to this. but ideally you want incremental query updates, which I believe Materialize provides.

but yes, I agree this is quite often what one wants, and would remove a lot of grot from the client

Sounds like you want to read the log file to be honest.

Can someone explain to me why SQL error messages are so bad? I routinely have some monster query where the message is effectively, "Illegal syntax somewhere, dufus".

I'd guess people just haven't put much effort into it. Lots of programming language compilers have absolutely terrible error messages. In SQL its usually just one line, so "somewhere" isn't that big a place.

This sounds like a proper of the parser, rather than of the language

A long time ago, I had to write SQL parsers (for 3 of the most popular DBs at the time). It is a surprisingly difficult language to parse and disambiguate - particularly when having to deal with the warts of its variants. Sure it's not quite C++ but it's was easily the most annoying parser work I ever had to do. And in my experience, the trickier it is to parse a particular language, the more difficult it is to provide feedback to users in the form of helpful parse errors.

This is true, but the observation is still salient. There does seem to be some correspondence between languages with inherent friction and parsers that aren’t interested in being helpful. E.g. sometimes a user base just collectively decides they’re ok with some level of pain.

PRQL is one of the best attempts at a new query language IMO

I've been working on a Lean4-based query lang that compiles to substrait, I think the power it has wrt to types and functional programming could improve on SQL ergonomics a good deal

https://github.com/dataphor/Dataphor

SQL is what it is today because it is battle tested and has to handles a very hard problem of handling arbitrary concurrent reads/writes, so the likely scenario is that trying to replace general SQL wholesale will just end up making a worse, less tested version of SQL that developers are less familiar with. So, I think the best query language is probably whatever query feature that's already in your backend language, LINQ for C# for example. The only room for an SQL replacement in my opinion is if you are willing to trade flexibility for speed a la TigerBeetle.

The good thing about having built your own programming language via LLM nowadays is that you don't really have to speculate about a theoretical language when you can just have Codex/Claude implement it and try it out for yourself. I did it yesterday when I wanted to try out this theoretical high-performance database architecture that I had in mind and just added query functionalities to the language I already have.

If anyone is interested about the results, the default naive mode for this new database is ~0.2x the speed of concurrent durable mutation workloads, but if you specialize it to the particular application, you can get ridiculous 50-100x performance increases on filters and maps at the cost of flexibility and more upfront design. Experimental results are promising, definitely not production ready though.

No, SQL is what it is today because it was crappy in the beginning and no one managed to replace it.

It was only a partial implementation of the relational model, we could have been so much better had it not become the standard

The sad reality is that having something that works, even if badly, is better than having a theoretically elegant architecture that is not implemented. See JS or Linux vs Hurd for other examples.

Ask yourself this question, supposedly somebody made the full implementation of the relationship model into a database engine tomorrow, will you use it yourself, and can you convince your company to use it in place of SQL? Again, I wish this wasn't the case, but I'm not sure if there is anything we can do about the adoption problem.

...but we've had full implementations of the relational model for decades, with great performance etc. It's just a query language. A DB can use whatever query language it implements. Just like JS, there were schemes before JS (including the working browser one before management made Eich redo it into JS), there were other languages used in the browser even!

I never did Scheme in the browser, there was no "redo".

https://x.com/BrendanEich/status/1997032329689346359

Oh! Thank you for correcting me.

Previously, I've seen it described (including on Wikipedia) that you were hired to embed Scheme, but then they made you change it right before delivery.

It’s a fine tool for most data storage/retrieval jobs.

Crying about some theoretical relational model doesn’t do anything to further your point.

No one cries about theoretical things

doesnt meant the syntax isnt a pile of dog farts

I agree, yeah, SQL syntax is awful. But the easier solution is what pretty much what backend has converged on, have something in your backend programming language that lowers to SQL so you never have to write any raw SQL at all except as a low-level escape hatch, so that in most instances SQL just becomes an IR that nobody really needs to think about in normal application code.

Are we really this devolved as coders? We can’t handle different syntaxes? We need LLMs to write queries? What the heck is going on with our industry?

Old man rant off.

I don't take it as too-much-syntax in the brain[0], but all of the problems bad syntax causes. We could still be writing code in assembly, but we have found that different languages make things easier or safer to construct.

I can trivially handle having to repeatedly bounce to the top-then-to-the-bottom of a query I am writing because I want to change the group-by or sorting order, but that is annoying friction. Since the language does not compose well, you need to keep most of the query in your head and cannot build it up piecemeal as easily as something like PRQL (https://prql-lang.org/)

[0] Although, it would be incredible if I could write timestamp formatting without having to look up the bespoke vendor incantation every time I switch dialects.

Does CTE not allow composability?

Is it really THAT terribly difficult to replace "SELECT vars FROM table..." with "FROM table SELECT vars...", so that tools can provide suggestions ?

The difficulties that even experienced programmers have with SQL are far more than just the syntax. And, given what most people need the database for, that difficulty is pretty disproportionate to the complexity of the task.

I think the “it’s just syntax bro, learn it!” critique is about as ill-fitting as the claim that embedding a scripting language in a larger program is pointless because “assembly/C89 is just syntax bro, learn it!”

I take what you’re saying about difficulty being disproportionate to the task as an indicator of ability.

It’s literally so damn simple to knock out a database & some crud functions either as a desktop app or a website that the complaints in this thread are hilarious.

That comes across as arrogant and rude. Assuming you actually want to engage, I discussed some of the reasons that this can be hard here: https://news.ycombinator.com/item?id=49411927

The link in the top comment further expands on the cognitive overhead: https://www.scattered-thoughts.net/writing/against-sql

Particularly relevant is the part of that link which discusses having to pervasively refactor queries to add even a simple synthetic join or computed column. That’s a pain in the ass even for experienced DBAs, and is fundamentally not time well spent for row-at-a-time cases that are often, as you said, simple CRUD.

Are you sure you aren’t overfitting based on working on only one small, simple subset of the things people commonly use SQL for?

The example of pervasive refactor is a bit of a contrived example to be honest. Get the id of the record you're discussing with your pre-conditions and then retrieve the data you're after.

At least in SQL Server select x2 from foo group by x+1 as x2 you'd use select x+1 as x2 from foo group by x.

I've read your article and it's written well enough, I'm just not sure that's as big a hit piece as you think it is nor do I think here is the place to post a full rebuttal.

>Are you sure you aren’t overfitting based on working on only one small, simple subset of the things people commonly use SQL for?

I think on the contrary that esoteric features not used as commonly utilised deserve to be esoteric to use. The common path should be the easiest. That SQL is used by different professions and not just IT related ones is testament to a good language. You won't find BA's using C to write reports for instance. There's A LOT of value in that.

Not even an old man rant SQL isn't that hard to learn. I never had a problem with the syntax. Not everything needs to look like C. Remember when every complained about Python's whitespace indenting? Seems like everyone got over it.

Syntax affects readability and writing speed a lot

Writing speed is not really relevant anymore, LLMs do it. Reading is what matters most.

:rolleyes:

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.

This is exactly the same problem facing people trying to develop new music notations. In order to grasp the domain enough, they have to be experts in the existing music notation, and once you're an expert in it, the motivation to create something new goes away. From what I've seen, the people who want a new music notation are mostly people uncomfortable with sight reading.

Experts have been criticizing SQL since it was a proprietary IBM language. Take this typewritten rant from 1983 [0] as an example. And we've had better query languages for just as long, e.g. datalog. People really love SQL though, which I can only assume is because the vast majority of usecases are slight variations on SELECT * FROM table.

[0] https://courses.cs.duke.edu/spring03/cps216/papers/date-1983...

I hardly doubt that many people love SQL

They might love the relational model concepts that manage to seep through it

For example, Elastic. Much extra learning curve for little obvious gain.

I like to say, with zero research basis, that the New Shiny has to be an order of magnitude better than the Old Thing for people to say "Oh yeah, I gotta have that."

Exactly, we all know the merits of Esperanto, but few have switched away from English.

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.

Datalevin seems to fit the bill https://datalevin.org/docs

First paragraph of preface: “SQL has been the default language of application databases for half a century. That default is now holding application state back. Datalevin is a database built to replace SQL databases at the center of application systems: it stores data as small facts and queries those facts with Datalog”

this is exactly why i built Rad: a relational db with an IR as its public interface, so you can experiment with interesting query languages against a solid foundation and a real planner without needing to compile-up to sql (radengine.dev)

Oh this is awesome. This is exactly what I've been too lazy to build for years now.

We don't need a new syntax. SQL, PRQL or whatever else should compile down to the database's machine readable interface, same as language-integrated query builders or libraries like your generated clients.

Did you come across Substrait when working on this? Any thoughts?

This also sounds like what Turso imagine doing with their VDBE:

> Like SQLite, it compiles SQL into bytecode for that machine, the VDBE, and then runs the bytecode. That design is what lets one engine host more than one SQL dialect. SQLite is the first and primary frontend that compiles to it, and Postgres is now a frontend of its own, with its own dialect and wire protocol. More will follow. Our goal is to be for databases what LLVM is to compilers, with one modern and reliable core, and many frontends compiled down onto it.

Nothing around better error handling or schema updates? After working with SQL for 10+ years those are definitely the things I miss.

For error handling I mean things like deprecating a column and allowing a custom error message when someone queries it.

And for schema updates I mean allowing table versions. Same table name but allowing querying an older version of the schema

I like the idea of deprecating a column and getting a warning so much. Also versioned data built-in I would love.

SQL is the worst way to access a database, except for all of the others that have been tried from time to time

Cypher is pretty nice.

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.

Without repeatable benchmarks, it feels like obsessing over a language's syntax and semantics feels a little like debating whether you write assembly using AT&T or Intel syntax.

AIs would benefit from better query languages for the same reasons people would.

The difference is that the bulk of what an AI knows is baked in when it's trained, at least for now. There's no way for it to learn a language and improve with it.

That's not quite true in my experience; AI can pick up new languages very quickly and are able to adapt to novel syntax and semantics with just a description and a few examples. What's also baked into the AI are decades of PL research and it can quickly deploy esoteric PL concepts not found in 99% of languages.

In my experience it's rather people who have the most trouble with new languages, as the difference between the PL frontier and languages that most people use is quite extreme.

Conversely, AI is adept at staking out a point in the PL design space and developing a grammar and vocabulary around it. Then it writes a parser and interpreter to execute whatever semantics, writes a standard library to support writing programs, and finally writes the compiler in itself.

Because it's so good at doing this you can do a lot of exploration whereas before it would take years now it takes months.

It'll do something, but with a higher error rate and far more iterations needed.

Again it's just not been my experience through testing so I'm curious what kind of measurements you're citing here.

Here's some related work that matches my observations: https://danluu.com/pl-tokens/

Thanks, yeah I remember when that made the rounds a couple weeks ago. Although what I'm proposing is a little different than what's covered there: the AI designing a language for a particular task, writing the runtime to implement the language, and then solving the task in the language it designed. The blog rather is about how an AI performs with languages designed by people for general purposes.

What evals did you use to compare that to using an existing language with a large corpus of training data?

I've got two experiments running now and I'm going to do a third soon. The first area is linear algebra, where there's a readily available notation for the AI to operationalize. In this area it's very easy for the AI to one-shot write correct algorithms that are shorter than typical languages because they are already written down, and the notation is very compact, so all it has to do is a direct translation from a textbook. I'm now working on comparing with algorithms it doesn't already know.

The second area I'm working on now doesn't have a readily available notation, which is state machines. Here, the AI can concoct a very terse state machine representation and write very complex state machines that can be statically analyzed so it has a better time than writing in a plain language without that capability. Now I'm trying to test how it fares against other state machine DSLs.

The next area I will move to after this I think is music, which also has a readily available notation that AI can operationalize. No numbers to report yet but I'll publish my research when it's done.

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 don’t think that extreme is compatible with the reporting/analytics use case of SQL DBs. Even though entire roles/companies may never touch that kind of SQL, there’s a massive quantity of it out there.

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.

Could the same queries be 'planned', compiled down to pipelined KV operations, by the requester? I don't see that this is inherently less capable. You could even use an existing ORM – though I think you can do better when not compiling to something declarative, maybe more like polars.

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

It’s theoretically possible to do that kind of planning on the client, but difficult and not worth it compared to letting the database do it. Especially for reporting, there’s another disadvantage: many query planners use runtime statistics from the database to build the plan; synchronizing those onto the client would be difficult and error-prone.

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?

For most cases I mean compiled into the client, not planned at runtime. And yes, explicitly trading away access to the live latencies and data distribution (except maybe for interactive analysts) – if you want a better query you profile and edit the client, like with any other service. Or in the JIT analogy, writing Go/Rust over JS/Java, using experience and profile-guided optimization but not realtime heuristics. Certainly this would make it easier to improve on bad plans, but that's survivorship bias, maybe I don't understand how many currently-good executions a more naive abstraction wouldn't produce.

Fair, but I still think it’s not worth it. Distribution alone would be a pain.

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.

Yes, it would get rid of these things, maybe I overstated the similarity to existing SQL deployments.

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.

for most 'application' like workloads not analytics - standardizing or improving on mongo-query language (MQL) would be welcome.

the drawback is your query patterns have to be known before hand when designing your application. which isn't really a drawback since you're doing it before building the application. & hence not as flexible as SQL.

I think part of the issue is that SQL is nice for some things (do some aggregation on a row-filtered subset of columns) but perhaps not as much for other things (a query where later rows depend on earlier rows in complex ways). I think being able to compile a procedural programming language to SQL would be pretty nice for the latter.

See LINQ?

I love LINQ's syntax and monadic API, but that's about it.

It speed-runs juniors into thinking they are writing transactional code when they aren't.

And for seniors who are more aware of footguns and try to be careful, they're met with an inability to do so (e.g. upserts).

https://codeberg.org/veqq/declarative-dsls is like LinQ turned to ~11~ 50.

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.

Check out CodeQL, it's a modern relational query language based on Datalog.

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

What benchmarks did they use? It seems like on larger tasks, having the LLM be familiar with the language through a large volume of training data will compactness and tenseness.

See https://danluu.com/pl-tokens/

I wan't easier ways to work with nested structures, like relationships. All this flat table structure is a pain.

the only real differences between a query language and a normal language are quantification and unification. quantification is something that seems pretty easy to paper over (i.e by just having functions that operate on Set types).

SPJ is/was working on a lanauge Verse which provides a procedural looking language that is actually either fully unification or region-based under the hood. trivially this is just allowing relations (tables or functions) to implement only a subset of input/output signatures

so yes, I think its a great idea to just smoosh the two together, particularly if its in a host language with sufficient meta programming facilities to extract out the relational parts and evaluate them as streams

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

that is a pretty difficult place to apply leverage. if you don't support SQL you're at a big competitive disadvantage. because its a weird design with lots of sharp edges that's going to take a lot of your time - customers are going to be unhappy that you don't support the knobs and frills from their existing environment.

so you can certainly float an alternate QL on top of the same base, but its going to be hard to drive uptake. you can translate SQL to your internal variant, but oddities like group by are going to twist your internal model.

at this point I think its more interesting to start to deconstruct these large software systems like OSes and databases and move the composition of systems down a step.

The early-2ks crop of NoSQL solutions have all got SQL baked in now, haven't they?

Maybe when they've achieved wide adoption for a better language than SQL, they can work on getting rid of qwerty keyboards...

Why don't we have APL as the language here?

Reply on news.ycombinator.com