23 ms·

PRQL – A proposal for a better SQL

• fuzzieozzie 5y ago
  The time of SQL alternatives https://news.ycombinator.com/item?id=30053860 https://news.ycombinator.com/item?id=30053860
  • maximilianroos 5y ago
    Yup, I posted that yesterday too. I think Malloy is really interesting — compile to SQL but give more integrations to the DB, like schema-during-development. It has a proper team, led by Lloyd Tabb.
• metta2uall 5y ago
  Looks pretty cool. I'd be interested if the README had a comparison with Google's Logica (https://github.com/EvgSkv/logica https://github.com/EvgSkv/logica)
• api 5y ago
  We don't need better SQL. We need a programming language with relational concepts built into its collections library and a syntax and type system that makes using it easy. The fact that you have to go through all these intermediate layers to access your data is just stupid. Data should just be "there."
• Gehtio 5y ago
  I might just be good in SQL but I haven't seen anything which is easier to read fore than SQL. This example looks so weird and it only is getting worse.
• maximilianroos 5y ago
  I wrote this over the holidays, because I find SQL wonderfully elegant in its function, but really frustrating in its form. Let me know any feedback — as you can see it's still at the proposal stage. If it gains some traction I'll write an implementation.
  • roberto 5y ago
    This looks great! Clear docs and rationale, and the syntax is well thought. I'm definitely following this.
  • xi 5y ago
    Maybe you'd like to check FunSQL.jl, my library for compositional construction of SQL queries. It also follows algebraic approach and covers many analytical features of SQL including aggregates/window functions, recursive queries and correlated subqueries/lateral joins. One thing where it differs from dlpyr and similar packages is how it separates aggregation from grouping (by modeling GROUP BY with a universal aggregate function).
    • maximilianroos 5y ago
      This is awesome! I'll add a link to it on PRQL. I guess the biggest difference between FunSQL (and similarly dbplyr) and PRQL is that the former needs a Julia (or R) runtime to run. I really respect the library and keen to see how it develops.
      • clarkevans 5y ago
        Writing an alternative syntax is straight forward. Perhaps prototype PRQL using xi's excellent FunSQL backend? This way it's working out of the gate. Once syntax+semantics are pinned, writing another backend in the language of your choice would then be easier. Getting the backend correct is non-trivial work, and xi has done this already. Besides, we need a sandbox syntax anyway, so it might be fun to collaborate.
      • andreypopp 5y ago
        FunSQL.jl requires Julia to run (obviously as it is a Julia library) but it produces standard SQL so Julia in this case is just an implementation language. I have re-implemented parts of FunSQL in Python and OCaml (the one I have ended up using) and have added a concrete syntax similar to what you have in PRQL. from employees define salary + payroll_tax as gross_salary, gross_salary + benefits_cost as gross_cost where gross_cost > 0 and country = 'usa' group by title, country select title, country, avg(salary) as average_salary, sum(salary) as sum_salary, avg(gross_salary) as average_gross_salary, sum(gross_salary) as sum_gross_salary, avg(gross_cost) as average_gross_cost, sum(gross_cost) as sum_gross_cost, count() as count order by sum_gross_cost where count > 200 limit 20 But, in my mind, the biggest difference between PRQL and FunSQL is the way FunSQL treats relations with `GROUP BY` - as just another kind of namespaces, allowing to defer specifying aggregates. A basic example: from users as u join (from comments group by user_id) as c on c.user_id = u.id select u.username, c.count() as comment_count, c.max(created_date) as comment_last_created_date The `c` subrelation is grouped by `user_id` but it doesn't specify any aggregates - they are specified in the `select` below so you have all selection logic co-located in a single place. I think this approach is very powerful as it allows you to build reusable query fragments in isolation but then combine them into a single query which fully specifies what's being selected.
• bachmeier 5y ago
  My thought is that joins are the tough part of the SQL learning curve, but I don't see much in here that reduces the complexity of joins.
  • omarhaneef 5y ago
    There was this professor of language who would say "Do you think the question ('are carpets furniture?') tells you something about the ambiguity of the word carpet, or do you think it tells you something about the ambiguity in the world?" Similarly, I think joins are "tough" not because of the way SQL expresses them but because the logical possibilities of merging data from multiple tables are varied.
    • bob1029 5y ago
      There is no such thing as a domain-agnostic SQL database that holds up under this kind of semantic scrutiny. I don't think that there ever could be. If you are rolling a SQL schema for a home improvement contractor, it is extraordinarily unlikely that their specific business would expect any scenarios in which carpets are sometimes known as furniture. Having a bounded context to operate within is what makes SQL magical for me. When people don't understand the business or simply the game around how you talk about the business, things start getting messy wrt joins.
      • omarhaneef 5y ago
        The carpet discussion was simply to say that you can't take out all the complexity of a language if the domain it is meant to describe is complex. The language has a limit to how simple it can be. I was not proposing a SQL database of carpets, or furniture, as a thought experiment.
      • eurasiantiger 5y ago
        EdgeDB
    • bachmeier 5y ago
      Then make some cases easier and fall back to the SQL we already have for the rest?
• eatonphil 5y ago
  I'm really excited about languages that build on or are compiled to SQL, in the long-term (because I think it will take a very long time to build adoption). The ones that particularly excite me are shorthands for SQL, even though their heavy use of symbols may be a detriment. One particular use case is in easily defining static authorization policy-queries that are backed by database data plus and have request variables injected during evaluation. I am not very excited by datalog/prolog-based languages because I think logic languages are too unnatural to ever go mainstream. But I'd be excited to be wrong or for logic languages to become more friendly. Here are some others I'm watching. * https://github.com/mrumkovskis/tresql * https://www.htsql.org/doc/overview.html * https://github.com/cytosm/cytosm
  • larodi 5y ago
    in a way SQL is Prolog and all reasoning for improvement of SQL should start from Prolog, because is where SQL started from. the expressive power of both languages is theoretically the same, even though SQL is much more comprehensible. but then again - certain complex task turn SQL in difficult-to-comprehend series of nested declarative operations on algebraic sets.
• ggrothendieck 5y ago
  The link refers to dplyr not being able to use databases but actually there is a database backend for it in package dbplyr. See https://dbplyr.tidyverse.org/ https://dbplyr.tidyverse.org/
• nassimsoftware 5y ago
  It would be definitely interesting to have a TypeScript of some sort but for SQL. So a more practical and prettier syntaxe like what I'm seeing here that compiles to SQL queries.
  • eatonphil 5y ago
    TypeScript is more verbose than JavaScript. While I love to use TypeScript I don't think I'd categorize it as prettier than JavaScript. And practical... well if you mean it is more maintainable then yes but if you mean faster to write then no. I don't want a more verbose SQL I want a less verbose SQL!
    • nassimsoftware 5y ago
      I meant more the aspect that with TypeScript people would prefer to write in it and then compile to JavaScript because there is a benefit. With a PRQL with an SQL compilation target we would reap the benefit of a more practical and better syntax. In both cases they bring benefits but not in the same way.
    • text2db 5y ago
      >I don't want a more verbose SQL I want a less verbose SQL! A little off topic, but I have created many databases by writing and converting simple text to database scripts Try this: https://text2db.com/ https://text2db.com/
  • dirslashls 5y ago
    Go to https://sqlframes.com/demo https://sqlframes.com/demo and in the code editor enter the following and execute (this example is taken from the first example on PRQL github page). It generates SQL, but it also computes and displays the results within the browser (though the data set below gives no results). const employees = SQL.values([{ title: 'Developer', country: 'USA', salary: 120, payroll_tax: 20, healthcare_cost: 6 }]); employees.schemaName = 'employees'; const { groupBy, where: { gt, eq, and }, agg: { count, sum, avg } } = SQL; return employees.pdf(SQL.script('[salary]+[payroll_tax]').as('gross_salary'),SQL.script('[gross_salary]+[healthcare_cost]').as('gross_cost')) .fdf(and(gt('gross_cost',0),eq('country','USA'))) .gdf(groupBy('title','country') ,avg('salary').as('average_salary') ,sum('salary').as('sum_salary') ,avg('gross_salary').as('average_gross_salary') ,sum('gross_salary').as('sum_gross_salary') ,avg('gross_cost').as('average_gross_cost') ,sum('gross_cost').as('sum_gross_cost') ,count().as('count')) .having(gt('count',200)) .orderBy('sum_gross_cost');
• iblaine 5y ago
  The syntax seems similar to Apache Pig. Both are declarative and primarily built to be a procedural form of SQL.
• pmontra 5y ago
  First, kudos because it takes courage to take on SQL in this way. Second, this kind of reversed SQL (filter-first, select-last) is much easier to reason about than the original and keep in mind that I prefer to code complex queries in SQL than to build or translate them in the ORM of the project I'm working on. Maybe a transpiler is an inevitable first step but I think that any SQL replacement should be itself the target of ORMs and run directly in the database CLI tools (psql / mysql ...) or IDEs (pgAdmin, MySQLAdmin, ...). What's the long term plan of the project?
  • maximilianroos 5y ago
    Thanks! I agree that integrating with the DB would allow much more from a lang. But PRQL is a bet that languages which start there (e.g Kusto) get lost because it requires changing DB, which is really hard. I worry EdgeDB may hit this issue too (but I'm really hoping it works, and they have an excellent team). As I think you're suggesting — you could imagine a language starting out as a transpiler, and then over time DBs working with it directly, cutting out some of the impediment mismatch. Malloy [1] is another point in space — it targets existing DBs through SQL queries but can also ask for schemas etc while developing. [1] https://github.com/looker-open-source/malloy https://github.com/looker-open-source/malloy
    • anentropic 5y ago
      FYI I think the phrase you're looking for is "impedance mismatch" (I noticed this on the github readme too)
      • maximilianroos 5y ago
        Ah thank you!
  • dragonwriter 5y ago
    > Second, this kind of reversed SQL (filter-first, select-last) is much easier to reason about than the original Given that SQL clauses tend to be unambiguously terminated by the start of the next clause or the end of the statement, it surprises me that no engine has gone to accepting otherwise standard(-ish, as much as real DB vendor dialects are) SQL but without a mandated order of clauses. And then combine that with dev tools that allow easy rearrangement of clauses, perhaps based on configured preferences so that you don’t even see the original if its not your preferred order, so that “Bob likes old-school SELECT FROM WHERE GROUP BY and Alice likes FROM WHERE GROUP BY SELECT” isn’t a problem.
• Nican 5y ago
  Also worth looking at KQL: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...
  • leokennis 5y ago
    When you said “KQL” I thought you meant “Kibana Query Language” but I guess every letter of the alphabet followed by QL is already taken twice over…
• jeroen79 5y ago
  to be honest i find this makes it just more complex and less readable then normal sql
  • LittlePeter 5y ago
    Same here. I find the SQL query in the README more readable.
• tfehring 5y ago
  Very cool! A couple questions/suggestions off the top of my head: 1. Did you consider using a keyword like `let` for column declarations, e.g. `let gross_salary = salary + payroll_tax` instead of just `gross_salary = salary + payroll_tax`? It's nice to be able to scan for keywords along the left side of the window, even if it's a bit more verbose. 2. How does it handle the pattern where you create two moderately complex CTEs or subqueries (maybe aggregated to different levels of granularity) and then join them to each other? I always found that pattern awkward to deal with in dplyr - you have to either assign one of the "subquery" results to a separate dataframe or parenthesize that logic in the middle of a bigger pipeline. Maybe table-returning functions would be a clean way to handle this?
  • maximilianroos 5y ago
    Thanks! > Did you consider using a keyword like `let` for column declarations Yeah, the current design for that is not nice. Good point re the keyword scanning. I actually listed `let` as an option in the notes section. Kusto uses `extend`; dplyr uses `mutate`; pandas uses `assign`. I opened an issue here: https://github.com/max-sixty/prql/issues/2 https://github.com/max-sixty/prql/issues/2
    • maximilianroos 5y ago
      I've added the `let` keyword given a few people commented on this.
      • justinpombrio 5y ago
        Awesome that you're responding to feedback like this! Another suggestion around `let`: consider splitting it into two operations, for creating a new column and for modifying an existing one. E.g. called `let` and `set`. Those are in effect pretty different operations: you need to know which one is happening to know how many columns the table will have, and renaming a table column can with your current system change which operation is happening. Splitting them into separate operations would make things easier on the reader: they can tell what's happening without having to know all the column names of the table. And it shouldn't really be harder for the writer, who ought to already know which they're doing. I encountered something like this at my previous job. We had a DSL with an operation that could either create or modify a value. This made the code harder to read, because you had to have extra state in your head to know what the code was doing. When I rewrote the DSL (the rewrite was sorely needed for other reasons), I split the operation in two. I was worried people would have been too used to the old language, but in practice everyone was happy with it.
• gibsonf1 5y ago
  SPARQL. Representing human information in relational tables goes against how people actually think and use information. We humans think in tremendous numbers of nested hierarchies, and recursive hierarchy traversal is a nightmare in relational databases. A graph is the structure for data that works best, is most efficient, and actually reflects how things are connected in our brains.
  • mindcrime 5y ago
    I'm a big fan of SPARQL, but the one thing that would concern me about trying to use it outside of the SemWeb context is simply that it assumes data is stored in <S,P,O> triples. Legacy databases by and large are not, so you need an adapter to bridge the representations. And while I know some exist, I haven't really used them and am not sure about the performance impact.
    • mst 5y ago
      You can get quite far mapping the triple concept to (PK, column, value) or (PK, FK, related-row) and transpiling from there. (I played around with this some years back, not to the point where anything came out of it worthy of publishing, but enough to be pleasantly surprised how far 'quite far' turned out to be in practice)
• haolez 5y ago
  This actually looks like an improvement (and I like SQL). This feels closer to non-programmers, contrary to some other SQL "competitors" like that query language from InfluxDB.
  • thanatos519 5y ago
    It definitely scans better than 'Flux' from InfluxDB2. One thing I like about Flux is the ability to split streams and return multiple distinct aggregations. Very handy in Grafana dashboards!
• nikkinana 5y ago
• jacktheturtle 5y ago
  Do people still write SQL?
  • onlyrealcuzzo 5y ago
    It's regularly listed in the top 5 most commonly used programming languages in the world [=
  • badhombres 5y ago
    Absolutely. If I use a SQL db for my applications (I'm a software dev for context), I generally write raw SQL vs using an ORM. I find the long term issues of an ORM to not be worth investing and understanding SQL. I'm also not having to learn a new library, in addition to the standard DB connection libraries, ~if~ when I switch a language or platform for some project.
  • drpotato 5y ago
    Yes, quite a lot I would imagine. I use it extensively at work and similarly sql heavy software companies in the past. That being said, I’ve also worked at places where they’ve avoided it like the plague – largely because few people were competent at it – and were moving away from relational DBs due to scale.
  • extrapickles 5y ago
    I prefer to write SQL as most alternatives ether have runtime surprises or require more roundtrips to the database. I mostly work on line-of-business software, so if I was doing simple CRUD apps I might have a different opinion.
  • didip 5y ago
    Of course, and it is vastly better than using an ORM.
  • MrBuddyCasino 5y ago
    Do people still use ORMs?
  • deleted 5y ago
    [deleted]
• adamrezich 5y ago
  I like this a lot, and I eagerly anticipate an early version of a transpiler to play with!
• mongol 5y ago
  I think it would be worthwhile to develop a shorthand of the same thing, suitable for use on the command prompt. Something using symbols as synonyms for keywords. Less eligble but more useful in a future when shell tools understand this syntax.
• hackeredje 5y ago
  Is this not Linq ? https://stackoverflow.com/questions/tagged/linq https://stackoverflow.com/questions/tagged/linq ? And then dump the queries via https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework https://stackoverflow.com/questions/1412863/how-do-i-view-th... or https://www.linqpad.net/ https://www.linqpad.net/ ?
  • aloisdg 5y ago
    Indeed. It looks a lot like dotnet's Linq.
    • cerved 5y ago
      wr to linq to sql: the difference is linq works by making objects to tables and dotnet primitives to sql types, often producing really poor queries as a result
      • rswail 5y ago
        That's the underlying implementation decision, not necessarily caused by the language syntax/semantics.
• andreygrehov 5y ago
  I like it. Was it inspired by CloudWatch Logs Query Language? [0] Looks somewhat similar. [0] - https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax.html https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL...
• btown 5y ago
  For any who want this kind of pipelining way-of-writing-SQL that has the benefit of existing in live production databases today, I highly, highly recommend looking into Postgres' and Snowflake's LATERAL JOIN keyword. https://heap.io/blog/postgresqls-powerful-new-join-type-lateral https://heap.io/blog/postgresqls-powerful-new-join-type-late... https://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-join-and-a-subquery-in-postgresql https://stackoverflow.com/questions/28550679/what-is-the-dif... https://docs.snowflake.com/en/sql-reference/constructs/join-lateral.html https://docs.snowflake.com/en/sql-reference/constructs/join-... The TL;DR is that they allow you to reuse annotated and aggregated columns in an incredibly elegant way. Compared to OP's proposal, you still do need to start the query with what columns you want to come out at the end, and normal SQL weirdnesses still apply - but it's far, far, easier when writing massive analytics queries to see the flow of variables from one stage to another.
• rchrch 5y ago
  Awesome! Would love to see an implementation. I worked on something similar over the Summer. It’s just relational algebra with pipes for composition. If you are interested, we could get an antlr grammar going and plug it into this basic execution engine to get a feel for the language. - https://github.com/RCHowell/Sift https://github.com/RCHowell/Sift - https://github.com/RCHowell/Sift/blob/main/src/main/kotlin/com/rchowell/sift/language/v0/README.md https://github.com/RCHowell/Sift/blob/main/src/main/kotlin/c...
  • maximilianroos 5y ago
    Yes this looks really cool, and similar! Feel free to hit me up on Twitter https://twitter.com/max_sixty https://twitter.com/max_sixty
• lijogdfljk 5y ago
  I wonder how this compares in practice to EdgeQL, https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql Offhand i thought PRQL seemed easier to reason about, but something about EdgeQL seems better to me.. though i can't describe it.
  • maximilianroos 5y ago
    I see EdgeQL as an excellent replacement for SQL in OLTP settings — it has great language integration and a unified relational & typing approach. (Please correct me if this is mistaken though). I wrote the PRQL proposal for analytical / OLAP queries, where the pipeline of transformations are more important, and relations and typing are relatively less important.
    • RedCrowbar 5y ago
      EdgeQL is getting support for generic partitioning/aggregating `GROUP` very soon [1], so we are giving some love to the analytical side of things too :-) We definitely need more collective effort put into "Better SQL", so PRQL is a welcome sight! [1] https://github.com/edgedb/rfcs/blob/21e581a188715c6ff82944b628a85600044b8667/text/1009-group-by.rst https://github.com/edgedb/rfcs/blob/21e581a188715c6ff82944b6...
• hardwaregeek 5y ago
  I've always wondered why there aren't query languages that embrace algebraic data types and pattern matching. Seems like an obvious fit to me. There's many times where you'd want to model a table that has either this scheme or that schema.
  • jlokier 5y ago
    They can work well. In the project I'm working on the database uses algebraic datatype keys (i.e. tags and tag-dependent columns) to make the database faster and smaller than an equivalent relational schema, but the database is used via API rather than via a query language.
  • jayd16 5y ago
    Check out C# and LINQ. They pull it off with anonymous types.
• eximius 5y ago
  > A line-break doesn't created a pipeline in a few cases: ... > When the following line is a new statement, by starting with a keyword such as func. This feels like it could cause compatibility issues in the future.
• Ngunyan 5y ago
• munk-a 5y ago
  I like the flow direction compared to standard SQL. SQL is supposed to read like a sentence I suppose but I have many times looked at it and really wanted things to be in a more logical order. My main suggestion would be to be a bit less terse and introduce a bit more firm formatting. I'm not a huge fan of the term "split" and feel like jazzing that up to "split over" or even just reviving "group by" would improve readability. Additionally the aliasing could use work, I'd suggest reversing the assignment to be something closer to `use salary + payroll_tax as gross salary`. In terms of firm formatting, unless I'm missing something there isn't any reason to allow a filter statement before any aliases - so you can force two fixed positions for filter clauses which would make it always legal to reference aliases in filters. On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler - those silly do nothing queries like "SELECT * FROM customer WHERE deleted='f'` are written once[1] in a moments time and never inspected again. The complex queries are what you want to optimize for. 1. If they even are - with ORMs available a lot of those dead simple queries just end up being done through an ORM.
  • maximilianroos 5y ago
    This is great feedback, and I agree with you re de-prioritizing terseness. And I agree with you on both the assignments and `split` being a bit awkward. Kusto just uses `by`, WDYT?
    • tomtheelder 5y ago
      Not the original commenter, but just using `by` makes total sense to me.
      • maximilianroos 5y ago
        I've made this change [1]. Thank you! [1] https://github.com/max-sixty/prql/commit/dde7fcfc13daaadbdce0321beaa0f62e753f8473 https://github.com/max-sixty/prql/commit/dde7fcfc13daaadbdce...
• lxe 5y ago
  Nice. Why OCaml though? I think using a more conventional language to construct queries could yield more adoption. It also seems that ORMs kinda of exist to tackle a similar issue, at least in part.
  • HellsMaddy 5y ago
    I don’t see anything dealing with OCaml here, other than it being listed as an inspiration.
• tpoacher 5y ago
  meanwhile I'm still stuck trying to make prolog work xD
• beagle3 5y ago
  shakti / K / kdb+ implements "real SQL", which is concise but readable, and could give you a few ideas. Here's a copy-paste from https://shakti.sh/ https://shakti.sh/ under document/sql.d (cannot deep link, unfortunately). The most most magical aspects are automatic joins - both left joins and "foreign key chase" joins. The fk-chase joins, in particular, should be part of every query language, and can possibly be added in a backward compatible way to existing SQL implementations. example: TPC-H National Market Share Query 8 http://www.qdpma.com/tpch/TPCH100_Query_plans.html http://www.qdpma.com/tpch/TPCH100_Query_plans.html what market share does supplier.nation BRAZIL have by order.year for order.customer.nation.region AMERICA and part.type STEEL? real: select revenue avg supplier.nation=`BRAZIL by order.year from t where order.customer.nation.region=`AMERICA, part.type=`STEEL ansi: select o_year,sum(case when nation = 'BRAZIL' then revenue else 0 end) / sum(revenue) as mkt_share from ( select extract(year from o_orderdate) as o_year, revenue, n2.n_name as nation from t,part,supplier,orders,customer,nation n1,nation n2,region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'STEEL') as all_nations group by o_year order by o_year;
  • maxwelljoslyn 5y ago
    Thanks for the tip. That automatic "foreign key chasing" looks phenomenal. Byebye, much of that big tedious chunk in the middle of your 2nd example... Wish I had that for more of the SQL I write.
• BeefWellington 5y ago
  This is another in a series of these kinds of proposals that look excellent on first glance for perhaps the 75% case but start getting syntactically messy when I want to customize the resultset returned. On the surface, they're always neat but when you start to dig into how you'd implement something in an RDBMS, it begins to fall apart. Let's look at the example syntax: from employees filter country = "USA" # Each line transforms the previous result. gross_salary = salary + payroll_tax # This _adds_ a column / variable. gross_cost = gross_salary + healthcare_cost # Variable can use other variables. filter gross_cost > 0 aggregate split:[title, country] [ # Split are the columns to group by. average salary, # These are the calcs to run on the groups. sum salary, average gross_salary, sum gross_salary, average gross_cost, sum gross_cost, count, ] sort sum_gross_cost # Uses the auto-generated column name. filter count > 200 take 20 Where in here is it clearly stated which fields are returned? In the original SQL it's right up front but here it's buried into the "aggregate" function, and I'm not clear that this isn't an oversight. Another example that speaks to the "how do I implement this" side of the equation: from employees filter country = "USA" # Each line transforms the previous result. gross_salary = salary + payroll_tax # This _adds_ a column / variable. gross_cost = gross_salary + healthcare_cost # Variable can use other variables. filter gross_cost > 0 Does this mean that the database must scan all records of the employee table in order to return the result before moving to the next step in the query? Must I index all fields? If not, how does a query planner prepare for this scenario? The major tradeoff you make in most ORMs is exactly this: You lose out on being able to be explicit about how many queries are sent to the DB (and in many cases how efficient those queries are). Now this would become a language feature? What do I gain for that loss? I'm not saying that SQL Syntax is perfect; far from it. I'm not seeing how this is an improvement. I think if you want traction though, a proof of concept using an existing RDBMS would go a long way into providing evidence that this will work and is sufficiently thought out to deal with even the basics of what existing SQL databases have to. Query planning is hard, especially if you want it to be fast.
  • tomtheelder 5y ago
• magicalhippo 5y ago
  Now this is actually nice, unlike the other suggestion posted today[1]. Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gross_cost) AS sum_gross_cost, COUNT(*) as count FROM ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' ) emp WHERE gross_cost > 0 GROUP BY title, country ORDER BY sum_gross_cost HAVING count > 200 This cuts down the repetition a lot, and can also help the optimizer in certain cases. Could do another nesting to get rid of the HAVING if needed. Still, think the PRQL looks very nice, especially with a "let" keyword as mentioned in another thread here. [1]: https://news.ycombinator.com/item?id=30053860 https://news.ycombinator.com/item?id=30053860
  • ako 5y ago
    With a CTE it would read a bit more like prql: with usa_employees as ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' AND (salary + payroll_tax + healthcare_cost) > 0 ) select title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gross_cost) AS sum_gross_cost, COUNT(*) as emp_count from usa_employees group by title, country having count(*) > 200 order by sum_gross_cost limit 3 Readability is pretty similar to prql. It would really help in SQL if you could refer to column aliases so you don't have to repeat the expression.
    • magicalhippo 5y ago
      > With a CTE The DB we use supports those, I just learned about them too late so keep forgetting they exist :( > It would really help in SQL if you could refer to column aliases so you don't have to repeat the expression. The DB we use supports that, so in your CTE you could write AND gross_cost > 0 We do that all the time, which will be a pain now that we're migrating to a different DB server which doesn't.
• thelittlenag 5y ago
  I worked on a language at one of the big banks that looked very similar. The goal was to have a better-than-SQL language on top of Spark and I think we succeeded at that very well. Unfortunately, politics killed the language and platform.
• deepstack 5y ago
  Wouldn't it be better to just use something like prolog, there is already and extension for postgres?
• onphonenow 5y ago
  Any chance of starting with FROM perhaps to help with autocomplete? From Table or FROMT TABLEA JOIN TABLE B ... The autocomplete would then be able to introspect much better in the tooling side.
  • ithrow 5y ago
    Queries start with FROM, it's called a README for a reason...
• kovek 5y ago
  Could there be a tool that would translate PRQL to SQL? One could then write in ~/my_scripts/closest_points.prql and then run a command to get the sql equivalent and use that in exiting SQL tools that do not currently access PRQL (like Postgres).
  • ako 5y ago
    From the PRQL page: "PRQL transpiles to SQL, so it can be used with any database that uses SQL."
• divan 5y ago
  Obligatory reading https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/
• betimsl 5y ago
  We barely learned SQL for all these years and the guy wants to change it now. Thank you but no thank you. JK. Cool concept and hopefully it catches.
• ridaj 5y ago
  Very cool Design goal question - is the goal to have a language that blindly compiles to SQL, or will compilation require data schema knowledge? Suggestion: where possible stick to the well established SQL keywords (prefer "group by" to "aggregate by")
  • maximilianroos 5y ago
    Initially the transpiling would be context-free of the schema. It would really nice to have context when _writing_ the code, so we could do things like autocomplete (IIUC this is what Malloy does already). Are there features you can think of that would be helpful if we had the schema context during transpiling?
    • ridaj 5y ago
      I wasn't thinking it'd be useful, but more that it's good to remain as free as possible from any assumptions about the schema of the data. I often work with tables that have unusually large schemas (> 100MB) and have seen some products' performance severely degrade as a function of schema complexity. (But otherwise +1 to schema awareness during authoring.)
    • hadley 5y ago
      From dbplyr experience, folks want to be able to do stuff like `across(which(is.numeric), mean)`, which you can't do currently because dplyr doesn't know the column types (although it does maintain a list of the column names).
      • maximilianroos 5y ago
        Yes, that's a good example. Indeed — often SQL doesn't know the column _names_, let alone the types. So for cross-column selection (e.g. `across`), this does become necessary.
• ianbicking 5y ago
  I like it, it's readable, unlike some SQL alternatives I've seen it doesn't make me feel like I'm dumb and don't understand what a query even is. I can't decide if it would be better or worse if it stuck more closely to SQL keywords. You use "from" and "select", but not "where", "order by", "group by". There's some danger of it being in an uncanny valley of SQLish, but I'm pretty sure I'd prefer just using those terms verbatim (including the space in "order by"... that style is less common in modern languages but it's not really that much harder to parse). I'd like to see more examples of joins and composing SQL. Does this language make it easier to make more general SQL queries? Can I take two queries and squash them together in a reliable way? I feel like I end up with a lot of theme and variation in my queries, often involving optional filters. I might even like a notion of encapsulation that could help this query language when it's embedded in other languages. Like if I could say, in the language itself, that a query has certain unbound variables (and not just ? or other placeholders). This language seems like it would be better for generating than SQL, and sometimes generation is just necessary (like in any application that supports data exploration), but for most common cases I'd hope to avoid that. Defining inputs and then making whole filter sections or other statements conditional on those inputs would help here.
  • cogman10 5y ago
    Yup, I like a lot of things about the way this looks. In particular, I like how friendly this looks to be for things like auto complete (pretty annoying to need to practically type the entire sql query only to go back and fix up the columns in order to get autocomplete to work). Specific things I'd like to see. How do you handle column ambiguity. In the examples, they show a join of positions to employee on employee_id == id. But what happens when you have 2 columns with the same name that you are joining on? (like employee_id to employee_id in some mapping table). Subqueries are pretty important in what I do, so what do those look like (perhaps covered by the "thinking about CTEs section"). How about opportunities for optimization hints? In T-SQL you can hint at which index the optimizer should prefer to a specific query. Common SQL patterns would also be interesting. Like, how would you do keyset pagination? Edit: Also, I'd like a discussion about null. SQL null handling rules are terrible. I understand them, I work with them, but at the same time, they are so different from other languages concept of "null" that they are easy to trip over.
• slaymaker1907 5y ago
  This seems very similar to Kusto/KQL.
• nurettin 5y ago
  To be fair, lateral joins (cross/outer apply in mssql) can help with name aliasing and table functions give sql some reusability. I think the main pain points for sql are pivots and window functions. A lot of the time you just want to transpose your result, but you have to choose an aggregate and handle null cases to force pivot to work the way you want it. And a lot of the time you want to aggregate a window but keep the ids of the row so you avoid the having keyword altogether and go for row_number and dense_rank to get your aggregate results. If I were to write a query language, I would discard group by and having and make it easier to apply transpose and window functions.
• galkk 5y ago
  I'm quite opposed to the idea "from should be first". I want to understand what exactly the query returns, not the implementation detail of the source of this data (that can later be changed). Literally first example from page - I have no idea what is being returned: from employees filter country = "USA" # Each line transforms the previous result. let gross_salary = salary + payroll_tax # This _adds_ a column / variable. let gross_cost = gross_salary + benefits_cost # Variables can use other variables. filter gross_cost > 0 aggregate by:[title, country] [ # `by` are the columns to group by. average salary, # These are the calcs to run on the groups. sum salary, average gross_salary, sum gross_salary, average gross_cost, sum gross_cost, count, ] sort sum_gross_cost # Uses the auto-generated column name. filter count > 200 take 20 of course, similar things are happening to SQL too, with CTEs becoming more widespread and "real" list of the columns hidden somewhere inside, but it's still parseable
  • inglor 5y ago
    The big advantage of "from first" like we have in Kusto KQL (a database we use at Microsoft) is that it provides much better autocomplete (if I write the `from` it can easily autocomplete the projection). If you want an interesting example of how a query language built for developer experience and autocompletions looks definitely check it out!.
    • _jal 5y ago
      Designing languages around autocomplete is like designing toilets for better toilet paper dispensers. The language should be right for human understanding, not automated mad-lib generation.
      • emteycz 5y ago
        I'd agree if there was any way whatsoever of fixing this issue, but there simply isn't. The editor can't even begin to guess what you might want until you write your FROM.
• cies 5y ago
  I love quality language proposals like this. I'm not so much in data processing/bigdata, but have had to interact with SQL a lot. This syntax is lovely! It's more intuitively readable (and SQL is not that bad in that regard). My feedback: 1. Lower case, underscored everything makes the terms a bit hard to differentiate. Maybe set some classes of symbols in CamelCase, or add a !@#$%& prefixes to them to make it more readable. 2. I dont like to use another language (SQL or PRSQL for that matter) to db interaction, I like to write the queries in the language that I'm using to develop in. There are ORMs in this design space, but I'm a little fed up with them. In Java there's jOOQ. Other less-OO-more-functional ORMs exist in Rust and Haskell land. These often have a code generation step, a library is generated that guarantees some type safety for a give schema version. Some are more SQL-like, some provide a different API. PRQL is much more diverted from SQL than these, and for good reasons. Maybe several languages could easily have libs like this building on top of JPQL? 3. You solution is a bit like GraphQL in some regards; where there is a tool needed to convert the query to SQL. Tools like this exist, like Hasura and the likes. Hasura does a lot more. To me GraphQL has the huge advantage of serving a schema so that clients can be generated. I can interact with GraphQL in a type safe fashion from by generating a client in, say, Elm. The generated client lib does not allow my to write syntax errors in my queries and ensures all type conversions are sound. Maybe PRQL can also be a language like GraphQL in that regard, and provide a schema too. 4. JPQL. It's close to SQL. It improves to SQL, but I never found it enough of an improvement to justify the cost. I think your proposal is better. But still I think JPQL deserves a mention as maybe one of the most widespread compile-to-SQL languages.
• 999900000999 5y ago
  >Compatible — PRQL transpiles to SQL, so it can be used with any database that uses SQL. Where possible PRQL can unify syntax across databases. PRQL should allow for a gradual onramp — it should be practical to mix SQL into a PRQL query where PRQL doesn't yet have an implementation. Awesome. I hate SQL so much, I know for personal projects this is gold. I imagine actually using it at work might draw some questions though
• peoplefromibiza 5y ago
  This is the kind of things that a well crafted DSL can solve, if the language you use supports macros. PRQL looks very similar to Ecto, the Elixir Query DSL https://hexdocs.pm/ecto/Ecto.Query.html https://hexdocs.pm/ecto/Ecto.Query.html
• akdor1154 5y ago
  Looks really nice, i've been scribbling away in a little notebook all the things i would do in "akdor's dream sql", and what you have here hits pretty much exactly. Wondering about generic use of `let` - you have let for col defns, but `func` for functions and a TODO for tables/CTEs - could/should `let` do the lot? (Like another commenter posted, this is how MS's M language, used in PowerQuery in PowerBI and Excel works). Could enable an escape from point-free for entire queries if taken to extreme generality, not sure if that's a good thing, maybe it could be? Bikeshedding: even with some OCaml/F# experience, i find `f x y` harder to read than `f(x, y)`.
  • maximilianroos 5y ago
    Thanks! At the moment `let` is used to add a column as part of an existing pipeline. [1] `func` is the start of new expressions / pipelines. And I just added a proposal for `table = `, which would be the same. Does that make sense? Very open to more feedback... [1] I just added `let` based on feedback here, it's better than it was, but not perfect, as it can be confused for a new pipeline given its use in other langs.
• ajkjk 5y ago
  Just wanna say, I absolutely love this.
  • ajkjk 5y ago
    One piece of feedback: "sort sum_gross_cost # Uses the auto-generated column name." ... seems like a huge landmine. Languages really should not have any implicit way of constructing identifiers (among other reasons it is not easily greppable). You might consider using a syntax like `sum:gross_cost` which can function as a sort parameter and an aggregation, but is actually recognizable as an object instead of having an implicit transformation going on in the background. Like this: ... filter gross_cost > 0 aggregate by:[title, country] [ sum:gross_cost, ] sort sum:gross_cost ...
    • topspin 5y ago
      > # Uses the auto-generated column name The fact that someone felt the need to add that comment hints at a design mistake. Synthesizing symbols is weird, unnecessary and is probably a violation of the principle of least surprise. Otherwise I think PRQL has some value. Nice work. I strongly suspect that if SQL looked more like this there would be a lot more people willing to use the query language directly and perhaps fewer that are compelled to bury it under shifting layers of fragile abstractions.
    • maximilianroos 5y ago
      Yes, I agree with the downsides of the existing approach, and you're absolutely right re "Languages really should not have any implicit way of constructing identifiers". I'd still say it's fine if it implicitly names output columns, for convenience. I referenced your comment in an issue and listed some options: https://github.com/max-sixty/prql/issues/5 https://github.com/max-sixty/prql/issues/5
• snidane 5y ago
  You don't need additional SQL repetitive cruft. from population select country, rollup(city), count(*) sort Can represent this repetitive SQL query: select country, city, count(*) from population group by country, rollup(city) order by country, city Information in group by is often redundant. You can tell which columns are measures vs dimensions by examining the 'aggregate' function - rollup or no function vs sum, count, avg. Order by can have a default to sort by all columns instead of naming them one by one.
• blintz 5y ago
  This is a nice idea, especially given all the work people have done recently to make in-language querying nicer (Spark comes to mind). My only gripe is the 'auto-generated' column names for aggregates. This seems like a recipe for disaster - what if there is already (as there almost certainly will be) named "sum_gross_cost"? The behavior also just seems rather unexpected and implicit. My suggestion would be simple syntax that lets you optionally give a name to a particular aggregate column: ... filter gross_cost > 0 aggregate by:[title, country] [ average salary, sum gross_salary, average gross_cost, let sum_gc = sum gross_cost, count, ] sort sum_gc While it might seem a little uglier, it seems much more sustainable in the long run. If this is really too gross, I'd advocate some token other than underscore that is reserved for aggregation variables; perhaps `sum@gross_cost` or `sum#gross_cost`.
  • maximilianroos 5y ago
    Definitely — giving the option of naming them is great. I'm not sure whether we should force naming? When I'm writing a query often I'm fine with something auto-generated when starting out.
  • yen223 5y ago
    > My only gripe is the 'auto-generated' column names for aggregates For what it's worth, a similar problem already exists with SQL. Something simple like select count(*) from my_table; automatically aliases the column to `count`, even if `my_table` has a column called `count`. In practice, I don't think this is a major problem.
    • ziml77 5y ago
      Which database does that? MSSQL doesn't assign any name to the column in this case.
      • yen223 5y ago
        Postgres does this
• nextaccountic 5y ago
  I also saw a proposal for a better syntax for SQL, called BQL, that was a strict superset but allowed for better modularity http://intelligiblebabble.com/a-better-query-language-bql-language-specification/ http://intelligiblebabble.com/a-better-query-language-bql-la... It had this github repo https://github.com/lelandrichardson/BQL https://github.com/lelandrichardson/BQL but never went anywhere I hope PRQL has a better fate! unfortunately, by deviating from SQL lexical conventions (using :, using [], etc) we lose the ability to copy-paste from sql code elswhere. I want a better SQL, but I also want some compatibility. Like typescript is for javascript.
• jcdreads 5y ago
  I like that everyone is trying to make something like SQL that reads more naturally to them. More alternatives is good! SQL is a widely accepted standard, and has strictly defined and super broadly accepted semantics. As someone who has written quite a few half-baked-for-general-use but fit-for-purpose SQL generator utilities over the years, I'll suggest that if you intend for a novel syntax to be a general SQL replacement then being isomorphic to SQL would massively increase usefulness and uptake: 1. novel syntax to SQL; check! Now novel syntax works with all the databases! 2. any valid SQL to novel syntax; a bit harder, but I'd start by using a SQL parser like https://github.com/pganalyze/libpg_query https://github.com/pganalyze/libpg_query and translating the resulting AST into the novel syntax. 3. novel syntax to SQL back to novel syntax is idempotent; a nice side effect is a validator/formatter for "novel syntax" 4. SQL to novel syntax back to SQL is idempotent; a nice side effect is a validator/formatter for SQL, which would be awesome. (See also https://go.dev/blog/gofmt https://go.dev/blog/gofmt, which is where I learned this "round trip as formatter" trick.) I don't mean for this to sound negative, and I know that 2, 3, and 4 are kind of hard. Thank you for building prql!
• CarVac 5y ago
  I was reading the criticism and suggestions and wondering what they were talking about, it seemed to read perfectly fine… Then I saw that the project had already been changed in response! Impressive.
• 29athrowaway 5y ago
  SQL is like BASIC for data. It is a language created to read like natural language, to facilitate its adoption. Not only developers use SQL, less technical users also use it, especially in the finance industry. Now, do you use BASIC professional software development? While some do, most don't. So why do we use SQL then? An imperative language that compiles to SQL can be attractive, but unless the compilers come with good compiler warnings, we may end up created bloated database code.
• cryptonector 5y ago
  The most important dialect of SQL we should get is SQL-with-no-literal-values so we can force the use of query parameters, and then not have SQLi.
• spullara 5y ago
  SQL could get a lot better by just adopting the ordering of operations like they did with LINQ: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/basic-linq-query-operations https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...
  • tester756 5y ago
    Funnily enough LINQ Query syntax is really uncommon and everybody uses method syntax var list = new List<int>{1,2,3} var extracted = list ....................Where(x => x > 1) ....................Select(x => $"my number: {x}) ....................ToList();
    • aloisdg 5y ago
      Because the original felt odd in C#.
    • louthy 5y ago
      > everybody Not everybody
      • tester756 5y ago
        99%? 98%?
• dgudkov 5y ago
  >PRQL is intended to be a modern, simple, declarative language for transforming data It's not declarative. It's functional. I believe that the approach that is followed by PRQL is more practical than SQL. We've implemented a similar approach in our visual ETL tool for non-technical people (https://easymorph.com https://easymorph.com) and it works wonderfully. Other cool things you can do with this approach (and can't with SQL): * Modify existing columns without re-selecting the whole dataset * Loops (iterations) * Conditional IF/THEN/ELSE branching as a workflow statement * Exceptions & error handling
• oblio 5y ago
  Man, I really hope something like this takes off. SQL is so entrenched but it's really showing its age and its pitiful standardization.
• skissane 5y ago
  How does this compare to QUEL? Or Tutorial D?
• dragonwriter 5y ago
  > It's not declarative. It's functional. Functional, logical, and relational paradigms are the most commonly cited examples given of declarative programming.
• bchammer 5y ago
  This looks like a turing-complete language if you add function. One benefit or current SQL is that it can (and is) often rewritten to be executed more efficiently. This requires a simpler and declarative model - far from a programming language. If you want simple 'forward declaration' take a look at the SQL WITH clause.
• politician 5y ago
  I prefer "from first". When I write a SQL query, 99 times out of 100, I'll start with "select * from", then fill in the query, then go back and select the columns. After a few basic joins, "easy" column names have probably grown aliases or have been subsumed entirely. For me, "select * from" is automatic.
• oblio 5y ago
  To the author, if he's reading this. I wish you luck! To get this adopted, try to make it modular so maybe it can be made a core module of PostgreSQL, MariaDB, MySQL. If you somehow get into those 3 I wouldn't be surprised if Oracle, SQL Server and DB2 integrate it themselves just to keep up. Ah, Presto could be another popular target. It's a huge effort but something which could have colossal payback.
  • twic 5y ago
    This language transpiles to SQL, so it can be implemented entirely client-side. No need for modules for the database engines, just wrappers round the clients.
• JimmyRuska 5y ago
  Wow, between this and Malloy , there's a lot of great ideas. It'd be great if ideas from sparql/datalog/datomic were also picked up. It's easier to write recursive queries.
• glogla 5y ago
  This is cool! I love it. Few notes: 1) SQL also allows you to define windowing reusably, like this select sum(blah) over window_abc, avg(blah) over window_abc from table_xyz window window_abc as (partition by x order by y) so that second example could be written somewhat less repetitively but it wouldn't change the whole point. 2) Sadly my main pain point with SQL for ETL is not possible to solve with a transpiler - SQL has exactly one target so doing things like "I want these records to go to a table A and those records go to table B" is not possible with one query. 3) It would be cool to see how this does typically annoying and repetitive cases from analytics / data warehousing world. I'm thinking like SCD1/2 implementation. But I don't even know if mutation is there yet. 4) I would recommend investing in one canonical formatter, like Go has. So that there isn't infinite number of ways the same query could be formatted for people to argue over preference. EDIT: 5) Since this seems to be focused on analytics (by the choice of queries and Snowflake in examples), I want to highlight that someone suggested to use TPC-H (or TPC-DS) queries as a benchmark. It does sound like a good idea.
