18 ms·

PRQL: a simple, powerful, pipelined SQL replacement

• baltimore 4y ago
  This is getting transpiled to SQL, right? So I still have to understand how the (now generated) SQL will perform on my data and DBMS, plus I got to learn this new syntax. This will be a hard sell.
  • leohonexus 4y ago
    SQL query optimisation has been studied since the days IBMers were competing against Quel. If the transpiled SQL has sensible optimisations performance could be equal to or even faster than hand-written SQL. I don't see how this differs from a "language extension" that adds a bit of Pythonic flavour to SQL, which IMO is the right step forward.
  • DemocracyFTW2 4y ago
    If that was the end of the story, no transpiled language could ever succeed. But they sometimes do. One could say the very same about e.g. MarkDown which typically renders as HTML, so you still have to understand HTML to a degree. And in the right environment (e.g GitHub repo readmes) you can always fall back to HTML in places where the transpiler support is lacking. The great thing about transpilers is when they 1) produce tractable, nicely formatted output and 2) you get an 'escape hatch' so you can fall back on the transpilation target. Because then you can always 1) check the output to understand details of an unfamiliar construct, 2) just use the target language in cases where you want to copy-paste a solution written in the target, and 3) just opt out of using the transpiler by using the code it produced as a starting point for your new implementation.
    • cerved 4y ago
      yes but databases are a bit more critical in nature
      • Zvez 4y ago
        not exactly. Just like with the regular code, most of the stuff done on database are pretty trivial and not resource heavy. And the things that are really perfomance-critical are usually crafted very differently even in SQL
    • progx 4y ago
      "no transpiled language could ever succeed".... TypeScript.
      • orthoxerox 4y ago
        TypeScript doesn't introduce new runtime semantics in practice, in 99% of the cases the generated JS is your TS code with types erased. There're no magic keywords that expand into pages of generated JS.
        • aftbit 4y ago
          They implemented the `x.y?.z` syntax pretty long before JS did, so that was at least transpiled for a while. I'll bet there are more features like that.
          • aabbcc1241 4y ago
            Similar story for await and iterator
  • snthpy 4y ago
    PRQL is not aiming to be an ORM or data layer replacement and is focusing on a specific use case, viz making it simple and ergonomic to write *Analytical Queries*. See the the FAQ [0] for more on this. In most cases you want to be able to interactively do some data exploration and craft your query as you go along - the sequential flow of PRQL is great for this as you can simply add lines as you go along. For most people, the RDBMS query optimiser will do a good job of turning the resulting SQL into an optimised query plan. If you need to hand craft the production SQL code for the last few percent of performance, then PRQL gives you full access to the SQL to do that. You probably will still have saved yourself development time by generating the SQL from PRQL in the first place though. [0]: https://prql-lang.org/faq/#:~:text=How%20is%20PRQL%20different%20from%20all%20the%20projects%20that%20SQL%20has%20defeated%3F https://prql-lang.org/faq/#:~:text=How%20is%20PRQL%20differe...
  • tjpnz 4y ago
    If you're spoiled your friendly neighbourhood DBA will help you there. The problem I have with these tools is that you then have to reincorporate their optimizations in such a way that the transpiled SQL is identical. If you have to resort to an ORM expression API or raw SQL you gain nothing and are arguably in a worse situation.
• CGamesPlay 4y ago
  I was dealing with a tricky SQL problem yesterday: take a table of file paths and return a JSON tree with `name` and `children` keys. Definitely requires a recursive CTE, but I ultimately gave up an implemented the transformation in the host language. It was the first thing that I tried in the playground, since it was top of mind, but unfortunately it doesn't seem like the language supports recursive queries (yet). https://github.com/PRQL/prql/issues/407 https://github.com/PRQL/prql/issues/407
  • sa46 4y ago
    I wrestled with a graph traversal problem and Postgres docs for a hot minute before realizing that the "recursive" working set can only access the results of the previous iteration. With that in mind, I took a crack at your problem with the following approach: 1. Build the JSON from the bottom up starting at the deepest path. 2. At each step, handle two cases: a) build the parent of the working set, and b) collect any sibling paths at the same depth. 3. The last step aggregates all children together since all paths descend from '/'. Solution: https://www.db-fiddle.com/f/qTcG5BUjM8GzuzBfHamnVJ/4 https://www.db-fiddle.com/f/qTcG5BUjM8GzuzBfHamnVJ/4 There was a fair bit of trickiness. I used a CTE in the recursive step to reference the recursive table multiple times and to enable aggregation in the recursive step. I used a few helper functions for my own sanity.
