>
The biggest problem is that SQL has no well defined "compile time". For all practical purposes, it's always an interpreted language. I can't check my code against the production database without running it.I get what you're saying but I've seen that as a pretty common problem outside of SQL as well.
Firstly, and sorry for the nitpicking, but it will explain my point, I don't think the "interpreted language" is the right description here. Nearly all modern "interpreted" languages are still technically compiled, albeit it's bytecode that's then executed by the language runtime. Usually when people talk about interpreted languages they're talking about JIT compiled (like Perl, Python, PHP, Ruby and Javascript, to name a few) but technically one could argue that Java and other, similar AOT compiled languages are also interpreted. Even some AOT compilers that produce a Windows PE's (.EXE files) and Linux ELF (for example) will be interpreted (either that or JIT compiled to machine code when executed).
Anyhow, the reason I raise that point is because there are a lot of parallels between SQL and other JIT compiled languages. With JIT compiled languages, they are obviously still compiled - even if it is just to bytecode. But in many cases the compiler will perform many of the same sanity checks that an AOT compiler would. Likewise SQL is compiled albeit by the RDBMS to something that's optimised for execution on that platform and it's during that compilation that table names and types are checked. And much like a JIT compiled language, you have to make a few assumptions about the running environment when you write the code .eg the same and correct Perl cpan or Javascript npm modules are installed on dev, UAT and live environments; or with databases you have to make assumptions that your dev database schemas mirror your live database.
The problem RDBMS suffer from is that some of the solutions for the JIT compiled environment problem (eg docker) don't work so well for databases because you're dealing with data which can be sensitive and is often in flux, rather than code which should be pretty much unchanged across the deployment pipeline (whatever that may be).
The data is the key problem here. If you wrote a C++ routine to handle data encoded in JSON files then you'd still be at the mercy of executing your program to check it can parse the data correctly (eg the field names are correct and the data exists). Likewise if you wrote the same routine in Haskell. Or if you replaced JSON with XML, YAML or CSV files.
I'm not saying SQL is without its warts though nor that a functional-paradigm reimagining wouldn't be better. But I do think some of the biggest challenges with SQL is data related and thus could very easily rear their proverbial heads in other languages too (particularly if you don't want to trust your language to perform too much background magic for fear of risking the integrity of the data you're retrieving).
