PRql

Jun 27, 2022

https://prql-lang.org/
https://prql-lang.org/examples/

A re-imagining of SQL in a sensible order. Compiles to SQL. Would love to play around with it more, I've wanted something like this for a long time.

ex query:

from employees # Each line transforms the previous result. filter start_date > @2021-01-01 # Clear date syntax. derive [ # `derive` adds columns / variables. gross_salary = salary + payroll_tax, gross_cost = gross_salary + benefits_cost # Variables can use other variables. ] filter gross_cost > 0 group [title, country] ( # `group` runs a pipeline over each group. aggregate [ # `aggregate` reduces a column to a row. average salary, sum salary, average gross_salary, sum gross_salary, average gross_cost, sum_gross_cost = sum gross_cost, # `=` sets a column name. ct = count, ] ) sort [sum_gross_cost, -country] # `-country` means descending order. filter ct > 200 take 20

which compiles to the equivalent of this SQL query:

SELECT TOP 20
  title,
  country,
  AVG(salary) AS average_salary,
  SUM(salary) AS sum_salary,
  AVG(salary + payroll_tax) AS average_gross_salary,
  SUM(salary + payroll_tax) AS sum_gross_salary,
  AVG(salary + payroll_tax + benefits_cost) AS average_gross_cost,
  SUM(salary + payroll_tax + benefits_cost) AS sum_gross_cost,
  COUNT(*) AS ct
FROM
  employees
WHERE
  start_date > DATE('2021-01-01')
  AND salary + payroll_tax + benefits_cost > 0
GROUP BY
  title,
  country
HAVING
  COUNT(*) > 200
ORDER BY
  sum_gross_cost,
  country DESC

derive means, given the previous results, create these new columns. Being able to do multiple derive/filter pairs would be so nice!

In golang, I think the best way to integrate this tool would be to use it at code generation time? Looks like the work has not been done there yet, this project is v new.

↑ up