14 ms·

PRQL: Pipelined Relational Query Language

• kot-behemoth 3y ago
  Just a heads up that Prefect integration link on the landing page doesn't work - 404. This is the one I was interested in!
  • maximilianroos 3y ago
    Thanks! We just did a big refactoring so we do have some links failing in our tests. Just PR-ed a fix to this one: https://github.com/PRQL/prql/pull/3075 https://github.com/PRQL/prql/pull/3075 (PRQL dev here)
  • snthpy 3y ago
    Sorry that that was your first experience. I have opened an issue for this for you: https://github.com/PRQL/prql/issues/3074 https://github.com/PRQL/prql/issues/3074 The correct link is the following: https://prql-lang.org/book/project/integrations/prefect.html https://prql-lang.org/book/project/integrations/prefect.html TBH, that one is not much of an integration yet. It essentially boils down do. $ pip install prql-python and then import prql_python PostgresExecute.run(..., query=prql_python.compile(prql)) I don't personally work with Prefect, so if you have any ideas about what you would like to see here, please comment on the issue or on Discord. We're also very open to Pull Requests and they usually get merged fast ;-) Disclaimer: I'm a PRQL contributor.
• 0cf8612b2e1e 3y ago
  I really want this to take off and become a first party supported language for Postgres. Yes, yes, relational algebra is beautiful and all that jazz. SQL is a garbage, first pass design that should have been replaced decades ago. Even Codd has complaints about it. It is amazing what he invented, but we have learned a lot about PL design since then.
  • vkazanov 3y ago
    Relational algebra is still great, it is the sql that made an unbelievable mess out of this beautiful idea.
  • aerzen 3y ago
    People really like to associate relational algebra with SQL, probably because they learned them one alongside another. But SQL is really terrible relational language - it breaks a few core concepts in different places, like relations being unordered sets, that you can ORDER BY. This bubbles up as relations losing ordering after being wrapped into a subquery, which is really unexpected. PRQL has a data model very similar to the relational one. The only big difference is that relations are ordered - they are defined as arrays of tuples. So let's hope that PRQL gets to be known as "the relational language Mk II"
    • cmrdporcupine 3y ago
      Seems like you'd have to break quite a few relational model concepts in order to retain compatibility with SQL, no? Ordered tuples like you say, but also duplicate tuples. And null values.
  • burcs 3y ago
    Yeah, I have never liked that you choose what you are querying before you select the source. I think the formatting here is so much more intuitive.
  • snthpy 3y ago
    Not quite what you're asking for but DuckDB has both PRQL [1] and Postgres [2] extensions, so you could probably query your Postgres database with PRQL from there. There's also a DBeaver plugin [3] which we still need to document better and simplify the usage of but you could potentially also use that to query Postgres with PRQL. Finally there is pyprql [4] with which you could query Postgres from a Jupyter notebook. [1]: https://github.com/ywelsch/duckdb-prql https://github.com/ywelsch/duckdb-prql [2]: https://duckdb.org/docs/extensions/postgres_scanner.html https://duckdb.org/docs/extensions/postgres_scanner.html [3]: https://github.com/PRQL/prql/issues/1643 https://github.com/PRQL/prql/issues/1643 [4]: https://github.com/PRQL/pyprql https://github.com/PRQL/pyprql (Disclaimer: I'm a PRQL contributor.)
• samwillis 3y ago
  Previous Show HN: https://news.ycombinator.com/item?id=31897430 https://news.ycombinator.com/item?id=31897430 Show HN: PRQL 0.2 – a better SQL - 378 points by maximilianroos on June 27, 2022, 161 comments Original post when it was conceived: https://news.ycombinator.com/item?id=30060784 https://news.ycombinator.com/item?id=30060784 PRQL – A proposal for a better SQL - 650 points by maximilianroos on Jan 24, 2022, 295 comments
