17 ms·

Show HN: PRQL 0.2 – a better SQL

378 points by maximilianroos 4y ago

Hi everyone — thanks for your interest in PRQL — let us know any questions or feedback! We're excited to be releasing 0.2[1], the first version of PRQL you can use in your own projects. It wouldn't exist without the feedback we got from HackerNews when we originally posted the proposal. [1]: https://github.com/prql/prql/releases/tag/0.2.0 https://github.com/prql/prql/releases/tag/0.2.0

deleted 4y ago
[deleted]
ewuhic 4y ago
Previous discussion: PRQL – A proposal for a better SQL https://news.ycombinator.com/item?id=30060784 https://news.ycombinator.com/item?id=30060784
nawgz 4y ago
Is it true that this is somehow an analogue to how JS development is really ultimately targeting browser-compatible-JS in the end, even though we use the latest ECMAScript features & TypeScript in development? I.e. is it expected someone writes PRQL and then transpiles before executing against the database? Is there a REPL one can use against a local Postgres or something?
- maximilianroos 4y ago
  > Is there a REPL one can use against a local Postgres or something? Somewhat — you can use it in Jupyter now[1]; e.g.: %%prql from p = products.csv group categoryID ( aggregate [average unitPrice] ) This doesn't yet have the benefits we'd get from e.g. autocomplete, so there's much more to do there. There's also a cool TUI in PyPrql[2] [1]: https://pyprql.readthedocs.io/en/latest/magic_readme.html https://pyprql.readthedocs.io/en/latest/magic_readme.html [2]: https://pyprql.readthedocs.io/en/latest/readme.html https://pyprql.readthedocs.io/en/latest/readme.html
dvirsky 4y ago
Nice. A few years ago I designed a query language to do aggregations in RediSearch, and it's quite similar in its structure. https://redis.io/docs/stack/search/reference/aggregations/ https://redis.io/docs/stack/search/reference/aggregations/
cogman10 4y ago
Awesome to see the progress here. Looks like the language has significantly matured since last it popped up on HN.
ComputerGuru 4y ago
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?
- scottlamb 4y ago
  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.
  - ComputerGuru 4y ago
    Thanks, I missed that it transpiles to SQL.
  - BeefWellington 4y ago
    Transpiling to SQL doesn't mean all the underlying SQL features are being exposed to you.
    - scottlamb 4y ago
      Yes, that's what I meant by "not sure about the reverse".
- aerzen 4y ago
  As said, currently PRQL transpiles to SQL, so all expressions in PRQL are can be expressed in SQL. But not all SQL expression can be translated back into PRQL - some intentionally and some are just not yet implemented (UNION - i.e. vertical concat). But we also have plans for doing things that some SQL databases may not support, such as pivot (rows to columns).
  - coremoff 4y ago
    hopefully you'll forgive my pedantry - "union all" is vertical concat - "union" without the "all" gives you the distinct list
roG0d 4y ago
I've been contributing to this project on a few little things due to my little knowledge level. But I felt like home with such a good company of people!. I not mature enough to fully appreciate the technical potential of the project, but the good ambient, the kindness and the growth potential is for sure worthwhile. I truly encourage everyone to contribute!
- cogman10 4y ago
  In particular, I think this is looking pretty good and I'd want to see even more complicated examples. For example, What do window functions end up looking like? [1] What about crazy operations like calculating percentile_cont? [2] Or just in general, how would "implementation specific" queries end up looking? [1] https://www.postgresql.org/docs/current/tutorial-window.html https://www.postgresql.org/docs/current/tutorial-window.html [2] https://docs.microsoft.com/en-us/sql/t-sql/functions/percentile-cont-transact-sql?view=sql-server-ver16 https://docs.microsoft.com/en-us/sql/t-sql/functions/percent...
  - maximilianroos 4y ago
    Great questions! Window functions are here [1]. (We should add these to the homepage too) Implementation specific queries can be handled by the Dialect parameter [2], though there's still lots of work to do to build that out. [1]: https://prql-lang.org/book/transforms/window.html https://prql-lang.org/book/transforms/window.html [2]: https://prql-lang.org/book/queries/dialect_and_version.html https://prql-lang.org/book/queries/dialect_and_version.html
    - maximilianroos 4y ago
      Window example is now on the homepage, thanks for the question: https://github.com/prql/prql/pull/692 https://github.com/prql/prql/pull/692
p33p 4y ago
This looks great. I've thought about something similar to this for quite a while now. Column autocomplete is key for me from a quality of life perspective and to make it truly usable. I'd absolutely love to see the next level of this pipeline be continued where something like Observable Plot or ggplot2 like functionality where you can take your pipeline data analysis and directly plot it to visualize it.
davidw 4y ago
A good example might be a groupwise maximum. Those always tend to be a bit of a PITA in SQL if you're not writing them regularly. Be interesting to see what it transpiles to, as well.
- aerzen 4y ago
  If you only want maximum of one column, the PRQL is quite simple: from my_table group column_a ( aggregate (max column_b) ) If you want the row with the maximum value it gets interesting: from my_table group column_a ( sort [-column_b] take 1 ) You can read more about group here: https://prql-lang.org/book/transforms/group.html https://prql-lang.org/book/transforms/group.html
  - beaugunderson 4y ago
    Opened an issue as I couldn't get this to work against sqlite: https://github.com/prql/prql/issues/695 https://github.com/prql/prql/issues/695
    - maximilianroos 4y ago
      Thanks a lot for testing and opening an issue! This is now fixed and released [1]. Let us know if you still face any problems. (We need better tests against real DBs, which is very much on our roadmap) [1]: https://github.com/prql/prql/pull/698 https://github.com/prql/prql/pull/698
      - beaugunderson 4y ago
        Thank you for the amazingly quick fix!
