I always find the SQL composability argument weird. Just because something is not JSON does not mean the only way to modify it is by doing fiddly things with strings. I assume this does not exist yet but you could totally have a library that lets you programatically modify the SQL AST.
Also this example is kinda weird - if I create the ageGroup, and then filter it, does the filter happen before the groupBy? I would find that counterintuitive.
The problem with this is that “the” SQL AST does not really exist. There exist different ASTs for different database dialects. There are so many variations between db vendors in what custom keywords they add for special operations, all of which tend to be useful in practice.
And even if you were to only target “standard SQL”, there are very few rules to how these statements are constructed, meaning you would have to implement the full spec from scratch, with special cases for every single statement. And in the end you’d most likely have an API that is as baroque as the SQL syntax itself, which isn’t very useful to programmatically work with.
I’ve tried to build an AST like you describe, and gave up on it. I wrote up my thoughts on the matter in case you’re interested (but it boils down to what I said here).
What you’d really need is a more regular syntax that works on a deeper level, essentially the substrate onto which the SQL syntax gets mapped as well. Databases in the end need something sane to work with as well, so their SQL parsers have to do that already. Might as well bypass the tricky SQL parsing entirely. Unfortunately, that substrate would be even more vendor-specific.
What they mean by composable is that vanilla sql makes it hard to chain a series of select, group, “map”, select again together. You can sort of do it with sub selects or a CTE. But it’s really awkward which often means you end up issuing multiple sql statements when you could have done it in one. There are libraries that will write the sql for you while giving you a more ergnonomic API but SQL itself doesn’t make it easy.
I mean, the problem is that you can do things that produce something that looks like a table, but can’t treat it as a table. It’s not closed under composition, and so it has a lot of warts and pointy edge bits.
from sql’s POV, everything you listed (tables, views, set-returning functions, values()) is named a “relation”, and is very much composable, as it’s the building block of a relational engine. There are some known quirks about composability with UNION tho.
But yes, SQL strings are definitely not ideal to build a query programatically, so I understand why you say it’s not composable.
The objects a sql query is referring to however, are composable by essence.
For the Democratising Change Events and Soft Deletes section, there is SQL:2011 standard which contain description of “System Temporal Tables” which allows for exactly that (for now I ignore the fact that Democratising Change Events is supported by some DBs, like PostgreSQL, but in non-standard way).
And about SQL composability - we have libraries like Java’s jOOQ or Elixir’s Ecto which allows exactly that while still using SQL.
This is my #1 request, and it isn’t just a technical one. I often find that PMs and other business coworkers are completely surprised when I can’t pull up historical information about a piece of data.
So I often build log tables by default, in anticipation of this. It’s not hard, but it’s extra work.
A: “We should move some of this common functionality out of optional support libraries into a database product”
B: “Don’t be stupid, you only think you want that in your database because you obviously didn’t know about this optional support library”
I always find the SQL composability argument weird. Just because something is not JSON does not mean the only way to modify it is by doing fiddly things with strings. I assume this does not exist yet but you could totally have a library that lets you programatically modify the SQL AST.
Also this example is kinda weird - if I create the
ageGroup, and thenfilterit, does thefilterhappen before thegroupBy? I would find that counterintuitive.The problem with this is that “the” SQL AST does not really exist. There exist different ASTs for different database dialects. There are so many variations between db vendors in what custom keywords they add for special operations, all of which tend to be useful in practice.
And even if you were to only target “standard SQL”, there are very few rules to how these statements are constructed, meaning you would have to implement the full spec from scratch, with special cases for every single statement. And in the end you’d most likely have an API that is as baroque as the SQL syntax itself, which isn’t very useful to programmatically work with.
I’ve tried to build an AST like you describe, and gave up on it. I wrote up my thoughts on the matter in case you’re interested (but it boils down to what I said here).
What you’d really need is a more regular syntax that works on a deeper level, essentially the substrate onto which the SQL syntax gets mapped as well. Databases in the end need something sane to work with as well, so their SQL parsers have to do that already. Might as well bypass the tricky SQL parsing entirely. Unfortunately, that substrate would be even more vendor-specific.
What they mean by composable is that vanilla sql makes it hard to chain a series of select, group, “map”, select again together. You can sort of do it with sub selects or a CTE. But it’s really awkward which often means you end up issuing multiple sql statements when you could have done it in one. There are libraries that will write the sql for you while giving you a more ergnonomic API but SQL itself doesn’t make it easy.
Some libs exist for quite some time now, like https://github.com/pganalyze/libpg_query (which is used in extensions for some languages like ruby or python) and https://github.com/prisma/quaint for rust.
I mean, the problem is that you can do things that produce something that looks like a table, but can’t treat it as a table. It’s not closed under composition, and so it has a lot of warts and pointy edge bits.
from sql’s POV, everything you listed (tables, views, set-returning functions,
values()) is named a “relation”, and is very much composable, as it’s the building block of a relational engine. There are some known quirks about composability withUNIONtho. But yes, SQL strings are definitely not ideal to build a query programatically, so I understand why you say it’s not composable. The objects a sql query is referring to however, are composable by essence.isn’t that what subqueries, CTEs and views are for?
For the Democratising Change Events and Soft Deletes section, there is SQL:2011 standard which contain description of “System Temporal Tables” which allows for exactly that (for now I ignore the fact that Democratising Change Events is supported by some DBs, like PostgreSQL, but in non-standard way).
And about SQL composability - we have libraries like Java’s jOOQ or Elixir’s Ecto which allows exactly that while still using SQL.
This is my #1 request, and it isn’t just a technical one. I often find that PMs and other business coworkers are completely surprised when I can’t pull up historical information about a piece of data.
So I often build log tables by default, in anticipation of this. It’s not hard, but it’s extra work.
A: “We should move some of this common functionality out of optional support libraries into a database product” B: “Don’t be stupid, you only think you want that in your database because you obviously didn’t know about this optional support library”
I liked this! Two more things from my wishlist: