4 ms·

This is the umpteenth attempt at replacing SQL. Just like all previous attempts, it may well address some weaknesses of SQL, however, it introduces a whole new

This is the umpteenth attempt at replacing SQL. Just like all previous attempts, it may well address some weaknesses of SQL, however, it introduces a whole new range of issues. SQL has been around so long as it mostly works.

carlineng 4y ago
I think Malloy is meaningfully different from previous attempts (e.g., PRQL), and describe why in the post, namely the inclusion of a semantic layer as part of the language. Take a look at the post, and would love to hear if you agree or not.
- oxfordmale 4y ago
  How is this better than the SQL equivalent? How can I break down this query and run parts of it for debugging purposes? query: sessionize is { group_by: flight_date is dep_time.day group_by: carrier aggregate: daily_flight_count is flight_count nest: per_plane_data is { top: 20 group_by: tail_num aggregate: plane_flight_count is flight_count nest: flight_legs is { order_by: 2 group_by: [ tail_num dep_minute is dep_time.minute origin_code dest_code is destination_code dep_delay arr_delay ] } } }
  - anon84873628 4y ago
    That's an example with lots of model definitions inline. The idea is that you would define various components separately so they can be reused (and to your point, broken down and debugged separately). Malloy brings the modularization and reusability that is missing from SQL itself.
    - oxfordmale 4y ago
      It is the first thing I try to teach CS graduates: DRY ( re-usability) is not applicable in the SQL world. Trying to re-use code, as proposed in Malloy generally results in poor performing queries. Malloy also seems needlessly complex compared to highly successful tools like DBT. One other reason that DBT is successful is the low threshold to migrate your existing codebase. It is larger than Typescript Vs JavaScript, however, it can generalky be done in a week or two.
dominotw 4y ago
It only works if you are just running queries in console. Any serious work with sql requires all sorts of weird stuff built around it like dbt, cubejs[1]. 1. https://cube.dev/docs/ https://cube.dev/docs/
- munk-a 4y ago
  I don't really think that's true - we write quite a lot of SQL in-house and we have no issues doing it safely. There are some tools we rely on to make our lives easier but these mostly revolve around making batch operations easier to express (like IN(:array) as opposed to having to glue some string joining logic relying on an array count into every individual query). SQL definitely isn't the cleanest thing ever and I have a number of improvements I'd personally really appreciate (changing statement order, trailing commas, better aggregate and window modifier definitions) but doing Serious SQL is definitely an accomplishable thing - even weird statistical bucketing and aggregation that produces a query that's... 291 lines long.
  - dominotw 4y ago
    what do you make of things like dbt which is awkward mix of python templating and sql mixed into each other.
    - munk-a 4y ago
      I write highly complex SQL for a living so I don't think I'm the best person to comment on it - I have found that pretty much anything you want to do can be done with pure SQL and the resultant mass is usually going to yield much better performance over mixed solutions... but I haven't used dbt so my comments are more directed at traditional mixed solutions (like sequentially submitted queries being stitched together in C++/PHP or pruning and joining multiple ORM delivered results into a full in memory data set). The way I usually like to write mixed SQL is preparation, execution, cleanup - have a blob of complex logic to preprocess the request into the appropriate SQL recipe - execute said recipe - then go back to imperative programming land to apply any different sourced joins (i.e. combine a DB query result with something coming back from OpenSearch, a memcached query or some other non-relational database source) apply any complex customization and value cleanup (like pulling data out of a JSONB blob and actually sending it as a plain array to the consumer) and then shuffling it off to whoever requested it. Again, I don't feel comfortable commenting on any tool I haven't personally used and I hope that was helpful - if you have any other questions I'm happy to try and answer.
BeefWellington 4y ago
I really do marvel at the number of times this has come up on HN over the past half decade I've been lurking/participating. These kinds of approaches always have one very large flaw: The problem areas of SQL are well understood and there is lots of community support available. Why should this be traded away for a different syntax that has its own yet-undiscovered problem areas? What efficiencies are we actually losing by using SQL?
default-kramer 4y ago
None of the Javascript alternatives (except maybe CoffeeScript) really gained any traction until Typescript did. Just because something is hard doesn't mean that it shouldn't be attempted, and just because something is unlikely to succeed doesn't mean that it is a lost cause. To look at a language like SQL and say "meh, that's good enough" is crazy to me. I estimate that a widely-adopted good SQL alternative could have saved me over 1000 hours over the last 10 years.
- oxfordmale 4y ago
  TypeScript is backward compatible with JavaScript. It means there is pretty much zero investment required to get started with TypeScript. Refactoring existing SQL codebases to Malfoy would have cost me more than 1000 hours over the last ten years. The problem with SQL alternatives is that they tend to lack features once you get beyond the shiny examples on their documentation pages.

