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

111–120 of 166 posts

Re: Show HN: PRQL 0.2 – a better SQL

#111

Post by andyferris »

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 totally agree with this. Interesting point about NoSQL!

I'm not sure if it's just the query language though - the definition language needs to make creating columns that are sum types trivial. For one-to-many data this might be a slight generalization of foreign key (compound of table tag + foreign key for that table). This can work for one-to-one data too, but can be a bit annoying having lots of tables compared to doing adding a couple nullable columns (plus there's also data locality differences). I suppose a wrapper language that covers both DDL and DML could work.

Re: Show HN: PRQL 0.2 – a better SQL

#113

Post by strbean »

kbenson wrote: post #49
JohnDeHope wrote: post #42

Earlier quoted context omitted.

"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 chan…

That sounds like the perfect solution!

Re: Show HN: PRQL 0.2 – a better SQL

#114

Post by joelthelion »

Tangentially related, but does anyone know of a sql alternative that carries the execution plan with it? Sometimes you don't want a black box interpreter sitting between you and the database, so it would be nice to specify not only what you want, but also how to run it.

Re: Show HN: PRQL 0.2 – a better SQL

#115

Post by 1st1 »

maximilianroos wrote: post #109
1st1 wrote: post #104

I also recommend looking at EdgeQL -- https://www.edgedb.com/showcase/edgeql -- a new query language aimed to eliminate some of the SQL quirks. (I'm a co-founder)

I'm a huge fan of EdgeDB! Possibly our focus is a bit different — I see EdgeDB as primarily focused on transactional queries, whereas PRQL is very focused on analytical queries. PRQL doesn't do quite as much — e.g. we don't model the relationships between entities, which is less functional but more compatible. Feel free to reach out on Twitter if you think there's some way of us collaborating, or if you have any feed…

Replied on Twitter!

> I see EdgeDB as primarily focused on transactional queries, whereas PRQL is very focused on analytical queries.

That's true to an extent currently, but we actually envisioned EdgeQL to be a capable analytical query language too. We'll release EdgeDB 2.0 in a couple of weeks and it will feature a powerful GROUP BY statement (read more about it here [1]) and in 3.0 we might ship window functions (or some equivalent).

With all that said PRQL looks cool!

[1] https://github.com/edgedb/rfcs/blob/master/text/1009-group.r...

Re: Show HN: PRQL 0.2 – a better SQL

#116

Post by kiwicopple »

joelthelion wrote: post #114

Tangentially related, but does anyone know of a sql alternative that carries the execution plan with it? Sometimes you don't want a black box interpreter sitting between you and the database, so it would be nice to specify not only what you want, but also how to run it.

I don’t know if this directly answers your question, but Gurjeet is working on a Postgres extension that allows you to “lock in” a query plan: https://github.com/DrPostgres/pg_plan_guarantee

Re: Show HN: PRQL 0.2 – a better SQL

#118

Post by oarabbus_ »

digisign wrote: post #108
oarabbus_ wrote: post #83

Earlier quoted context omitted.

> a modernized syntax with variables to reuse subqueries would be lovely. CTEs provide this functionality already, don't they?

When I've needed them I've needed them for multiple statements, once is not enough. Currently have to use plpgsql for this, which is half awesome, half abomination. :-D A single simple language sounds easier to learn.

I'm not totally sure I follow, as you can re-reference/manipulate the subquery as much as needed. Is it for some kind of dynamic programming like finding a column containing a certain value

  SELECT cols from table where  like '%foobar%'"
which would need to dynamically insert values into the query

  select col1 from table where col1 like '%foobar%' union select col2 from table where col2 like '%foobar%' union ...
This type of usage is not possible/prohibitively difficult in standard SQL but I'm interested to know if it's a different use-case.

Re: Show HN: PRQL 0.2 – a better SQL

#119

Post by slaymaker1907 »

srcreigh wrote: post #65
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…

You'd have 1 table per sum type which requires extra data. Then polymorphic foreign key (aka a pair of fields school_type, school_id). (No foreign key constraints, but those are falling out of use in some cases due to inability to online migrate mysql schemas anyways.)

Yep, and you can also solve this problem by having a separate table for each variant and joining whenever you need to deal with common stuff. Personally, I think it's usually better just to have one giant table and allow the different columns to be null since it lets you avoid a bunch of verbose joins.

Re: Show HN: PRQL 0.2 – a better SQL

#120

Post by slaymaker1907 »

vore wrote: post #71
BeefWellington wrote: post #48

Earlier quoted context omitted.

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

It's not straightforward to do polymorphic joins: one common pattern is to have child tables for each case of the union, but there's no integrity constraint such that each parent must only have one child, e.g. CREATE TABLE schools (id SERIAL PRIMARY KEY); CREATE TABLE colleges (id INTEGER NOT NULL REFERENCES schools (id)); CREATE TABLE high_schools (id INTEGER NOT NULL REFERENCES schools (id)); How can you ensure tha…

Not to mention actually using this schema ends up being really verbose since you have to do a bunch of joins. Additionally, besides being verbose, these joins can wreak havoc with the optimizer since join optimization is exponential. The optimizer might play nicely and just join all these on the ID column in whatever query you're doing, but that is very dependent on how the optimizer understands the input queries. Having a single table instead of 3 limits the number of ways the optimizer can think about a particular query.
Post reply on HN