• _dain_ 5y ago
  Does this let you write functions that take tables as parameters and return tables? It always seemed weird to me that SQL doesn't let you do this.
• hyperpallium2 5y ago
  \tangent What are today's data transformation needs (differ from Codd's inspiration?), how does relational algebra serve them, and design an "SQL" around that. There's got to be a 10x leapfrog in benefit for some niche in there, and that's the gateway to adoption.
  • tristanz 5y ago
    To get 10x I think you need to wed it to solving broader workflow challenges, like dbt does today.
• hyperpallium2 5y ago
  nice name
• goodlinks 5y ago
  maybe i am jaded (too many hours reading and writing sql as it is) but the structure of SQL is very closely linked to how i write a query: what data do i want to see: most important where doest is come from: next most important thing What filters and restrictions am i going to put on it: least important
• erezsh 5y ago
  It seems people here are really interested in alternatives to SQL. So perhaps you'd also like to have a look at https://github.com/erezsh/Preql https://github.com/erezsh/Preql (Same name, same goal, different approach, and already working)
• johnthescott 5y ago
  using COMMON TABLE EXPRESSIONS (cte) can greatly improve readability of complex sql queries. adding "flow" just feels like a variation on sequential programming. i am against "improving" sql. instead, i thing a whole rethink of the engineering behind relational engines needs to occur. for example, why can't a relational database support both SQL and other languages simultaneously, instead of being so black-boxish?
  • The_rationalist 5y ago