• fbdab103 4y ago
  I really really want this to take off. SQL sucks. No, I do not care if relational algebra is beautiful, or simple, or represents some other mathematical purity which makes it the best way to represent data. SQL, as implemented, is a rough language which makes too many things difficult. Unfortunately, it is really difficult for me to lean against this for anything “real” without more years of battle testing. Queries have to be right. I do not want something to blow-up one day because the translation hit a buggy edge case. SQL has many flaws, but everyone knows it. Nobody ever got fired for writing SQL.
  • dmz73 4y ago
    SQL does have faults but PRQL is not fixing any of those and instead is just a python-esque version that retains all the SQL faults and introduces additional complexity without providing much in return. Main complaints about SQL are that SELECT comes before FROM and that there are too many dialects. The SELECT part is only partially valid when writing the query but it is much nicer/easier to read than FROM first options presented elsewhere. This same issue plagues majority of programming languages, most of which optimise for terse syntax that is easy to write but painful to read even when most of the time reading is what people need to do when writing in a programming language. Dialects is a silly one. Programmers treat databases as interchangeable back end instead of foundation of the data storage. No-one ever even thinks about complaining that you can't swap C for Python or Java without major rewrite yet these languages are nothing but glue between database and UI that SHOULD be interchangeable at any time.
    • jeltz 4y ago
      Yeah, SQL has plenty of flaws but PRQL does not seem to be fixing any of those and instead seems to be introducing new issues, e.g. what is up with 'derive country_code = s"LEFT(country, 2)"'.
    • eigenrick 4y ago
      To my untrained eyes, SQL did not appear to have things like windowing, hierarchical relationships, (trees, graphs) in mind when initially designing the language. Support for these things have been wedged into the language by numerous additions, and I think the fundamental syntactical framework of SQL is cracking under the pressure. Granted, it's been a while since I've had to traverse a tree using SQL, but I do recall that the query was very painful to write, and even more painful to execute efficiently. Also, these things only work if you've wedged your data model into rows and columns. (or you use non-standard language extensions like jsonb) PRQL, on the other hand, rearranges the syntactical structure to be more extensible, and also agnostic over the underlying shape of the data model. It works the same regardless of whether you're using an RDBMS or a graph/document database. Is the result more complicated? IMO, only in the simplest examples. PRQL shines vs SQL when you have multiple stages of aggregation, like pre and post aggregation filtering, or referencing synthesized data, etc.
• tbrownaw 4y ago
  What would be cool is "take this view with N columns, and add back in these other Y columns that were projected away" or "add/remove/modify these columns from the source table while preserving whatever others there may be".
  • aerzen 4y ago
    You mean join back with original table?
    • tbrownaw 4y ago
      That's the current solution, but the issue is that the query optimizer can't always handle being tortured like that.
  • snthpy 4y ago
    What you are describing sounds like the `derive` transform in PRQL: it allows you to add new columns based on existing columns you already have. See [0]. Of course this is mostly also possible in SQL but PRQL makes this a bit nicer in that 1) `derive` makes it explicit that you'll be only adding columns and not projecting any away, and 2) you can refer to the newly created fields straight away. [0]: https://prql-lang.org/book/transforms/derive.html https://prql-lang.org/book/transforms/derive.html
• obi1kenobi 4y ago
  SQL really sucks. In general I'm not a fan of SQL-like syntax since it makes queries with lots of joins really difficult to read -- it's hard to keep track which value came from where. GraphQL has some neat ideas and I really love the clarity the nesting provides. But GraphQL is also too limiting: it can't express arbitrary filters, recursion, optional edges (i.e. left joins), etc. My own take on this is an embeddable GraphQL-like language with all those features and with strong guarantees about execution performance. I've been calling it Trustfall, and you can play around with it (or check out example queries over HN data) in your browser here: https://play.predr.ag/ https://play.predr.ag/ For example, here's the query "Find the GitHub or Twitter accounts listed in the bios of HN users that comment on HN stories pointing to Open AI's website": https://play.predr.ag/hackernews#?f=1&q=IyBDcm9zcyBBUEkgcXVlcnkgKEFsZ29saWEgKyBGaXJlYmFzZSk6CiMgRmluZCBjb21tZW50cyBvbiBzdG9yaWVzIGFib3V0ICJvcGVuYWkuY29tIiB3aGVyZQojIHRoZSBjb21tZW50ZXIncyBiaW8gaGFzIGF0IGxlYXN0IG9uZSBHaXRIdWIgb3IgVHdpdHRlciBsaW5rCnF1ZXJ5IHsKICAjIFRoaXMgaGl0cyB0aGUgQWxnb2xpYSBzZWFyY2ggQVBJIGZvciBIYWNrZXJOZXdzLgogICMgVGhlIHN0b3JpZXMvY29tbWVudHMvdXNlcnMgZGF0YSBpcyBmcm9tIHRoZSBGaXJlYmFzZSBITiBBUEkuCiAgIyBUaGUgdHJhbnNpdGlvbiBpcyBzZWFtbGVzcyAtLSBpdCBpc24ndCB2aXNpYmxlIGZyb20gdGhlIHF1ZXJ5LgogIFNlYXJjaEJ5RGF0ZShxdWVyeTogIm9wZW5haS5jb20iKSB7CiAgICAuLi4gb24gU3RvcnkgewogICAgICAjIEFsbCBkYXRhIGZyb20gaGVyZSBvbndhcmQgaXMgZnJvbSB0aGUgRmlyZWJhc2UgQVBJLgogICAgICBzdG9yeVRpdGxlOiB0aXRsZSBAb3V0cHV0CiAgICAgIHN0b3J5TGluazogdXJsIEBvdXRwdXQKICAgICAgc3Rvcnk6IHN1Ym1pdHRlZFVybCBAb3V0cHV0CiAgICAgICAgICAgICAgICAgICAgICAgICAgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNpdGVQYXR0ZXJuIl0pCgogICAgICBjb21tZW50IHsKICAgICAgICByZXBseSBAcmVjdXJzZShkZXB0aDogNSkgewogICAgICAgICAgY29tbWVudDogdGV4dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICBieVVzZXIgewogICAgICAgICAgICBjb21tZW50ZXI6IGlkIEBvdXRwdXQKICAgICAgICAgICAgY29tbWVudGVyQmlvOiBhYm91dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICAgICMgVGhlIHByb2ZpbGUgbXVzdCBoYXZlIGF0IGxlYXN0IG9uZQogICAgICAgICAgICAjIGxpbmsgdGhhdCBwb2ludHMgdG8gZWl0aGVyIEdpdEh1YiBvciBUd2l0dGVyLgogICAgICAgICAgICBsaW5rCiAgICAgICAgICAgICAgQGZvbGQKICAgICAgICAgICAgICBAdHJhbnNmb3JtKG9wOiAiY291bnQiKQogICAgICAgICAgICAgIEBmaWx0ZXIob3A6ICI%2BPSIsIHZhbHVlOiBbIiRtaW5Qcm9maWxlcyJdKQogICAgICAgICAgICB7CiAgICAgICAgICAgICAgY29tbWVudGVySURzOiB1cmwgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNvY2lhbFBhdHRlcm4iXSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBAb3V0cHV0CiAgICAgICAgICAgIH0KICAgICAgICAgIH0KICAgICAgICB9CiAgICAgIH0KICAgIH0KICB9Cn0%3D&v=ewogICJzaXRlUGF0dGVybiI6ICJodHRwW3NdOi8vKFteLl0qXFwuKSpvcGVuYWkuY29tLy4qIiwKICAibWluUHJvZmlsZXMiOiAxLAogICJzb2NpYWxQYXR0ZXJuIjogIihnaXRodWJ8dHdpdHRlcilcXC5jb20vIgp9 https://play.predr.ag/hackernews#?f=1&q=IyBDcm9zcyBBUEkgcXVl... The repo is here: https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall It's not just a toy implementation -- it has real-world uses too: - I gave a conference talk on how to use Trustfall to query any kind(s) of data sources, from APIs to DBs to raw files: https://www.hytradboi.com/2022/how-to-query-almost-everything https://www.hytradboi.com/2022/how-to-query-almost-everythin... - cargo-semver-checks, a semver linter for Rust crates, uses Trustfall to define its lint rules: https://github.com/obi1kenobi/cargo-semver-checks https://github.com/obi1kenobi/cargo-semver-checks
