> The other problem, IMO, is that programming languages are very poor at bridging the difference between the SQL domain and the language domain. We really need plugins for compilers because ORM libraries often are harder to learn than the database itself.

Actually in past year I discovered that JetBrains IDEs (in my case PyCharm) have nice feature that seems like not everyone is aware. It is due to their integration with DataGrip. If you configure the IDE to connect to your database, it will start looking for SQL in your strings, it will then do code highlighting, autocomplete table names and fields. If you use refactoring it will understand SQL, if you refactor database it will produce migration statements. It appears to solve all the issues that ORM supposed to solve, and you still have full control since SQL didn't need to be abstracted from you.

Edit: this video shows how it works in action (forwarded to a relevant part): https://youtu.be/_FlpiNno088?t=2868

>This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code.

My problem is that it is like some sort of black magic to me. If I write a complex query I have no idea if what is spit back to me is actually what I want. The only way is seeding lots of records and then manually checking that each filter and calculation is doing what I want.

In code complex things can be broken down into more simple items. Then I can reason about and test those building blocks into something I understand and am confident that is working as intended.

Yes, SQL's biggest fault is that it's not very composable. Complex queries end up being long and repetitive, and the order of the parts of a query is totally unintuitive (it should go something like: FROM, GROUP BY, SELECT, ORDER BY rather than SELECT, FROM, GROUP BY, ORDER BY, which makes autocompletion hard).

QUEL[1] was like that, but thanks to Oracle SQL won.

[1] https://en.wikipedia.org/wiki/QUEL_query_languages

One thing I never understood is that the SQL language, and its alternatives, share the same theoretical IR -- the relational algebra -- it shouldn't be that difficult to implement for postgres/mysql alternative relational languages like QUEL or Datalog. Or even just a simplified SQL with a sane, consistent syntax.

I know PG has a bunch of procedural-language alternatives, but afaik, no relational-language alternatives.

At the same time, I'd also expect it to not be that difficult to transpile from say mysql to postgres, yet there's very little in that space, at least in open source (there is however many ORMS that map to either mysql or postgres..)

Both of which, amongst the procedural-languages, you'd find a hundred transpilers (JS->C) and VM-languages (Clojure,Scala,etc on jvm) implemented even by bored/curious students. Which makes my suspicion that there's no technical blocker, just a cultural one.

That is, QUEL losing to SQL, such that you can't find any implementation, is an absurd concept -- it should be available on postgres (perhaps requiring some special starting keyword) -- but somehow it is not.

The original query language for Postgres95 was QUEL.

libpq refers to PostQUEL.

Ingres became Post-Ingres which became POSTGRES which became Postgres95 which became PostgreSQL.

https://www.postgresql.org/docs/12/history.html

CTEs can help a bit with composition (and readability)

>programming languages are very poor at bridging the difference between the SQL domain and the language domain

Depends a lot on the language. I've lost countless hours to things like JOOQ trying to figure out how to get it to do what I want, or express the query in its quirky not-quite-right DSL, plus dealing with mappings, pojos, auto-generation, and so on.

However, on the other hand, in a dynamic language with just enough support to move the result of your queries into a map of key/value, I feel no friction at all. I'm using little more than a simple jdbc wrapper in Clojure and even after months on the project, I'm still just continuously stoked with seamless the whole thing is.

jOOQ can be used in a less-type-safe way. For example, `fetchMaps` [1] does more-or-less what you describe.

However, I have found it worthwhile to learn to use the more advanced features you mention. Extending type safety to queries is incredibly useful. Consider cases when developers are making code and schema changes concurrently that overlap.

[1] https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/Result...

SQL (at least in Postgres) is already type safe.

jOOQ can effectively extend the type system of Java to the construction of ad-hoc queries by way of code generation. jOOQ generates Java classes that correspond to the database schema, which can be used in its query building DSL.

For example, if I have a `timestamp with time zone` column in PostgreSQL, that is represented in the generated code. I will be prevented from inserting a Java `String` into that column - I will have to provide an `OffsetDateTime`. (This is how it would be in a typical configuration - it is flexible enough to do pretty much anything).

Another example, let's say I reference some column in a query in one of my feature branches. Meanwhile, somebody has dropped that column on the master branch. When I rebase my feature branch onto master, my build will fail.

The thing that really worries me when I'm writing SQL is the possibility that the query planner will get frisky and choose some disastrously slow join order, but only once in a while. I've run into way too many hard-to-reproduce performance issues like this.

Having the database automatically figure out how to run a query is a great feature, but most of the time I'd happily just write explicit nested loops for the sake of predictability.

(I could be wrong,) but I thought one of the points of stored procedures was that the query plan is precompiled?