7 ms·

There still is, but SQL is mostly used as a compiler target these days, with users using higher level languages (e.g. "ORMs") to hide the nastinesses. As such,

There still is, but SQL is mostly used as a compiler target these days, with users using higher level languages (e.g. "ORMs") to hide the nastinesses. As such, its flaws don't impact all that many people. Which is a shame as it needn't be that way. A well designed relational calculus could avoid these abstractions that come with their own problems.

Swizec 2y ago
> with users using higher level languages (e.g. "ORMs") to hide the nastinesses Maybe I’ve been doing it wrong my whole career because every project I’ve worked with the engineers eventually said ”Screw this leaky abstraction. Having to know SQL _and_ how the ORM works on top is too much, we’ll just put everything in an ORM.rawQuery(…)”
- magicalhippo 2y ago
  We prefer writing our own SELECTs so we have more predictable performance, also it makes debugging production issues easier. A poor ORM-generated query might be fast enough during testing but crashes and burns when it hits client's database. Not us, but a perfect example was the recent story discussed here[1] on optimizing an ORM-generated query to get 1200x speedup. [1]: https://news.ycombinator.com/item?id=40069583 https://news.ycombinator.com/item?id=40069583
- hn_throwaway_99 2y ago
  100%. I've come to believe that essentially every software engineer, if they write software long enough, comes to despise ORMs. As I always say "They make the easy stuff a little easier, and they make the harder stuff way harder". I have yet to see an ORM actually save me time in the long run; most of the time it just feels like I'm battling it. SQL definitely has its warts, but I don't feel like ORMs make it possible to ignore those warts at all. I'd love it if a better language like PRQL was accepted by DB engines and just compiled to SQL in the DB. Was a good blog post a couple years ago that I think highlights a lot of problems with ORMs and query builders: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
  - smaudet 2y ago
    I too think ORM was the wrong approach. I still enjoy SQLite - its not perfect, but every time I see it being used I get the warm and fuzzies. We should have just built better libraries around using/targeting one flavor (SQLite, e.g.), instead of wasting time with bad libraries that transformed one bit of bad code to another bit of bad code and ran it on a database engine that wasn't even doing it right... When you solve a problem, you solve it at its source, not five miles away with a teleprompter. If relational algebra is what we should all have been using, then relational algebra is what we should have been optimizing for, not overpriced SQL database engines. To shamelessly steal from another comment here: https://www.scattered-thoughts.net/writing/unexplanations-relational-algebra-is-math/ https://www.scattered-thoughts.net/writing/unexplanations-re... https://www.scattered-thoughts.net/writing/unexplanations-sql-is-syntactic-sugar-for-relational-algebra/ https://www.scattered-thoughts.net/writing/unexplanations-sq...
pulisse 2y ago
> SQL is mostly used as a compiler target these days, with users using higher level languages This is not remotely true of analysts and data engineers, who probably write most of the world's SQL.
- deleted 2y ago
  [deleted]
- megadal 2y ago
  Have you seen the queries an ORM generates? Pretty sure they have data engineers beat in query volume, and there's more web developers using ORMs than data engineers.
  - randomdata 2y ago
    But of the SQL queries written by people, those who write SQL queries write most of the world's SQL queries.
    - cies 2y ago
      I think pulisse meant "compared to SQL queries written by proxy (e.g. jooq, linq, ORMs, etc.) I'm not sure it's true.
