20 ms·

Disclaimer: I'm a core contributor to PRQL [1] and post about it a lot on HN. Apologies for jumping in on other people's threads, but for people interested in t

by snthpy 3y ago

Disclaimer: I'm a core contributor to PRQL [1] and post about it a lot on HN. Apologies for jumping in on other people's threads, but for people interested in the headline, PRQL might be of interest. At PRQL[1] we believe that SQL is a combination of two things: 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. When people say that SQL will never die, they are usually thinking about Relational Algebra because SQL has been used interchangeably with that. With PRQL we agree that Relational Algebra is fundamental to thinking about data and we intend to keep that. However we've learned a lot about programming languages in the last 50 years and so PRQL is a revamp of SQL that brings the composability of functional languages and modern ergonomics to data transformations in order to improve the DX and UX of data scientists, data analysts and analytics engineers. PRQL is simply a compiler that produces SQL so you can use it with whatever database you are currently using. It's completely open source with zero commercial associations and is deeply committed to staying that way forever. 1: https://prql-lang.org/ https://prql-lang.org/

seanhunter 3y ago
SQL is based on relational calculus rather than relational algebra, which is why it's declarative. Relational algebra is built on fundamental relational operators (select, project, filter, product etc) which are imperatively applied. You can find out more about it here https://techdifferences.com/difference-between-relational-algebra-and-relational-calculus.html https://techdifferences.com/difference-between-relational-al...
danielvaughn 3y ago
That first PRQL code sample is wonderfully readable.
- Timon3 3y ago
  It is! One suggestion to make it even more convincing: I'd love to see the SQL statement it compiles to.
  - leeuw01 3y ago
    This +1. I've been burned before when using ORMs which translate simple-looking queries to terribly inneficient SQL statements underwater.
    - danielvaughn 3y ago
      Yeah I’m a web dev, and recently I found out the most popular JS ORM doesn’t produce joins. It’ll just execute multiple queries in sequence. I don’t know how common that is in the ORM landscape but for me that’s a deal breaker.
      - smaddox 3y ago
        Common enough to have a name: the N+1 query problem.
        Izkata 3y ago
        1+N is clearer I think, and what I remember seeing in the past - it matches what's actually happening with this problem. First time I saw N+1 sometime around a year ago, I had no idea it referred to the same thing and thought it was something different. N+1 looks like an issue with aggregation after a parallel run, something I've encountered with celery tasks before.
      - setr 3y ago
        That’s just a direct result of lazy loading — if you don’t grab the related objects in the initial query, then there’s no joins to be had. Most ORMs do lazy loading by default, but also have a way of doing eager loading — either requiring the nested object to always be loaded as well, or dropping down to some pseudo-sql. In c#/EFCore, I always prefer to avoid lazy loading and just write LINQ, and just use the ORM to map the resultset back to objects
  - Timon3 3y ago
    For reference, this is the output: WITH table_1 AS ( SELECT customer_id, total, total - 0.8 AS _expr_0 FROM invoices WHERE invoice_date >= DATE '1970-01-16' ), table_0 AS ( SELECT COALESCE(SUM(_expr_0), 0) AS sum_income, customer_id FROM table_1 WHERE _expr_0 > 1 GROUP BY customer_id ORDER BY sum_income DESC LIMIT 10 ) SELECT c.customer_id, CONCAT(c.last_name, ', ', c.first_name) AS name, table_0.sum_income, version() AS db_version FROM table_0 JOIN customers AS c ON table_0.customer_id = c.customer_id ORDER BY table_0.sum_income DESC
- snthpy 3y ago
  Awesome! That's what we're hoping for. Great to hear that you find it wonderfully readable!
frogulis 3y ago
Pretty cool, your description got my click. I particularly enjoy that a filter is a filter before and after grouping. One thing, the "showcase" section is not usable for me on mobile. The code box does not fit on the screen horizontally and I can't scroll right to see the remainder of it.
- snthpy 3y ago
  Thank you for the feedback. I'll let the team know. We definitely want people on all devices to be able to learn about the project.
continuational 3y ago
In that first example, is the last line superfluous? It doesn't seem to be used.
iamcreasy 3y ago
How would you compare prql with dbt?
- anon84873628 3y ago
  dbt is just an orchestration tool. It uses SQL because that's what you need to pass to the target database. There is a python plugin if you prefer to use that for your models instead. Theoretically dbt could wrap any language your target system accepts. The actual configuration of the dbt runtime itself is done with yaml files.
