> What is exactly hard about sql?
- No non-nullable types (at the expression level). No way to express e.g. normal boolean logic
- No real data structures (at the expression level), unless you count rows, which are not first-class values. Even collections aren't first-class
- Very awkward control flow constructs. E.g. look at how you write recursive queries. Even if/else is weird. It's a classical Turing Tarpit: everything is possible but nothing is easy
- Absurdly complex grammar. Something like 200+ keywords. Bizarre and inconsistent quoting rules
- Weird rules about where expressions can be used, and where you have to put variables. Often if you want to extract out a common subexpression and reuse it you have to completely restructure your statement.
- Missing basic functionality, e.g. no string concatenation operator (except in some nonstandard dialects)
- No libraries. No standard way of reusing code. No unit testing framework, which means in practice most projects have no testing.
It's a terrible language by any reasonable standard. Its closest cousin is COBOL, and that's honestly less awful to write.
If you run into issues with non nullable types and missing of real data structures then i guess you dont get the purpose of SQL and its tech. Its a query language to retreive data. thats the only purpose. With under the hood a crafty machine that enables atomic principles and depending on the sql tech other different functionalities like scalability and more.. No Libraries? There are a zillion battle tested libs out there. Some are used by companies like Paypal, SpaceX, Tiktok, youtube, Gmail and many more. And why you would unit test datasets in a database? You normally use mocks to test against your data layer. Most if not all software stacks have mock libs available.
> If you run into issues with non nullable types and missing of real data structures then i guess you dont get the purpose of SQL and its tech. Its a query language to retreive data. thats the only purpose.
That's no excuse for making it bad.
> No Libraries? There are a zillion battle tested libs out there.
Not in SQL.
> And why you would unit test datasets in a database?
For the same reason as anywhere else.
> Most if not all software stacks have mock libs available.
Exactly. They're a basic piece of functionality you'd expect most languages to provide, but not SQL, because it's awful.
> no string concatenation operator
`||` is the SQL standard. IMO its biggest deficiency is a single NULL nullifies the whole thing. But at least that's consistent with how NULL behaves in many other cases.
- No custom sum types
I would love to be able to say e.g.: this column is either a PK in the “results” table or a PK in the “errors” table.
What are your thoughts on the prql language?
Sounds promising. I haven't had the chance to actually use it though.