6 ms·

This is my problem with SQL in general. The facilities for which SQL is an API are incredible, but that API has so many weird design choices behind it that it o

This is my problem with SQL in general. The facilities for which SQL is an API are incredible, but that API has so many weird design choices behind it that it often feels like I'm being forced to play some kind of perverse minigame in order to get the database to do what I want efficiently. And then, once I've managed to bend the query planner to my will, I often have to load it down with an absurd number comments to explain to future folk what exactly is happening – ostensibly the sort of thing that a declarative language is supposed to prevent.

amelius 8y ago
Perhaps someone should design a purely functional language that translates to SQL (?)
- virtualwhys 8y ago
  See Haskell's Esqueleto, Scala's Slick and Quill, C#/F#'s LINQ to SQL + Type Providers, probably others. In the case of Haskell and Scala the aforementioned query DSLs serve as CTE generators without the performance hit (albeit non-recursive). Being able to compose complex statements based on statically typed query snippets is really, really nice -- strips out the reams of repeated boilerplate that one inevitably is forced to write with string-y SQL.
  - amelius 8y ago
    Interesting. Could these be extended to support recursion using these new recursive CTEs? Or would they miss some fundamental mechanism, such as the ability to pass parameters down the recursive queries?
    - virtualwhys 8y ago
      > Could these be extended to support recursion using these new recursive CTEs? Sure, in the end they generate (prepared) sql statements; CTEs are just another statement with an expected result type. As for supporting parameters in a recursive CTE, I don't think providing runtime values would be an issue. WITH RECURSIVE $name (n) AS ( SELECT $init UNION ALL SELECT n + $incr FROM $name WHERE n + $incr <= $limit ) SELECT n FROM $name; Could use it right now with built-in string interpolator, but not sure how this would look in DSL form since recursive CTEs can support both real and temporary tables (i.e. the DSL has to know the structure of the temporary table in order to statically determine the result type of the query).
- tathougies 8y ago
  My haskell library beam[1] does just that. It'll catch scoping errors, aggregation errors, and more, through the type system. I'm implementing support for recursive queries right now! Also, for the record, SQL is a strongly typed, declarative language, even if it doesn't seem that way. [1] (http://tathougies.github.io/beam/ http://tathougies.github.io/beam/)
- mistercow 8y ago
  A side project I have stewing in my head is to try to hack something into a Postgres plugin to provide a nice AST format for producing specific query plans. I think this can be done, given that there are some old plugins that allow stuff like saving query plans, but I haven't looked much into the details yet. My thinking is that once you have that, you open the door for a lot of experimentation into query language alternatives that still maintain the power of SQL. And since you're cutting the query planner out of the equation (and moving it instead into a compilation step), you can hopefully take some of the frustrating black magic out of it. Key assumptions I'm working from here are: 1. Actual programmers (in contrast with SQL's original target audience) generally know exactly what data access patterns are appropriate for a particular task; they just don't want to write them from scratch and have to worry about stuff like locking and transactions at a fine grained level. 2. Being able to switch plans on the fly based on query input and data statistics is not a huge win in most real world scenarios, and isn't worth the unpredictability that it entails. 3. Most of the shallow pain of working with SQL comes from the fact that it was a strange paradigm to start with, and then had a bunch of features hacked on top of it. 4. Most of the deep pain of working with SQL comes from the fact that it abstracts too much, forcing you to reverse engineer the desired query plan through those abstraction layers. And like a lot of reverse engineering, the result is fragile and might change to something far less efficient in the future for inscrutable reasons like database upgrades or subtle changes in how the data is shaped. Pretty much any replacement can address 3. Ideas that really excite me address 1 and 4, and it seems to me that those are more likely to look procedural than purely functional. But like I said, the main thing is that having a low level target to compile to would allow us to try out different ideas and see what works.
  - rosser 8y ago
    Speaking as someone who has worked as a PostgreSQL DBA for over a decade, in environments ranging from hundreds of queries per second to hundreds of thousands, I can say with confidence that your assumptions do not map to my lived reality, or that of any other DBA with whom I've spoken about the challenges of working with developers who think the database is a black-box dump-truck they neither have to understand how to use well, nor care about the implications of using poorly. 1. "Actual programmers" tend to have net negative clue about which data access patterns are appropriate. It is, for example, staggeringly common for me to have to explain that a "table scan" is often more efficient than random IO ("index scan") — even on NAND media — if you're reading more than some threshold of the data in a table. If you (the general "you") understand data access patterns that poorly, no, you absolutely should not be dictating query plans. If you think "hav[ing] to worry about stuff like locking and transactions" is an imposition, you don't want me to sit in your interview. I will hard pass. 2. See above. 3. The relational algebra is not a "strange paradigm". It's very, very simple. "Strange features" like what? Ordering? Aggregation? Set intersection and exclusion? 4. I don't even understand this complaint.
