6 ms·

Dunno if it's the quality of the posts here, but I find interesting to see that when we talk about SQL nobody complains about it : nobody says there are better

by wiz21c 10y ago

Dunno if it's the quality of the posts here, but I find interesting to see that when we talk about SQL nobody complains about it : nobody says there are better alternative, nobody talks about the great schism between the last 2 major revisions of the SQL standard,... (IMHO, SQL is one of the oldest languages and it is still super powerful and used like hell in production environment, but that's just a point of view)

tragomaskhalos 10y ago
I have plenty of gripes about SQL but am not aware of any real alternatives ... which I do find bizarre - in the realm of programming languages in general we live in a time of Cambrian explosion in terms of the number and type of languages being used and developed, and yet SQL sits aloof and unassailable on its pedestal. There are a number of clear paths not taken: 1/ A new, better language that compiles down to SQL; 2/ A set of enhancements to SQL that compile down to regular SQL (<= best imo); 3/ A new language that compiles down to the same AST that is generated from SQL: this of course would need to be done by the database implementors. That we don't see this I attribute to: 1/ Perception that SQL is "good enough". Which is true as far as it goes, but again odd compared with the endless innovation in the world "normal" programming languages; 2/ Innate conservativism of the database tech community; 3/ Wide use of tools that already sit on top of SQL - query builder APIs, ORMs etc.
- garysieling 10y ago
  I've wondered about this too. I think ORMs serve effectively the same purpose as Javascript transpilers (which gives you 1 & 2) You can also write stored procedures in Postgres in other languages.
  - __jal 10y ago
    ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators. Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. Fabian Pascal (who has made a career out of SQL criticism) proposed a replacement system called Raquel that would have a "real" set-theory-based query language. I don't think it went anywhere. My best guess as to why there aren't many alternatives: SQL is both mature and Good Enough(tm), but importantly, also unsexy and complicated. Database engine development is more similar to kernel development than, say, web application development, and there are simply far fewer engineers qualified to do it. Building some framework in Javascript currently gets a ton of love, and in comparison is far easier than developing a SQL replacement that is roughly as performant as SQL and either less awkward linguistically or functionally superior. People do take up those sorts of projects, but they're pretty lonely unless some combination of talent, luck, and timing turns into rock star status (Linus wasn't the only one writing unix clones at the time).
    - dancek 10y ago
      > Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. PostgreSQL distributions come with support for PL/Python, PL/Tcl, PL/Perl and PL/pgSQL (a PostgreSQL specific PL/SQL clone). Other languages exist, but aren't included. See https://www.postgresql.org/docs/9.6/static/external-pl.html https://www.postgresql.org/docs/9.6/static/external-pl.html
      - __jal 10y ago
        Yes. And PL/Perl is perversely fun. I don't think I've ever seen other languages used in production, though[1]; I think folks tend to stick to PLSQL because of defaults. [1] I'm sure people do, but this discussion has an anecdata deficiency.
