Dear SQL: A Love-Hate Relationshit
I think a lot about SQL, mostly because I write a lot of SQL, but also because I hate SQL with a passion... hating something with a passion is a form of love - right?
SQL is the hideous and knobbly and hideous external surface of the most important component of the application stack: The persistent state management system.
You have some data, it has a shape, how do we describe the shape of the data? a series of DDL statements.
What is the current schema of your database? it is the result of a long sequence of migrations which may include data movements from tables that used to exist into new tables which may or may not still exist!
Your tables have properties that are absolutely crucial to the integrity of your system's state, do we describe these in the same statement as the table? probably not! instead we provide separate statements like: CREATE UNIQUE INDEX or ALTER TABLE ADD FOREIGN KEY
There is no declarative and complete representation of the the schema and what's more, integrity may be mediated by functions and triggers which must also be viewed as separate DDL statements. All of these secondary mechanisms have syntax that is monkey-patched into your DBMS of choice and are completely non-portable.
You have some data, you want to change some of it, how do you describe the change? a series of DML statements.
Changes to data (without changing the schema) are described by DMLs, of which there are definitely three: INSERT UPDATE DELETE but also there could be more of them, but those are non-standard, non-portable, and use monkey-patched syntax extensions that are unique to your DBMS of choice.
You have some data, you want some of it, how do you get it? if you're lucky you can do it in one DQL statement, good luck writing it... good luck reading and modifying it in six months, just kidding, luck won't help you.
SELECT and its ilk are Data Query Language statements. 'Standard' DQL does not expose even the most basic features of the underlying semantics (I have to make a brief concession that CTEs were a truly useful appendage, and without them SQL would be insufferable in the modern age), and every DBMS will add its own inane extensions intending to provide a marginally more usable query language.
Somehow OLTPs are almost completely stuck with SQL, but there are other languages available in adjacent persistence engines... what shocks me is that some of them decide that they should include an SQL layer anyway. It's not like the other data management languages are significant improvements - I would describe most as lateral movements - but at least they are exploring the space, and they might happen on something good...
...like PRQL. Sure it's a little fresh but at least when you come back to a query six months later you can understand it and make modifications without great stress.
Yes, I have ideas and would love to build a query language of my own that solves all problems etc., but mostly I just needed to bitch, because I hate SQL so much I am occasionally overwhelmed.