- snthpy 3y ago
  dbt integration was one of our major goals early on but we found that the interaction wasn't as straightforward as we had hoped. There is an open PR in the dbt repo: https://github.com/dbt-labs/dbt-core/pull/5982#issuecomment-1710337532 https://github.com/dbt-labs/dbt-core/pull/5982#issuecomment-... I have some ideas about future directions in this space where I believe PRQL could really shine. I will only be able to write those down in a couple of hours. I think this could be a really exciting direction for the project to grow into if anyone would like to collaborate and contribute!
hiAndrewQuinn 3y ago
This is tremendous. I'm curious to know if a CLI `prqlite3` exists which wraps around the `sqlite3` CLI many of us know and love.
- snthpy 3y ago
  Thank you. The CLI usability was one of the aims behind [prql-query (pq)](https://github.com/prql/prql-query/ https://github.com/prql/prql-query/). sqlite integration was on the roadmap but unfortunately that project has been largely unmaintained by me for the past 6 months. (This is just referring to prql-query and not PRQL which is under very active development.) I'm working on a new project which will do exactly this (and a lot more!) which I hope to release next week. I'll drop the link here when that's ready.
jug 3y ago
At least superficially this looks a lot like C# LINQ to me in terms of structure and database independence (as for EF Core + LINQ). It’s in my top 3 features of that language. https://www.tutorialsteacher.com/linq/sample-linq-queries https://www.tutorialsteacher.com/linq/sample-linq-queries Edit: Shortened to link due to formatting issues
- ilyt 3y ago
  > Iterating through that would use lazy evaluation by default, returning row by row from the db as needed. That's... not an advantage in most cases
  - jug 3y ago
    Well, it’s an option when and IIRC a default. Here’s a discussion https://stackoverflow.com/questions/31366236/lazy-loading-vs-eager-loading https://stackoverflow.com/questions/31366236/lazy-loading-vs...
- snthpy 3y ago
  LINQ is definitely one of the big influences along with many other great projects that form the prior art in this space. See this section in our FAQ: https://prql-lang.org/faq/#:~:text=Something%20here%20reminds%20me%20of%20another%20project%2C%20did%20you%20take%20the%20idea%20from%20them%3F https://prql-lang.org/faq/#:~:text=Something%20here%20remind...
- intrasight 3y ago
  Also reminds me of the Power Query M language - semantically anyway
- biglyburrito 3y ago
  Yep, it reads very easily like C# LINQ method & query syntax.
laerus 3y ago
hey, is compile time verification of queries supported for PRQL in Rust?
- snthpy 3y ago
  Not yet, but looking at what sqlx does, I think we should be able to do something similar. It's been a small team of core contributors so far but in the last three months we've seen more people making their first PR and then going on to contribute more over time so the momentum is growing. We'd definitely be open to contributions in this space.
spion 3y ago
How is the language server support?
- snthpy 3y ago
  We do have grammars for many editors. I'm not sure about LSP off the top of my head but you should find something in the docs under "Integrations". There is also a VSCode extension: https://marketplace.visualstudio.com/items?itemName=prql-lang.prql-vscode https://marketplace.visualstudio.com/items?itemName=prql-lan...
- maximilianroos 3y ago
  [PRQL dev here] We don't have LSP support yet, but it's on the Roadmap. We've designed the language to be very LSP-friendly — one of the benefits of starting with `from` and pipelining each function.
roenxi 3y ago
Great project, wish you all the best. Anything to try and unseat SQL from common use (we can all wish for the day we run PostgrespostSQL in production). At the moment the project is probably going to lose people because it isn't obvious how to get started - many SQL beginners don't know what a compiler is and will get confused by the docs. For the sake of their sanity, it'd be worth considering putting an example of using the compiler on a local text file somewhere prominent on that site. That way beginners can go in, write some PSQL, compile it and use it against real SQL databases. Or if not the compiler, make it clear how beginners are supposed to engage with this. There is a big need out there for something dplyr-like that works. There are a dizzying array of options and that isn't going to help some good people who need a bit of handholding.
- snthpy 3y ago
  Thank you for your feedback. That's really valuable! We have the [PRQL Playground](https://prql-lang.org/playground/ https://prql-lang.org/playground/) exactly for that purpose. We'll try and make it more prominent on the front page. I've also felt that we should have a "Getting started" page and will push that as a priority.
  - roenxi 3y ago
    Ah, I see. Maybe you're just losing beginners who NoScript then. I hadn't figured out what the Playground was supposed to do but it turned out there was an extra disabled script. Nevermind, thanks.
snthpy 3y ago
For those interested who want to learn more, we have a number of presentations coming up at conferences on three continents: - [QCon SF, October 2nd, San Francisco, USA: ](https://qconsf.com/presentation/oct2023/prql-simple-powerful-pipelined-sql-replacement https://qconsf.com/presentation/oct2023/prql-simple-powerful...) - [PyconZA, October 5th, Durban, South Africa: ](https://za.pycon.org/ https://za.pycon.org/) - [Community over Code (ApacheCon), October 9th, Halifax, Canada: ](https://communityovercode.org/schedule-list/#FT005 https://communityovercode.org/schedule-list/#FT005) - [data2day, October 12th, Karlsruhe, Germany: ](https://www.data2day.de/veranstaltung-21353-0-prql-a-modern-language-for-data-transformation.html https://www.data2day.de/veranstaltung-21353-0-prql-a-modern-...)
ledauphin 3y ago
I've been excited in the abstract about PRQL for quite a while. But something FQL seems to have a much better handle on is the value of document-orientedness, or what you might alternatively call "gradual schematization". This problem has been solved (if not beautifully, at least acceptably) by modern SQL databases that support a JSON storage format and associated "secondary query language". I know PRQL has had an open issue on this subject for a while. I just want to note that I think this is truly one of the critical "missing pieces" to PRQL, without which it may never be able to break out into common usage.
- anon84873628 3y ago
  FQL is interesting because it focuses on transactional systems and eliminating the need for an ORM in applications. I feel many of the SQL replacement projects like PRQL and Malloy instead come from the analytics side of the house, which doesn't really help application developers at all. (But does raise the question, how do I do analytics in Fauna? Do I ETL to a traditional warehouse system?)
  - panda888888 3y ago
    That's a good question. If you need to ETL to a traditional data warehouse in order to do analytics, wouldn't you need an ORM, which is exactly what they're trying to avoid? (Also note that it's usually ELT instead of ETL these days, like if you're using Snowflake) Or is Fauna a hybrid transactional/analytical database so you can do analytics in it using FQL? (Maybe the long-awaited possibility of true hybrid databases is real?!) But then you'd need to train all of your business intelligence analysts on FQL, which would be a drawback.
ttfkam 3y ago
The syntax comparison section will likely sour a lot of viewers who already know SQL. You try too hard to highlight how easy and terse PRQL is by putting comma-separated items on their own lines but in SQL you put each item on separate lines. It may be typical of many SQL users and formatters, but it leaves a poor taste in the mouth that you aren't interested in an actual comparison but in marketing. For those who already know SQL, the real question is: will it make my queries faster? Putting the FROM first isn't sufficiently compelling on its own. Having a processing pipeline, though marginally more elegant to look at, doesn't actually improve upon CTEs. When you say you can use it with any database, how do you handle functions, stored procedures, jsonpath, and the massive differences in functionality between Oracle, MS SQL Server, Postgres, DB2, MySQL, MariaDB, H2, SQLite, etc.? Lowest common denominator? After 49 years of SQL, more than syntax has to change; you need an engine that supports this natively and can actually improve planner behavior over existing engines. I will grant that if you are limiting your target audience to primarily analytics, it's probably sufficient. The marketing of PRQL doesn't always appear to do this however.
- anon84873628 3y ago
  You might find Malloy interesting as it makes a greater departure from SQL syntax. Queries are first class objects which can be nested within each other in order to do trellising. It still compiles to SQL because that is the only language accepted by DBMSs today; however it will automatically write symmetric aggregate calculations and do those nestings that are hard for a human to write. https://www.malloydata.dev/ https://www.malloydata.dev/
  - ako 3y ago
    How are views not first class query objects?
    - ttfkam 3y ago
      Yup. "SQL doesn't have first-class query objects and isn't composable." Uh… views, set-returning functions, temporary tables, CTEs… "No, not like that! Something not declarative and set-oriented!" SQL is declarative and set-oriented. 3rd generation language models for data were tried and discarded for good reason decades ago. "But… but… I don't like set-oriented models! They make me feel dumb. I'm not dumb, so SQL must be dumb. Not meeeeeeeee!!!"
raverbashing 3y ago
> we believe that SQL is a combination of two things: > 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. Your belief is as real as my belief that it rains too much in London ;) (that is, it is correct) But why people have such hold on to such a quirky syntax beats me
psacawa 3y ago
Is there any intention of eventually supporting DML or DDL statements? That's when the COBOL-like nature of SQL syntax is most frustrating. For example, in order to run "ALTER COLUMN ..." I have to parse a ridiculous BNF like this[0] almost every time. I'll never remember it. Usually, the error is a gotcha built into the language syntax (e.g. forgot the keyword "TO"). [0] https://www.postgresql.org/docs/13/sql-altertable.html https://www.postgresql.org/docs/13/sql-altertable.html
lolinder 3y ago
SQL will never die for the same reason that JavaScript will never die: because it's built in to all major database engines. In both cases, any other language will be starting as a second class citizen that has to compile to SQL/JS. During this phase of a new language's lifetime, it is either a surface-level syntactic change (a la Coffeescript) that provides no objective improvement, or it has to compile its simple semantic structures into opaque SQL/JS structures that will be off the beaten path and therefore not highly optimized by the runtime. Neither will reach sufficient adoption to become a first-class citizen in a major existing platform. TypeScript succeeded where others failed because it provided much-needed static analysis while keeping the changes minimal enough that it's completely obvious what the runtime code will look like, so there are no unexpected performance gotchas. SQL, on the other hand, doesn't really need a TypeScript because SQL is highly statically analyzable by nature. It's not that I don't believe we could do with an improvement on SQL, but I really don't see a realistic path forward for a replacement.
- anon84873628 3y ago
  Well, Malloy is developed within Google by the founder of Looker, so there is a chance it could be natively integrated into BigQuery. At that point you have a next gen SQL replacement available on one of the most widely used analytics and transformation engines.
- paulddraper 3y ago
  While I mostly agree, there is a bit of Stockholms syndrome. A lot of people don't know what they even could be missing. For example, there is no succinct way of writing an antijoin in SQL . The MERGE command has only been implemented by some engines due to (IIRC) concurrency concerns/ambiguities. ANSI SQL JSON operations have improved but are still clunky. Boolean NULL and IN is a clusterf of footguns. Etc.
  - lolinder 3y ago
    Oh, I agree! SQL is far from optimal, as is JS. My thoughts are more about the feasibility of a replacement than the need for one. US electrical outlets are also highly flawed, but we're never going to replace them either. Standards, once entrenched, are nearly impossible to uproot.
deleted 3y ago
[deleted]
deburo 3y ago
That looks awesome. Does it support directly querying against databases (PostgreSQL, SQL Server, ...)? ie. is there a "Run" command in vscode that takes care of compiling & running the compiled sql?

by snthpy 3y ago

seanhunter 3y ago

SQL is based on relational calculus rather than relational algebra, which is why it's declarative. Relational algebra is built on fundamental relational operators (select, project, filter, product etc) which are imperatively applied. You can find out more about it here https://techdifferences.com/difference-between-relational-algebra-and-relational-calculus.html https://techdifferences.com/difference-between-relational-al...

danielvaughn 3y ago

That first PRQL code sample is wonderfully readable.

Timon3 3y ago
It is! One suggestion to make it even more convincing: I'd love to see the SQL statement it compiles to.
- leeuw01 3y ago
  This +1. I've been burned before when using ORMs which translate simple-looking queries to terribly inneficient SQL statements underwater.
  - danielvaughn 3y ago
    Yeah I’m a web dev, and recently I found out the most popular JS ORM doesn’t produce joins. It’ll just execute multiple queries in sequence. I don’t know how common that is in the ORM landscape but for me that’s a deal breaker.
    - smaddox 3y ago
      Common enough to have a name: the N+1 query problem.
      - Izkata 3y ago
        1+N is clearer I think, and what I remember seeing in the past - it matches what's actually happening with this problem. First time I saw N+1 sometime around a year ago, I had no idea it referred to the same thing and thought it was something different. N+1 looks like an issue with aggregation after a parallel run, something I've encountered with celery tasks before.
    - setr 3y ago
      That’s just a direct result of lazy loading — if you don’t grab the related objects in the initial query, then there’s no joins to be had. Most ORMs do lazy loading by default, but also have a way of doing eager loading — either requiring the nested object to always be loaded as well, or dropping down to some pseudo-sql. In c#/EFCore, I always prefer to avoid lazy loading and just write LINQ, and just use the ORM to map the resultset back to objects
- Timon3 3y ago
  For reference, this is the output: WITH table_1 AS ( SELECT customer_id, total, total - 0.8 AS _expr_0 FROM invoices WHERE invoice_date >= DATE '1970-01-16' ), table_0 AS ( SELECT COALESCE(SUM(_expr_0), 0) AS sum_income, customer_id FROM table_1 WHERE _expr_0 > 1 GROUP BY customer_id ORDER BY sum_income DESC LIMIT 10 ) SELECT c.customer_id, CONCAT(c.last_name, ', ', c.first_name) AS name, table_0.sum_income, version() AS db_version FROM table_0 JOIN customers AS c ON table_0.customer_id = c.customer_id ORDER BY table_0.sum_income DESC
snthpy 3y ago
Awesome! That's what we're hoping for. Great to hear that you find it wonderfully readable!

frogulis 3y ago

Pretty cool, your description got my click. I particularly enjoy that a filter is a filter before and after grouping. One thing, the "showcase" section is not usable for me on mobile. The code box does not fit on the screen horizontally and I can't scroll right to see the remainder of it.

snthpy 3y ago
Thank you for the feedback. I'll let the team know. We definitely want people on all devices to be able to learn about the project.

continuational 3y ago

In that first example, is the last line superfluous? It doesn't seem to be used.

iamcreasy 3y ago

How would you compare prql with dbt?

anon84873628 3y ago
dbt is just an orchestration tool. It uses SQL because that's what you need to pass to the target database. There is a python plugin if you prefer to use that for your models instead. Theoretically dbt could wrap any language your target system accepts. The actual configuration of the dbt runtime itself is done with yaml files.
snthpy 3y ago
dbt integration was one of our major goals early on but we found that the interaction wasn't as straightforward as we had hoped. There is an open PR in the dbt repo: https://github.com/dbt-labs/dbt-core/pull/5982#issuecomment-1710337532 https://github.com/dbt-labs/dbt-core/pull/5982#issuecomment-... I have some ideas about future directions in this space where I believe PRQL could really shine. I will only be able to write those down in a couple of hours. I think this could be a really exciting direction for the project to grow into if anyone would like to collaborate and contribute!

hiAndrewQuinn 3y ago

This is tremendous. I'm curious to know if a CLI `prqlite3` exists which wraps around the `sqlite3` CLI many of us know and love.

snthpy 3y ago
Thank you. The CLI usability was one of the aims behind [prql-query (pq)](https://github.com/prql/prql-query/ https://github.com/prql/prql-query/). sqlite integration was on the roadmap but unfortunately that project has been largely unmaintained by me for the past 6 months. (This is just referring to prql-query and not PRQL which is under very active development.) I'm working on a new project which will do exactly this (and a lot more!) which I hope to release next week. I'll drop the link here when that's ready.

jug 3y ago

At least superficially this looks a lot like C# LINQ to me in terms of structure and database independence (as for EF Core + LINQ). It’s in my top 3 features of that language. https://www.tutorialsteacher.com/linq/sample-linq-queries https://www.tutorialsteacher.com/linq/sample-linq-queries Edit: Shortened to link due to formatting issues

ilyt 3y ago
> Iterating through that would use lazy evaluation by default, returning row by row from the db as needed. That's... not an advantage in most cases
- jug 3y ago
  Well, it’s an option when and IIRC a default. Here’s a discussion https://stackoverflow.com/questions/31366236/lazy-loading-vs-eager-loading https://stackoverflow.com/questions/31366236/lazy-loading-vs...
snthpy 3y ago
LINQ is definitely one of the big influences along with many other great projects that form the prior art in this space. See this section in our FAQ: https://prql-lang.org/faq/#:~:text=Something%20here%20reminds%20me%20of%20another%20project%2C%20did%20you%20take%20the%20idea%20from%20them%3F https://prql-lang.org/faq/#:~:text=Something%20here%20remind...
intrasight 3y ago
Also reminds me of the Power Query M language - semantically anyway
biglyburrito 3y ago
Yep, it reads very easily like C# LINQ method & query syntax.

laerus 3y ago

hey, is compile time verification of queries supported for PRQL in Rust?

snthpy 3y ago
Not yet, but looking at what sqlx does, I think we should be able to do something similar. It's been a small team of core contributors so far but in the last three months we've seen more people making their first PR and then going on to contribute more over time so the momentum is growing. We'd definitely be open to contributions in this space.

spion 3y ago

How is the language server support?

snthpy 3y ago
We do have grammars for many editors. I'm not sure about LSP off the top of my head but you should find something in the docs under "Integrations". There is also a VSCode extension: https://marketplace.visualstudio.com/items?itemName=prql-lang.prql-vscode https://marketplace.visualstudio.com/items?itemName=prql-lan...
maximilianroos 3y ago
[PRQL dev here] We don't have LSP support yet, but it's on the Roadmap. We've designed the language to be very LSP-friendly — one of the benefits of starting with `from` and pipelining each function.

roenxi 3y ago

Great project, wish you all the best. Anything to try and unseat SQL from common use (we can all wish for the day we run PostgrespostSQL in production). At the moment the project is probably going to lose people because it isn't obvious how to get started - many SQL beginners don't know what a compiler is and will get confused by the docs. For the sake of their sanity, it'd be worth considering putting an example of using the compiler on a local text file somewhere prominent on that site. That way beginners can go in, write some PSQL, compile it and use it against real SQL databases. Or if not the compiler, make it clear how beginners are supposed to engage with this. There is a big need out there for something dplyr-like that works. There are a dizzying array of options and that isn't going to help some good people who need a bit of handholding.

snthpy 3y ago
Thank you for your feedback. That's really valuable! We have the [PRQL Playground](https://prql-lang.org/playground/ https://prql-lang.org/playground/) exactly for that purpose. We'll try and make it more prominent on the front page. I've also felt that we should have a "Getting started" page and will push that as a priority.
- roenxi 3y ago
  Ah, I see. Maybe you're just losing beginners who NoScript then. I hadn't figured out what the Playground was supposed to do but it turned out there was an extra disabled script. Nevermind, thanks.

snthpy 3y ago

For those interested who want to learn more, we have a number of presentations coming up at conferences on three continents: - [QCon SF, October 2nd, San Francisco, USA: ](https://qconsf.com/presentation/oct2023/prql-simple-powerful-pipelined-sql-replacement https://qconsf.com/presentation/oct2023/prql-simple-powerful...) - [PyconZA, October 5th, Durban, South Africa: ](https://za.pycon.org/ https://za.pycon.org/) - [Community over Code (ApacheCon), October 9th, Halifax, Canada: ](https://communityovercode.org/schedule-list/#FT005 https://communityovercode.org/schedule-list/#FT005) - [data2day, October 12th, Karlsruhe, Germany: ](https://www.data2day.de/veranstaltung-21353-0-prql-a-modern-language-for-data-transformation.html https://www.data2day.de/veranstaltung-21353-0-prql-a-modern-...)

ledauphin 3y ago

I've been excited in the abstract about PRQL for quite a while. But something FQL seems to have a much better handle on is the value of document-orientedness, or what you might alternatively call "gradual schematization". This problem has been solved (if not beautifully, at least acceptably) by modern SQL databases that support a JSON storage format and associated "secondary query language". I know PRQL has had an open issue on this subject for a while. I just want to note that I think this is truly one of the critical "missing pieces" to PRQL, without which it may never be able to break out into common usage.

anon84873628 3y ago
FQL is interesting because it focuses on transactional systems and eliminating the need for an ORM in applications. I feel many of the SQL replacement projects like PRQL and Malloy instead come from the analytics side of the house, which doesn't really help application developers at all. (But does raise the question, how do I do analytics in Fauna? Do I ETL to a traditional warehouse system?)
- panda888888 3y ago
  That's a good question. If you need to ETL to a traditional data warehouse in order to do analytics, wouldn't you need an ORM, which is exactly what they're trying to avoid? (Also note that it's usually ELT instead of ETL these days, like if you're using Snowflake) Or is Fauna a hybrid transactional/analytical database so you can do analytics in it using FQL? (Maybe the long-awaited possibility of true hybrid databases is real?!) But then you'd need to train all of your business intelligence analysts on FQL, which would be a drawback.

ttfkam 3y ago

The syntax comparison section will likely sour a lot of viewers who already know SQL. You try too hard to highlight how easy and terse PRQL is by putting comma-separated items on their own lines but in SQL you put each item on separate lines. It may be typical of many SQL users and formatters, but it leaves a poor taste in the mouth that you aren't interested in an actual comparison but in marketing. For those who already know SQL, the real question is: will it make my queries faster? Putting the FROM first isn't sufficiently compelling on its own. Having a processing pipeline, though marginally more elegant to look at, doesn't actually improve upon CTEs. When you say you can use it with any database, how do you handle functions, stored procedures, jsonpath, and the massive differences in functionality between Oracle, MS SQL Server, Postgres, DB2, MySQL, MariaDB, H2, SQLite, etc.? Lowest common denominator? After 49 years of SQL, more than syntax has to change; you need an engine that supports this natively and can actually improve planner behavior over existing engines. I will grant that if you are limiting your target audience to primarily analytics, it's probably sufficient. The marketing of PRQL doesn't always appear to do this however.

anon84873628 3y ago
You might find Malloy interesting as it makes a greater departure from SQL syntax. Queries are first class objects which can be nested within each other in order to do trellising. It still compiles to SQL because that is the only language accepted by DBMSs today; however it will automatically write symmetric aggregate calculations and do those nestings that are hard for a human to write. https://www.malloydata.dev/ https://www.malloydata.dev/
- ako 3y ago
  How are views not first class query objects?
  - ttfkam 3y ago
    Yup. "SQL doesn't have first-class query objects and isn't composable." Uh… views, set-returning functions, temporary tables, CTEs… "No, not like that! Something not declarative and set-oriented!" SQL is declarative and set-oriented. 3rd generation language models for data were tried and discarded for good reason decades ago. "But… but… I don't like set-oriented models! They make me feel dumb. I'm not dumb, so SQL must be dumb. Not meeeeeeeee!!!"

raverbashing 3y ago

> we believe that SQL is a combination of two things: > 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. Your belief is as real as my belief that it rains too much in London ;) (that is, it is correct) But why people have such hold on to such a quirky syntax beats me

psacawa 3y ago

Is there any intention of eventually supporting DML or DDL statements? That's when the COBOL-like nature of SQL syntax is most frustrating. For example, in order to run "ALTER COLUMN ..." I have to parse a ridiculous BNF like this[0] almost every time. I'll never remember it. Usually, the error is a gotcha built into the language syntax (e.g. forgot the keyword "TO"). [0] https://www.postgresql.org/docs/13/sql-altertable.html https://www.postgresql.org/docs/13/sql-altertable.html

lolinder 3y ago

SQL will never die for the same reason that JavaScript will never die: because it's built in to all major database engines. In both cases, any other language will be starting as a second class citizen that has to compile to SQL/JS. During this phase of a new language's lifetime, it is either a surface-level syntactic change (a la Coffeescript) that provides no objective improvement, or it has to compile its simple semantic structures into opaque SQL/JS structures that will be off the beaten path and therefore not highly optimized by the runtime. Neither will reach sufficient adoption to become a first-class citizen in a major existing platform. TypeScript succeeded where others failed because it provided much-needed static analysis while keeping the changes minimal enough that it's completely obvious what the runtime code will look like, so there are no unexpected performance gotchas. SQL, on the other hand, doesn't really need a TypeScript because SQL is highly statically analyzable by nature. It's not that I don't believe we could do with an improvement on SQL, but I really don't see a realistic path forward for a replacement.

anon84873628 3y ago
Well, Malloy is developed within Google by the founder of Looker, so there is a chance it could be natively integrated into BigQuery. At that point you have a next gen SQL replacement available on one of the most widely used analytics and transformation engines.
paulddraper 3y ago
While I mostly agree, there is a bit of Stockholms syndrome. A lot of people don't know what they even could be missing. For example, there is no succinct way of writing an antijoin in SQL . The MERGE command has only been implemented by some engines due to (IIRC) concurrency concerns/ambiguities. ANSI SQL JSON operations have improved but are still clunky. Boolean NULL and IN is a clusterf of footguns. Etc.
- lolinder 3y ago
  Oh, I agree! SQL is far from optimal, as is JS. My thoughts are more about the feasibility of a replacement than the need for one. US electrical outlets are also highly flawed, but we're never going to replace them either. Standards, once entrenched, are nearly impossible to uproot.

deleted 3y ago

[deleted]

deburo 3y ago

That looks awesome. Does it support directly querying against databases (PostgreSQL, SQL Server, ...)? ie. is there a "Run" command in vscode that takes care of compiling & running the compiled sql?