5 ms·

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-

• 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!!!"
• remram 3y ago
  It's just syntax, it compiles to SQL and runs on today's DBMS. It has no difference in speed or functionality.
  • ttfkam 3y ago
    (I couldn't help but notice you didn't comment on the difference in formatting in the examples.) Do you have examples of PRQL working with jsonpath? Generating JSON? Unnesting arrays? Returning ids from an INSERT or UPDATE without making a separate read query? Not trying to be argumentative. Honest question.
    • remram 3y ago
      I am not affiliated in any way with the PRQL project. Those are great questions though, I hope we get an answer.
      • snthpy 3y ago
        @remram I replied to the parent comment.
    • snthpy 3y ago
      Thanks, that's a great question. You're right in that so far we haven't highlighted working with JSON and I hadn't actually tried until this point. IMHO the true power of PRQL comes from the fact that it allows you to define functions and with that you get the power of composability which is the true power of almost every programming language (and which is for the most part completely lacking in SQL). So with that said, I tried the following POC (remember that PRQL is just a SQL generator so the JSON capabilities depend on your underlying RDBMS): ```sh > prqlc compile <<EOF let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]' } EOF WITH table_0 AS ( SELECT '{"duck": [1, 2, 3]}' AS data ) SELECT data -> '$.duck[0]', data ->> '$.duck[1]', json_extract(data, '$.duck[2]') FROM table_0 -- Generated by PRQL compiler version:0.9.4 (https://prql-lang.org) ``` What's going on here is that I used [s-strings](https://prql-lang.org/book/reference/syntax/s-strings.html https://prql-lang.org/book/reference/syntax/s-strings.html) to define custom PRQL functions `get`, `getstr` and `extract` which translate into the underlying `->`, `->>` and `json_extract` SQL constructs. You could then for example pipe that query to DuckDB (the example is taken from the following DuckDB blogpost [Shredding Deeply Nested JSON, One Vector at a Time](https://duckdb.org/2023/03/03/json.html https://duckdb.org/2023/03/03/json.html)): ```sh > prqlc compile <<EOF - | duckdb let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]'} EOF ┌───────────────────────┬──────────────────────────┬───────────────────────────────────┐ │ "data" -> '$.duck[0]' │ ("data" ->> '$.duck[1]') │ json_extract("data", '$.duck[2]') │ │ json │ varchar │ json │ ├───────────────────────┼──────────────────────────┼───────────────────────────────────┤ │ 1 │ 2 │ 3 │ └───────────────────────┴──────────────────────────┴───────────────────────────────────┘ ``` HTH
• iamsam123 3y ago
  Does it SELECT * by default if I never define a SELECT below my FROM? ... Continuing to encourage folks by allowing them to SELECT * easier is would not be fun for me... I could be wrong? Agreed, just parsing out the formatting so its "fewer lines" than traditional SQL soured me. The expressions example is ridiculous, in Redshift I can do this all day?? SELECT 1 + 2 AS num1 , num1 * 2 AS num2 -- Literally no difference Just learn SQL...
  • Zak 3y ago
    > Just learn SQL... I know SQL, and I imagine the authors of PRQL know it better than I do. Doesn't it seem weird that dozens of application languages have become popular since the 1970s, but we're still using dialects of the same old database query language? If it had a really elegant syntax, perhaps it wouldn't, but SQL's syntax is anything but. Some of the semantics can be awkward as well. I, for one welcome attempts to move things forward (which is different from saying I'm going to run out and use PRQL in production tomorrow).
    • ttfkam 3y ago
      > Doesn't it seem weird that dozens of application languages have become popular since the 1970s, but we're still using dialects of the same old database query language? Indeed. Do you honestly believe that a half-century of data storage professionals and vendors are blindly moving forward with a hobbled tool? Or maybe there are aspects of SQL as a set-oriented 4th generation programming language that aren't apparent to folks who are intimately tied to an imperative or functional programming paradigm as opposed to a declarative DSL for set theory.
      • Zak 3y ago
        Within popular application languages, for any given paradigm, there are almost always several languages that aren't merely dialects of each other. C# isn't a dialect of Java. Ruby isn't a dialect of Python. Rust isn't a dialect of C++. PRQL demonstrates that a set-oriented declarative language need not be a dialect of SQL and isn't the first language to do so (QUEL appeared in the 1970s). It seems odd to me these alternatives haven't gained much popularity.
• wackget 3y ago
  Yeah the syntax comparison is deliberately misleading. They style it as "4 lines vs 10 lines!" when it's actually 4 lines vs 4 lines. # PRQL from employees select {id, first_name, age} sort age take 10 # Misleading SQL SELECT id, first_name, age FROM employees ORDER BY age LIMIT 10 # Actual SQL SELECT id, first_name, age FROM employees ORDER BY age LIMIT 10 The join example is similarly deceptive: # PRQL from employees join b=benefits (==employee_id) join side:left p=positions (p.id==employees.employee_id) select {employees.employee_id, p.role, b.vision_coverage} # Misleading SQL SELECT employees.employee_id, p.role, b.vision_coverage FROM employees JOIN benefits AS b ON employees.employee_id = b.employee_id LEFT JOIN positions AS p ON p.id = employees.employee_id # Actual SQL SELECT employees.employee_id, p.role, b.vision_coverage FROM employees JOIN benefits b USING employee_id LEFT JOIN positions p USING employee_id Nonsense.
  • OJFord 3y ago
    Also there's no need to capitalise, so the 4 lines vs. 4 lines is actually just 'order by & limit' vs. 'sort & take'. NBD really, but it's probably nicer in more complex examples. But I wanted about this the last time PRQL was submitted in its own right - https://news.ycombinator.com/item?id=36869376 https://news.ycombinator.com/item?id=36869376 - so I'll leave it there!
  • sebastianz 3y ago
    It is also (for me) quite a bit noisier and less readable than SQL. The join example has multiple different syntactic symbols, for no clear intuitive reason (= vs ==, {} vs (), side:left vs p=positions).
    • biglyburrito 3y ago
      I actually like it; it makes sense at a glance, without having read a single line of the official documentation. But I think people should be free to use either syntax -- method or query -- as they see fit, without judgment of which one is better.
  • 3y ago
• Izkata 3y ago
  > Putting the FROM first isn't sufficiently compelling on its own. Personally I see that as not even neutral, it's a downside. Optimizing for autocomplete is an antipattern, code is read far more often that it's written and the SELECT clause is the interface to the following code. It should be easy to find when skimming, not buried in the query. The SELECT clause is also akin to an assignment and it's extremely rare I see anyone advocating flipping the order of those to match what they say they want in SQL. Edit: Since I'm sure someone is going to jump on it, yes, I'm conflicted about the WITH clause: It's extremely useful and I like what it does, so I do use it, but I don't like where it's positioned. I've been toying with indentation to work around it so SELECT is still just as visible as otherwise.
• maximilianroos 3y ago
  [PRQL dev here] I strongly think we should have the best examples of SQL to compare against. I've ironically made this complaint for other libraries, so I'm alarmed that folks think we might have done the same. We would take PRs for any improvements to the SQL that make it a better comparison.
  • chrisjc 3y ago
    Not a criticism, more of an observation. The point of being able to write "FROM" before "SELECT" has become moot to me since many DBs support that SQL "convention" already. The newline/indentation is something I would do for readability in the PRQL too. At the end of the day, I'm becoming less concerned about these various query language syntaxes and more concerned about the logical and physical plans generated by the respective query engines. In my experience, what ends up most problematic is that each query-engine/optimizer sometimes/usually requires SQL tweaks/nuances to perform as efficiently as possible. That's where reading/writing/maintaining queries can get really confusing/difficult. An abstraction such as PRQL, dataframes, etc might add value here, but only if the abstraction (or runtime/evn built around it) can tune the output SQL to each query engine as it is needed. There are a lot of projects using frameworks/libraries such as Substrait and SQLGlot to accomplish this. https://substrait.io/ https://substrait.io/ https://github.com/tobymao/sqlglot https://github.com/tobymao/sqlglot