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, and 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 extension
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.
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.
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?
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.
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.
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
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.
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.
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.
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.
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
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.
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."
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)
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.
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.
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.
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.
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.
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.
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
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.
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 extension
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.
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.
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.
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.
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.
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
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
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.
(https://news.ycombinator.com/item?id=24106608, https://news.ycombinator.com/item?id=19871051)
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
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.
It was only a partial implementation of the relational model, we could have been so much better had it not become the standard
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.
Old man rant off.
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.
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.
[0] https://courses.cs.duke.edu/spring03/cps216/papers/date-1983...
They might love the relational model concepts that manage to seep through it
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."
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.
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.
See https://danluu.com/pl-tokens/
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
Maybe when they've achieved wide adoption for a better language than SQL, they can work on getting rid of qwerty keyboards...
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.