PRQL – A proposal for a better SQL

> 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.

I don’t see anything dealing with OCaml here, other than it being listed as an inspiration.

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 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.

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.

Same here. I find the SQL query in the README more readable.