• Inviz 4y ago
  I wish this was more programmatic and less stringy. Intermediate layer of string queries feels redundant. But I understand it makes clear separation of layers and concerns
  • aerzen 4y ago
    String queries? What do you mean? Producing SQL serialized as strings? You can go around that and use RQ representation - but not db supports it yet.
• kasajian 4y ago
  PRQL is cool, except I liked it better when it was called LINQ.
  • akmittal 4y ago
    I keep hearing LINQ is the best, Can anyone explain how its better than say prisma?
    • maxfurman 4y ago
      LINQ is neat because it maps from the C# IEnumerable API to SQL. So you use the same methods to work with in-memory collections as you do database tables, which leads to a very natural feeling programmer experience. Compare to Rails' Active record where `things.where(foo: 6)` calls out to the database but `things.filter { |t| t.foo == 6 }` will pull all the things into memory and then iterate through them to create the filtered list. In LINQ those are both `things.Where(t => t.foo == 6)` and the type of "things" determines where the query executes.
      • rzzzt 4y ago
        This needs language / introspection support when converting to SQL as you need to pick apart the expression's AST to figure out that it's eg. a simple equals check on one of the fields.
    • contravariant 4y ago
      Well one of the things that makes it neat is that it works for any Monad. Where Monad means any implementation of the following method F<Y> SelectMany(F<X> input, Func<X, F<Y>> map) this could be a function where you have a list of things, apply a function to each element that returns a list and the result is the concatenation of the individual lists. Or you could use it for stuff like tasks where the result is a task that returns something like `await map(await input)`.
  • aerzen 4y ago
    So LINQ that can be used with languages other than C#?
• biggio 4y ago
  no, thanks
• phplovesong 4y ago
  I really like SQL, and think its one of the most important languages a dev needs to learn (all you managers, you could learn some SQL too!). However to syntax is often very restrictive. As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. When working with big tables, its super annoying to select all but one column. So why not have syntax for this? Eg SELECT !username from users could expand to all columns without username. I feel SQL has so many low hanging fruits from a user perspective, but its very slow moving, and the standard is even more slow to make progress. So SQL with sugar that compiles down to vanilla SQL has a market imho. I will study PRQL more and see if i could use it on a daily basis.
  • whateveracct 4y ago
    > As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. Haskell's esqueleto SQL eDSL actually enables just this BUT acting like this is a big deal is Wadler's Law for sure.
    • phplovesong 4y ago
      Sure, multiple ORMs and query builders allow this, but when writing pure SQL thats dynamic its annoying to have to check for some condition before knowing "Should i use WHERE or AND next". This is traditionally solved with an hard coded WHERE 1 = 1 in the beginning of the query. Still its a wart, even tho a small one. Point being, there are many other small things like this that (can) make SQL a burden, eg the select all but <n+1> columns.
  • mmsimanga 4y ago
    DuckDb has the feature to exclude columns in the SELECT statement [0] [0]https://duckdb.org/2022/05/04/friendlier-sql.html https://duckdb.org/2022/05/04/friendlier-sql.html
  • snthpy 4y ago
    > As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. PRQL does this: from employees filter country_code == "USA" # Each line transforms the previous result. filter age > 50 compiles to: SELECT * FROM employees WHERE country_code = 'USA' AND age > 50 -- Generated by PRQL compiler version 0.3.1 (https://prql-lang.org)
