5 ms·

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

• 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.
    • strokirk 3y ago
      It’s certainly not a feature. A lot of important SQL usage is ad-hoc queries, and they are more annoying to type than they should be.
      • mulmen 3y ago
        I don’t understand how changing the order of the clauses makes a query easier to type.
        • spullara 3y ago
          If you have auto-complete it can see what table you are using and complete the column names. Starting with SELECT it could be any column in the database.
          • nhinck2 3y ago
            I see this complaint stated over and over again, how hard is it really to type SELECT * FROM x a and then go back?
            • spullara 3y ago
              Is there any downside at all to putting FROM first?
              • mulmen 3y ago
                Disrupting decades of inertia to placate a small humber of unskilled novices.
          • mulmen 3y ago
            Huh. I have been doing this for like 15 years and never have a problem. Text editors like Sublime Text suggest names based on the content of the file. SQL Developer, SQLWorkbench, and DataGrip all seem to handle it just fine.
            • spullara 3y ago
              It will suggest names that aren't in the table you are going to query unless it is psychic.
              • mulmen 3y ago
                It is psychic in the sense that it makes educated guesses based on previous entries and the current database and schema. This is the same kind of educated guess it would make with a table name.
• slt2021 3y ago
  I think main difference is how PRQL translates into query execution plan, with SQL you need to read entirety of query to get rough understanding of order of operations (especially if you join two-three tables and have some nested and lateral queries). with PRQL I see that the order will be explicit and set up by developer, so any footguns will be evident. things like predicate push down, optimization fence, variable rewrite, etc are not needed to be relied upon, because pipeline of query is more explicit in PRQL also since it is new lang, it can be naturally extended into ETL type data pipelines also because PRQL can be translated into query execution plan - it can be converted into non-SQL languages like MongoDB or pandas / spark / etc, eliminating SQL altogether for distributed nosql engines
  • aidos 3y ago
    Is that true? Doesn’t this just effectively translate into sql to be executed on the db as usual if you’re running on an rdbms? Looking at the examples and the sql generated in the playground you’re just going to have the query planner doing its thing to decide how to execute eventually anyway.
    • slt2021 3y ago
      It transpiles into sql currently, but my understanding it is not long term vision. Language arch allows in the future to replace sql for querying data
• 0cf8612b2e1e 3y ago
  The attraction is something that was designed after decades of usage and PL research. Consistency of syntax is a big one for me. A favorite example of mine: SELECT substring('PostgreSQL' from 8 for 3); SELECT substring('PostgreSQL', 8, 3); -- PostgreSQL-only syntax SELECT trim(both from 'yxSQLxx', 'xyz'); SELECT extract(day from timestamp '2001-02-16 20:38:40'); Taken from: https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql Maybe if SQL would give me that monumental ask of trailing commas, perhaps I would hate it less.
• snthpy 3y ago
  If you're happy with SQL then there isn't much point. For the folks building and supporting PRQL, SQL just has a few too many warts and the popularity of tools like Pandas, dplyr, Polars, LINQ, ... shows that for analytical work we often like to work with our data differently. Other frameworks and languages feel that we should throw out Relational Algebra as well but we feel that's like throwing the baby out with the bathwater. PRQL's core tenets are that Relational Algebra is foundational and a great abstraction but what's needed is a modern, ergonomic language that allows us to work with data the way most of us conceive of it - as pipelines of transformations. Personally I feel much more productive in PRQL. I can start with a dataset and append one transformation at a time, writing from top to bottom as I go along without having to jump back and forth between the SELECT and the WHERE and the GROUP BY clause etc... . Also, if I want to take out a step, I can just comment out that line and the rest of the pipeline usually still works. This might seem like a minor thing but in my experience it's those kind of ergonomics that make the difference in actual day to day work rather than fancy theoretical features you only use once in a blue moon. It's therefore worth noting that this was an intentional design decision. You try and take out some steps from your SQL query and see how well the rest of your query holds up.
  • snthpy 3y ago
    Disclaimer: I'm a PRQL contributor.
  • aidos 3y ago
    For what it’s worth, it looks really readable to me. I have decades of sql experience at this point so consider myself pretty proficient but I can see the appeal of having a terser syntax for transformations. I especially like the “it just makes sql” approach. Stepping through the second example on that page I know how I could do the same in sql, and I also know that it would be harder for most people to follow. Question about that example though - why is there an aggregate without an assignment to a variable (average gross_salary) like the other examples?
    • snthpy 3y ago
      I couldn't find that example right now but there is a similar line in the main example on the prql-lang.org homepage: aggregate { average total, ... } I can't definitively say why it is there, other than perhaps just to show that you can specify aggregations without having to give them an alias. The column name won't be pretty but if you're just interactively trying something out and want to see the results then you probably won't care. Does that help?
• dlisboa 3y ago
  Maybe you didn't see the more complex examples, but some of the SQL queries it abstracts are very unintuitive to write by hand. PRQL turns it into something more readable and closer to the expected mental model. For people who can write error-free and complex portable SQL queries by heart this may not be really interesting. From experience, however, that is not a skill most developers have.