• andrewl-hn 3y ago
  For me the examples on the website https://prql-lang.org/ https://prql-lang.org/ are the biggest selling point for PRQL, in particular the SQL it generates. It looks clean, straightforward, something I would've written myself. In general, I like this slightly more careful take on modern database development. 10-15 years people would start a brand new database like Mongo, or Riak, or Influx, or whatever, and would try to convince application developers to select it for new projects. But recently we started getting more conservative options like EdgeDB, TimescaleDB, or even PRQL which all expect us to run Postgres with some addons and / or query preprocessors. Tech like this is so much easier to adopt! I'm really liking what Edge folks are doing with schemas and migrations, but I do find PRQL syntax much more intuitive. My application code is littered with data transformation pipelines already: all these map / filter chains in TYpeScript, iterators in Rust, enumerables in Ruby, streams in Java, LINQ in .net, Rx in dozens of languages etc. etc. So the concept is very, very familiar. Getting nice SQL queries out of PRQL that I can store, inspect later, see the query plans, add indices where necessary, is just great. It's such a good tool!
  • t8sr 3y ago
    I don't get that - to me the examples are much less readable than SQL and I don't understand why I should want to use this. Like, yes, you can reorder the query sections, which seems to be everyone's complaint about SQL, but then you also have multiple types of brackets, colons and other syntax for no reason, all while not really accomplishing anything SQL doesn't already do. What's the attraction?
    • spullara 3y ago
      it seems like any SQL parser could let you put FROM first and solve a lot of the annoyances of SQL
      • mulmen 3y ago
        The stiff syntax is a feature. The infamous 1000 line sales report query is already a bear to maintain. If you get a clever developer reordering syntax it will only be worse.