paol 4y ago
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.
- aidos 4y ago
  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.
  - paulhodge 4y ago
    Agree about not implicitly finding the join key. But as long as we're brainstorming imaginary features, then maybe as part of the schema, we could somehow declare the default join key to use, for any given two tables. In most cases it's pretty obvious what the best join key would be.
- aerzen 4y ago
  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 intermediate table?
  - JohnDeHope 4y ago
    "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 <expression> 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 <expression> 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.
MasterIdiot 4y ago
I've seen similar solutions being built internally in multiple companies, none with a syntax as well thought out as this. Amazing work!
slotrans 4y ago
Hot takes: SQL is great, actually. This thing isn't better.
- breakfastduck 4y ago
  As much as I love SQL it can often be a pain and involve lots of nested subqueries to do 'simple' things. I like this way this abstracts it. Would I use this instead of proper SQL in a data warehouse / large app? Maybe not. Would I use it to manually query DBs when I need some ad hoc info? For sure.
  - notimportant0 4y ago
    If this becomes a full-fledge DQL that can be used in a proc or function in a running Postgres instance, I would use it in production.
- mellosouls 4y ago
  No, but it's not as foreign a paradigm or language to (presumably?) its main target audience - developers.
- catchclose8919 4y ago
  Not sure if this is better, but SQL is HORRIBLE... we probably put up with it bc it's based on sane math & theory, and we almost never write it by hand. There's zero thought to any kind of ergonomics, there's no way to say "join table Y but prefix all its columns with employee_", it's expressed backwards ffs (instead of starting with FROM), results of queries with joins are forced to be flat tables and there's no way to get trees as you need 99% in app code - all the repetitve app code to "nest" entities in results that also needs to make brittles assumptions about ordering and uniqueness because people couldn't standardize on a "RESULT AS TREE [NESTING <Y> INTO <X>]" clause or smth. equivalent etc. etc. PRQL though seems to also lack all the essetial features you would expect around joins. Suff like Arrango DB's AQL seems to be a nice example of adding the missing feature to SQL, probably more of the need to accomodate graph data too, but it actually solves SQLs problems even in relational contexts - see https://www.arangodb.com/docs/stable/aql/tutorial-join.html#merging-characters-and-traits https://www.arangodb.com/docs/stable/aql/tutorial-join.html#... .
qolop 4y ago
Why should I use this instead of SQL?
- ithrow 4y ago
  To avoid working with SQL strings.
  - hui-zheng 4y ago
    SQL/jinja like dbt could also avoid working with SQL strings. what would be the better advantage?
    - snthpy 4y ago
      There is already an integration for dbt: https://github.com/prql/dbt-prql https://github.com/prql/dbt-prql For example {% prql %} from source = {{ source('salesforce', 'in_process') }} derive expected_sales = probability * value join {{ ref('team', 'team_sales') }} [name] group name ( aggregate (sum expected_sales) ) {% endprql %} would appear to dbt as SELECT name, SUM(source.probability * source.value) AS expected_sales FROM {{ source('salesforce', 'in_process') }} AS source JOIN {{ ref('team', 'team_sales') }} USING(name) GROUP BY name dbt is definitely a use case we are very aware of and I am personally very keen on (since I use that in my $dayjob). With some of the ideas in https://github.com/prql/prql/issues/381 https://github.com/prql/prql/issues/381 , I think PRQL could really shine in this area! With your contribution we can get there faster!
- snthpy 4y ago
  That's a really good question! (and one we should probably answer explicitly in the [FAQ](https://prql-lang.org/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!
OJFord 4y ago
Somehow I didn't see it coming - > pronounced "Prequel". - and I burst out laughing. Very good.
- foreigner 4y ago
  If you appreciated that check out "Franchise - a notebook SQL client. What you get when you have a lot of sequels": https://github.com/MobilityDB/Franchise https://github.com/MobilityDB/Franchise
elforce002 4y ago
This is really a SQL renaissance.
ranjanprj 4y ago
Great project, was looking something like this but wish this was somehow encoded as JSON, so you could easily build pipeline UI for complex SQL Generation.
rackjack 4y ago
Obligatory dismissive comment: > 0.2 No it ain't (in production). Anyway, this looks great. I LOVE the fact that you've provided a book too. Consider me a fan!
- maximilianroos 4y ago
  We're definitely not ready for production! Sorry if that was implied. But we are ready for people to start using it in their development work. Lmk if there's a better way of describing that.
Flimm 4y ago
Looks fantastic. There are a lot of rough edges when building a string representing an SQL query in the programming language that you're using. You have to be careful to avoid SQL injections, for starters. Do the bindings for PRQL innovate at this level?
- setr 4y ago
  SQL injections will always be a thing, regardless of SQL vs Not-SQL, if you’re building strings to represent programs. Parameterization is precisely how you properly differentiate between code and data, and it’d be the same strategy no matter the language/system.
