https://prql-lang.org/

[icon]

PRQL

  * Book^
  * Playground^
  * Roadmap
  * Examples
  * FAQ

Star

Pipelined Relational Query Language, pronounced "Prequel"

PRQL is a modern language for transforming data

-- a simple, powerful, pipelined SQL replacement

We are looking for help! Contribute with any of these:

  * compiler development (Rust) ^
  * IDE (a web application in brainstorming phase) ^
  * website design & copy ^
  * language design ^

          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



Why PRQL?

For data engineers

  * PRQL is concise, with abstractions such as variables & functions
  * PRQL is database agnostic, compiling to many dialects of SQL
  * PRQL isn't limiting -- it can contain embedded SQL where necessary
  * PRQL has bindings to most major languages (and more are in
    progress)
  * PRQL allows for column lineage and type inspection (in progress)

For analysts

  * PRQL is ergonomic for data exploration -- for example, commenting
    out a filter, or a column in a list, maintains a valid query
  * PRQL is simple, and easy to understand, with a small number of
    powerful concepts
  * PRQL allows for powerful autocomplete, type-checking, and helpful
    error messages (in progress)

For tools

  * PRQL is a stable foundation to build on; we're open-source and
    will never have a commercial product
  * PRQL is a single secular standard which tools can target
  * PRQL is easy for machines to read & write

For HackerNews enthusiasts

  * The PRQL compiler is written in rust
  * We talk about "orthogonal language features" a lot

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
Basic example Friendly syntax Dates Orthogonality F-strings Windows 
Functions Top n items S-strings Joins Null handling Dialects

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 sql_dialect:mssql  # Will generate TOP rather than LIMIT

from employees
sort age
take 10

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

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 simple and composable -- 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.

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.

prqlr^

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

[B6muDm76_n]

Hamilton Ulmer

@hamiltonulmer
very excited for prql!
2
13:03 * May 6, 2022
Twitter
[iTL4gp7m_n]

Swanand.

@_swanand
A few years ago, I started working on a language, called "dsql",
short for declarative SQL, and a pun on "the sequel (to SQL)". I
kinda chickened out of it then, the amount of study and research I
needed was massive. prql here is better than I imagined: github.com/
max-sixty/prql
20
13:18 * Jan 25, 2022
Twitter
Column aliases would have saved me hundreds of hours over the course
of my career. -- @dvasdekis
[JHuUzhjU_n]

Rishabh Software

@RishabhSoft
SQL's hold on data retrieval is slipping! 8 new databases are
emerging, and some speak entirely new languages for data querying.
Know more infoworld.com/article/365490... #SQL #DataQuery #GraphQL #
PRQL #WebAssembly
0
16:32 * Apr 13, 2022
Twitter
[BurakEmir-]

Burak Emir

@burakemir
I want to give the PRQL a little boost here, "pipeline of
transformations" is IMHO a good choice for readable query languages
that need to deal with SQL-like aggregations, group by and count and
sum all: github.com/max-sixty/prql
2
12:52 * Jan 25, 2022
Twitter
Having written some complex dbt projects...the first thing...it gets
right is to start with the table and work down. This is an enormous
readability boost in large projects and leads to great intellisense. 
-- Ruben Slabbert
[CsbMRVRG_n]

Michael Sumner

@mdsumner
what an *excellent* criticism of SQL #PRQL github.com/max-sixty/prql#
an...
1
02:02 * Jan 25, 2022
Twitter
Just wanna say, I absolutely love this. -- Alex Kritchevsky

Help the project

  * Send a link to PRQL to a couple of people whose opinion you
    respect.
  * Rewrite any of your own queries to PRQL to see if it makes sense.
    You can use the playground^ and submit issues here^ . We are
    looking for any use-cases that expose a poor design choice, a
    need of a feature, a pain point or are just a sharp edge of the
    language.

Contribute^

Subscribe to issue #1 for a GitHub notification on updates^ .

Join our Discord server^ .

  

(c) 2022, PRQL developers. Apache 2.0 Licensed