shubb 10y ago
In Enterprise Land a lot of people use ETL tooling as an alternative / in addition to writing SQL. Databases, data interchange formats, and the concepts they represent are mapped out in graphical tools like ER Studio. The SQL operations you do to load in data from various sources are done in graphical ETL tools like Microsoft SSIS, Talend etc let you draw diagrams which replace SQL. They are more properly a way of programming an engine that runs the SQL but also does things like monitor for incoming files, query webservices etc. Some also provide a technology neutral way to express transforms that can be run in SQL but also on Hadoop or whatever. That said, you sometimes need to go in and write the SQL for optimization. Enterprise Service Bus sit between multiple databases and multiple data sources and consumers, and the interfaces these provide replace SQL queries. Typically graphical tools or configuration files are used to configure the relationship between the endpoints they provide and databases. Even outside enterprise land, people rarely write SQL when defining and interfacing with databases. They use an ORM, and a lot of popular ORMs will create a database and provide a way of accessing it so that you never write SQL. Technologies like OData mean that applications can query data by proxy without needing to know the model it is stored as, and the mapping between the model OData presents and the database is typically implemented using an ORM. Cloud computing means people with very large databases increasingly want to spread the processing across many slower machines rather than spending the budget on one specialist data processing server with very fast IO. SQL is replaced by other languages in this case (although SQL like languages are making a bit of a comeback). Finally, in academia and certain very data datacentric industries, SPARQL and successors are still a thing. I think there is a big divide between enterprise development and what we learn in school / hackernews here. A lot of people outside the industry are not aware of these ecosystems of applications. I wouldn't recommend any of these over SQL in all cases. They are technologies that solve a problem and you should consider them if you have that problem.
marcosdumay 10y ago
Hum.. SQL is a "mathematically pure" language, like Lisp or, to a smaller extent, Haskell. Those tend to not attract criticism (mostly because they age slowly, but also because the most vocal language critics are biased towards them). SQL is also a standardized language. People tend to see those as something you should deal with, not something you should improve. Thus less criticism. It's also a language that is mostly embedded inside others. So any weakness is easy to compensate on the outer code. Besides this, it is a pretty well designed language.
mamcx 10y ago
The main trouble is that even if a better alternative is suggested, how convince the RDBMS developers to use it? Unfortunately, RDBMS are highly coupled. I remember when someday I ask if is possible to remove the SQL part of sqlite and substitute with my own, and get laughed! RDBMS are "expected" to be a black box enclosed in dark mystery.
saosebastiao 10y ago
I use it (way more than a lot) and I love it, but we could do better. 1) It really needs algebraic data types (and pattern matching!). I refuse to use a general purpose language that doesn't have them, and I always end up struggling to represent them using SQL. 2) Null, and the subsequent binary-but-maybe-ternary logic that it imposes on everything is a terrible hazard. It needs Option types. I guess this goes along with algebraic data types, but it is important enough to merit its own mention. 3) It needs more expressive constraints: foreign keys on partial unique indexes or views, declarable immutability, window constraints, etc. 4) It needs better graph representation and queryability. Anything remotely resembling a graph (or even trees!) ends up as a huge incomprehensible hack. Graphs are still relational, they're just ignored in favor of constructs that only tenably represent them. 5) It needs better ways to present output. Dumping everything as a table leaves the end user to their various hacky devices if what they really want is not an array of flat data structures. Users should be able to easily output data as a graph, tree, arbitrarily nested structs, associative maps, sets, arrays, and even scalars. 6) It needs extensive compile time checking of queries and statements, complete with errors and warnings and lints. As it stands right now, you only get syntax validation and checks for object existence. And although this is implementation level and not language level, SQL engines need to provide way better IDEs to compliment the better compilation. 7) It needs phantom keys for multi-column foreign keys against multi-column primary-keyed tables. As it stands right now, I have to lose the semantic benefits of a natural key in favor of a surrogate key in order to not duplicate a large number of columns in a 1-many table relationship. I should be able to use the many-columned natural foreign key, but invisible and behind the scenes map to a surrogate primary key.

by wiz21c 10y ago

tragomaskhalos 10y ago

I have plenty of gripes about SQL but am not aware of any real alternatives ... which I do find bizarre - in the realm of programming languages in general we live in a time of Cambrian explosion in terms of the number and type of languages being used and developed, and yet SQL sits aloof and unassailable on its pedestal. There are a number of clear paths not taken: 1/ A new, better language that compiles down to SQL; 2/ A set of enhancements to SQL that compile down to regular SQL (<= best imo); 3/ A new language that compiles down to the same AST that is generated from SQL: this of course would need to be done by the database implementors. That we don't see this I attribute to: 1/ Perception that SQL is "good enough". Which is true as far as it goes, but again odd compared with the endless innovation in the world "normal" programming languages; 2/ Innate conservativism of the database tech community; 3/ Wide use of tools that already sit on top of SQL - query builder APIs, ORMs etc.

garysieling 10y ago
I've wondered about this too. I think ORMs serve effectively the same purpose as Javascript transpilers (which gives you 1 & 2) You can also write stored procedures in Postgres in other languages.
- __jal 10y ago
  ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators. Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. Fabian Pascal (who has made a career out of SQL criticism) proposed a replacement system called Raquel that would have a "real" set-theory-based query language. I don't think it went anywhere. My best guess as to why there aren't many alternatives: SQL is both mature and Good Enough(tm), but importantly, also unsexy and complicated. Database engine development is more similar to kernel development than, say, web application development, and there are simply far fewer engineers qualified to do it. Building some framework in Javascript currently gets a ton of love, and in comparison is far easier than developing a SQL replacement that is roughly as performant as SQL and either less awkward linguistically or functionally superior. People do take up those sorts of projects, but they're pretty lonely unless some combination of talent, luck, and timing turns into rock star status (Linus wasn't the only one writing unix clones at the time).
  - dancek 10y ago
    > Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. PostgreSQL distributions come with support for PL/Python, PL/Tcl, PL/Perl and PL/pgSQL (a PostgreSQL specific PL/SQL clone). Other languages exist, but aren't included. See https://www.postgresql.org/docs/9.6/static/external-pl.html https://www.postgresql.org/docs/9.6/static/external-pl.html
    - __jal 10y ago
      Yes. And PL/Perl is perversely fun. I don't think I've ever seen other languages used in production, though[1]; I think folks tend to stick to PLSQL because of defaults. [1] I'm sure people do, but this discussion has an anecdata deficiency.