jitl 4y ago
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 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 examples lack a bit of the "relation" part; we could just as easily be compiling a DSL to a chain of `array.filter`, `array.reduce`, `array.map` calls. Joins are what makes relational modeling interesting! I would love to see Datalog/SPARQL-style implicit joins to make graph traversals like "which users have edited documents I own?" less verbose.
- maximilianroos 4y ago
  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.
  - maximilianroos 4y ago
    Added: https://github.com/prql/prql/pull/697 https://github.com/prql/prql/pull/697
  - igorkraw 4y ago
    I looked at the book after this and have to say, I'd heavily recommend spending the next dew months just improving joins (and complex joins especially). Like GP says, relational modelling is the interesting bit about SQL and I don't feel exaggerative in saying the only reason I use SQL are joins, and so the only reason I'd introduce the complexity of your project into my stack would be if it makes handling joins, views and other aspects of relational modeling and slicing nicer - one example could be many to many relationships, or the gradient between graph/document based and normalised table based modeling
    - 4y ago
deleted 4y ago
[deleted]
jeroen79 4y ago
SQL doesn't need fixing or improving if you ask me, its well supported and just works.
hardwaregeek 4y ago
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<Degree> }, 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.
- jhgb 4y ago
  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.
- BeefWellington 4y ago
  What is annoying about implementing something like this in a relational database?
  - vore 4y ago
    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 that a school is either a college or high_school but not both? Another alternative is to make one big table with check constraints but that's also hairy in its own right: CREATE TYPE school_type AS ENUM ('college', 'high_school'); CREATE TABLE schools ( id SERIAL PRIMARY KEY, type school_type, /* college columns */, /* high school columns */, CHECK (type = 'college' AND /* college column constraints */), CHECK (type = 'high_school' AND /* high school column constraints */) ); The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending on your database, it can also be painful to renumber the other items.
pgt 4y ago
For those interested in database query languages, it is worth knowing about Datalog, the query language behind Datomic, XTDB and Datahike: http://www.learndatalogtoday.org/ http://www.learndatalogtoday.org/ E.g. a parameterised aggregate query that retrieves the name and average rating of a film starring cast members whose names match the input names: [:find ?name (avg ?rating) :in $ [?name ...] [[?title ?rating]] :where [?p :person/name ?name] [?m :movie/cast ?p] [?m :movie/title ?title]] To reveal the answer, click on tab labelled "3" and then "I give up!": http://www.learndatalogtoday.org/chapter/7 http://www.learndatalogtoday.org/chapter/7
- jitl 4y ago
  This is the Datomic/Clojure dialect of Datalog. I had an easier time learning a stand-alone datalog variant. I think compiling Datalog to SQL is an interesting idea. I wrote a toy Datalog -> SQLite compiler: https://percival.jake.tl/ https://percival.jake.tl/ Other Datalog -> SQL compilers I know of: - Originally from Mozilla, now independent: https://github.com/qpdb/mentat https://github.com/qpdb/mentat - From Google: https://logica.dev/ https://logica.dev/
  - techplex 4y ago
    See also - https://github.com/sqwishy/owoof https://github.com/sqwishy/owoof
airtnp 4y ago
This sounds like LINQ, or SparkSQL. Instead of a full new language, it makes feel better to create libraries in languages that supports embedded DSL easily.
jhgb 4y ago
I wonder, why would you go for a "pipeline" of relational operations, when it's strictly weaker than allowing for a tree of operations? The way the examples seem to be written, a stack machine would subsume the existing syntax, since you first specify an operand (like "from employees") and then you specify and operation (like "filter country == "USA"), where in a stack machine an operation such as "from X" would put the relation X onto the top of stack, whereas an operation such as "filter" would then replace the top of stack with a transformed relation. This could be extended by for example "join on ..." being simply an operation consuming two relations from the top of stack and putting one result back, joining two pipelines into one.
bornfreddy 4y ago
Looks awesome! I don't think it adds much to SQL when the queries are simple, but when you have this looong and complex query I can totally see the appeal.
vander_elst 4y ago
Congrats for the milestone!! The syntax looks more intuitive than SQL. Great to see viable alternatives to SQL!
digisign 4y ago
Thanks, I've frequently wanted a query language that was designed after the 70s. The ideas are sound, but a modernized syntax with variables to reuse subqueries would be lovely. This looks like it. I noticed one issue though... please don't copy the prefix of f-strings! That only exists because Python boxed itself in and it was literally the only ascii syntax left that could be used for string interpolation. It's mildly ugly but the best that could be done given those requirements. Not so here. The way shells do it with single quotes producing literal strings and double quotes available for interpolation has not been topped imho. Triple quotes are a nice extension as well, not sure if that made it in.
- aerzen 4y ago
  Interesting suggestion. We added f-strings because we already had s-strings (pass trough to SQL) and r-strings (for raw multi-line text). And would you rather see "My {name}" or "My ${name}"? I personally dislike the $ prefix for all variables and interpolations...
  - psychoslave 4y ago
    Languages like Perl, Ruby and more offer plethora of additional ways to encode interpolated strings. I especially love the squiggly heredoc for multi line quotations https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-squiggly-heredoc/ https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-sq...
  - Kinrany 4y ago
    Choose backticks as the quote style for interpolation to attract Markdown fans and confuse the hell out of MySQL users :D
  - digisign 4y ago
    The first one, the $ is redundant if braces required. Multi-line could be triple quoted. SQL, that one I'm not so sure.
- oarabbus_ 4y ago
  > a modernized syntax with variables to reuse subqueries would be lovely. CTEs provide this functionality already, don't they?