• banku_brougham 4y ago
  My first thought was "here we go, someone is going to improve a 50 y/o language with things that dont respect the power of SQL." But this, I like it. SQL readability is bad, especially when multiple team members are touching the same query profile. The top to bottom transformation flow solves one of the backward things, the filters in SQL being at the bottom.
• tommica 4y ago
  Neat project - I like the flow
• pmontra 4y ago
  A suggestion. Just use let instead of derive, like almost any other language. Example derive id = f"{title}_{country}" vs let id = f"{title}_{country}"
  • deleted 4y ago
    [deleted]
  • aerzen 4y ago
    We are holding back on `let` to be used for defining a new variable, while `derive` is used to define a new column in a relation.
    • pmontra 4y ago
      Understood, thanks. But is that really different from the point of view of the developer? As long as the column is not used in the output it's just a variable. I elaborate because I checked the examples since my first comment: There are two derive sections in introduction.prql. derive [ # This adds columns / variables. gross_salary = salary + payroll_tax, gross_cost = gross_salary + benefits_cost # Variables can use other variables. ] Note the comments about "columns / variables" and "Variables can use other variables", so the distinction is probably not very clear even now. From my point of view that would be let gross_salary = salary + payroll_tax let gross_cost = gross_salary + benefits_cost Then I'd get gross_salary and gross_cost in the result or not, according to the select I'm doing at the end. [1] By the way, in the examples I saw select [name, salary, average_country_salary] which has some unnecessary brackets. Why not just omitting them? select name, salary, average_country_salary Same for the parentheses in aggregate average_country_salary = (average salary) Maybe omitting [ ] and ( ) would make the parser too complex but I'm all in to offload work to computers instead of to developers :-) And why not only one type of brackets? Example: the join clause join average_salaries [==country] which gets compiled to JOIN average_salaries ON newest_employees.country = average_salaries.country Why square brackets there instead of round ones? That's a condition and we're conditioned to put conditions inside (), as in if () {} else {} And omit == by default, so join average_salaries (country) [1] Edit: maybe you can add a negative select, to remove columns from the output. Maybe: remove col1, col2 That would be handy where there are 100 columns and one wants to remove only 5 of them. No need to enumerate the other 95.
• tanin 4y ago
  This looks awesome. I want to integrate this with my SQL tool (using JS). I've noticed the limited standard library. I wonder if prql supports "passthrough" function where it just uses function as-is e.g. if I invoke "random_function(a)", it transpiles to "random_function(a)" in SQL.
  • krab 4y ago
    In the examples, they show S- strings. If I understand it correctly, s"random_function(a)" does what you want. The question is whether you can rely on `a` that you reference being still named `a` in SQL.
    • snthpy 4y ago
      Correct, s-strings are the general "escape hatch" to allow pass-through for things that are not yet implemented, like dialect specific functions. See the section in the PRQL Book [0] for more detail. It does make it opaque to the compiler though so the user becomes responsible for ensuring symbol names are valid and accessible in the scope. [0]: https://prql-lang.org/book/language-features/s-strings.html https://prql-lang.org/book/language-features/s-strings.html
      • viraptor 4y ago
        I wish they made it more obvious in the docs. S-string is not something I would look at when trying to figure out how to use "NOW()". I spent a reasonable time looking for a way to call functions and then just abandoned the idea of prql since it seemed impossible.
        • maximilianroos 4y ago
          (PRQL core-dev here, not sure whether you'll see this given it's a day later) Thanks for the feedback. We had similar feedback before, and we added an s-string to the example on the homepage. derive country_code = s"LEFT(country, 2)" Any thoughts on how to communicate this more clearly? They're also an item in the Showcase. Without them, PRQL would indeed be harder to start using now, so keen to make them more obvious.
• hbc 4y ago
  The pipeline style syntax is super cool! I wonder what's the author view on Kusto query language [0], which is using a similar thread/pipeline style of syntax. [0]: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://learn.microsoft.com/en-us/azure/data-explorer/kusto/...
  • snthpy 4y ago
    Kusto is one of the inspirations but there are many others like dplyr, LINQ, ... Have a look at the FAQ [0] for a more complete list. [0]: https://prql-lang.org/faq/#:~:text=Something%20here%20reminds%20me%20of%20another%20project%2C%20did%20you%20take%20the%20idea%20from%20them%3F https://prql-lang.org/faq/#:~:text=Something%20here%20remind...
    • hbc 4y ago
      perfect, thanks for the pointer!
• GleamingGazelle 4y ago
  I initially had concerns about a proposed change to a 50-year-old language potentially disrespecting the power of SQL, but ultimately liked the change because it improved SQL readability and addressed the issue of filters being placed at the bottom of SQL statements.