- mtanski 8y ago
  http://www.try-alf.org/ http://www.try-alf.org/
ris 8y ago
My advice is to always use an ORM with SQL if you possibly can, or if not an ORM per se, at least an abstraction library like the sqlalchemy "core". It sounds ironic to say but "SQL" is the worst part of SQL. That shouldn't stop you loving SQL though.
- specialist 8y ago
  It’s fine to use an ORM until you need joins, order by, group by, subqueries, case clauses... You get the point.
  - spdionis 8y ago
    What ORM doesn't handle joins, order by and group by? Most should even support subqueries and case clauses.
    - specialist 8y ago
      Also, using HQL (or equiv) defeats the purpose of using an ORM. Just use SQL. --- Imagine you're troubleshooting your query, mapping. It's got some modest joins. So you capture the emitted SQL. Then you fuss with that SQL to make it something reasonable. Once that SQL is working, you then wrestle with the ORM to try to coerce it to re-emit your desired SQL. This is also called reverse engineering, pushing rope, fighting the 800lb angry gorilla sitting between you and your work. Eventually you'll figure out you should just use SQL, whatever dialect your backend supports.
      - ris 8y ago
        Um... how many ORMs do you have experience with? I suggest you look at the mentioned sqlalchemy core's ability to map more or less directly to SQL with reduced syntactic ugliness.
        spacemanmatt 8y ago
        There is a fruitful discussion that can be had on helpful ORMs vs harmful ORMs, and it usually ends up describing a category of marshalling helpers that don't quite form a regular ORM as the ideal situation. My take-home on the whole thing is, you need something that makes working with relations in your code easy but you don't need something attempting a ridiculous abstraction like "relations are objects."

amelius 8y ago

Perhaps someone should design a purely functional language that translates to SQL (?)

virtualwhys 8y ago
See Haskell's Esqueleto, Scala's Slick and Quill, C#/F#'s LINQ to SQL + Type Providers, probably others. In the case of Haskell and Scala the aforementioned query DSLs serve as CTE generators without the performance hit (albeit non-recursive). Being able to compose complex statements based on statically typed query snippets is really, really nice -- strips out the reams of repeated boilerplate that one inevitably is forced to write with string-y SQL.
- amelius 8y ago
  Interesting. Could these be extended to support recursion using these new recursive CTEs? Or would they miss some fundamental mechanism, such as the ability to pass parameters down the recursive queries?
  - virtualwhys 8y ago
    > Could these be extended to support recursion using these new recursive CTEs? Sure, in the end they generate (prepared) sql statements; CTEs are just another statement with an expected result type. As for supporting parameters in a recursive CTE, I don't think providing runtime values would be an issue. WITH RECURSIVE $name (n) AS ( SELECT $init UNION ALL SELECT n + $incr FROM $name WHERE n + $incr <= $limit ) SELECT n FROM $name; Could use it right now with built-in string interpolator, but not sure how this would look in DSL form since recursive CTEs can support both real and temporary tables (i.e. the DSL has to know the structure of the temporary table in order to statically determine the result type of the query).