shubb 10y ago

In Enterprise Land a lot of people use ETL tooling as an alternative / in addition to writing SQL. Databases, data interchange formats, and the concepts they represent are mapped out in graphical tools like ER Studio. The SQL operations you do to load in data from various sources are done in graphical ETL tools like Microsoft SSIS, Talend etc let you draw diagrams which replace SQL. They are more properly a way of programming an engine that runs the SQL but also does things like monitor for incoming files, query webservices etc. Some also provide a technology neutral way to express transforms that can be run in SQL but also on Hadoop or whatever. That said, you sometimes need to go in and write the SQL for optimization. Enterprise Service Bus sit between multiple databases and multiple data sources and consumers, and the interfaces these provide replace SQL queries. Typically graphical tools or configuration files are used to configure the relationship between the endpoints they provide and databases. Even outside enterprise land, people rarely write SQL when defining and interfacing with databases. They use an ORM, and a lot of popular ORMs will create a database and provide a way of accessing it so that you never write SQL. Technologies like OData mean that applications can query data by proxy without needing to know the model it is stored as, and the mapping between the model OData presents and the database is typically implemented using an ORM. Cloud computing means people with very large databases increasingly want to spread the processing across many slower machines rather than spending the budget on one specialist data processing server with very fast IO. SQL is replaced by other languages in this case (although SQL like languages are making a bit of a comeback). Finally, in academia and certain very data datacentric industries, SPARQL and successors are still a thing. I think there is a big divide between enterprise development and what we learn in school / hackernews here. A lot of people outside the industry are not aware of these ecosystems of applications. I wouldn't recommend any of these over SQL in all cases. They are technologies that solve a problem and you should consider them if you have that problem.

marcosdumay 10y ago

Hum.. SQL is a "mathematically pure" language, like Lisp or, to a smaller extent, Haskell. Those tend to not attract criticism (mostly because they age slowly, but also because the most vocal language critics are biased towards them). SQL is also a standardized language. People tend to see those as something you should deal with, not something you should improve. Thus less criticism. It's also a language that is mostly embedded inside others. So any weakness is easy to compensate on the outer code. Besides this, it is a pretty well designed language.

mamcx 10y ago

The main trouble is that even if a better alternative is suggested, how convince the RDBMS developers to use it? Unfortunately, RDBMS are highly coupled. I remember when someday I ask if is possible to remove the SQL part of sqlite and substitute with my own, and get laughed! RDBMS are "expected" to be a black box enclosed in dark mystery.

saosebastiao 10y ago

I use it (way more than a lot) and I love it, but we could do better. 1) It really needs algebraic data types (and pattern matching!). I refuse to use a general purpose language that doesn't have them, and I always end up struggling to represent them using SQL. 2) Null, and the subsequent binary-but-maybe-ternary logic that it imposes on everything is a terrible hazard. It needs Option types. I guess this goes along with algebraic data types, but it is important enough to merit its own mention. 3) It needs more expressive constraints: foreign keys on partial unique indexes or views, declarable immutability, window constraints, etc. 4) It needs better graph representation and queryability. Anything remotely resembling a graph (or even trees!) ends up as a huge incomprehensible hack. Graphs are still relational, they're just ignored in favor of constructs that only tenably represent them. 5) It needs better ways to present output. Dumping everything as a table leaves the end user to their various hacky devices if what they really want is not an array of flat data structures. Users should be able to easily output data as a graph, tree, arbitrarily nested structs, associative maps, sets, arrays, and even scalars. 6) It needs extensive compile time checking of queries and statements, complete with errors and warnings and lints. As it stands right now, you only get syntax validation and checks for object existence. And although this is implementation level and not language level, SQL engines need to provide way better IDEs to compliment the better compilation. 7) It needs phantom keys for multi-column foreign keys against multi-column primary-keyed tables. As it stands right now, I have to lose the semantic benefits of a natural key in favor of a surrogate key in order to not duplicate a large number of columns in a 1-many table relationship. I should be able to use the many-columned natural foreign key, but invisible and behind the scenes map to a surrogate primary key.