• breischl 3y ago
  Having spent more time in MongoDB aggregations than I'd like to lately, I really wish they'd support this. So much more sensible than the madness they've got going on.
  • snthpy 3y ago
    This sounds interesting. Could you provide a link to the MongoDB aggregations that your are referring to because they're not something I'm familiar with? (Disclaimer: I'm a PRQL contributor.)
    • zX41ZdbW 3y ago
      Here are examples of MongoDB aggregations: https://github.com/ClickHouse/ClickBench/blob/main/mongodb/queries.js https://github.com/ClickHouse/ClickBench/blob/main/mongodb/q... They are painful to write compared to SQL queries. Although the commercial version of MongoDB has support for SQL, it's not available for general MongoDB users.
      • snthpy 3y ago
        Thanks! Those do look awful and more what I remember from the brief period when I used MongoDB in around 2013 or so. I take it GP comment was just trolling then?
        • breischl 3y ago
          I was not trolling, I actually dislike MongoDB aggregation syntax and it seems like PRQL is something that could potentially replace it or go on top. Admittedly I only took a quick glance at PRQL though, so perhaps I misunderstood something or it's not applicable to this case. There are examples on Mongo's page, eg https://www.mongodb.com/docs/manual/core/aggregation-pipeline/ https://www.mongodb.com/docs/manual/core/aggregation-pipelin... eg: db.orders.aggregate( [ // Stage 1: Filter pizza order documents by pizza size { $match: { size: "medium" } }, // Stage 2: Group remaining documents by pizza name and calculate total quantity { $group: { _id: "$name", totalQuantity: { $sum: "$quantity" } } } ] )
• yevpats 3y ago
  Looks interesting but I wonder if this abstraction doesn't leak and it doesn't just become a more limited version of vanilla python + vanilla SQL.
  • aerzen 3y ago
    Vanilla python? Where do you get this impression? The leak to SQL is trough s-strings, which are chunks of SQL that gets inlined into the resulting SQL. The long-term plan is to remove them completely, but they are needed as an escape-hatch for now.
    • yevpats 3y ago
      I see that PRQL is compiled to SQL. How do you deal with different type of SQLs? i.e Snowflake/Postgres/Clickhouse? Isn't it a potentially impossible abstraction or at least there will be places where you will need to use the native support sql language (unless of course databases will adopt that in the first place which might be great)?
  • sbuttgereit 3y ago
    This. If you are layering an abstraction on top of SQL to avoid the SQL, you fail that goal once something goes wrong. The reason you fail the goal is that you end up having to have skills to troubleshoot the SQL anyway. Wrong here can be: bad result, bad performance, or even just bad syntax. Unless you're ready to accept being helpless and rely on the knowledge of others... you'll want to look at what's actually hitting the database to know if it's a database problem, a PRQL problem, or problem in your own code and to do that we'll you better be well versed in SQL, your database, and how your DB vendor have implemented their flavor of SQL. So to use this DSL truly competently, you need to know everything that you'd need to know to write plain SQL... and then PRQL and how it might translate into the target SQL of your application. And taking PRQL on its own it doesn't really seem any simpler than SQL from what I can see... what you get in increased compositional simplicity you have to pay back in understanding its own quirks. To be fair this approach can be worth it, but I don't think for a stand alone DSL like PRQL. For example, in .NET land there's C# and Linq where you get a query DSL... but you also get a more seamless integration into C# and its paradigms. Same could be said of Elixir and the Ecto query DSL; all the other things I above remain true, but the DSL brings the value of clean integration in to the code base of your app. Needless to say, I understand the why they'd try something like this... but I'm skeptical that the marketing here seems to oversell what you end up really getting.
• karmakaze 3y ago
  How are joins handled? A relational db is largely about the relations, otherwise this is more of a document query language.
  • athenot 3y ago
    I had the same question. From the docs, it looks pretty elegant: from employees join side:left positions (employees.id==positions.employee_id) which translates to SELECT employees.*, positions.* FROM employees LEFT JOIN positions ON employees.id = positions.employee_id
    • qorrect 3y ago
      I like it too. You can also alias them easily from e=employees join p=positions (e.id==p.employee_id)
      • trollied 3y ago
        positions p is a SQL alias. Was that too difficult?
        • kbenson 3y ago
          It's less about it being difficult, and more about it being easy, obvious to read/write, and consistent within the new design. Was that not obvious?
    • erezsh 3y ago
      Except if I wrote it by hand, it would be - SELECT * FROM employees LEFT JOIN positions ON employees.id = positions.employee_id which isn't really that different.
• t8sr 3y ago
  If the main complaint people have about SQL is that you can't swap SELECT, FROM and WHERE, then that's pretty good for a language designed in the 70s. This, by contrast, looks like it has a bunch of random line noise for syntax. Why on earth should I like this: `join side:left p=positions (p.id==employees.employee_id)` better than this: `LEFT JOIN positions AS p ON p.id = employees.employee_id` ?
  • kbenson 3y ago
    Because now the type of join is an argument on the "join" operation, and join is the first word of that statement making it more obvious what the operation actually is? I also prefer foo(bar) over "bar to foo()" as well, which seems like the equivalent in a more general purpose function call in a language example.
  • lofatdairy 3y ago
    Maybe it's my familiarity with R and the tidyverse paradigm, but I think in general this paradigm and syntax is pretty readable. The example you chose is perhaps a case where brackets and commas go a long way, but that aside it's really not too bad in context of the rest of the language. `:` seems to consistently be treated as keyword arguments, and `=` for aliasing.
  • jdmichal 3y ago
    I've had two real gripes with SQL. The rest of it has been, as you said, pretty good. Complaint 1: Not being able to use selected columns later in the same select. SELECT gnarly_calculation AS some_value, some_value * 2 AS some_value_doubled Instead: SELECT subquery.*, some_value * 2 AS some_value_doubled FROM ( gnarly_calculation AS some_value ) AS subquery Complaint 2: Not being able to specify all columns except. This combines with the above, where I have to pull some intermediate calculations forward from a subquery, but I don't need them in the final output. So I have to then enumerate all the output columns that I actually want, instead of being able to say something like `* EXCEPT some_value`.
    • snthpy 3y ago
• smartmic 3y ago
  This must not be missing here then: "I don't want to learn your garbage query language" [1] [1] https://erikbern.com/2018/08/30/i-dont-want-to-learn-your-garbage-query-language.html https://erikbern.com/2018/08/30/i-dont-want-to-learn-your-ga...
  • maximilianroos 3y ago
    Big fan of this post! We link to it from PRQL website. Our goal for PRQL is a great language to integrate with & build on. So we don't have N languages for N databases. Because PRQL will always be open-source, and won't ever have a commercial product, we think that's much more feasible than a DB-specific or product-specific language. (PRQL dev here)
    • phlakaton 3y ago
      As evidenced from the poster's ORM rant, two languages for the same DB seem just as offensive to them as different languages for different DBs. I suspect you're still going to have an uphill battle converting them, unless they were hit with a blinding shaft of sunlight on the road to the SQL History Museum or something.
  • default-kramer 3y ago
    The best rebuttal: https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/
• LinXitoW 3y ago
  This feels like the perfect level of SQL abstraction. SQL is great in theory, but gets really hard too read once you throw in joins with subqueries.
  • smartmic 3y ago
    Why not break these subqueries into their own common table expressions and prepend them to your join statements?
    • mulmen 3y ago
      Or their own tables or views.
      • aidos 3y ago
        Adding tables doesn’t sound like a solution at all and views come with operational overheads that you need to be aware of. CTEs should be the first thing you reach for when trying to clarify the intent of the way you’re chopping up your data.
        • mulmen 3y ago
          Depends on the situation. Creating tables is a space/CPU tradeoff. Views can be managed the same way as the rest of your data pipeline. CREATE TEMPORARY VIEW and CREATE TEMPORARY TABLE are both valid SQL. > CTEs should be the first thing you reach for when trying to clarify the intent of the way you’re chopping up your data. Sure but they aren't a silver bullet. Hence the "or" in my comment. I use Redshift a lot and temporary tables are useful there for defining the sort and distribution keys when those are not favorable to the current query. Think joining several demographic tables into a single customer dimension, distributing it on the customer key then filtering the product_id distributed sales and re-distributing it on customer before joining. You can't do that with a CTE. Views are handy when you have ugly CASE statements or other derived logic and need a convenience column that does something like string concatenation. I have a whole repository of them I deploy on top of the default system views for simplifying admin tasks. If you are struggling to keep your SQL clean it's because you are doing too much at once. Take a step back and re-evaluate your data model. If your physical model is bad then no number of CTEs will save you.
• snthpy 3y ago
  This is well timed as PRQL 0.9 was just released a few hours ago. Have a look at the release notes here: https://github.com/PRQL/prql/releases/tag/0.9.0 https://github.com/PRQL/prql/releases/tag/0.9.0 There is a rather large breaking change in the syntax from `[]` to `{}` for tuples. This is because initially it seemed like these were lists but over time we've realised that they are actually tuples. As per the release notes, freeing up `[]` clears the way to start supporting arrays soon. Disclaimer: I'm a PRQL contributor.
• getmeinrn 3y ago
  >declarative An admirable ideal, but declarative languages always seem to devolve towards some frankenstein imperative/declarative hybrid. We need to stop going down this path and embrace Pulumi's pattern: use existing general purpose imperative languages to generate a declarative structure. Instead, people try to take their not-mature declarative language and fit a weird general purpose language inside it. EDIT>> I'm not suggesting that SQL needs to be declarative, only that if a problem space would benefit from declarative structures, generate them imperatively instead.
  • 0cf8612b2e1e 3y ago
    PRQL seems the most realistic evolution out of SQL. Changing the programming paradigm will never convince the SQL true believers.
    • getmeinrn 3y ago
      I don't really know what you're saying, can you say it another way? "Most realistic evolution"... why is that needed? If the problem is different database engines implementing the SQL spec differently, that's not something that can be papered over with another abstraction without a lot of wrinkles.
      • 0cf8612b2e1e 3y ago
        SQL does not need to be replaced, in the same way that COBOl never needed to be replaced. It can get the job done, but we have superior tools today. Better abstractions enable more productivity. That there are N flavors of SQL is annoying, but there are foundational design choices in the language which we are stuck with today. PRQL is quite readable to those with SQL experience and feels like a plausible next language in the space without reinventing paradigms.
        • getmeinrn 3y ago
          To go back to my original post, my main beef is with turning an imperative language into a declarative one. If you've seen enough of these types of languages degrade, you start to see a pattern.
• clatan 3y ago
  The sooner SQL is phased out in favor of something more akin to Hadley Wickham's dplyr the better. Don't particularly like the syntax of this but it's the right direction.
• bob1029 3y ago
  Forwarding this to some of my team members right now. We use SQL for a lot of things, almost all of them implementation of customized business rules. When attempting to develop a business rule that needs to manifest as a single SQL query, your brain is forced to work in a very particular crystalline way. Many business rules are defined in procedural terms at requirements time, so having a way to implement them in the same could be beneficial.
• glogla 3y ago
  Last time this showed up on HN, I complained about it not having CASE WHEN equivalent, which I've seen heavily used in ETL usecases. Now I see they added it! Pretty cool and responsive. I'll go take it up for a spin.
• heywhatupboys 3y ago
  This is just tidyr ?
  • aerzen 3y ago
    In a way, yes.
  • snthpy 3y ago
    Kind of. dplyr, Pandas, LINQ, Kusto, etc... are big inspirations. The fact that these are so popular and have reinvented the same workflows with slightly different syntaxes to me is a sign that they capture something fundamental about how humans like to think about data transformations. PRQL is indeed very close to dplyr. In my (biased) opinion, PRQL is actually a bit cleaner than dplyr because it is its own language and doesn't have to work as DSL inside R. The same goes for comparisons with Pandas and Polars having to work as DSLs inside Python. Compare from mtcars filter cyl > 6 select {cyl, mpg} sort {-mpg} with mtcars %>% filter(cyl > 6) %>% select(cyl, mpg) %>% arrange(dplyr::desc(mpg)) Incidentally, I produced the dplyr code from the PRQL with ```R install.packages("prqlr", repos = "https://eitsupi.r-universe.dev", lib="~/.local/R_libs/") library(prqlr, lib.loc="~/.local/R_libs/") library("tidyquery") " from mtcars filter cyl > 6 select {cyl, mpg} sort {-mpg} " |> prql_to_sql() |> tidyquery::show_dplyr() ``` (Disclaimer: I'm a PRQL contributor.)