• the_mitsuhiko 3y ago
  I don't use PRQL but I absolutely get the appeal but specifically on the readability part, some things that are easy in PRQL are just awful in SQL. From the website for instance this is a nightmare to do in SQL: from employees group role (sort join_date take 1)
  • zX41ZdbW 3y ago
    What does it mean? Is it the same as "LIMIT BY" in ClickHouse? https://clickhouse.com/docs/en/sql-reference/statements/select/limit-by https://clickhouse.com/docs/en/sql-reference/statements/sele...
    • the_mitsuhiko 3y ago
      It picks the first result grouped by role and sorted by join date. I believe this can be expressed with limit by in ClickHouse.
  • snthpy 3y ago
    Unfortunately the linebreaks were lost and, as shown that isn't a valid PRQL query. It would have to be either from employees group role ( sort join_date take 1 ) or from employees | group role (sort join_date | take 1) In English: Take the 1st employee by (earliest) join_date for each role from the set of employees
    • zX41ZdbW 3y ago
      ClickHouse: SELECT * FROM employees ORDER BY join_date LIMIT 1 BY role
  • virtualwhys 3y ago
    What is the generated SQL of that expression? This is indeed a sticky problem, one that usually requires a subselect or other workaround to address the non-determinism of group by + order by; i.e. one cannot simply "select * from employees group by role order by join_date limit 1" and be guaranteed to get the expected ordering.
• andrewl-hn 3y ago
  Oh, I'm a big fan of raw SQL, too. There's very little out there that can bring me same joy as a well-written 30-50 lines-long sql query! Like, I can spend an hour or two on it, but when it runs and produces the results in the desired form, it's sooo rewarding! And realizing that producing similar results in my main language (be it Rust or TypeScript, or whatever) would take me 3-4 days instead (and it would run much longer) makes me appreciate SQL programming more and more. In terms of "usefulness per minute spend" nothing comes close to SQL! However, the syntax can be a bit clunky from time to time. I'm very fortunate that I use Postgres only in the past 5-7 years, so CTEs, and things like Timescale or JSONB, or PlV8 / PlRust for stored procedures are often on the menu. Yet, simple things still require repeating fragments of code from time to time, and complex grouping or window functions code often looks too convoluted. And as I wrote in my GP comment: I (and many other developers) already use pipelines for data manipulation in code - beyond databases. Say, we got data from an API endpoint, or we mixing data from several data sources. SQL is familiar because I see it often, PRQL is familiar because I use similar pipelining concepts often in my no-SQL code. Would I use PRQL for super simple queries? Probably not. Would I be upset if one of my coworkers used it for simple queries? No, why would I? Would I try implementing complex reporting query using PRQL? Yeah, I would. Partially because I suspect I would get to my data in desired form quicker with pipelines, and partially because I know: if I get stuck I would convert my intermediary query to SQL and pick up from there. For me PRQL looks better then most DSLs for ORMs or Query Builders in most languages. Adding a new tool to a project may be annoying, but depending on a project I'd rather pay this price once if it makes my less SQL-savvy team mates more comfortable. "Yet another syntax" is obviously the main point against, but from the application developer perspective ORMs / Query Builders often force one to learn "yet another syntax" anyway. "Don't use ORM / Builder" is an often voiced opinion and yet in practice we work with them all the time, too. So, I view PRQL not as an alternative to SQL but as a third database communication dialect in addition to SQL and my ORM / Query Builder DSLs.
• hn_throwaway_99 3y ago
  The attraction is that, especially for more complicated queries with complex joins, subqueries, aggregates, etc., that the structure of PRQL much more closely matches the "English", mental-model of the query. I can just read PRQL linearly, from top to bottom, and at each point I know what is being "pipelined" into the next phase of the query. With SQL I have to look all over the place. And it's not just that FROM should come before SELECT, it's that if I'm doing, say, an aggregation with a group by, normal SQL doesn't really have anything to make me think about the ungrouped rows, and then merge them together to get the aggregate values. With PRQL I can just go top to bottom, and for me it's much easier to reason about (i.e. first get all the rows, then group by some specific columns, then take aggregates, etc.) And I say this as someone who spends about half my days in SQL at present.
  • pragmatic 3y ago
    The downside is, debugging what actual sql this monstrosity spits out.
    • hn_throwaway_99 3y ago
      Two points: 1. The SQL that I've seen output by PRQL isn't monstrous, at least no more than it would be coding by hand. 2. I do agree that I don't think PRQL will have much uptake until it is baked in to DB systems, at least as a plugin. One reason (among many) that I hate ORM systems is that with native SQL it's very easy for me to just capture a SQL statement from my DB logs and then search my code for an offending statement. But given how PRQL is transpiled, I don't see any reason it couldn't be essentially part of the query planner, so that query logs etc. are reported in PRQL and not SQL. E.g. the query planner already takes text (SQL) -> Query Plan, it seems rather straightforward to go text (PRQL) -> text (SQL) -> Query Plan, with the SQL intermediate just being an implementation detail I don't have to care about as a DB user.
• ravi-delia 3y ago
  I mean if you can look at the "expressions" example on the homepage and say the SQL is more readable than PRQL, then more power to you. Hell, actually more power to you, that's extremely impressive. But I think for many people, especially programmers more used to parsing brackets and nesting than pseudo-english, PRQL is easier in the places where you're trying to compose.
• deleted 3y ago
  [deleted]