• xwowsersx 4y ago
  > For HackerNews enthusiasts > The PRQL compiler is written in rust. > We talk about “orthogonal language features” a lot That gave me a good laugh. Well played.
• sklivvz1971 4y ago
  IMO this will only appeal to people who don't like SQL or that have a narrow set of problems to solve. First of all, the number of different dialects is a non-issue because projects that have to send the same queries to different databases are very rare. Second, in my world where performance matters, you need to hand optimize queries. This is done quite specifically based on the database load and size, let alone specific quirks and strengths of the actual DB product. Something like this would only get in the way. Third, even if all of these aren't important to you, and you just want to avoid SQL because you don't like it, there are plenty of ORMs in circulation. Many of them allow to specify queries in a fluent fashion. Finally I think the cardinal issue is expecting that inventing a new dialect will change anything -- imagine the PRQL that compiles to a hundred-lines complex SQL query: would it be necessarily more readable? I doubt it.
  • tored 4y ago
    And it also rare too switch database vendor mid project, some rare constraints if that happens or if you have too much vc money. And you should use your database to its full capabilities, including vendor specific queries, otherwise you are just wasting computation. Becuase of this I don't even use query builders anymore, just raw SQL.
  • r_hoods_ghost 4y ago
    "projects that have to send the same queries to different databases are very rare." That's true in business certainly but once you head into academia or are working with medical data / the public sector you often find yourself having to write queries against databases from different vendors to construct datasets, which gets annoying fast. I used to work as an RA in an economic modelling group and hit this issue a few times, so I can see the use of something like this in that niche.
  • password4321 4y ago
    > projects that have to send the same queries to different databases Software vendors that support client's existing commercial database licenses are the ones volunteering for this nightmare. This is actually a good question to ask during interviews - avoid if at all possible!
• usgroup 4y ago
  That PRQL is not composable makes it —- so far as I can see —- mostly an alternative syntax. What I’d personally really appreciate is the ability to split queries up into individuality testable and reusable parts. I think that’d be game changing because suddenly large sql code bases become manageable.
  • ernestoo 4y ago
    Try dbt for that
  • trollied 4y ago
    Use CTEs (Common Table Expressions, otherwise known as "with clauses"). This actually solves some of their gripes & reasoning for creating PRQL in the first place.
  • snthpy 4y ago
    Composability is definitely a major design goal and I think we are getting close to that. Watch this space!
    • silon42 4y ago
      I wonder how you plan to handle the query optimizer which often behaves unpredictably as queries get more complicated.
  • thelastbender12 4y ago
    You might enjoy the FunSQL library; SQL verbs are represented using regular Julia objects and you pipeline them together to build bigger queries. A key difference to other SQL builders is that you assemble a tree of "tabular operations" instead of a SQL syntax tree. https://github.com/MechanicalRabbit/FunSQL.jl https://github.com/MechanicalRabbit/FunSQL.jl
• usgroup 4y ago
  Could you provide some examples of non trivial SQL queries expressed as PRQL and then what PRQL actually produces as a result? As others have suggested, I do wonder how optimal the translation is for non trivial cases because that often matters both for cost and performance reasons.
  • aerzen 4y ago
    https://prql-lang.org/book/examples/employees.html https://prql-lang.org/book/examples/employees.html The goal is for compiler to be smart about what SQL constructs are used. We want to lift at least some burden of regard for performance from user and pass it to the compiler. But there is a caveat: when writing SQL, you only have to know roughly how does the database optimizer work. When writing PRQL, you also have to know a little about PRQL compiler.
