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.