4 ms·

I still don’t understand why so many people seem to talk about replacing SQL. It’s not complicated, can be learned in a few hours, it’s relatively easy to read

I still don’t understand why so many people seem to talk about replacing SQL. It’s not complicated, can be learned in a few hours, it’s relatively easy to read and it fairly portable across databases and stacks. It’s been the most important detail of my job for the last 20 years.

jandrewrogers 4y ago
SQL has a couple legitimate limitations as a query language, largely because we expect databases to do so much more today than when it was designed. I've been both a heavy user of SQL and implementor of SQL databases focused on data models like graph, spatial, et al, so I feel the pain from both directions. Some useful things are difficult to effectively express in SQL because it was not designed to make them expressible. Adding support after the fact introduced a lot of compromises and complexity; you can tell which parts of SQL are second-class citizens. SQL would have been designed differently if its original scope had been broader and it is an inelegant mess for some types of data models. You can make it work but it isn't pleasant. Attempts to address these gaps explain much of why SQL databases all have their own non-standard SQL dialect, and it has made SQL effectively non-portable across databases. In principle, a more modern query language could elegantly address the much broader scope of how we use databases today in a standardized way, instead of the zoo of non-standard extensions and mismatched parts that are grafted onto specific SQL implementations.
bob1029 4y ago
SQL is uniquely challenging for many to learn because you cannot poke around in the bushes procedurally and still arrive at a good outcome. The declarative nature of SQL usually means all or nothing. I do recall how frustrating it was to learn all of the join shapes. It's so trivial to me now, but it was a nightmare at the beginning of my journey. The popularity of ORMs is absolutely not a surprise to me.
- btilly 4y ago
  And all frameworks migrate over time towards being more declarative with this exact failure mode.
setr 4y ago
because as a language it's quite complicated, except in simple cases, the syntax is completely arbitrary and randomly injects keywords willy-nilly in an absurd attempt at being "english-readable" because it lacks composability, making dynamic query generation a massive pain in the ass, as well as manual edits of queries. because watching DBA's flailing strings about, smashing them together like apes with stones, just so they can struggle to feed it into an EXEC SQL call is perhaps one of the greatest tragedies of our programmatic times. because it's not at all portable, with every database arbitrarily extending the SQL standard left-and-right, and even basic conveniences and functions end up breaking your portability goals; the ANSI SQL standard defines less a standard and more of an aesthetic mold for each dialect to try to match against. because it lacks any portable ability to extend the language, with nearly every function added not as a stdlib function but instead as part of the language itself, often with custom keywords to go with it; there's a reason you find imports for anything in python, and next to nothing for rdbms's. Nearly any extension/package implemented is hooked into the DB internals, and not portable. because the editor tooling is basically stuck in the 80's, with universally useless error messages, absurd limitations like select-clause ordering requirements (e.g. you can't use a select alias in the where clause, because the where clause gets evaluated first; this is exactly equivalent to C requiring forward declarations), select-before-from breaking autocomplete, and only advanced editors bother to even forward data-type mapping from schema definitions to your autocomplete because of the open-world assumption (the schema could change under your feet); hell, even SQL formatters have largely given up on trying to format anything but the most rudimentary constructs, because of the ridiculous amount of language they need to support. because the language stuffs a trinary logic (true,false,null) into a binary logic system and grants you access to all of the benefits of both false positive results and false negatives. because the relational algebra is elegant, in all the ways the SQL language is not. But we'll stay with it, because at the end of the day, no one gives a shit about SQL. The SQL language is just the interface to the Glory of Codd, the relational algebra, the RDBMS, and in this we find all true value. But we can still dream of a better world. A world where RDBMS's support more than one query language.

jandrewrogers 4y ago

SQL has a couple legitimate limitations as a query language, largely because we expect databases to do so much more today than when it was designed. I've been both a heavy user of SQL and implementor of SQL databases focused on data models like graph, spatial, et al, so I feel the pain from both directions. Some useful things are difficult to effectively express in SQL because it was not designed to make them expressible. Adding support after the fact introduced a lot of compromises and complexity; you can tell which parts of SQL are second-class citizens. SQL would have been designed differently if its original scope had been broader and it is an inelegant mess for some types of data models. You can make it work but it isn't pleasant. Attempts to address these gaps explain much of why SQL databases all have their own non-standard SQL dialect, and it has made SQL effectively non-portable across databases. In principle, a more modern query language could elegantly address the much broader scope of how we use databases today in a standardized way, instead of the zoo of non-standard extensions and mismatched parts that are grafted onto specific SQL implementations.

bob1029 4y ago

SQL is uniquely challenging for many to learn because you cannot poke around in the bushes procedurally and still arrive at a good outcome. The declarative nature of SQL usually means all or nothing. I do recall how frustrating it was to learn all of the join shapes. It's so trivial to me now, but it was a nightmare at the beginning of my journey. The popularity of ORMs is absolutely not a surprise to me.

btilly 4y ago
And all frameworks migrate over time towards being more declarative with this exact failure mode.

setr 4y ago

because as a language it's quite complicated, except in simple cases, the syntax is completely arbitrary and randomly injects keywords willy-nilly in an absurd attempt at being "english-readable" because it lacks composability, making dynamic query generation a massive pain in the ass, as well as manual edits of queries. because watching DBA's flailing strings about, smashing them together like apes with stones, just so they can struggle to feed it into an EXEC SQL call is perhaps one of the greatest tragedies of our programmatic times. because it's not at all portable, with every database arbitrarily extending the SQL standard left-and-right, and even basic conveniences and functions end up breaking your portability goals; the ANSI SQL standard defines less a standard and more of an aesthetic mold for each dialect to try to match against. because it lacks any portable ability to extend the language, with nearly every function added not as a stdlib function but instead as part of the language itself, often with custom keywords to go with it; there's a reason you find imports for anything in python, and next to nothing for rdbms's. Nearly any extension/package implemented is hooked into the DB internals, and not portable. because the editor tooling is basically stuck in the 80's, with universally useless error messages, absurd limitations like select-clause ordering requirements (e.g. you can't use a select alias in the where clause, because the where clause gets evaluated first; this is exactly equivalent to C requiring forward declarations), select-before-from breaking autocomplete, and only advanced editors bother to even forward data-type mapping from schema definitions to your autocomplete because of the open-world assumption (the schema could change under your feet); hell, even SQL formatters have largely given up on trying to format anything but the most rudimentary constructs, because of the ridiculous amount of language they need to support. because the language stuffs a trinary logic (true,false,null) into a binary logic system and grants you access to all of the benefits of both false positive results and false negatives. because the relational algebra is elegant, in all the ways the SQL language is not. But we'll stay with it, because at the end of the day, no one gives a shit about SQL. The SQL language is just the interface to the Glory of Codd, the relational algebra, the RDBMS, and in this we find all true value. But we can still dream of a better world. A world where RDBMS's support more than one query language.