• dtheodor 3y ago
  Reminds me of Microsoft's KQL https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://learn.microsoft.com/en-us/azure/data-explorer/kusto/...
  • joeldo 3y ago
    The one joy I have while using Azure :)
  • antonyt 3y ago
    KQL is excellent, I miss it whenever I write SQL.
• nonethewiser 3y ago
  This is tangential but a new query language is inevitably based in the idea that SQL is deficient in some manner (hard, not ergonomic, whatever). More interestingly, it also implies that the countless alternatives aren't good enough either. Is there an existing query language that anyone will argue is better than SQL? I have limited exposure on this, but if SQL is really not that good then I'd expect there to be a better one at this point. All the new entrants kinda suggest that maybe SQL isn't so bad after all. Even many noSQL db's use an SQL-like language (see PartiQL). I'm not that familiar with alternatives though.
  • 0cf8612b2e1e 3y ago
    I think of it in the same way as JavaScript. Clearly, it has issues, but if you want to work on the web, it is what you have available. If you want to speak to Oracle/MySQL/SQL Server/Postgres database you are writing SQL. New entrants, could make a new language, but now they are fighting a battle on two fronts: the novel technology +query language.
    • mhink 3y ago
      > I think of it in the same way as JavaScript. Clearly, it has issues, but if you want to work on the web, it is what you have available. And, interestingly, the approach they're taking here is similar to how folks have dealt with JS: introduce a transpiled language whose paradigms are close enough to the host language to feel familiar. Reminds me of CoffeeScript, actually (although if we're being honest, I couldn't stand CoffeeScript.)
  • animal_spirits 3y ago
    I counter the argument that just because it is still around it is the best solution. Many things stick around just because of inertia, for example, the QWERTY keyboard.
    • nonethewiser 3y ago
      Then state the better solution. Besides, I'm not saying it's the best because it's most common, or even that its the best at all. It just makes it difficult to understand the argument that SQL sucks when its so widely used and no one can agree on an alternative. Including in new databases which could opt for these "new and improved" variants. I grant that there is a first-mover advantage and it's not easy to shift paradigms. That's a big factor. That's why I am honestly - not rhetorically - asking which query language is clearly better than SQL? And why do people keep making new challengers instead of backing something that's already better than SQL? There seems to be very little support for these query languages despite many people saying SQL sucks and they want a redo.