• eurasiantiger 5y ago
  No graph query support and no possibility to directly output nested structures = no donut.
• int_19h 5y ago
  I'm kinda surprised that the list of influences doesn't mention XQuery. Yes, it's not a relational query language... but it covers much of the same ground in practice, especially the part that they call "FLWOR expressions" (for/let/where/order/return) that operate on "tuple streams": https://www.w3.org/TR/xquery-31/#id-flwor-expressions https://www.w3.org/TR/xquery-31/#id-flwor-expressions And it has grouping, windowing functions etc. I bet you could define a subset that is specifically tailored to the same use cases as SQL - basically, get rid of everything to do with elements and attributes, and only allow scalars and sequences (and maybe maps?). But otherwise keep XDM data types and their semantics.
• deleted 5y ago
  [deleted]
• gigatexal 5y ago
  i really like this, going to follow it for a bit
• political12345 5y ago
  Why not use R's data.table? You can achieve literally the same pipeline style flow, internally it's written in C, it's not the fastest but gets the job done. If you want real speed but no external package, kdb/q is probably better (has q-sql). Good work for the effort etc. but I often don't see the value added of these toy projects of people apart from personal development and a bullet point on the CV of the author.
• josefrichter 5y ago
  It looks very much like Ecto Query http://hexdocs.pm/ecto/Ecto.Query.html http://hexdocs.pm/ecto/Ecto.Query.html
