Introduction

PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. Like SQL, it’s readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it transpiles to SQL.

Let’s get started with an example:

PRQL

  1. from employees
  2. filter start_date > @2021-01-01               # Clear date syntax
  3. derive [                                      # `derive` adds columns / variables
  4.   gross_salary = salary + (tax ?? 0),         # Terse coalesce
  5.   gross_cost = gross_salary + benefits_cost,  # Variables can use other variables
  6. ]
  7. filter gross_cost > 0
  8. group [title, country] (                      # `group` runs a pipeline over each group
  9.   aggregate [                                 # `aggregate` reduces each group to a value
  10.     average gross_salary,
  11.     sum_gross_cost = sum gross_cost,          # `=` sets a column name
  12.   ]
  13. )
  14. filter sum_gross_cost > 100_000               # `filter` replaces both of SQL's `WHERE` & `HAVING`
  15. derive id = f"{title}_{country}"              # F-strings like Python
  16. derive country_code = s"LEFT(country, 2)"     # S-strings allow using SQL as an escape hatch
  17. sort [sum_gross_cost, -country]               # `-country` means descending order
  18. take 1..20                                    # Range expressions (also valid here as `take 20`)

SQL

  1. WITH table_3 AS (
  2.   SELECT
  3.     title,
  4.     country,
  5.     salary + COALESCE(tax, 0) + benefits_cost AS _expr_1,
  6.     salary + COALESCE(tax, 0) AS _expr_2
  7.   FROM
  8.     employees
  9.   WHERE
  10.     start_date > DATE '2021-01-01'
  11. ),
  12. table_1 AS (
  13.   SELECT
  14.     title,
  15.     country,
  16.     AVG(_expr_2) AS _expr_0,
  17.     SUM(_expr_1) AS sum_gross_cost
  18.   FROM
  19.     table_3 AS table_2
  20.   WHERE
  21.     _expr_1 > 0
  22.   GROUP BY
  23.     title,
  24.     country
  25. )
  26. SELECT
  27.   title,
  28.   country,
  29.   _expr_0,
  30.   sum_gross_cost,
  31.   CONCAT(title, '_', country) AS id,
  32.   LEFT(country, 2) AS country_code
  33. FROM
  34.   table_1 AS table_0
  35. WHERE
  36.   sum_gross_cost > 100000
  37. ORDER BY
  38.   sum_gross_cost,
  39.   country DESC
  40. LIMIT
  41.   20

As you can see, PRQL is a linear pipeline of transformations — each line of the query is a transformation of the previous line’s result.

You can see that in SQL, operations do not follow one another, which makes it hard to compose larger queries.