• zX41ZdbW 3y ago
  We have recently merged PRQL support into ClickHouse: https://github.com/ClickHouse/ClickHouse/pull/50686 https://github.com/ClickHouse/ClickHouse/pull/50686 It's currently more like an experiment - I'm not sure if it will be usable or useful. There are some concerns about Rust, although minor: https://github.com/ClickHouse/ClickHouse/issues/52053#issuecomment-1643805521 https://github.com/ClickHouse/ClickHouse/issues/52053#issuec...
  • snthpy 3y ago
    This is awesome! Thank you! Would that mean it would also be able to use it in clickhouse-local?
    • zX41ZdbW 3y ago
      Yes: clickhouse-local --dialect prql or: $ clickhouse-local :) SET dialect = 'prql'
• netcraft 3y ago
  I dont know the right words, but creating a plugin for DBT for this would give it a lot of traction I bet.
  • snthpy 3y ago
    Totally agree. This was a high priority goal for us and @maximilianroos did a lot of work putting something together. In the end, I believe it was really fighting against how dbt is set up though as it is so tightly interwoven with Jinja2. Perhaps with the introduction of the Python dbt models, things might have changed now and it's worth taking another look. https://github.com/PRQL/dbt-prql https://github.com/PRQL/dbt-prql (Disclaimer: I'm a PRQL contributor.)
• RGamma 3y ago
  Sweet, it looks like the language M should have been (or should become)! Granted, M would massively benefit from better tooling too.
  • snthpy 3y ago
    Ah, nice to see a mention of M. I don't see it mentioned a lot on HN and get the feeling most folks here probably don't even really know about it because it's so embedded in PowerQuery and PowerBI. It's actually not a bad language, it's just quite verbose in terms of the keywords. I actually draw on my experience with M a lot in terms of the input that I give to PRQL because for data munging M is quite a good language. Until we did the [] to {} syntax swap in 0.9, PRQL actually started to look a lot like M because M is the only other language I know that has [] for tuples. (Disclaimer: PRQL contributor here)
    • RGamma 3y ago
      Yeah, it's a neat tool generally speaking. What holds it back most of all I feel is the prison that is the Power Query editor (I have a sizable list of small and large papercuts in mind). Now that PBI allows experimental export to pbip, change may be on the horizon. Hoping for more fine-grained tools, a better profiler and a better editor. Will look at PRQL when I find the time. The planned column lineage feature and type checking e.g. seem like a godsend. Good work!