carlineng 4y ago

I think Malloy is meaningfully different from previous attempts (e.g., PRQL), and describe why in the post, namely the inclusion of a semantic layer as part of the language. Take a look at the post, and would love to hear if you agree or not.

oxfordmale 4y ago
How is this better than the SQL equivalent? How can I break down this query and run parts of it for debugging purposes? query: sessionize is { group_by: flight_date is dep_time.day group_by: carrier aggregate: daily_flight_count is flight_count nest: per_plane_data is { top: 20 group_by: tail_num aggregate: plane_flight_count is flight_count nest: flight_legs is { order_by: 2 group_by: [ tail_num dep_minute is dep_time.minute origin_code dest_code is destination_code dep_delay arr_delay ] } } }
- anon84873628 4y ago
  That's an example with lots of model definitions inline. The idea is that you would define various components separately so they can be reused (and to your point, broken down and debugged separately). Malloy brings the modularization and reusability that is missing from SQL itself.
  - oxfordmale 4y ago
    It is the first thing I try to teach CS graduates: DRY ( re-usability) is not applicable in the SQL world. Trying to re-use code, as proposed in Malloy generally results in poor performing queries. Malloy also seems needlessly complex compared to highly successful tools like DBT. One other reason that DBT is successful is the low threshold to migrate your existing codebase. It is larger than Typescript Vs JavaScript, however, it can generalky be done in a week or two.

dominotw 4y ago

It only works if you are just running queries in console. Any serious work with sql requires all sorts of weird stuff built around it like dbt, cubejs[1]. 1. https://cube.dev/docs/ https://cube.dev/docs/

munk-a 4y ago
I don't really think that's true - we write quite a lot of SQL in-house and we have no issues doing it safely. There are some tools we rely on to make our lives easier but these mostly revolve around making batch operations easier to express (like IN(:array) as opposed to having to glue some string joining logic relying on an array count into every individual query). SQL definitely isn't the cleanest thing ever and I have a number of improvements I'd personally really appreciate (changing statement order, trailing commas, better aggregate and window modifier definitions) but doing Serious SQL is definitely an accomplishable thing - even weird statistical bucketing and aggregation that produces a query that's... 291 lines long.
- dominotw 4y ago
  what do you make of things like dbt which is awkward mix of python templating and sql mixed into each other.
  - munk-a 4y ago
    I write highly complex SQL for a living so I don't think I'm the best person to comment on it - I have found that pretty much anything you want to do can be done with pure SQL and the resultant mass is usually going to yield much better performance over mixed solutions... but I haven't used dbt so my comments are more directed at traditional mixed solutions (like sequentially submitted queries being stitched together in C++/PHP or pruning and joining multiple ORM delivered results into a full in memory data set). The way I usually like to write mixed SQL is preparation, execution, cleanup - have a blob of complex logic to preprocess the request into the appropriate SQL recipe - execute said recipe - then go back to imperative programming land to apply any different sourced joins (i.e. combine a DB query result with something coming back from OpenSearch, a memcached query or some other non-relational database source) apply any complex customization and value cleanup (like pulling data out of a JSONB blob and actually sending it as a plain array to the consumer) and then shuffling it off to whoever requested it. Again, I don't feel comfortable commenting on any tool I haven't personally used and I hope that was helpful - if you have any other questions I'm happy to try and answer.

BeefWellington 4y ago

I really do marvel at the number of times this has come up on HN over the past half decade I've been lurking/participating. These kinds of approaches always have one very large flaw: The problem areas of SQL are well understood and there is lots of community support available. Why should this be traded away for a different syntax that has its own yet-undiscovered problem areas? What efficiencies are we actually losing by using SQL?

default-kramer 4y ago

None of the Javascript alternatives (except maybe CoffeeScript) really gained any traction until Typescript did. Just because something is hard doesn't mean that it shouldn't be attempted, and just because something is unlikely to succeed doesn't mean that it is a lost cause. To look at a language like SQL and say "meh, that's good enough" is crazy to me. I estimate that a widely-adopted good SQL alternative could have saved me over 1000 hours over the last 10 years.

oxfordmale 4y ago
TypeScript is backward compatible with JavaScript. It means there is pretty much zero investment required to get started with TypeScript. Refactoring existing SQL codebases to Malfoy would have cost me more than 1000 hours over the last ten years. The problem with SQL alternatives is that they tend to lack features once you get beyond the shiny examples on their documentation pages.