• thomasmg 4y ago
  SQL has many problems: "from" should come first to help auto-complete; null handling is complicated; the syntax in general is weird. Compatibility is a problem (I have implemented some relational databases in Java: Hypersonic SQL, H2; compatibility with other databases is hard). There is little innovation in the database space: there are hundreds of general-purpose programming languages, but very few "database" languages. I also tried to specify a new language, https://newsql.sourceforge.net/ https://newsql.sourceforge.net/ many years ago. There's GraphQL, but more innovation in this area would be great. Many developers end up writing huge SQL statements (one statement that is hundreds of lines). You wouldn't do that in a "proper" programming language; but in languages like SQL (or maybe Excel) this will happen. Another problem is proper encapsulation. Views can be used, but often developers have access to all tables, and if you have many components this becomes a mess. Regular programming languages have module system and good encapsulation. SQL statements often don't use indexes, for one reason or the other. With regular programming languages, it is harder to end up in this position (as you have to define the API properly: encapsulation). Regular programming languages don't require "query optimizers". SQL is used to reduce network roundtrips. Clients send SQL (or GraphQL) statements to the server, which are actually small programs. Those programs are executed on the server, and the response is sent to the client. (Stored procedures and for GraphQL persisted queries can be used - but that's often happening afterwards.) Possibly it would be better to send small programs written in a "better" language (something like Lua) to the server?
  • noduerme 4y ago
    These are all really great complaints; and yet I still prefer writing plain SQL to using ORMs and their nifty tricks. I think the main reason is that I don't ever want to have a lot of SQL logic in my code. Short queries (say, 10 lines or less) are okay, if they're one-off and pertain to the function's logic and are never repeated by other pieces of code. But writing complex queries through an ORM layer means they're going to be in code -- and harder-to-maintain -- than if they were where they should be: In views, functions and procedures. ORMs also obscure the execution plan and make optimization more difficult, which in production isn't something I really want to worry about. Writing SQL is like uploading a program; just like a shader to a GPU. And if the network stopped being the bottleneck, the DB server's load would become the problem. Just like with a GPU, you want to upload your programs and data as infrequently as possible. But unlike a GPU, you have full flexibility to permanently store your logic right there in your SQL DB. If it's well-documented, it's much more streamlined to write views and views-on-views, than to keep all that DB logic in your code. I don't find that it becomes a mess at all, because properly crafted views and procedures strongly encourage forced indexing and checking execution plans when you refactor them; as opposed to in-code, ad-hoc queries where it's hard to know if someone ever ran tests to optimize them. Some statements do need to be very large, for efficiency. Others are better being broken up into sub-procedures, temp tables or even roundtrips. A great query is a work of art where each part of it is optimized not just for execution plan but also for read/write, network state and expected load. What I like about SQL is the flexibility to decide which way to go, and prioritize based on how often something will be used and what kind of strain it'll place on each of the layers in the stack. As with anything powerful, there are a million ways it can be abused. But what I've found in the ORM space and also with noSQL solutions is that simplicity of use comes at the price of flexibility and control. [edit] >> null handling is complicated This would probably also be my biggest complaint about the language itself; the existence of IS NULL / IS NOT NULL and both null-safe and -unsafe comparisons gets confusing (and especially painful if you're trying to write a parser). I think it's one of those things like `undefined` vs `null` vs `false` in Javascript where, on second thought, it's just too late to fix it without breaking half a billion websites.
• password4321 4y ago
  PRQL was mentioned yesterday along with dbt, SQLAlchemy, Jinja, esqueleto, jOOQ, XSLT, LINQ, sqlpp, and ecto: SQL + M4 = Composable SQL https://news.ycombinator.com/item?id=34159699#34160479 https://news.ycombinator.com/item?id=34159699#34160479
• jooz 4y ago
  Some real life problems using standard SQL are: - UPDATE does not return number of rows updated. - There is no "upsert" - There is no "merge" - List all tables and explain command are not standard. Do you plan to cover them on your project ?
  • acarl005 4y ago
    Actually standard SQL does specify a MERGE command (which lets you implement an upsert) and it's implemented in Postgres
    • password4321 4y ago
      MERGE debuted in PostgreSQL 15 fairly recently, and has some caveats. https://news.ycombinator.com/item?id=33236780 https://news.ycombinator.com/item?id=33236780
  • aerzen 4y ago
    - PRQL is focused on analytical queries and it does not support DML (yet at least, see https://github.com/PRQL/prql/issues/1092 https://github.com/PRQL/prql/issues/1092). Thus there is no UPDATE or "upsert" - What is merge? How is it different from join? How is it different from union? - SQL does define `information_schema`, which can be used to list tables and columns. We had ideas about introspection and listing tables, but this is not on the roadmap yet. A probable outcome here will be improvements to tooling so you will be able to find tables via autocomplete (LSP). - There are no plans for EXPLAIN
    • dinedal 4y ago
      > What is merge? How is it different from join? How is it different from union? I believe the author is referencing https://www.essentialsql.com/difference-merge-update/ https://www.essentialsql.com/difference-merge-update/ - which you answered in your first point. There's no UPDATE support yet, therefore there's no MERGE support yet.
      • 4y ago
• eurasiantiger 4y ago
  Feels like the join syntax is too low-level to be db-agnostic. What about graph support?
• Too 4y ago
  Nice. Pipelines are lot more intuitive than sql, and the other added niceties were desperately overdue. Funny considering sql is supposed to be human readable hehe. Feels a lot like MongoDB aggregation pipeline, but with a more integrated syntax rather than its verbose chaining of json values. New language is a double edged sword though, a special syntax will be much more difficult to compose from other programming languages, without resorting to string formatting and all the injection vulnerabilities that opens up for. With json-objects or chainable functions it’s easier to programmatically build queries. What’s the strategy for composability? Related, maybe some times conflicting, requirement, can type safety with host language be possible?
• andness 4y ago
  For the last two years I've been using dbt for some ETL work. It is quite useful, but at the same time quite ugly and clunky. It's basically just a bunch of macros, and it gets unwieldy pretty quickly. It's weird to have to declare the dependencies between the nodes in the DAG when they should be easy to figure out from the code. And dbt is also very restrictive in that it's designed around transformations that are functional so making more procedural transformations is really hard. So I think the world would buy a much better dbt. In other words, I think your focus on analytical queries is probably a good idea. What makes using something like dbt palatable is that there really aren't any good alternatives in this space. dbt does have some strengths that are worth looking to, such as the extra tooling you get by using their IDE, the generated docs, their ideas about a metrics layer. I think that for PRQL to be really useful it needs to extend beyond just the syntax and really make writing these data pipelines much easier. The syntax of the SQL isn't the worst part of it. But I think there's a big potential market here.
  • Hasnep 4y ago
    dbt-prql lets you write prql in your DBT https://pypi.org/project/dbt-prql/ https://pypi.org/project/dbt-prql/
• glogla 4y ago
  I tried to play with it, rewrite some code to it to find out if it really is more readable ... but without a CASE statement, this can't actually do much business logic yet, so it will have to wait.
  • maximilianroos 4y ago
    (PRQL core-dev here, though responding the next day) We merged this last week! https://github.com/PRQL/prql/pull/1278 https://github.com/PRQL/prql/pull/1278 Docs & a release are forthcoming
    • glogla 4y ago
      Very nice! I'll take it for a spin then.
• felixge 4y ago
  Having written lots of advanced SQL (tons of CTEs, window functions, user defined aggregates, json manipulation, etc.) in my previous job, this looks really promising. The main issue I encountered while playing with it is that the ordering semantics of transforms are unclear to me. This is a notorious foot gun in SQL which does not propagate row order from sub queries or CTEs into parent queries unless those sort conditions are restated. Hopefully prql can come up with a better answer to this problem: https://github.com/PRQL/prql/issues/1363 https://github.com/PRQL/prql/issues/1363
  • taffer 4y ago
    This is on purpose. Forcing the propagation of row order from subqueries would make many or even most optimizations impossible.
  • tremon 4y ago
    I'm not sure I'd call that a footgun -- it's a fundamental property of relational algebra that relations are unordered. That said, it's not universally true that subquery ordering is not propagated to the final result; in the absence of an ORDER BY clause, the query engine will return the results in whatever order is most convenient for execution. For example, this query: select *, rank() over (partition by lastname order by firstname) from employees; will definitely return a result set ordered by lastname,firstname even though it's not explicitly specified (tested on MS' azure sql database).
    • VelJun 4y ago
      With a large enough employees table, Azure SQL could parallelize the scan operation, in which case the ordering would not necessarily be maintained.
    • fuy 4y ago
      without ORDER BY in the outermost query the ordering is definitely not guaranteed and may be influenced by optimizations
• jakubmazanec 4y ago
  I think EdgeQL (https://www.edgedb.com/docs/edgeql/index https://www.edgedb.com/docs/edgeql/index) is a much better SQL replacement, because it is composable, and there is already a working DB using it.
  • tgv 4y ago
    What a non-engaging website. I can't see an example. Don't make me install anything. Don't point at a blog which rehashes SQL's negatives. Don't give me a tutorial that shows for ten types how to have a query return a constant. Especially don't show me queries that should return a constant but cause an error because of some dumb syntactic problem! Show me what it looks like and why it's better than SQL.
    • raghavkukreti 4y ago
      I mean the link points to the docs, what do you expect? Go to the homepage and you'll find what you're looking for
      • tgv 4y ago
        Indeed, that would have been a better link. A bit hard to guess. The linked page has "get started", "overview", "learn", and "blog". I expected to find it somewhere there. My bad, but I never take much time to trawl the whole site, and I think many with me.
• faserx 4y ago
  You could do better with the joins, imho less readable than the equivalent in sql.
• brap 4y ago
  Neat, but why make a new language when you could build something with better ergonomics using client libraries (essentially a newish form of ORM)? It all transpiles to SQL anyway.
• haspok 4y ago
  I'm wondering if it would be worth skipping the SQL layer and compiling straight to the execution plan level. This would make PRQL tightly coupled to a database, but would also unlock performance and fine-tuning potential. And, maybe it would be possible to run it inside the database process, enabling data processing right at the source. (eg. in Postgres, this would be more or less a replacement of the PREPARE statement.)
  • assface 4y ago
    > I'm wondering if it would be worth skipping the SQL layer and compiling straight to the execution plan level. You have to join five tables in a query. What join order will you pick in your execution plan? Will you use the same ordering for different WHERE clause parameters? What if you add an index? What you're proposing was tried in the 1970s (see CODASYL) and it was a terrible idea.
  • snthpy 4y ago
    PRQL actually goes through two intermediary ASTs (PL and RQ) before being compiled into SQL. This is not that well documented yet but have a look at the comments at the top of the lib.rs [0]. Both representations are accessible as json in the Rust and Python APIs. On the roadmap are plans to support DataFrame backends such as Polars, Pandas, Ibis, ... and Substrait.io [1]. The aim of Substrait is similar to what you describe, i.e. to create a universal relational algebra representation that can be executed directly by backend systems without having to go through SQL. [0]: https://github.com/PRQL/prql/blob/main/prql-compiler/src/lib.rs https://github.com/PRQL/prql/blob/main/prql-compiler/src/lib... [1]: https://substrait.io/ https://substrait.io/
• epalm 4y ago
  This looks cool. I like SQL but I have to admit it’s often cumbersome. Something that came to mind after looking at some example queries is the mix of parentheses and square brackets. It’s not immediately obvious to me when I’d use which. Also (and this might just be a personal pet peeve), why do new languages insist on using flashy new keywords when the status quo would do just fine. What’s the point of filter/derive instead of just calling them where/select. I sometimes notice languages really go out of their way to ‘be different’, I think it’s xslt (or something xml-related) that uses when/otherwise instead of just if/else. Everyone knows if/else, stop trying so hard :P
  • xigoi 4y ago
    “select” implies that you're selecting from some options, not creating new values. “where” is a preposition whereas “filter” is a verb, which fits the pipeline approach better.
    • epalm 4y ago
      Sure, that’s fair. You’re not wrong. But PRQL is meant to substitute for SQL, my argument here is keyword familiarity outweighs absolute linguistic precision.
      • xigoi 4y ago
        It's meant to be a better alternative, which includes using more precise language.
  • snthpy 4y ago
    Square brackets are for lists/list arguments. Parentheses are when a pipeline/transforms need to be supplied, most notably with `group`. More details here: https://prql-lang.org/book/queries/syntax.html https://prql-lang.org/book/queries/syntax.html
• frakt0x90 4y ago
  This reminds me a lot of R's dplyr which is by far my favorite data manipulation suite. I have never enjoyed pandas even though I use it every day. This project is one of the few SQL offshoots that I actually hope succeeds. Well done and may you find your way to the hall of the data kings next to dplyr and ggplot.
  • snthpy 4y ago
    The last example in this notebook [0] shows how similar PRQL is to dplyr. The rest of the notebook shows how you can use PRQL from R, Python and the command line. [0]: https://github.com/snth/normconf2022/blob/main/notebooks/normconf.ipynb https://github.com/snth/normconf2022/blob/main/notebooks/nor...
• totalhack 4y ago
  At first glance this seems more confusing, particularly the grouping/aggregation syntax, though I suppose that's something I'd just get used to. Some of the syntactic sugar is nice, but some things are also unlike SQL for no apparent reason which just makes adoption harder than necessary (join syntax for example). IMO the main selling point would be the "database agnostic" part, but I already achieve that through SQLAlchemy Core and/or a warehouse layer like https://github.com/totalhack/zillion https://github.com/totalhack/zillion (disclaimer: I'm the author and this is alpha-level stuff, though I use it regularly). It seems like many newer DB technologies/services I'd want to use either speak PostgreSQL or MySQL wire protocol anyway. The roadmap is worth a read, as it notes some limitations and expected challenges supporting the wide variety of DBMS features and syntax. That said, I can see where this might be useful in the cases where I do have to jump into direct SQL, but want the flexibility to easily switch the back end DB for that code -- that's assuming it can cover the use cases that forced me to write direct SQL in the first place though.
• slotrans 4y ago
  This comes up periodically, but it's never interesting. SQL is good. What people don't like about it is all rooted in their unwillingness to learn it, on its own terms. They invariably want to import ideas from imperative languages that simply don't make sense within the functional/relational paradigm.
  • vlovich123 4y ago
    I don’t understand this attitude. We have many tens of programming languages for logic. Why is it that we really only have one that’s portable and that we came up with this idea 30 years ago? That just doesn’t seem plausible to me.
    • ttfkam 4y ago
      Almost 50 years ago in fact. And there were alternatives all along that timeline and many before it. SQL is the first and possibly the only successful fourth gen programming language, and the most substantive complaints generally boil down to "SELECT should have been after FROM and WHERE". The vast majority of the seekers to the crown have been third generation languages trying to fake it with relational algebra, and data is just too important to allow side effects. In short, don't assume because something has lasted in the computing world for 50 years that it is fundamentally flawed and only due to inertia. That would be a very foolish assumption indeed. SQL is a DSL for set theory and as such any viable replacement will look substantially like it. When the differences are subtle, the impetus to retool/rewrite just evaporate.
• jeroen79 4y ago
  Makes it just less readable in my opinion, learning sql isn't all that hard.
• Taikonerd 4y ago
  Elsewhere in the "SQL replacement" space, I'd like to recommend EdgeDB [0]... * The query language is less like COBOL, more like modern languages * Instead of returning rows, queries return objects linked to other objects through properties * Supports both relational and graph queries * ...but it's Postgres under the hood! And it's open-source -- they're planning to make their money through a cloud-hosted solution. [0]: https://www.edgedb.com/ https://www.edgedb.com/
  • maximilianroos 4y ago
    (PRQL core-dev here) Very much agree! We're very friendly with the folks at EdgeDB, have lots of overlap of contributors. They're part of the inspiration behind PRQL [1]. To focus on the differences for a moment: * PRQL is focused on analytical queries, whereas the genesis of EdgeDB was on transactional queries (though I know Yury & the team are building out more analytical capabilities) * PRQL works with any existing database — you can query any tables you have today — and using PRQL doesn't preclude you or your colleagues from using SQL. In exchange for that openness & simplicity, we give up some functionality — we don't yet know the types of all your columns, for example. TBC, EdgeDB has improved on this dimension since their initial product — it's been able to run on a vanilla Postgres instance for a while now, no extensions needed. [1]: https://prql-lang.org/faq/ https://prql-lang.org/faq/
    • agumonkey 4y ago
      both projects are very cool, thanks
• bbatchelder 4y ago
  This is pretty much how I already write SQL, just using CTEs. In fact I bet the most straightforward way to transpile PRSQL to SQL would be using CTEs.
• hrdwdmrbl 4y ago
  Really happy to see this problem see continued development. I remember when it was just a proposal.
• garfieldnate 4y ago
  At my previous employer, data scientists would regularly spend entire days only running 3-4 Hive queries and waiting hours for the results to come back... job failed. PRQL looks much easier to use, and the goals of good auto-complete and type-checking will make it even better. If any corporation with lots of data scientists would like to fund work on an open-source project, this seems like a no-brainer.