2 ms·
> variations in SQL mostly reflect the variations in the supported feature set I mean, the notion of modifying the language itself to enable various features i
by setr 3y ago
> variations in SQL mostly reflect the variations in the supported feature set
I mean, the notion of modifying the language itself to enable various features is a kind of psychosis not seen in decades in the application-language universe -- functions becoming keywords, and flags becoming keywords, and keywords injected randomly for english-ness [TRIM(LEADING "c" FROM col)] is generally absurd. For the most part, these should be modules/libraries/etc but SQL defines an aesthetic, and that aesthetic is a smattering of new keywords for every feature/function.
> Developers have also found themselves burned for decades by trying to interact with databases in languages that aren't SQL, including and especially various DSLs and ORM frameworks.
I think the fundamental issue here for DSLs is that at the end of the day, they're all compile-to-SQL languages, because the database does not offer any other API than SQL. And the general confusion is that SQL pretends to be standardized, so DSLs try to support all the databases, and end up implementing only some minimal shared subset... no matter how nice the DSL might be, you end up having to leave it anyways.
ORMs are a different story; they're not just trying to hide the SQL language, they're trying to hide the relational model itself, and the vast majority of issues you have revolve around that object-relational mismatch. If one gets turned off by the mismatch, fixes it by writing raw SQL directly, and avows to never not-write SQL again (including the avoidance of query builders), then I don't know. They've conflated the two unnecessarily.
But if you admit to query-builders being useful (and I demand you must; who could look at C# LINQ and think "nah, I LIKE smashing strings together and carefully administering the correct number of commas and precise ordering of clauses"?), then you admit to the possibility a better API exists.
> Maybe PRQL is the one that finally succeeds, but the lack of success until this point is not for lack of trying or imagination.
The fundamental confusion I have is that looking at other systems with fancy underlying engines, you would typically see a division between the API/language interface and the engine itself -- the frontend, and backend. And the frontend may be swapped out cleanly. Erlang/Elixer both sit on BEAM. Java/Scala/Clojure sit on the JVM. But an RDBMS vendor will only ever implement and support the singular dialect of SQL; there is nothing else. They'll probably implement support for app-language support for procedural logic (mssql has language extensions, postgres has PL/*, etc), but to execute actual database commands you end up submitting... SQL. There is nothing else.
It's intimately (and afaik unnecessarily) tied to database engine, and I don't really understand why. And anyone who tries to do anything different must do so as a compile-to-SQL, and inherit all of the issues of transpilation and the underlying language itself. I could understand it as market forces, except even our one hope for good in the database world, postgres, makes the same choice
- nerdponx 3y agoI really liked this response and I agree with all of it, but I especially agree with this statement: > I think the fundamental issue here for DSLs is that at the end of the day, they're all compile-to-SQL languages, because the database does not offer any other API than SQL. And the general confusion is that SQL pretends to be standardized, so DSLs try to support all the databases, and end up implementing only some minimal shared subset... no matter how nice the DSL might be, you end up having to leave it anyways. Until databases offer a better interface for actually interacting with the database, we are stuck with SQL, and as long as we are stuck with SQL, X-that-compiles-to-SQL will always be challenging to design and use successfully. I know libpq/Postgres has a binary wire protocol, but I'm not sure what it consists of, or to what extent it improves on the current model of sending a blob of SQL to be parsed the server. My bias against query builders specifically I think comes from spending too long with Python, where SQLAlchemy Core is the only standalone query builder in town, and it comes with a lot of ORM-derived baggage and a relatively high level of abstraction (not to mention complexity). This IMO makes it not much better than string templating because it's a relatively large amount of fuss to get it working, unless you also want to take advantage of other features like uniform logging, client-side connection pooling, client-side parameter binding of arbitrary Python objects, supporting multiple database backends in an application like Airflow, etc. Whereas in Common Lisp I very much enjoy S-expression-based DSLs like SxQL (https://github.com/fukamachi/sxql https://github.com/fukamachi/sxql), and I agree that they're much better than constructing SQL out of raw strings. I'm not aware of a Scheme equivalent, although it looks like Gauche is planning to add one in the future (https://practical-scheme.net/gauche/man/gauche-refe/SQL-parsing-and-construction.html https://practical-scheme.net/gauche/man/gauche-refe/SQL-pars...): "The plan is to define S-expression syntax of SQL and provides a routine to translate one form to the other."
- setr 3y agoright; this is why I won't accept that SQL is good-enough, so DSL's aren't worth looking at. The current state of affairs should be despised just as much as JS-only browsers -- these DSLs are an attempt to alleviate the suffering! SQL even shares many of the same issues -- weird semantics (stuffing a trinary logic into boolean operations, enabling both false negatives and false positives), lack of a proper library/module system, inconsistent language, non-composable features, etc. The difference is only that with JS, there are many better-behaved popular languages to look to and criticize it on. With SQL, there are competing languages implementing the relational algebra, but they're mainly serve as a curiosity and few use them in practice; there is SQL and only SQL. You can fairly argue that the DSL cannot fundamentally succeed until RDBMS vendors finally buy into it, but you can't argue that SQL is such a great representation of the relational algebra that it isn't worth bothering with; it's just the language we happen to be stuck with.