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

PRQL – A proposal for a better SQL

github.com

Post reply on HN
Print view View original

201–210 of 302 posts

Re: PRQL – A proposal for a better SQL

#201

Post by gunshai »

ako wrote: post #86
magicalhippo wrote: post #62

Now this is actually nice, unlike the other suggestion posted today[1]. Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gros…

With a CTE it would read a bit more like prql: with usa_employees as ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' AND (salary + payroll_tax + healthcare_cost) > 0 ) select title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_s…

My brain just thinks in CTEs over sub queries. I really dislike that my co-workers use these ridiculously nested sub sub sub queries.

I just look at something like this and I immediately know what's going on. If it's nested sub queries it always takes me much longer.

Re: PRQL – A proposal for a better SQL

#207

Post by mollerhoj »

Given that a lot of times (most of the time?) SQL is written by applications, not humans, how about a syntax based on some data language (e.g. json)? It always seemed strange to me that the API to our databases are (SQL) strings.. The order isn't even important.. Something like yaml or json seems like a better fit? Then of course it would be trivial to build other languages PRQL? on top of that..

Re: PRQL – A proposal for a better SQL

#208

Post by flukus »

makeitdouble wrote: post #193
hn_throwaway_99 wrote: post #180

Earlier quoted context omitted.

> On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler Hallelujah! But, to your footnote, this is a major reason why I despise ORMs. In my mind they make writing simple code slightly easier, but they make complicated SQ…

On ORMs, the best use I see of them is for “transparent” queries that you don’t define. Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter. That’s 98% of what we do against the DB, and I’m all for having it basically invisible. Then let’s just bypass the ORM altogether the minute we think about joining or grouping things…

With a middle ground like a micro ORM those transparent queries are barely visible anyway, literally a line or two lines of embededed sql strings. Especially micro ORMs that can handle dynamic filters. They're generally write once and only get looked at again when modifications are necessary, so they're not worth "optimizing" by adding the complexity of an ORM.

A common pattern seems to be over engineering these simple scenarios though. Someone decides that embedded sql is evil and needs to be extracted out of normal code, often to stored procs. Then these simple queries have enough friction that an ORM starts to look good, then you end up with an ORM generating simple queries dynamically in the same place that used to have a simple embedded string.

Re: PRQL – A proposal for a better SQL

#209

Post by api »

We don't need better SQL. We need a programming language with relational concepts built into its collections library and a syntax and type system that makes using it easy.

The fact that you have to go through all these intermediate layers to access your data is just stupid. Data should just be "there."

Re: PRQL – A proposal for a better SQL

#210

Post by dmoura »

I am writing a language and CLI that mixes SQL with Python (spyql: https://github.com/dcmoura/spyql), and it is interesting to see that we are tackling some of the same problems:

- code/formula reutilization where we need to repeat logic over the query

- functionalities like `EXCEPT` and `REPLACE` modifiers for `SELECT *`, like in google bigquery (in most SQL databases its frustrating when you have a large number of columns and you only want to hide or replace a couple of them)

I do think SQL is all over the place, and while not perfect, it’s familiar and we got used to express our queries the SQL way. At the end, since you are generating SQL to interact with databases, you would have to understand SQL in order to optimise your queries (it might be challenging to get the perfect SQL query from PRQL as you do not know statistics about the tables or which indexes are available).

With SPyQL I am taking a different approach that tries to extend simple SQL SELECT statements so that some of these annoying features are tackled. In addition, by using Python to define expressions and conditions you solve another problem typically present on databases: extensibility. By including an IMPORT clause in your query you can import any Python module, so the sky is the limit. You also get a simple and intuitive way to work with objects and hierarchical data (like JSON).

I do find the language you are proposing very readable and flexible, bringing several advantages to SQL. If you build a parser I would love to bring it to spyql :-) The issues I have brought earlier would not be a problem to spyql since it is a tool to query files and data-streams in the command-line.

Post reply on HN