SQL is pretty good though. It could compose better, sure, but it’s well suited to data queries. I think people dislike learning a new language, even to the point of writing bad software. I wonder why?

I'd say that pretty much everything about SQL is bad. Apart from composability, the syntax is super annoying (the ordering of keywords), many databases have pretty much no error reporting if you get it wrong and the linters suck. You can't unit test it. It's not very well standardized and it's actually more than one language (DML vs DDL). Plus, it's not very readable. And the declarative nature of the language is eventually going to back-stab you when you discover yet another edge case of your query planner that flips a reasonable plan to one that's literally going to take ages.

Few things: Regarding unit testing, it's now very possible to productively unit test a SQL database - I'm familiar with a Java package that boots up an embedded PostgreSQL, obviously Docker is now in vogue, etc, etc. It's not quite 'just run a function', but you write your code in the same style as a unit test and you're running hundreds of tests in seconds. You can use transactions to automagically clear out your state, which means performance is also excellent. Regarding the standardization of the language - no, it's not totally standard (although arguably that's a good thing, we don't want 10 implementations of exactly the same thing, they'd never evolve), but because they all work sort-of-the-same, a lot of language support ends up transferrable. You can use ActiveRecord on many backing databases, for example. Query planner problem is definitely true, know folks who've spent a lot of time hinting Oracle and scaring Postgres into the same, though personally only took a few days.

However, SQL is IMO the best of a bad bunch. Most non-SQL databases I've used have been extremely sharp cornered, whether this be via: unpredictable/unbounded worst case performance in pessimal storage cases (RocksDB, Cassandra), various parts of the code where unbounded memory/storage usage is required (Cassandra, Spark, Elasticsearch), extremely low level APIs making it easy to make unobservably (as in, you won't notice it and will eat the pain every read) slow APIs (RocksDB). I'm not saying there's nothing better, but it's probably not an all-rounder.

There are certainly things I don't like about most SQL databases. Few SQL databases are columnar, which is _the_ key to good performance in the 2010s, and native LZ4-style data compression is also not widely used. But for most engineers they solve a lot of problems for you without having to think about them.

> I'm familiar with a Java package that boots up an embedded PostgreSQL, obviously Docker is now in vogue, etc, etc.

I think you are talking about Testcontainers, although it only matches the second part of the comment (can manage different external moving parts in containers for integration tests): https://www.testcontainers.org/

I'd be interested in seeing an embedded Postgres library; I only see H2 and maybe Derby being used for tests and "getting to know the ropes" use cases, eg. a default install.