notimportant0 4y ago
I mostly work using T-SQL but I like PRQL. Are you able to add in the examples the following: 1) Use of delimiters for names that include space, etc. I don't know if PRQL uses double quote or square brackets. 2) Use of two/three/four-naming convention to refer to servers, databases, tables and columns.
ssalka 4y ago
I just noticed the play on words "prequel" vs "sequel". Nice.
emmelaich 4y ago
Doesn't the pipelining mean that it's not declarative? At least not in that part.
oarabbus_ 4y ago
Is it correct there's no CASE WHEN and instead you have to define a function using a ternary operator? CASE WHENs may be verbose but when you have a dozen of them they're more readable and the waterfall nature is far preferable to a giant block of ternary clauses.
skybrian 4y ago
I see that the JavaScript package is at [1] and it's implemented by compiling the Rust code to WASM. That should eventually make it pretty easy to run it. It has a typescript definition file, but it looks like it's autogenerated and a bit clunky. You get back a CompileResult and have to call free() explicitly, it seems? That doesn't seem very idiomatic for JavaScript. Also, the links to the documentation and examples in the README are broken. [1] https://www.npmjs.com/package/prql-js https://www.npmjs.com/package/prql-js
- maximilianroos 4y ago
  Thanks a lot — issue added: https://github.com/prql/prql/issues/708 https://github.com/prql/prql/issues/708
- aerzen 4y ago
  That's true - the package is auto-generated using [wasmpak](https://github.com/rustwasm/wasm-pack https://github.com/rustwasm/wasm-pack), that's why TypeScript definitions are clunky. I did the initial prql-js release and I'm actually not sure about the free() issue you are talking about. We are currently working on compiling it for both Node.js and the browser target, and would be happy to see some advice if you are familiar with WASM!
  - skybrian 4y ago
    I didn't actually try it out and I'm not all that familiar with WASM. Here is the typescript I see (stripped of boilerplate comments): export function compile(s: string): CompileResult; export class CompileResult { free(): void; readonly error: CompileError | undefined; readonly sql: string | undefined; } What is the purpose of the free() method?
