Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: PRQL 0.2 – a better SQL

github.com

Post reply on HN
Print view View original

41–50 of 166 posts

Re: Show HN: PRQL 0.2 – a better SQL

#41

Post by maximilianroos »

jitl wrote: post #37

I'm surprised that none of the examples on Github or the website deals with join. I eventually found some in the "book" here: https://prql-lang.org/book/transforms/join.html from employees join side:left positions [id==employee_id] turns into SELECT employees.*, positions.* FROM employees LEFT JOIN positions ON id = employee_id I would love to see joins worked into the main learning examples. Without join, the exampl…

Great point, we'll add that.

I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.

Re: Show HN: PRQL 0.2 – a better SQL

#42

Post by JohnDeHope »

aerzen wrote: post #23
paol wrote: post #15

Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.

Hello another contributor here! Compilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys. So joins without conditions would be possible, we'll look into it! What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an interme…

"What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?"

If it's not ambiguous, then let me do it. If I rely on ambiguity then throw an exception. In the case of multiple foreign keys, throw an exception, as there's no way to know which one I mean. It'd be nice if I could disambiguate the situation though. Normal SQL allows the `on` clause.

  from TableA
  inner join TableB on
What if I could specify a foreign key constraint just as easily...

  from TableA
  inner join TableB by ConstraintC
Where ConstraintC is the name of a foreign key constraint between Table A and Table B. It'd be nice to specify the constraint without having to specify the column name details.

The same goes for the many to many relationship with an intermediate table. It could look something like this...

  from TableA
  inner join TableB through TableC
I wouldn't introduce TableC into the scope of the statement. It's not in the FROM clause. It's used in the query but is not available for selecting from. If you want to bring in columns from it, join on it the usual way.

As applications grow, and initially simple lookup table semantics get more nuanced, it might be nice to be able to constrain the join on the lookup table like this...

  from TableA
  inner join TableB through TableC where
That way if my TableC has some extra columns, such as effective dates, or deleted flags, or that sort of thing, then I can filter out some of the joins that might usually happen.

Re: Show HN: PRQL 0.2 – a better SQL

#43

Post by aidos »

paol wrote: post #15

Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.

This is something you might end up regretting later.

It’s annoying adding another foreign key later and then having previously working queries fail at runtime due to an ambiguous join condition.

Re: Show HN: PRQL 0.2 – a better SQL

#44

Post by maximilianroos »

maximilianroos wrote: post #41
jitl wrote: post #37

I'm surprised that none of the examples on Github or the website deals with join. I eventually found some in the "book" here: https://prql-lang.org/book/transforms/join.html from employees join side:left positions [id==employee_id] turns into SELECT employees.*, positions.* FROM employees LEFT JOIN positions ON id = employee_id I would love to see joins worked into the main learning examples. Without join, the exampl…

Great point, we'll add that. I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.

Added: https://github.com/prql/prql/pull/697

Re: Show HN: PRQL 0.2 – a better SQL

#45

Post by hardwaregeek »

I've thought about building a better query language too. I'd love the ability to model sum types in databases, something like:

    enum SchoolType {
       College {
           degrees: Vec
       },
       HighSchool
    }
It's such a common pattern and yet it's so annoying to model in a normal relational database. I wouldn't be surprised if the rise of NoSQL is tied to the inability of relational databases to model basic patterns like this.

Part of me has wondered if a language is the solution. Maybe just a better query builder with support for sum types is necessary. But I suppose there's something useful about having a consistent model based around a language, even if people aren't writing the language directly.

Re: Show HN: PRQL 0.2 – a better SQL

#46

Post by snthpy »

qolop wrote: post #25

Why should I use this instead of SQL?

That's a really good question! (and one we should probably answer explicitly in the [FAQ](https://prql-lang.org/faq/) rather than just implicitly)

The README states that "PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. Like SQL, it's readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it transpiles to SQL."

What that means to me is that PRQL more naturally maps onto how I think about and work with data.