• sdesol 3y ago
  This is from the GitHub page > PRQL is being actively developed by a growing community You can see this at https://devboard.gitsense.com/PRQL https://devboard.gitsense.com/PRQL which shows a good mix of new and not new contributors. Not sure why there was a spike in the last couple of days though. Maybe the maintainers that in this thread can comment on this. Full Disclosure: This is my tool
  • snthpy 3y ago
    Great tool! Thanks for sharing that. Regarding the activity, there's been a lot of preparation for the 0.9 release. As to the new contributors, I don't know the reasons for that but all contributions are very welcome. It's a very welcoming community and a great way to make an impact! Full Disclosure: PRQL contributor
    • sdesol 3y ago
      Thanks for the insights. You might also want to add a "good-first-issue" topic an/or labels to your project to signal how open you guys are to new contributors. In the near future, I'm going to start surfacing projects with these labels/topics.
• des1nderlase 3y ago
  So, in principle, how is this different approach than DBT?
  • anon84873628 3y ago
    dbt is a tool for defining and orchestrating in-database transformation jobs. Typically you write SQL statements to define the transformations (plus some template language to pull in additional context if needed). PRQL is an alternative language for writing queries which compiles to SQL. There is a dbt plugin which presumably allows you to define the transformations in PRQL instead of SQL, while everything is still orchestrated by dbt.
• Taikonerd 3y ago
  The limitation of PRQL is that it only does SELECTs, by design. If you want to insert/update/delete data, you're back to SQL. That means that your team's data scientist might give you a query written in PRQL, but if you want to actually incorporate it into the data pipeline, you'll need to translate it into SQL. I wish that PRQL would support at least a limited ability to insert -- for example, maybe just the case of inserting into a new temp table. No update or ON CONFLICT logic to worry about. It could look like this: from tracks filter artist == "Bob Marley" save bob_marley_tmp
  • cpursley 3y ago
    Yep, these is where I lost interest.
  • 10000truths 3y ago
    I don't see why PRQL can't support data mutation. Just have an insert/update/delete operator that must go at the end of a pipeline, and which takes a table name as an argument. An SQL query like this: UPDATE counters SET value = value + 1 WHERE name LIKE 'prefix.%' Could then be written in PRQL as something like this: from counters filter startswith(name, 'prefix.') derive { new_value = value + 1 } select { name, new_value } update counters
    • BrentOzar 3y ago
      This feels way less intuitive than SQL: UPDATE counters SET value = value + 1 WHERE name LIKE 'prefix.%'
      • strken 3y ago
        I really hate that SQL doesn't let you do UPDATE counters WHERE name LIKE 'prefix.%' SET value = value + 1 In a SELECT it's fine, but in a mutation it's dangerous to make updating literally everything so easy if you leave off the tail.
• chubot 3y ago
  Hm, I just realized there are two similar projects with very similar names: this one, and https://github.com/erezsh/Preql https://github.com/erezsh/Preql
• nonethewiser 3y ago
  There seems to be very little support for these SQL challengers despite many people saying SQL sucks and they want a redo. Why is that? Some guesses: - There is some popular non-sql query language that has gained lots of momentum that I just dont know about. - People are more effective with SQL because they know it so no new and especially existing databases will switch. This is definitely true to some extent, but if this is the whole reason then I guess SQL is pretty good since people rely on it to be effective. - There is support for it but a few key decision makers are to blame (big data or the MYSQL lobbyists) - People actually dont dislike SQL that much and/or its not that bad and hackernews is not representative - These query languages look nice but have technical or performance issues compared to SQL
  • rcme 3y ago
    I think the main reason mainline SQLs continue to be popular is that, unlike general purpose programming languages, the implementation of the query runner is the most important aspect, not then language spec. Take Python for instance. The official implementation objectively sucks, yet it remains extremely popular because the language implementation is only a part of a language’s success. That won’t fly for a db engine.
  • erezsh 3y ago
    The last one is definitely a big factor, combined with the fact that you still have to compile to SQL, i.e. you are limited by its limitations, and there isn't always a workaround. The rest are also true to some degree. But I think that once a serious contender comes along, people will eventually move to it.
• sakras 3y ago
  It's been really cool seeing PRQL come to life! I'm not involved with it, but I still remember the very first HN post about a year ago where it was just a proposal. And look at it now! It's really gaining steam, I'm really excited about it!
