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? well, how did you build the shape? and then... how did you change it? and THEN how did you change it? did you change it again after that? you did!?

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 non-standard syntax extensions specific to 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? it's a big change? it will trigger other changes? later changes depend upon earlier changes and the later ones might file so we have to take back the later ones?

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 syntax extensions that are unique to your DBMS of choice.

Of course the common standard for INSERT UPDATE DELETE is so limited as to be useless, and so you will end up using syntax extensions anyway to do even the most basic operations.

This is just unpleasant, the real trouble starts with stored procedures, and triggers. Databases introduced transactions because some changes are necessarily dependent on the propagation of a change that might fail at a later step... but any amount of computation is permissible within a transaction! So in order to 'speed up' computation, let's keep it in the database instead of sending back and forth to the application... thus was born the stored procedure. To some extent an artifact of the failure to express enough transformation in single DML statements, still a necessary evil.

Triggers are less necessary, triggers move logic from the application into the database, in some cases this is the right place for it (audit logs, data integrity checks), but any business logic probably does not belong there!

It's not that These Things are not necessary... it's just that SQL being SQL makes them necessary, because in many cases the language cannot express enough about the data model to make them superfluous.

You have some data, you want some of it, how do you get it? maybe you can do it in one tidy SELECT statement... who are you kidding? this is going to be a monster... just like every other SELECT statement that produces any useful information... 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 beyond 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 - most cases I would generously describe 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 suffering.

You have a database... and an application, you can encode your logic wherever you like... why not both? you look like you always wanted to tussle with a hydra.

In most systems I have worked on, I have created a mechanism that exports the live schema and stores it in version control... note that this is separate from whatever migratory bullshit might cause changes to it.

The problem is that everything in the schema, and most especially the definitions of views, sprocs, functions, and triggers, are part of your application's domain logic, so they must all be versioned along with the code... In some cases, there are even tables that are part of the application logic... like, there's may be a thicket of triggers, functions, and views, which have different effective behavior depending on the contents of a particular table.

You have a database... no wait, a different database... well, just discover its shape and then do relevant things ok?

In many cases the only 'correct' way to interact with a database is by dynamically generating queries based on either a SSoT schema, or just querying the database itself to extract its schema.

These generated queries are very often peak suffering. Think about it: you are writing $lang to generate SQL to generate data to build data structures in $lang to generate SQL that retrieves or generates more data just so you can interact with it in $lang... but there is peaker suffering: SQL that generates SQL. Yes, some of you may not know this, or may not have been exposed to it, but I speak the truth.

I don't even blame the programmer... I've reviewed some of these and it really was the right choice ...the reason it is the right choice is that SQL is so wrong.

Sure I would love to build a data language of my own that solves all problems etc. but mostly I just need to bitch, because I hate SQL so much I am occasionally overwhelmed.

Also this will serve as a reference document for when people ask me why I hate SQL so much.