Pipelined Relational Query Language, pronounced “Prequel”

PRQL is a modern language for transforming data

— a simple, powerful, pipelined SQL replacement

          from employees
filter start_date > @2021-01-01
derive [
  gross_salary = salary + (tax ?? 0),
  gross_cost = gross_salary + benefits_cost,
]
filter gross_cost > 0
group [title, country] (
  aggregate [
    average gross_salary,
    sum_gross_cost = sum gross_cost,
  ]
)
filter sum_gross_cost > 100000
derive id = f"{title}_{country}"
derive country_code = s"LEFT(country, 2)"
sort [sum_gross_cost, -country]
take 1..20

        

Principles

Pipelined

A PRQL query is a linear pipeline of transformations

Each line of the query is a transformation of the previous line’s result. This makes it easy to read, and simple to write.

Simple

PRQL serves both sophisticated engineers and analysts without coding experience.

By providing a small number of powerful & orthogonal primitives, queries are simpler — there’s only one way of expressing each operation. We can eschew the debt that SQL has built up.

Open

PRQL is open-source, with an open community

PRQL will always be fully open-source and will never have a commercial product. By compiling to SQL, PRQL is compatible with most databases, existing tools, and programming languages that manage SQL. We’re a welcoming community for users, contributors, and other projects.

Extensible

PRQL is designed to be extended, from functions to language bindings

PRQL has abstractions which make it a great platform to build on. Its explicit versioning allows changes without breaking backward-compatibility. And in the cases where PRQL doesn’t yet have an implementation, it allows embedding SQL with S-Strings.

Analytical

PRQL’s focus is analytical queries

PRQL was originally designed to serve the growing need of writing analytical queries, emphasizing data transformations, development speed, and readability. We de-emphasize other SQL features such as inserting data or transactions.

Showcase

PRQL consists of a curated set of orthogonal transformations, which are combined together to form a pipeline. That makes it easy to compose and extend queries. The language also benefits from modern features, such syntax for dates, ranges and f-strings as well as functions, type checking and better null handling.

Playground Book
from employees
select [id, first_name, age]
sort age
take 10
SELECT id, first_name, age
FROM employees
ORDER BY age
LIMIT 10
from order               # This is a comment
filter status == "done"
sort [-amount]           # sort order
SELECT
  order.*
FROM
  order
WHERE
  status = 'done'
ORDER BY
  amount DESC
from employees
derive [
  age_at_year_end = (@2022-12-31 - dob),
  first_check_in = start + 10days,
]
SELECT
  employees.*,
  DATE '2022-12-31' - dob AS age_at_year_end,
  start + INTERVAL '10' DAY AS first_check_in
FROM
  employees
from employees
# Filter before aggregations
filter start_date > @2021-01-01
group country (
  aggregate [max_salary = max salary]
)
# And filter after aggregations!
filter max_salary > 100000
SELECT
  country,
  MAX(salary) AS max_salary
FROM
  employees
WHERE
  start_date > DATE '2021-01-01'
GROUP BY
  country
HAVING
  MAX(salary) > 100000
from web
# Just like Python
select url = f"http://www.{domain}.{tld}/{page}"
SELECT CONCAT('http://www.', domain, '.', tld,
  '/', page) AS url
FROM web
from employees
group employee_id (
  sort month
  window rolling:12 (
    derive [trail_12_m_comp = sum paycheck]
  )
)
SELECT
  employees.*,
  SUM(paycheck) OVER (
    PARTITION BY employee_id
    ORDER BY
      month ROWS BETWEEN 11 PRECEDING
      AND CURRENT ROW
  ) AS trail_12_m_comp
FROM
  employees
func fahrenheit_from_celsius temp -> temp * 9/5 + 32

from weather
select temp_f = (fahrenheit_from_celsius temp_c)
SELECT
  temp_c * 9/5 + 32 AS temp_f
FROM
  weather
# Most recent employee in each role
# Quite difficult in SQL...
from employees
group role (
  sort join_date
  take 1
)
WITH table_0 AS (
  SELECT
    employees.*,
    ROW_NUMBER() OVER (
      PARTITION BY role
      ORDER BY
        join_date
    ) AS _rn
  FROM
    employees
)
SELECT
  table_0.*
FROM
  table_0
WHERE
  _rn <= 1
# There's no `version` in PRQL, but
# we have an escape hatch:
derive db_version = s"version()"
SELECT
  version() AS db_version
from employees
join benefits [employee_id]
join side:left p=positions [id==employee_id]
select [employee_id, role, vision_coverage]
SELECT
  employee_id,
  role,
  vision_coverage
FROM
  employees
  JOIN benefits USING(employee_id)
  LEFT JOIN positions AS p ON id = employee_id
from users
filter last_login != null
filter deleted_at == null
derive channel = channel ?? "unknown"
SELECT
  users.*,
  COALESCE(channel, 'unknown') AS channel
FROM
  users
WHERE
  last_login IS NOT NULL
  AND deleted_at IS NULL
prql dialect:mssql  # Will generate TOP rather than LIMIT

from employees
sort age
take 10
SELECT
  TOP (10) employees.*
FROM
  employees
ORDER BY
  age

Integrations

dbt

Allows writing PRQL in dbt models. This combines the benefits of PRQL’s power & simplicity within queries; with dbt’s version control, lineage & testing across queries.

Jupyter/IPython

PyPrql contains a Jupyter extension, which executes a PRQL cell against a database. It can also set up an in-memory DuckDB instance, populated with a pandas DataFrame.

Visual Studio Code

Extension with syntax highlighting and an upcoming language server.

Prefect

Add PRQL models to your Prefect workflows with a single function.

Tools

Playground

Online in-browser playground that compiles PRQL to SQL as you type.

PyPrql

Python TUI for connecting to databases. Provides a native interactive console with auto-complete for column names and Jupyter/IPython cell magic.

pip install pyprql

prql-compiler

Reference compiler implementation. Has a CLI utility that can transpile, format and annotate PRQL queries.

brew install prql/prql/prql-compiler

Bindings

prql-python

Python bindings for prql-compiler.

prql-js

JavaScript bindings for prql-compiler.

prql-compiler

PRQL’s compiler library, written in Rust.

cargo install prql-compiler

What people are saying

It starts with FROM, it fixes trailing commas, and it's called PRQL?? If this is a dream, don't wake me up. — Jeremiah Lowin, Founder & CEO, Prefect.