• zX41ZdbW 3y ago
  The ergonomics could be better: https://pastila.nl/?01359244/9ac65f960385a02b3193778b4c6af102 https://pastila.nl/?01359244/9ac65f960385a02b3193778b4c6af10... Significant newlines. Non-obvious way to specify database/schema.
  • snthpy 3y ago
    There are two symbols for separating transforms in a pipeline - \n and | so you can use | instead of \n if you prefer. So for your query from the linked gist, the following should work: from system.numbers | select number Otherwise, if you want to be really strict: prql target:sql.clickhouse from `system.numbers` | select {number} You can see here for more details: https://prql-lang.org/book/reference/syntax/keywords.html https://prql-lang.org/book/reference/syntax/keywords.html
• lovasoa 3y ago
  I am currently building a SQL-only web framework [1], and I was contacted by the prql folks to include the language by default in sqlpage. I have to say, I have mixed feelings about it: on one hand, it indeed feels more natural to query data with a clean pipeline of operators. On the other hand, it makes me think of the famous xkcd comic about standards [2]: there were too many data querying languages, so we made a new one. [1] SQLPage: https://sql.ophir.dev https://sql.ophir.dev [2] https://xkcd.com/927/ https://xkcd.com/927/
• maxloh 3y ago
  How does this compare to EdgeQL?
  • jakubmazanec 3y ago
    This seems like just a compiler from a better language (PRQL) to SQL, while EdgeDB is actually a database that comes with not just a better language (EdgeQL), but also other great features (I love their TypeScript query builder or how incredibly frictionless the migrations are).
    • cpursley 3y ago
      Frictionless until you need to do standard stuff like a stored procedure or a view.
• soulbadguy 3y ago
  Naive question : Isn't something like F# computation expression or C# LINQ a simpler and more generic way of acheiving the same effect?
• evantbyrne 3y ago
  Has anyone benchmarked it or the bindings? SQL tends to be embedded directly in software programs, so building would likely happen on the fly in real-world cases.
• cowthulhu 3y ago
  This is insanely cool. It’ll be interesting to see if this gets decent adoption, and whether the query language ends up being easier to maintain than sql.
• rzmmm 3y ago
  I couldn't find a mention if this supports SQL transactions?
• slotrans 3y ago
  Hot takes: SQL is good. Great, in fact. Not just in what it's capable of doing, but in form. It has warts, yeah, but over the years I've been writing it I've realized most of them are there for a reason. To me it's more than useful, it's beautiful. It's the absolute last thing I want a replacement for.
  • Abekkus 3y ago
    How do we ever hope to differentiate between useful evangelism and stockholm syndrome? Either way, I'm probably learning a lot of postgresql this year. I do really wish we had at least settled on one data format instead of having enough differences between PL data and DB data to start building ORMs.
    • ellisv 3y ago
      Unsurprisingly, not everything is intuitive. There are language nuances that can surprise and frustrate users but are often well thought out. It’s exceedingly rare to have a SQL query written 20 years ago that couldn’t be run today, and written today it may be the same or very similar to one written long ago. It’s hard to say the same for other programming languages.
• galkk 3y ago
  Syntax doesn't matter much, what in the optimizer does. I think that right now a lot of SQL problems are baggage. Too many people are battle-taught to write hard to read, complex, deeply nested queries because older engines were bad at handling modern constructs (e.g. look, somebody got common table expession (CTE) here, let's materialize it immediately). So older style was mess of SELELT FROM (SELECT FROM (SELECT FROM A JOIN (SELECT ) B ON A.x=B.x))) etc With things like improved CTEs handling by engines, it is getting easier and easier to write understandable, composable queries of structure like below. Add modern window functions that decrease another layer of complexity, things like SELECT * EXCEPT, more complex TOP statements like TOP X FOLLOWING etc and SQL will be even more simpler. WITH A AS ( ), WITH B AS ( ), WITH C AS ( SELECT y FROM A) , SELECT result from C WHERE and in new-ish world minor improvements of different syntax shape (like that PRQL) do not really bring the benefit, from my POV. Yes, you can rewrite it in imperative looking pipeline, but optimizer can throw away some of it etc Yes, there are things that can and should be done better in SQL, but I have yet to see compelling case for cosmetic, syntactical differences.
• 1attice 3y ago
  This looks similar in many ways to Kusto Query Language, one of my favourite tropes of Azure: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://learn.microsoft.com/en-us/azure/data-explorer/kusto/... I'm delighted to see an open alternative emerge <3
  • avneraa 3y ago
    Note that Kusto Query language is open source see https://github.com/microsoft/Kusto-Query-Language https://github.com/microsoft/Kusto-Query-Language