Say I have some dataset, `employees`, and I want to answer some questions about it like, for US employees, what is the maximum and minimum salary and how many employees are there:

    from employees
    filter country == "USA"                       # Each line transforms the previous result.
    aggregate [                                   # `aggregate` reduces column to a value.
      max salary,
      min salary,
      count,                                      # Closing     commas are allowed :)
    ]
Moreover, after each line you have a valid pipeline which you can transform further by adding more steps/lines to your pipeline. This matches more closely how people construct data pipelines in R using dplyr/tidyverse and in Python using Pandas.

If you find that it doesn't map well onto how you think about data pipelines then please let us know as we're constantly looking for more real world examples to help us iterate on the language!

Re: Show HN: PRQL 0.2 – a better SQL

#47

Post by jhgb »

hardwaregeek wrote: post #45

I've thought about building a better query language too. I'd love the ability to model sum types in databases, something like: enum SchoolType { College { degrees: Vec }, HighSchool } It's such a common pattern and yet it's so annoying to model in a normal relational database. I wouldn't be surprised if the rise of NoSQL is tied to the inability of relational databases to model basic patterns like this. Part of me ha…

I'm not sure that relational databases are "unable" to model something like this, since I recall from years ago Date describing how to do something like this. Don't remember the details, but you might want to look into Date's writings.

Re: Show HN: PRQL 0.2 – a better SQL

#48

Post by BeefWellington »

hardwaregeek wrote: post #45

I've thought about building a better query language too. I'd love the ability to model sum types in databases, something like: enum SchoolType { College { degrees: Vec }, HighSchool } It's such a common pattern and yet it's so annoying to model in a normal relational database. I wouldn't be surprised if the rise of NoSQL is tied to the inability of relational databases to model basic patterns like this. Part of me ha…

What is annoying about implementing something like this in a relational database?

Re: Show HN: PRQL 0.2 – a better SQL

#49

Post by kbenson »

JohnDeHope wrote: post #42
aerzen wrote: post #23

Earlier quoted context omitted.

Hello another contributor here! Compilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys. So joins without conditions would be possible, we'll look into it! What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an interme…

"What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?" If it's not ambiguous, then let me do it. If I rely on ambiguity then throw an exception. In the case of multiple foreign keys, throw an exception, as there's no way to know which one I mean. It'd be nice if I could disambiguate the situation thou…

Unambiguous things can become ambiguous at later points. As soon as you add a second relation between the tables, what once was unambiguous now is, and because of something which may be entirely unrelated to the specifics of the original query.

This is where many conveniences that use implicit data run into problems. A small convenience now for the possibility of accidentally breaking because of mostly unrelated changes later is a poor trade off for anyone that wants to have stable and consistent software.

This is likely one of those cases where you're better off with tooling to help make writing the correct unambiguous code easier (or automated away) than introducing a feature which leads to less stable systems in some cases.

Edit: Along the lines of what you note at the end, I would rather see joins able to use named relations as defined in the schema. Of there's a relation from table movie to table actor specifically names roles in the schema, I would rather be able to join movie on roles and have actors joined correctly using that relation, and aliases to roles which I could then use. Then you're using features that are designed and stable and not implicit and subject to changing how or whether they function based on semi-unrelated changes.

That might look like: "from movie relate roles" which is equivalent to "from movie join actor roles on movie.id = roles.movie_id", but because actor.movie_id has a constraint in the schema named roles which restricts it to a movie.id already.

Re: Show HN: PRQL 0.2 – a better SQL

#50

Post by BeefWellington »

scottlamb wrote: post #12
ComputerGuru wrote: post #7

From a mathematical point-of-view are there any transforms/operations (note: not end results, but actual operations) that this can do that SQL can't or vice-versa?

I assume it can't do anything SQL can't, because they write "It can be used with any database that uses SQL, since it transpiles to SQL." Not sure about the reverse. I'm used to SQL syntax, but this has definite appeal. As a small example, I like that it starts with the "from" clause, so autocomplete is more viable.

Transpiling to SQL doesn't mean all the underlying SQL features are being exposed to you.
Post reply on HN