vkazanov 2y ago
Unfortunately, this is wrong. Sql is everywhere in databases. I think it's horrible, it's an endless torture of the beautiful-beautiful relational algebra, but it is more popular than ever and there no way around it.
refset 2y ago
Relevant context & reading: > for most of the history of sql we did not know how to translate it to relational algebra, and now that we do know most databases still don't do it. https://www.scattered-thoughts.net/writing/unexplanations-sql-is-syntactic-sugar-for-relational-algebra/ https://www.scattered-thoughts.net/writing/unexplanations-sq... > None of this could be expressed in the original relational algebra, and once you add it all it's not obvious we should even still be calling this an algebra, let alone granting it any mathematical mystique. I'd settle for calling it the 'sql calculus'. Or 'the algebra formerly known as relational'. > It is still a reasonably good compiler IR though, and that's still the most useful way of thinking about it. https://www.scattered-thoughts.net/writing/unexplanations-relational-algebra-is-math/ https://www.scattered-thoughts.net/writing/unexplanations-re...
civilized 2y ago
I don't think a single one of these claims is true: 1. SQL is not mostly used as a compiler target these days. 2. ORMs do not hide SQL nastiness. 3. Relational calculus, however well-designed, does not manage to avoid the practical problems of data management.
- randomdata 2y ago
  Leaving the following claims that you think are true: 1. There is still a lot of criticism about SQL. 2. Users use higher level languages (e.g. "ORMs") [without involving SQL?] 3. Its flaws don't impact all that many people. 4. It is a shame [that SQL is mostly used as a compiler target] as it needn't be that way. 5. A well designed relational calculus could avoid these abstractions that come with their own problems. To be fair, it could be that you don't know what you think about those claims, but since, as the original link points out, SQL does not have a "don't know" representation...
  - smaudet 2y ago
    > 1. There is still a lot of criticism about SQL. And rightly so, while you can write whole programs inside it, the debugger/tooling/syntax for most if not all implementations is some mix of inferior/more arcane/legitimately worse. > 2. Users use higher level languages (e.g. "ORMs") [without involving SQL?] In certain fields (web dev in particular) with "Enterprise" development mindsets, this is often true. ORMs make it eas(y|ier) to hide your SQL. At least, until you screw something up and end up either rolling back (expensive) or writing various code snippets (often in SQL) to undo changes. So even in the most common case, no you end up writing SQL if you are doing anything non-trivial or making trivial mistakes. As an aside, this more or less lines up with my experience, any time there's an additional target (SQL, Assembly, IL, make files from cmake, visual studio projects from random build system, autotools), usually the higher level tool doesn't save me from having to debug the lower level one. It usually just gets in my way and I curse the person who was "making my life easier". Source code in a common language does an unusually good job at this, actually, its a miracle I don't usually need to bust out reversing tools or stare at processor assembly. But these are the popular, well-trodden-path tools, the fringe ones usually are not worth the trouble. ORMs are just barely worth it, IMO. > 3. Its flaws don't impact all that many people. Hard disagree - I posit most tools worth using are more complicated than the "easy" case, which is a dictionary - inputs and outputs. A "tool" usually has several dictionaries, or several relational tables. So after your third relation, it becomes very easy to miss-design queries (maybe the tooling has just got way better? But that's been my experience, at least). And then, if its affecting most tools, its affecting those people who use those tools. So I'd argue the effect is actually very common, if only underappreciated ("I upgraded my PC to be 10x faster and things are fast now!" - used to be a common refrain, that's less true each year). > 4. It is a shame [that SQL is mostly used as a compiler target] as it needn't be that way. Agreed. Although normally in ORM situations, you'll find the complex/difficult queries hard coded (with parameters of course), so it is used (as not just a compiler target?). > 5. A well designed relational calculus could avoid these abstractions that come with their own problems. Maybe? Despite complaining, I'm not a database guru, however if you've been paying attention to AI at all, graph databases seem to appear to be superior, in function and form. Relational databases are good enough for many things, but I don't think they are the form most natural for data, just what we as humans like to see as reports.
tpm 2y ago
It does not matter how well designed it would be, it would still remain a compile target for the folks of 'everything we write is in _one_language_'. You could expect a beginner Java developer to also write proper SQL, but we don't do that anymore, which leads us to suboptimal SQL written by ORMs. I don' really blame anyone, it's a natural development and can't be changed. People use tools they don't understand all the time.
- deleted 2y ago
  [deleted]
elevation 2y ago
Could you describe the features would you add to a data query language that would eliminate the need for an ORM?
- randomdata 2y ago
  To be clear, we're talking about what would more reasonably be called a query builder, but which for some reason has taken on the "ORM" monicker. Ever since duality views were invented, it's questionable how relevant ORMs (in the original sense) even are – maybe as a remaining stop gap for working with DB engines that haven't yet acquired such a feature. I'm not sure if "add" is the right framing, but I posit the reason people reach for query builders/ORMs is: 1. Because SQL is not reasonably composable. 2. Because SQL syntax is horrid. 3. Because SQL implementations are terribly inconsistent. Adding some layer of protection above the pitfalls Codd speaks to in this paper is an additional benefit, but I don't expect that is a driving motivator; still something to address. I'm probably forgetting other things, but something that tackles those issues would go a long way in reliving any desire to add another abstraction, with its own set of problems, on top.
dools 2y ago
Every time I've done a project using an ORM, I wound up either writing SQL directly or spending a long time trying to make the ORM do what I knew the SQL should do.
jandrewrogers 2y ago
I think you greatly overestimate how many people use ORMs instead of SQL. SQL usage is ubiquitous in my experience. That said, even SQL databases usually don't run SQL. They run a virtual machine against which SQL is compiled. A more enlightening question is why there are no alternative/better query languages compiled against the same target that SQL targets since that would be strictly additive.

Swizec 2y ago

> with users using higher level languages (e.g. "ORMs") to hide the nastinesses Maybe I’ve been doing it wrong my whole career because every project I’ve worked with the engineers eventually said ”Screw this leaky abstraction. Having to know SQL _and_ how the ORM works on top is too much, we’ll just put everything in an ORM.rawQuery(…)”

magicalhippo 2y ago
We prefer writing our own SELECTs so we have more predictable performance, also it makes debugging production issues easier. A poor ORM-generated query might be fast enough during testing but crashes and burns when it hits client's database. Not us, but a perfect example was the recent story discussed here[1] on optimizing an ORM-generated query to get 1200x speedup. [1]: https://news.ycombinator.com/item?id=40069583 https://news.ycombinator.com/item?id=40069583
hn_throwaway_99 2y ago
100%. I've come to believe that essentially every software engineer, if they write software long enough, comes to despise ORMs. As I always say "They make the easy stuff a little easier, and they make the harder stuff way harder". I have yet to see an ORM actually save me time in the long run; most of the time it just feels like I'm battling it. SQL definitely has its warts, but I don't feel like ORMs make it possible to ignore those warts at all. I'd love it if a better language like PRQL was accepted by DB engines and just compiled to SQL in the DB. Was a good blog post a couple years ago that I think highlights a lot of problems with ORMs and query builders: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
- smaudet 2y ago
  I too think ORM was the wrong approach. I still enjoy SQLite - its not perfect, but every time I see it being used I get the warm and fuzzies. We should have just built better libraries around using/targeting one flavor (SQLite, e.g.), instead of wasting time with bad libraries that transformed one bit of bad code to another bit of bad code and ran it on a database engine that wasn't even doing it right... When you solve a problem, you solve it at its source, not five miles away with a teleprompter. If relational algebra is what we should all have been using, then relational algebra is what we should have been optimizing for, not overpriced SQL database engines. To shamelessly steal from another comment here: https://www.scattered-thoughts.net/writing/unexplanations-relational-algebra-is-math/ https://www.scattered-thoughts.net/writing/unexplanations-re... https://www.scattered-thoughts.net/writing/unexplanations-sql-is-syntactic-sugar-for-relational-algebra/ https://www.scattered-thoughts.net/writing/unexplanations-sq...

pulisse 2y ago

> SQL is mostly used as a compiler target these days, with users using higher level languages This is not remotely true of analysts and data engineers, who probably write most of the world's SQL.

deleted 2y ago
[deleted]
megadal 2y ago
Have you seen the queries an ORM generates? Pretty sure they have data engineers beat in query volume, and there's more web developers using ORMs than data engineers.
- randomdata 2y ago
  But of the SQL queries written by people, those who write SQL queries write most of the world's SQL queries.
  - cies 2y ago
    I think pulisse meant "compared to SQL queries written by proxy (e.g. jooq, linq, ORMs, etc.) I'm not sure it's true.

vkazanov 2y ago

Unfortunately, this is wrong. Sql is everywhere in databases. I think it's horrible, it's an endless torture of the beautiful-beautiful relational algebra, but it is more popular than ever and there no way around it.

refset 2y ago

Relevant context & reading: > for most of the history of sql we did not know how to translate it to relational algebra, and now that we do know most databases still don't do it. https://www.scattered-thoughts.net/writing/unexplanations-sql-is-syntactic-sugar-for-relational-algebra/ https://www.scattered-thoughts.net/writing/unexplanations-sq... > None of this could be expressed in the original relational algebra, and once you add it all it's not obvious we should even still be calling this an algebra, let alone granting it any mathematical mystique. I'd settle for calling it the 'sql calculus'. Or 'the algebra formerly known as relational'. > It is still a reasonably good compiler IR though, and that's still the most useful way of thinking about it. https://www.scattered-thoughts.net/writing/unexplanations-relational-algebra-is-math/ https://www.scattered-thoughts.net/writing/unexplanations-re...

civilized 2y ago

I don't think a single one of these claims is true: 1. SQL is not mostly used as a compiler target these days. 2. ORMs do not hide SQL nastiness. 3. Relational calculus, however well-designed, does not manage to avoid the practical problems of data management.

randomdata 2y ago
Leaving the following claims that you think are true: 1. There is still a lot of criticism about SQL. 2. Users use higher level languages (e.g. "ORMs") [without involving SQL?] 3. Its flaws don't impact all that many people. 4. It is a shame [that SQL is mostly used as a compiler target] as it needn't be that way. 5. A well designed relational calculus could avoid these abstractions that come with their own problems. To be fair, it could be that you don't know what you think about those claims, but since, as the original link points out, SQL does not have a "don't know" representation...
- smaudet 2y ago
  > 1. There is still a lot of criticism about SQL. And rightly so, while you can write whole programs inside it, the debugger/tooling/syntax for most if not all implementations is some mix of inferior/more arcane/legitimately worse. > 2. Users use higher level languages (e.g. "ORMs") [without involving SQL?] In certain fields (web dev in particular) with "Enterprise" development mindsets, this is often true. ORMs make it eas(y|ier) to hide your SQL. At least, until you screw something up and end up either rolling back (expensive) or writing various code snippets (often in SQL) to undo changes. So even in the most common case, no you end up writing SQL if you are doing anything non-trivial or making trivial mistakes. As an aside, this more or less lines up with my experience, any time there's an additional target (SQL, Assembly, IL, make files from cmake, visual studio projects from random build system, autotools), usually the higher level tool doesn't save me from having to debug the lower level one. It usually just gets in my way and I curse the person who was "making my life easier". Source code in a common language does an unusually good job at this, actually, its a miracle I don't usually need to bust out reversing tools or stare at processor assembly. But these are the popular, well-trodden-path tools, the fringe ones usually are not worth the trouble. ORMs are just barely worth it, IMO. > 3. Its flaws don't impact all that many people. Hard disagree - I posit most tools worth using are more complicated than the "easy" case, which is a dictionary - inputs and outputs. A "tool" usually has several dictionaries, or several relational tables. So after your third relation, it becomes very easy to miss-design queries (maybe the tooling has just got way better? But that's been my experience, at least). And then, if its affecting most tools, its affecting those people who use those tools. So I'd argue the effect is actually very common, if only underappreciated ("I upgraded my PC to be 10x faster and things are fast now!" - used to be a common refrain, that's less true each year). > 4. It is a shame [that SQL is mostly used as a compiler target] as it needn't be that way. Agreed. Although normally in ORM situations, you'll find the complex/difficult queries hard coded (with parameters of course), so it is used (as not just a compiler target?). > 5. A well designed relational calculus could avoid these abstractions that come with their own problems. Maybe? Despite complaining, I'm not a database guru, however if you've been paying attention to AI at all, graph databases seem to appear to be superior, in function and form. Relational databases are good enough for many things, but I don't think they are the form most natural for data, just what we as humans like to see as reports.

tpm 2y ago

It does not matter how well designed it would be, it would still remain a compile target for the folks of 'everything we write is in _one_language_'. You could expect a beginner Java developer to also write proper SQL, but we don't do that anymore, which leads us to suboptimal SQL written by ORMs. I don' really blame anyone, it's a natural development and can't be changed. People use tools they don't understand all the time.

deleted 2y ago
[deleted]

elevation 2y ago

Could you describe the features would you add to a data query language that would eliminate the need for an ORM?

randomdata 2y ago
To be clear, we're talking about what would more reasonably be called a query builder, but which for some reason has taken on the "ORM" monicker. Ever since duality views were invented, it's questionable how relevant ORMs (in the original sense) even are – maybe as a remaining stop gap for working with DB engines that haven't yet acquired such a feature. I'm not sure if "add" is the right framing, but I posit the reason people reach for query builders/ORMs is: 1. Because SQL is not reasonably composable. 2. Because SQL syntax is horrid. 3. Because SQL implementations are terribly inconsistent. Adding some layer of protection above the pitfalls Codd speaks to in this paper is an additional benefit, but I don't expect that is a driving motivator; still something to address. I'm probably forgetting other things, but something that tackles those issues would go a long way in reliving any desire to add another abstraction, with its own set of problems, on top.

dools 2y ago

Every time I've done a project using an ORM, I wound up either writing SQL directly or spending a long time trying to make the ORM do what I knew the SQL should do.

jandrewrogers 2y ago

I think you greatly overestimate how many people use ORMs instead of SQL. SQL usage is ubiquitous in my experience. That said, even SQL databases usually don't run SQL. They run a virtual machine against which SQL is compiled. A more enlightening question is why there are no alternative/better query languages compiled against the same target that SQL targets since that would be strictly additive.