💾 Archived View for tilde.pink › ~kaction › log › 2021-10-07.1.gmi captured on 2022-07-16 at 14:31:24. Gemini links have been rewritten to link to archived content

-=-=-=-=-=-=-

SQL -- the broken and the only

Structured Query Language is a prominent example of an idiot-first design and the problems it brings.

To cater to pointy-hair suits (err, business analytics), SQL has verbose and non-expressive syntax similar to natural language. Let's consider several examples of how inferior this syntax is. Code is written in Postgres dialect but is easily transferrable to any other SQL database.

Question of permissions

Databases have the concepts of a table, user, and permissions. A user may or may not have permission to perform some operations with a specific table. How would intelligent being design functionality of granting and revoking permissions? Probably something along the following lines:

set_permissions("schema.table", "joe", ["select", "update"])

In SQL, getting this done would look like the following:

BEGIN;
  REVOKE ALL ON TABLE schema.table FROM JOE;
  GRANT SELECT ON TABLE schema.table TO JOE;
  GRANT UPDATE ON TABLE schema.table TO JOE;
COMMIT;

The difference is more profound than verbosity. SQL syntax encourages you to grant permission without considering what the entire list of joe's permissions is.

Alteration

Suppose you want to make sure the table has a column. It should be straightforward, right?

ALTER TABLE schema.table ADD COLUMN IF NOT EXISTS foo bigint;

Wrong. This query grabs AccessExclusiveLock on the table, whether it already has column "foo" or not. The correct way to do it is to use conditionals and check "information_schema" instead.

List can be empty, can't it?

It is pretty typical to select rows where some value belongs to some set, like the following:

SELECT id, foo, bar FROM schema.table WHERE bar in (42, 39);

So far, so good. It is natural to generate such a query with desired values of "bar" determined programmatically. One day, we will end up with this query:

SELECT id, foo, bar FROM schema.table WHERE bar in ();

In every sane programming language, check whether something is a member of an empty list returns false. In SQL, it is a syntax error. Here is the robust way that handles an empty list:

SELECT id, foo, bar FROM schema.table
   WHERE bar = ANY(ARRAY[1, 2, 3]::bigint[]);

Yes, typecast is mandatory.

Conclusion?

These are just a couple examples of general SQL property -- every day-to-day task has simple, well-documented, and wrong ways to do; the correct way is unnecessarily convoluted. This property is no coincidence -- it is the inevitable consequence of idiot-first design.

Furthermore, SQL failed even its primary target audience. Nowadays, suits don't expect to query data with weird language; they want to do everything with a point-and-click web interface. We, people, inherited this defective piece as the only way to design and query ACID databases. Sad.

https://www.metabase.com