tathougies 8y ago
My haskell library beam[1] does just that. It'll catch scoping errors, aggregation errors, and more, through the type system. I'm implementing support for recursive queries right now! Also, for the record, SQL is a strongly typed, declarative language, even if it doesn't seem that way. [1] (http://tathougies.github.io/beam/ http://tathougies.github.io/beam/)
mistercow 8y ago
A side project I have stewing in my head is to try to hack something into a Postgres plugin to provide a nice AST format for producing specific query plans. I think this can be done, given that there are some old plugins that allow stuff like saving query plans, but I haven't looked much into the details yet. My thinking is that once you have that, you open the door for a lot of experimentation into query language alternatives that still maintain the power of SQL. And since you're cutting the query planner out of the equation (and moving it instead into a compilation step), you can hopefully take some of the frustrating black magic out of it. Key assumptions I'm working from here are: 1. Actual programmers (in contrast with SQL's original target audience) generally know exactly what data access patterns are appropriate for a particular task; they just don't want to write them from scratch and have to worry about stuff like locking and transactions at a fine grained level. 2. Being able to switch plans on the fly based on query input and data statistics is not a huge win in most real world scenarios, and isn't worth the unpredictability that it entails. 3. Most of the shallow pain of working with SQL comes from the fact that it was a strange paradigm to start with, and then had a bunch of features hacked on top of it. 4. Most of the deep pain of working with SQL comes from the fact that it abstracts too much, forcing you to reverse engineer the desired query plan through those abstraction layers. And like a lot of reverse engineering, the result is fragile and might change to something far less efficient in the future for inscrutable reasons like database upgrades or subtle changes in how the data is shaped. Pretty much any replacement can address 3. Ideas that really excite me address 1 and 4, and it seems to me that those are more likely to look procedural than purely functional. But like I said, the main thing is that having a low level target to compile to would allow us to try out different ideas and see what works.
- rosser 8y ago
  Speaking as someone who has worked as a PostgreSQL DBA for over a decade, in environments ranging from hundreds of queries per second to hundreds of thousands, I can say with confidence that your assumptions do not map to my lived reality, or that of any other DBA with whom I've spoken about the challenges of working with developers who think the database is a black-box dump-truck they neither have to understand how to use well, nor care about the implications of using poorly. 1. "Actual programmers" tend to have net negative clue about which data access patterns are appropriate. It is, for example, staggeringly common for me to have to explain that a "table scan" is often more efficient than random IO ("index scan") — even on NAND media — if you're reading more than some threshold of the data in a table. If you (the general "you") understand data access patterns that poorly, no, you absolutely should not be dictating query plans. If you think "hav[ing] to worry about stuff like locking and transactions" is an imposition, you don't want me to sit in your interview. I will hard pass. 2. See above. 3. The relational algebra is not a "strange paradigm". It's very, very simple. "Strange features" like what? Ordering? Aggregation? Set intersection and exclusion? 4. I don't even understand this complaint.
mtanski 8y ago
http://www.try-alf.org/ http://www.try-alf.org/

ris 8y ago

My advice is to always use an ORM with SQL if you possibly can, or if not an ORM per se, at least an abstraction library like the sqlalchemy "core". It sounds ironic to say but "SQL" is the worst part of SQL. That shouldn't stop you loving SQL though.

specialist 8y ago
It’s fine to use an ORM until you need joins, order by, group by, subqueries, case clauses... You get the point.
- spdionis 8y ago
  What ORM doesn't handle joins, order by and group by? Most should even support subqueries and case clauses.
  - specialist 8y ago
    Also, using HQL (or equiv) defeats the purpose of using an ORM. Just use SQL. --- Imagine you're troubleshooting your query, mapping. It's got some modest joins. So you capture the emitted SQL. Then you fuss with that SQL to make it something reasonable. Once that SQL is working, you then wrestle with the ORM to try to coerce it to re-emit your desired SQL. This is also called reverse engineering, pushing rope, fighting the 800lb angry gorilla sitting between you and your work. Eventually you'll figure out you should just use SQL, whatever dialect your backend supports.
    - ris 8y ago
      Um... how many ORMs do you have experience with? I suggest you look at the mentioned sqlalchemy core's ability to map more or less directly to SQL with reduced syntactic ugliness.
      - spacemanmatt 8y ago
        There is a fruitful discussion that can be had on helpful ORMs vs harmful ORMs, and it usually ends up describing a category of marshalling helpers that don't quite form a regular ORM as the ideal situation. My take-home on the whole thing is, you need something that makes working with relations in your code easy but you don't need something attempting a ridiculous abstraction like "relations are objects."