gigatexal 4y ago
I’m a raw-SQL-no-ORM snob and I really like this. I’d like to see it become more mainstream.
michelpp 4y ago
It seems like an obvious next step for Postgres support would be to make PRQL a stored Procedure Language. create function foo() returns bar as language prql $$<prql code here>$$;
exabrial 4y ago
I don't really find SQL that difficult, other than NULL !=NULL, I wish <=> could be switched to the default!
dmeijboom 4y ago
I recently started implementing the Postgres protocol in Rust (https://github.com/dmeijboom/postgres-conn https://github.com/dmeijboom/postgres-conn). So I guess I’ll be experimenting with creating a Postgres proxy which translates PRQL on-the-fly.
- maximilianroos 4y ago
  That looks really exciting! Please keep us in touch with your efforts and let us know if there's any way we can be helpful.
1st1 4y ago
I also recommend looking at EdgeQL -- https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql -- a new query language aimed to eliminate some of the SQL quirks. (I'm a co-founder)
- maximilianroos 4y ago
  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 feedback or guidance for us.
  - 1st1 4y ago
    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.rst https://github.com/edgedb/rfcs/blob/master/text/1009-group.r...
metaltyphoon 4y ago
Looks a lot like LINQ
joelthelion 4y ago
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.
- kiwicopple 4y ago
  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 https://github.com/DrPostgres/pg_plan_guarantee
intelijstupide 4y ago
__mharrison__ 4y ago
This reads a lot like the style of Pandas I teach.[0] This seems to have resolved a problem with SQL, you can't read it in a linear fashion. When we start getting query optimizers for Pandas, much of the benefit of SQL will go away. [0] https://store.metasnake.com/effective-pandas-book https://store.metasnake.com/effective-pandas-book
- aljazmerzen 4y ago
  What is a query optimizer for Pandas?
alphanumeric0 4y ago
I find SQL harder to work with because I'm used to reading and writing functions - functions that have explicit parameters with (hopefully) explicit types, and explicit return types. So I was hoping PRQL might have some sugar like that. It'd be nicer for my brain if I could treat any select like a function that transforms a set. Instead, even with PRQL, I'm stuck examining source tables and fully understanding them before being able to understand the query.
- mandaputtra 4y ago
  Is this like what ORM for?
  - alphanumeric0 4y ago
    Yeah, but without the objects.
eterevsky 4y ago
Is there a representation in PRQL of UNNEST and a sub-select over an array within a row? We use it quite extensively in the DB that I most often work with.
creamyhorror 4y ago
Wow! This is cool stuff. I was looking through the documentation for conditional logic, control flow, IF(), and the CASE...WHEN...THEN operator. It seems like the ternary operator is the single way to implement conditionality? At https://prql-lang.org/book/examples/functions.html https://prql-lang.org/book/examples/functions.html I found the example `func if_valid x`, which is then used in the `derive` expression `prices_adj | ret | if_valid`. This usage of `if_valid` at the end of the pipeline seems a bit awkward: if I want to do the whole calculation only if some condition is met, I'd like to write it at the front of the pipeline. Can I nest the `ret` function in the if-function like so?: `func ret_if_valid x -> is_valid_price ? (ret x) : null` Then I guess I'd have to do: return_total = prices_adj | ret_if_valid Next, can a string variable be used as part of a column name? It's something I needed recently in order to categorise values and use them to do a pivot. For example, I had to do: SELECT CASE category WHEN 'a' THEN 'x' AS newCategory ... SELECT SUM( if(newCategory = 'a', revenue, 0) ) AS aRevenue, ... Ideally I'd like to do: aggregate [ for x in ['a', 'b', 'c']: (concat x 'Revenue') = sum ( [new_category == x] ? revenue : 0 ), ...] i.e. I'd like to use x as part of the final column name. A pivot example would be great (or maybe you already have a PIVOT implementation in mind). I'd be interested in contributing to this, guess I'll take a look at the code and any community chat you've set up!

deleted 4y ago

[deleted]

ewuhic 4y ago

Previous discussion: PRQL – A proposal for a better SQL https://news.ycombinator.com/item?id=30060784 https://news.ycombinator.com/item?id=30060784

nawgz 4y ago

Is it true that this is somehow an analogue to how JS development is really ultimately targeting browser-compatible-JS in the end, even though we use the latest ECMAScript features & TypeScript in development? I.e. is it expected someone writes PRQL and then transpiles before executing against the database? Is there a REPL one can use against a local Postgres or something?

maximilianroos 4y ago
> Is there a REPL one can use against a local Postgres or something? Somewhat — you can use it in Jupyter now[1]; e.g.: %%prql from p = products.csv group categoryID ( aggregate [average unitPrice] ) This doesn't yet have the benefits we'd get from e.g. autocomplete, so there's much more to do there. There's also a cool TUI in PyPrql[2] [1]: https://pyprql.readthedocs.io/en/latest/magic_readme.html https://pyprql.readthedocs.io/en/latest/magic_readme.html [2]: https://pyprql.readthedocs.io/en/latest/readme.html https://pyprql.readthedocs.io/en/latest/readme.html

dvirsky 4y ago

Nice. A few years ago I designed a query language to do aggregations in RediSearch, and it's quite similar in its structure. https://redis.io/docs/stack/search/reference/aggregations/ https://redis.io/docs/stack/search/reference/aggregations/

cogman10 4y ago

Awesome to see the progress here. Looks like the language has significantly matured since last it popped up on HN.

ComputerGuru 4y ago

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?

scottlamb 4y ago
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.
- ComputerGuru 4y ago
  Thanks, I missed that it transpiles to SQL.
- BeefWellington 4y ago
  Transpiling to SQL doesn't mean all the underlying SQL features are being exposed to you.
  - scottlamb 4y ago
    Yes, that's what I meant by "not sure about the reverse".
aerzen 4y ago
As said, currently PRQL transpiles to SQL, so all expressions in PRQL are can be expressed in SQL. But not all SQL expression can be translated back into PRQL - some intentionally and some are just not yet implemented (UNION - i.e. vertical concat). But we also have plans for doing things that some SQL databases may not support, such as pivot (rows to columns).
- coremoff 4y ago
  hopefully you'll forgive my pedantry - "union all" is vertical concat - "union" without the "all" gives you the distinct list

roG0d 4y ago

I've been contributing to this project on a few little things due to my little knowledge level. But I felt like home with such a good company of people!. I not mature enough to fully appreciate the technical potential of the project, but the good ambient, the kindness and the growth potential is for sure worthwhile. I truly encourage everyone to contribute!

cogman10 4y ago
In particular, I think this is looking pretty good and I'd want to see even more complicated examples. For example, What do window functions end up looking like? [1] What about crazy operations like calculating percentile_cont? [2] Or just in general, how would "implementation specific" queries end up looking? [1] https://www.postgresql.org/docs/current/tutorial-window.html https://www.postgresql.org/docs/current/tutorial-window.html [2] https://docs.microsoft.com/en-us/sql/t-sql/functions/percentile-cont-transact-sql?view=sql-server-ver16 https://docs.microsoft.com/en-us/sql/t-sql/functions/percent...
- maximilianroos 4y ago
  Great questions! Window functions are here [1]. (We should add these to the homepage too) Implementation specific queries can be handled by the Dialect parameter [2], though there's still lots of work to do to build that out. [1]: https://prql-lang.org/book/transforms/window.html https://prql-lang.org/book/transforms/window.html [2]: https://prql-lang.org/book/queries/dialect_and_version.html https://prql-lang.org/book/queries/dialect_and_version.html
  - maximilianroos 4y ago
    Window example is now on the homepage, thanks for the question: https://github.com/prql/prql/pull/692 https://github.com/prql/prql/pull/692

p33p 4y ago

This looks great. I've thought about something similar to this for quite a while now. Column autocomplete is key for me from a quality of life perspective and to make it truly usable. I'd absolutely love to see the next level of this pipeline be continued where something like Observable Plot or ggplot2 like functionality where you can take your pipeline data analysis and directly plot it to visualize it.

davidw 4y ago

A good example might be a groupwise maximum. Those always tend to be a bit of a PITA in SQL if you're not writing them regularly. Be interesting to see what it transpiles to, as well.

aerzen 4y ago
If you only want maximum of one column, the PRQL is quite simple: from my_table group column_a ( aggregate (max column_b) ) If you want the row with the maximum value it gets interesting: from my_table group column_a ( sort [-column_b] take 1 ) You can read more about group here: https://prql-lang.org/book/transforms/group.html https://prql-lang.org/book/transforms/group.html
- beaugunderson 4y ago
  Opened an issue as I couldn't get this to work against sqlite: https://github.com/prql/prql/issues/695 https://github.com/prql/prql/issues/695
  - maximilianroos 4y ago
    Thanks a lot for testing and opening an issue! This is now fixed and released [1]. Let us know if you still face any problems. (We need better tests against real DBs, which is very much on our roadmap) [1]: https://github.com/prql/prql/pull/698 https://github.com/prql/prql/pull/698
    - beaugunderson 4y ago
      Thank you for the amazingly quick fix!

paol 4y ago

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.

aidos 4y ago
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.
- paulhodge 4y ago
  Agree about not implicitly finding the join key. But as long as we're brainstorming imaginary features, then maybe as part of the schema, we could somehow declare the default join key to use, for any given two tables. In most cases it's pretty obvious what the best join key would be.
aerzen 4y ago
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 intermediate table?
- JohnDeHope 4y ago
  "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 <expression> 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 <expression> 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.

MasterIdiot 4y ago

I've seen similar solutions being built internally in multiple companies, none with a syntax as well thought out as this. Amazing work!

slotrans 4y ago

Hot takes: SQL is great, actually. This thing isn't better.

breakfastduck 4y ago
As much as I love SQL it can often be a pain and involve lots of nested subqueries to do 'simple' things. I like this way this abstracts it. Would I use this instead of proper SQL in a data warehouse / large app? Maybe not. Would I use it to manually query DBs when I need some ad hoc info? For sure.
- notimportant0 4y ago
  If this becomes a full-fledge DQL that can be used in a proc or function in a running Postgres instance, I would use it in production.
mellosouls 4y ago
No, but it's not as foreign a paradigm or language to (presumably?) its main target audience - developers.
catchclose8919 4y ago
Not sure if this is better, but SQL is HORRIBLE... we probably put up with it bc it's based on sane math & theory, and we almost never write it by hand. There's zero thought to any kind of ergonomics, there's no way to say "join table Y but prefix all its columns with employee_", it's expressed backwards ffs (instead of starting with FROM), results of queries with joins are forced to be flat tables and there's no way to get trees as you need 99% in app code - all the repetitve app code to "nest" entities in results that also needs to make brittles assumptions about ordering and uniqueness because people couldn't standardize on a "RESULT AS TREE [NESTING <Y> INTO <X>]" clause or smth. equivalent etc. etc. PRQL though seems to also lack all the essetial features you would expect around joins. Suff like Arrango DB's AQL seems to be a nice example of adding the missing feature to SQL, probably more of the need to accomodate graph data too, but it actually solves SQLs problems even in relational contexts - see https://www.arangodb.com/docs/stable/aql/tutorial-join.html#merging-characters-and-traits https://www.arangodb.com/docs/stable/aql/tutorial-join.html#... .

qolop 4y ago

Why should I use this instead of SQL?

ithrow 4y ago
To avoid working with SQL strings.
- hui-zheng 4y ago
  SQL/jinja like dbt could also avoid working with SQL strings. what would be the better advantage?
  - snthpy 4y ago
    There is already an integration for dbt: https://github.com/prql/dbt-prql https://github.com/prql/dbt-prql For example {% prql %} from source = {{ source('salesforce', 'in_process') }} derive expected_sales = probability * value join {{ ref('team', 'team_sales') }} [name] group name ( aggregate (sum expected_sales) ) {% endprql %} would appear to dbt as SELECT name, SUM(source.probability * source.value) AS expected_sales FROM {{ source('salesforce', 'in_process') }} AS source JOIN {{ ref('team', 'team_sales') }} USING(name) GROUP BY name dbt is definitely a use case we are very aware of and I am personally very keen on (since I use that in my $dayjob). With some of the ideas in https://github.com/prql/prql/issues/381 https://github.com/prql/prql/issues/381 , I think PRQL could really shine in this area! With your contribution we can get there faster!
snthpy 4y ago
That's a really good question! (and one we should probably answer explicitly in the [FAQ](https://prql-lang.org/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!

OJFord 4y ago

Somehow I didn't see it coming - > pronounced "Prequel". - and I burst out laughing. Very good.

foreigner 4y ago
If you appreciated that check out "Franchise - a notebook SQL client. What you get when you have a lot of sequels": https://github.com/MobilityDB/Franchise https://github.com/MobilityDB/Franchise

elforce002 4y ago

This is really a SQL renaissance.

ranjanprj 4y ago

Great project, was looking something like this but wish this was somehow encoded as JSON, so you could easily build pipeline UI for complex SQL Generation.

rackjack 4y ago

Obligatory dismissive comment: > 0.2 No it ain't (in production). Anyway, this looks great. I LOVE the fact that you've provided a book too. Consider me a fan!

maximilianroos 4y ago
We're definitely not ready for production! Sorry if that was implied. But we are ready for people to start using it in their development work. Lmk if there's a better way of describing that.

Flimm 4y ago

Looks fantastic. There are a lot of rough edges when building a string representing an SQL query in the programming language that you're using. You have to be careful to avoid SQL injections, for starters. Do the bindings for PRQL innovate at this level?

setr 4y ago
SQL injections will always be a thing, regardless of SQL vs Not-SQL, if you’re building strings to represent programs. Parameterization is precisely how you properly differentiate between code and data, and it’d be the same strategy no matter the language/system.

jitl 4y ago

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 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 examples lack a bit of the "relation" part; we could just as easily be compiling a DSL to a chain of `array.filter`, `array.reduce`, `array.map` calls. Joins are what makes relational modeling interesting! I would love to see Datalog/SPARQL-style implicit joins to make graph traversals like "which users have edited documents I own?" less verbose.

maximilianroos 4y ago
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.
- maximilianroos 4y ago
  Added: https://github.com/prql/prql/pull/697 https://github.com/prql/prql/pull/697
- igorkraw 4y ago
  I looked at the book after this and have to say, I'd heavily recommend spending the next dew months just improving joins (and complex joins especially). Like GP says, relational modelling is the interesting bit about SQL and I don't feel exaggerative in saying the only reason I use SQL are joins, and so the only reason I'd introduce the complexity of your project into my stack would be if it makes handling joins, views and other aspects of relational modeling and slicing nicer - one example could be many to many relationships, or the gradient between graph/document based and normalised table based modeling
  - 4y ago

deleted 4y ago

[deleted]

jeroen79 4y ago

SQL doesn't need fixing or improving if you ask me, its well supported and just works.

hardwaregeek 4y ago

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<Degree> }, 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.

jhgb 4y ago
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.
BeefWellington 4y ago
What is annoying about implementing something like this in a relational database?
- vore 4y ago
  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 that a school is either a college or high_school but not both? Another alternative is to make one big table with check constraints but that's also hairy in its own right: CREATE TYPE school_type AS ENUM ('college', 'high_school'); CREATE TABLE schools ( id SERIAL PRIMARY KEY, type school_type, /* college columns */, /* high school columns */, CHECK (type = 'college' AND /* college column constraints */), CHECK (type = 'high_school' AND /* high school column constraints */) ); The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending on your database, it can also be painful to renumber the other items.

pgt 4y ago

For those interested in database query languages, it is worth knowing about Datalog, the query language behind Datomic, XTDB and Datahike: http://www.learndatalogtoday.org/ http://www.learndatalogtoday.org/ E.g. a parameterised aggregate query that retrieves the name and average rating of a film starring cast members whose names match the input names: [:find ?name (avg ?rating) :in $ [?name ...] [[?title ?rating]] :where [?p :person/name ?name] [?m :movie/cast ?p] [?m :movie/title ?title]] To reveal the answer, click on tab labelled "3" and then "I give up!": http://www.learndatalogtoday.org/chapter/7 http://www.learndatalogtoday.org/chapter/7

jitl 4y ago
This is the Datomic/Clojure dialect of Datalog. I had an easier time learning a stand-alone datalog variant. I think compiling Datalog to SQL is an interesting idea. I wrote a toy Datalog -> SQLite compiler: https://percival.jake.tl/ https://percival.jake.tl/ Other Datalog -> SQL compilers I know of: - Originally from Mozilla, now independent: https://github.com/qpdb/mentat https://github.com/qpdb/mentat - From Google: https://logica.dev/ https://logica.dev/
- techplex 4y ago
  See also - https://github.com/sqwishy/owoof https://github.com/sqwishy/owoof

airtnp 4y ago

This sounds like LINQ, or SparkSQL. Instead of a full new language, it makes feel better to create libraries in languages that supports embedded DSL easily.

jhgb 4y ago

I wonder, why would you go for a "pipeline" of relational operations, when it's strictly weaker than allowing for a tree of operations? The way the examples seem to be written, a stack machine would subsume the existing syntax, since you first specify an operand (like "from employees") and then you specify and operation (like "filter country == "USA"), where in a stack machine an operation such as "from X" would put the relation X onto the top of stack, whereas an operation such as "filter" would then replace the top of stack with a transformed relation. This could be extended by for example "join on ..." being simply an operation consuming two relations from the top of stack and putting one result back, joining two pipelines into one.

bornfreddy 4y ago

Looks awesome! I don't think it adds much to SQL when the queries are simple, but when you have this looong and complex query I can totally see the appeal.

vander_elst 4y ago

Congrats for the milestone!! The syntax looks more intuitive than SQL. Great to see viable alternatives to SQL!

digisign 4y ago

Thanks, I've frequently wanted a query language that was designed after the 70s. The ideas are sound, but a modernized syntax with variables to reuse subqueries would be lovely. This looks like it. I noticed one issue though... please don't copy the prefix of f-strings! That only exists because Python boxed itself in and it was literally the only ascii syntax left that could be used for string interpolation. It's mildly ugly but the best that could be done given those requirements. Not so here. The way shells do it with single quotes producing literal strings and double quotes available for interpolation has not been topped imho. Triple quotes are a nice extension as well, not sure if that made it in.

aerzen 4y ago
Interesting suggestion. We added f-strings because we already had s-strings (pass trough to SQL) and r-strings (for raw multi-line text). And would you rather see "My {name}" or "My ${name}"? I personally dislike the $ prefix for all variables and interpolations...
- psychoslave 4y ago
  Languages like Perl, Ruby and more offer plethora of additional ways to encode interpolated strings. I especially love the squiggly heredoc for multi line quotations https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-squiggly-heredoc/ https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-sq...
- Kinrany 4y ago
  Choose backticks as the quote style for interpolation to attract Markdown fans and confuse the hell out of MySQL users :D
- digisign 4y ago
  The first one, the $ is redundant if braces required. Multi-line could be triple quoted. SQL, that one I'm not so sure.
oarabbus_ 4y ago
> a modernized syntax with variables to reuse subqueries would be lovely. CTEs provide this functionality already, don't they?

notimportant0 4y ago

I mostly work using T-SQL but I like PRQL. Are you able to add in the examples the following: 1) Use of delimiters for names that include space, etc. I don't know if PRQL uses double quote or square brackets. 2) Use of two/three/four-naming convention to refer to servers, databases, tables and columns.