• lambda_dn 5y ago
  You've reinvented LINQ. https://en.wikipedia.org/wiki/Language_Integrated_Query https://en.wikipedia.org/wiki/Language_Integrated_Query
  • deleted 5y ago
    [deleted]
• livinglist 5y ago
  this reminds me of LINQ in C#
• mollerhoj 5y ago
  Given that a lot of times (most of the time?) SQL is written by applications, not humans, how about a syntax based on some data language (e.g. json)? It always seemed strange to me that the API to our databases are (SQL) strings.. The order isn't even important.. Something like yaml or json seems like a better fit? Then of course it would be trivial to build other languages PRQL? on top of that..
• dmoura 5y ago
  I am writing a language and CLI that mixes SQL with Python (spyql: https://github.com/dcmoura/spyql https://github.com/dcmoura/spyql), and it is interesting to see that we are tackling some of the same problems: - code/formula reutilization where we need to repeat logic over the query - functionalities like `EXCEPT` and `REPLACE` modifiers for `SELECT *`, like in google bigquery (in most SQL databases its frustrating when you have a large number of columns and you only want to hide or replace a couple of them) I do think SQL is all over the place, and while not perfect, it’s familiar and we got used to express our queries the SQL way. At the end, since you are generating SQL to interact with databases, you would have to understand SQL in order to optimise your queries (it might be challenging to get the perfect SQL query from PRQL as you do not know statistics about the tables or which indexes are available). With SPyQL I am taking a different approach that tries to extend simple SQL SELECT statements so that some of these annoying features are tackled. In addition, by using Python to define expressions and conditions you solve another problem typically present on databases: extensibility. By including an IMPORT clause in your query you can import any Python module, so the sky is the limit. You also get a simple and intuitive way to work with objects and hierarchical data (like JSON). I do find the language you are proposing very readable and flexible, bringing several advantages to SQL. If you build a parser I would love to bring it to spyql :-) The issues I have brought earlier would not be a problem to spyql since it is a tool to query files and data-streams in the command-line.
• gerdesj 5y ago
  I'm nowhere near an expert but an interested bystander. I find the SQL example easier to understand than the PRQL one. ... then I read the PRQL one again and again. It does read more like English and more like how people think. PRQL is much more descriptive. I quite like it.
• freedomben 5y ago
  Interesting, this language reminds me a lot of Ecto[1] (the DSL "ORM" for Elixir). It took a bit of getting used to but I'm coming to really enjoy Ecto and especially now that sqlite is fully supported, I'm finding myself using it in places I normally wouldn't have. [1]: http://hexdocs.pm/ecto/Ecto.Query.html http://hexdocs.pm/ecto/Ecto.Query.html
• kortex 5y ago
  Love it. This reads so much better in my mind than SQL. I love how easy it is to abstract with functions. My one suggestion: use a distinct symbol for assignment and equality, either :=/==, =/==, or even :=/=. I kinda like the Go way of doing things, := assigns and initializes, == is equality (not sure you have a need for = re-assignment, but maybe). But I would definitely warn against = for context-dependent assignment or equality comparison. You've got "let" so I guess that is kind of a syntactical difference; I guess the trippy part is seeing "=" do equality comparisons.
• hckr1292 5y ago
  Have you considered making this a frontend to apache arrow or datafusion? It seems like Flink or kSQLdb could use this as an alternative syntax to produce the same physical queries. Love kusto!!
• xiaodai 5y ago
  I love how it quotes inspiration from dplyr and also from Julia libraries.
• DiabloD3 5y ago
  We have a better SQL, it's called Postgres.
• datan3rd 5y ago
  Awkward syntax — when developing the query, commenting out the final line of the SELECT list causes a syntax error because of how commas are handled, and we need to repeat the columns in the GROUP BY clause in the SELECT list. There are some SQL varieties that actually allow a hanging comma! Also, the provided examples seem comma-dependent, no? As someone who writes a ton of analytical SQL, i think this would get super messy for long, complex queries with casting, case statements, windows functions, etc. Most people just need to learn to write better SQL!
• SNosTrAnDbLe 5y ago
  I would also take a look at MDX which comes from Microsoft for classical analytical processing workloads. I feel that it provides a nice way to think of data in terms of dimensions/attributes and hierarchies and measures as it gives a good way to think of what to aggregate vs what fields to use to do that aggregation. SQL is feels a little more generic and it would be nice for it to have a little more schema aware specifically for analytical workloads.
• setheron 5y ago
  You should lookup Morel by Julian Hyde for something similar as well.
• wodenokoto 5y ago
  There’s a lot to like here. The ordering and the ability to write functions. I’m not a big fan of the ternary operator. I think the ‘? :’ is hard to read and caters to programmers from system programming languages. A query language should cater to BI and stats people as they typically have a harder time learning syntax than a CS person has learning non-C-like syntax. I’ve always liked Pythons ‘val if condition else other_val’. It’s easy to read for new-comers, while the ‘? :’ is a devil to google for. In the second example I don’t understand why there are both let statements and select statements for the same columns. I also don’t understand why select uses square brackets. Maybe I’m too used to sql but why not just not have brackets?
• rixed 5y ago
  > Unnecessary repetition — the calculations for each measure are repeated, despite deriving from a previous measure. The repetition in the WHERE clause obfuscates the meaning of the expression In my own in-house SQL-like language I solve this simple issue by allowing previously defined columns to be reused: select salary + payroll_tax as _gross_salary, _gross_salary + benefits_cost as gross_cost, ...etc... the prefixing underscore meaning those columns are just temps excluded from the output. I do not believe other issues are actually issues, and am quite surprised by the volume of interest for this SQL alternative. Can't be because rust is mentioned, can it?
• landonxjames 5y ago
  PartiQL[0] is an open source library that is a superset of SQL that I really like. It supports querying nested structures inside columns, so if a column contains some JSON data you can use the standard dot notation to query nested JSON data directly [0] https://partiql.org/ https://partiql.org/
• SPBS 5y ago
  This only targets SELECT queries right? I guess there's not much to improve on for INSERT, UPDATE and DELETE queries. BTW window definitions are reusable using the WINDOW clause, there's no need to define it over and over in SELECT. SELECT date, CASE WHEN is_valid_price THEN price_adjusted / LAG(price_adjusted, 1) OVER w - 1 + dividend_return ELSE NULL END AS return_total, CASE WHEN is_valid_price THEN price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER w - 1 + dividend_return ELSE NULL END AS return_usd, CASE WHEN is_valid_price THEN price_adjusted / LAG(price_adjusted, 1) OVER w - 1 + dividend_return - interest_rate / 252 ELSE NULL END AS return_excess, CASE WHEN is_valid_price THEN price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER w - 1 + dividend_return - interest_rate / 252 ELSE NULL END AS return_usd_excess FROM prices WINDOW w AS (PARTITION BY sec_id ORDER BY date) ;
• zigzag312 5y ago
  This feels much more natural than SQL to me. I really like it.
• wigster 5y ago
  looks very reminiscent of 40 year old progress 4gl/openedge abl syntax
• orthoxerox 5y ago
  How does it handle subqueries? Especially correlated ones, where the subquery depends on the value provided by the outer query?
  • kbelder 5y ago
    This is what I'm most interested in. This is where SQL becomes very awkward and repetitive. select customer, ( select sum(revenue) from orders where customer=accounts.customer) as revenue, ( select count(transactionid) from orders where customer=accounts.customer) as orders from accounts; That's a very simple example, but it can get VERY wordy and complex, and in my dreams I'd be able to write something like: getrevenue(cust) is select sum(revenue) from orders where customer=cust getorders(cust) is select count(transactionid) from orders where customer=cust select customer, getrevenue(customer), getorders(customer) from accounts; In a way, it's just dynamic sql without hacking strings together in unmaintainable ways.
  • unfocussed_mike 5y ago
    My SQL life is seemingly dominated by correlated subqueries and self-joins where the join checks another row does _not_ exist (the row R with the largest column X is the one where there is no row R1 with a larger X1). I don't believe SQL queries like these can be improved by a metalanguage. They _can_ be meaningfully improved by indentation, whitespace, careful naming and detailed comments.
• victorNicollet 5y ago
  There are plenty of good ideas in here, I really like it. Here's a few things I can say from my experience (I have been working on a "better SQL" for almost 10 years now): - Your "aggregate by" taking a set of calculations to be performed on the groups is an excellent idea: the fact that "group by X" isn't a separate statement from the "select" that describes the calculations, means you can keep the idea that each line is an operation applied to the result of the previous line. - Be very careful with auto-generated column names, they will bite you when you implement "Go To Definition" or "Rename Symbol" tools. - The use of "filter" instead of "where" is a missed opportunity to be easily understood by people who know SQL. I suspect that you wanted to avoid the WHERE/HAVING confusion, but I'm not sure it is worth it. I do appreciate "sort" instead of "order by" (it's good to have only one keyword). - To support a space-based call convention `f a b` you will pay a very heavy price in the language grammar. Also, allowing optional arguments in this convention will prevent you from implementing first-class, higher-order or partial functions. This may be a price you have decided to pay, if not, look into the OCaml rules for optional arguments, they are very well thought-out (the general idea being, you must have at least one non-optional positional argument _after_ the optional ones). - Having [X, Y] be your list syntax, and X be a shorthand notation for [X], works out pretty well in practice, so long as you have specific positions in your grammar where lists are expected, so there is no ambiguity between X-as-a-column and X-as-a-shorthand-for-[X] - General syntax opinions: "from" is fine, your CTE syntax (non-point-free) is fine. Boolean operators should be usable as infix keywords (or prefix if unary), just like arithmetic ones, because that is the first thing users will attempt. - Raw syntax: for the sake of your syntax highlighter, I recommend having asymmetrical delimiter pairs like [|SQL|] instead of symmetrical pairs like `SQL`. In addition to the above, one of the issues I have with SQL in an analytics context is that it tends to "lose" the actual tables: since it operates by joining/filtering/aggregating tables into new tables, your "products" table from the database schema is quickly replaced by a CTE named "products5" which (in theory) contains the same lines as the original, plus some additional columns, but (in practice) some lines have been lost or duplicated in the middle. I called this the "vanishing schema" paradox[1]. Your design, with the "let" statements to add columns to a table, solves one portion of the problem, but I wonder if you would consider this worth solving more fully. [1]: https://nicollet.net/blog/vanishing-schema-paradox.html https://nicollet.net/blog/vanishing-schema-paradox.html