• abrookewood 3y ago
  You have to love the honesty: "Why PRQL? For HackerNews enthusiasts: The PRQL compiler is written in Rust [and] We talk about “orthogonal language features” a lot" (https://prql-lang.org/ https://prql-lang.org/)
• tln 3y ago
  Have completions been implemented at all? The language looks like it is designed to take that into account, but I don't see that after a cursory search
• anon84873628 3y ago
  Has anyone compared PRQL and Malloy head to head?
  • snthpy 3y ago
    I'm not aware of a head-to-head comparison. My impression is that they have slightly different aims. PRQL intends to express analytical queries as a series of transformations which form a data pipeline. My sense is that Malloy aims more to provide a kind of semantic layer as well as dealing with nested data and reports. (Disclaimer: I'm a PRQL contributor.)
• jtsuken 3y ago
  There is a point in moving the FROM ahead of SELECT and getting sensible auto-complete suggestions from your IDE as you type. LINQ, however, attempts to solve a much bigger problem: Use consistent code to process data and don't worry whether it is in a remote database or in a local array. If you manage to do it in a way that - integrates seamlessly with e.g. Python IDE and I don't have to pass and parse strings - allows me to access graph, document and relational data - allows me to pull and process the data from REST APIs, [O/J]DBC and straight from my RAM - and maybe even includes deductive (PROLOG/DATALOG) features I will be your most loyal client
• robbywashere_ 3y ago
  BlaBla.io Better Tomorrow Today(tm) Fullstack Engineer (React, Typescript, PRQL) (100% Remote) What We're Looking For: You’ve got 5+ years of experience in designing, building and maintaining large apps written in React, Typescript and PRQL ... I'll just add it to my experience just to be safe
• mcsoft 3y ago
  PRQL is a breath of fresh air. Reporting languages generally miss built-in visualization and drill-down capabilities. Ideal reporting query should define not only how to seek, join, and and aggregate data, but also how to visualize output and how to present details in reaction to user clicks. There are some limited efforts like in PowerBI and Splunk but we need a standard. I wonder if PRQL guys will address this need in the future.
• jiggawatts 3y ago
  There are about 17 of these "SQL but better if we start with the FROM statement at the top" languages. The language-integrated query (Linq) feature of .NET works the same away: var studentsGroupByStandard = from s in studentList group s by s.StandardID into sg orderby sg.Key select new { sg.Key, sg }; Another example is Microsoft's Kusto Query Language, which they use in Azure Log Analytics: StormEvents | where State == 'TEXAS' and EventType == 'Flood' | top 5 by DamageProperty desc | extend Duration = EndTime - StartTime
  • inopinatus 3y ago
    So did QUEL, as implemented in Ingres circa 1976. Perhaps this is what happens when you go back to basics and take a good long look at Codd's relational calculus (or, equivalently, relational algebra), to which SQL bears only a passing resemblance.
    • johnthescott 3y ago
      back in my day, i thought a WHERE clause on the "RANGE OF alias IS RELATION" would be useful. nowadays, a session level VIEW is the way to go.
• fridental 3y ago
  Why developing yet another language if we already have LINQ?
• lincpa 3y ago
  [dead]
• tabtab 3y ago
  Except for niches, one is not going to unseat SQL as the primary de-facto standard relational query language unless the replacement is significantly better, not just slightly. Unless a fad storm kicks in, being slightly better is rarely enough to unseat an established standard. As far as SQL competitor candidates, my personal favorite is SMEQL. It's more composable and uses tables for many operations instead of dedicated commands and syntax, making it more uniform where a handful of idioms do all the work instead of lots of specialized stuff for each task, like what SQL does.
  • snthpy 3y ago
    Thanks. I hadn't heard of SMEQL before. To me it structurally actually looks very similar to PRQL. PRQL just always has `srt` as an implicit parameter (now referenceable as `this` since 0.9) and a more linear flow whereas with SMEQL I guess you could have more complex chains. Actually you can do those with PRQL as well, you just need to break them out into separate `let` expressions. On the upside, PRQL can do away with a lot of the parentheses and some punctuation which makes it a bit nicer IMHO but YMMV. I agree with you that PRQL would need to be *significantly* better to justify the time investment in learning it. For some of that is already the case but we'll continue working on it to try and hit that sweetspot for even more people. All the best! (Disclaimer: I'm a PRQL contributor.)
    • tabtab 3y ago
      I frankly don't see many similarities to PRQL.