ssalka 4y ago

I just noticed the play on words "prequel" vs "sequel". Nice.

emmelaich 4y ago

Doesn't the pipelining mean that it's not declarative? At least not in that part.

oarabbus_ 4y ago

Is it correct there's no CASE WHEN and instead you have to define a function using a ternary operator? CASE WHENs may be verbose but when you have a dozen of them they're more readable and the waterfall nature is far preferable to a giant block of ternary clauses.

skybrian 4y ago

I see that the JavaScript package is at [1] and it's implemented by compiling the Rust code to WASM. That should eventually make it pretty easy to run it. It has a typescript definition file, but it looks like it's autogenerated and a bit clunky. You get back a CompileResult and have to call free() explicitly, it seems? That doesn't seem very idiomatic for JavaScript. Also, the links to the documentation and examples in the README are broken. [1] https://www.npmjs.com/package/prql-js https://www.npmjs.com/package/prql-js

maximilianroos 4y ago
Thanks a lot — issue added: https://github.com/prql/prql/issues/708 https://github.com/prql/prql/issues/708
aerzen 4y ago
That's true - the package is auto-generated using [wasmpak](https://github.com/rustwasm/wasm-pack https://github.com/rustwasm/wasm-pack), that's why TypeScript definitions are clunky. I did the initial prql-js release and I'm actually not sure about the free() issue you are talking about. We are currently working on compiling it for both Node.js and the browser target, and would be happy to see some advice if you are familiar with WASM!
- skybrian 4y ago
  I didn't actually try it out and I'm not all that familiar with WASM. Here is the typescript I see (stripped of boilerplate comments): export function compile(s: string): CompileResult; export class CompileResult { free(): void; readonly error: CompileError | undefined; readonly sql: string | undefined; } What is the purpose of the free() method?