• darksaints 5y ago
  This is awesome, I really like the ML syntactic approach. I also really like the composability. I might just have to sign myself up as a contributor, because this checks off a huge chunk of my list of things that is wrong with SQL, and I'd love to see this succeed. I know this is meant to transpile to SQL and so maybe this language is the wrong place to do this, but my biggest pet peeve with SQL is the ternary logic introduced by SQL nullability. I'm begging and pleading for this wart to go away, and I would love to see some algebraic sum types (Optional/Maybe, etc.) used in their place.
• AtlasBarfed 5y ago
  Misses autocompletion opportunities: SELECT sucks because the tables follow the columns, while if SELECT FROM <tables> COLUMNS <columns> would enable autocompletion.
• aksss 5y ago
  Jeez I don’t know - SQL is the eternal language and people are always trying to replace it or come up with alternatives so they don’t have to learn it, but those efforts have always turned into their own obscure learning domains with the exact same problems people complained about in the first place (I have to learn this obscure syntax to get and shape data). Just learn SQL fundamentals and move forward.
• undefinedblog 5y ago
  every now and then I forget the order of `where` and `group by`, SQL syntax certainly has it's counter-intutive quirks.
• kthejoker2 5y ago
  I both love this (it's very well done!) and hate this, because I feel it misses the forest for the trees. It's like that meme "Nobody says 'I want to be an Excel guru when I grow up.'" SQL is just the means to an end - to alter or retrieve data in a system. We shouldn't be coming up with nicer ways to handle autocompletion, or recursive semantics, or windowing functions. SQL was created to be declarative and human-friendly, to be read aloud. The next evolution of SQL should be natural language. It should be bounded at the schema and enriched with as much context as it can, both about the domain of the data, the data itself, and its relationships within itself and to other schemas. I just want to see all the people in my organization who haven't submitted their timesheet this week; all the orders in my ERP that are waiting for parts from a specific vendor; how much lift I'm getting from my targeted marketing campaign. I just want to delete any contact from CRM who hasn't responded to an email in the last 45 days! I don't want SQL; I want something that translates natural language into a logical plan, and a physical plan; and a separate tool that allows me to express many, many natural language concepts for my bounded schema ... perhaps in a SQL-like way. Like a metrics / definition store on steroids, or just a sea of rich aliases and computed columns and subqueries that can be composed without stressing over syntax or newlines or ordering or complex join conditions.