gigatexal 4y ago

I’m a raw-SQL-no-ORM snob and I really like this. I’d like to see it become more mainstream.

michelpp 4y ago

It seems like an obvious next step for Postgres support would be to make PRQL a stored Procedure Language. create function foo() returns bar as language prql $$<prql code here>$$;

exabrial 4y ago

I don't really find SQL that difficult, other than NULL !=NULL, I wish <=> could be switched to the default!

dmeijboom 4y ago

I recently started implementing the Postgres protocol in Rust (https://github.com/dmeijboom/postgres-conn https://github.com/dmeijboom/postgres-conn). So I guess I’ll be experimenting with creating a Postgres proxy which translates PRQL on-the-fly.

maximilianroos 4y ago
That looks really exciting! Please keep us in touch with your efforts and let us know if there's any way we can be helpful.

1st1 4y ago

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

maximilianroos 4y ago
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 feedback or guidance for us.
- 1st1 4y ago
  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.rst https://github.com/edgedb/rfcs/blob/master/text/1009-group.r...

metaltyphoon 4y ago

Looks a lot like LINQ

joelthelion 4y ago

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.

kiwicopple 4y ago
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 https://github.com/DrPostgres/pg_plan_guarantee

intelijstupide 4y ago

__mharrison__ 4y ago

This reads a lot like the style of Pandas I teach.[0] This seems to have resolved a problem with SQL, you can't read it in a linear fashion. When we start getting query optimizers for Pandas, much of the benefit of SQL will go away. [0] https://store.metasnake.com/effective-pandas-book https://store.metasnake.com/effective-pandas-book

aljazmerzen 4y ago
What is a query optimizer for Pandas?

alphanumeric0 4y ago

I find SQL harder to work with because I'm used to reading and writing functions - functions that have explicit parameters with (hopefully) explicit types, and explicit return types. So I was hoping PRQL might have some sugar like that. It'd be nicer for my brain if I could treat any select like a function that transforms a set. Instead, even with PRQL, I'm stuck examining source tables and fully understanding them before being able to understand the query.

mandaputtra 4y ago
Is this like what ORM for?
- alphanumeric0 4y ago
  Yeah, but without the objects.

eterevsky 4y ago

Is there a representation in PRQL of UNNEST and a sub-select over an array within a row? We use it quite extensively in the DB that I most often work with.

creamyhorror 4y ago

Wow! This is cool stuff. I was looking through the documentation for conditional logic, control flow, IF(), and the CASE...WHEN...THEN operator. It seems like the ternary operator is the single way to implement conditionality? At https://prql-lang.org/book/examples/functions.html https://prql-lang.org/book/examples/functions.html I found the example `func if_valid x`, which is then used in the `derive` expression `prices_adj | ret | if_valid`. This usage of `if_valid` at the end of the pipeline seems a bit awkward: if I want to do the whole calculation only if some condition is met, I'd like to write it at the front of the pipeline. Can I nest the `ret` function in the if-function like so?: `func ret_if_valid x -> is_valid_price ? (ret x) : null` Then I guess I'd have to do: return_total = prices_adj | ret_if_valid Next, can a string variable be used as part of a column name? It's something I needed recently in order to categorise values and use them to do a pivot. For example, I had to do: SELECT CASE category WHEN 'a' THEN 'x' AS newCategory ... SELECT SUM( if(newCategory = 'a', revenue, 0) ) AS aRevenue, ... Ideally I'd like to do: aggregate [ for x in ['a', 'b', 'c']: (concat x 'Revenue') = sum ( [new_category == x] ? revenue : 0 ), ...] i.e. I'd like to use x as part of the final column name. A pivot example would be great (or maybe you already have a PIVOT implementation in mind). I'd be interested in contributing to this, guess I'll take a look at the code and any community chat you've set up!