Use PRQL with knitr

In prqlr: R Bindings for the 'prqlc' Rust Library

Loading {prqlr} makes the prql code chunks executable in R Markdown and Quarto Markdown files.

Output formats

It can either output the results of the actual query execution or display a SQL query compiled from a PRQL query.

Use with {DBI} connections

If using with database connections (set the name of {DBI} connection to the connection chunk option), PRQL code chunks pass the output SQL to {knitr}'s SQL engine and behaves like SQL code chunks. So, usage is the same as for SQL code chunks.

For example, let's render an R Markdown file named test.Rmd with the following contents with the knitr::knit() function.

#| echo: false
#| results: asis

# Override the default SQL print function to avoid inserting '<div class="knitsql-table">'
# https://github.com/yihui/xaringan/issues/307
knitr::opts_knit$set(sql.print = \(x) paste(knitr::kable(x, format = "markdown"), collapse = "\n"))

src_with_con <- r"(
```r
#| echo: false

library(DBI)
library(prqlr)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
```

```{prql}
#| connection: con

from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```
)"

cat("````markdown", src_with_con, "````", sep = "")

After rendering, a Markdown file named test.md is generated with the following contents.

#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_with_con, quiet = TRUE),
  "````",
  sep = ""
)

Note that the syntax highlighting of Elm is the best for PRQL, so the output code blocks are marked as elm. This can be changed by setting the lang chunk option.

Without database connections

PRQL code chunks without the connection option will generate SQL code blocks.

#| echo: false
#| results: asis

src_without_con <- r"(
```r
#| echo: false

library(prqlr)
```

```{prql}
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```
)"

cat("````markdown", src_without_con, "````", sep = "")

The R Markdown file above will be converted to the Markdown file below.

#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_without_con, quiet = TRUE),
  "````",
  sep = ""
)

Engine options

Compiler options

We can pass some options of prql_compile() via the chunk option engine-opts (or engine.opts).

Note that the format option is always TRUE for PRQL code chunks.

#| echo: false
#| results: asis

src_engine_opts <- r"(
```r
#| echo: false

library(prqlr)
```

## YAML-style

```{prql}
#| engine-opts:
#|   target: sql.mssql
#|   signature_comment: false

from mtcars
take 3
```

## R-style

```{prql engine.opts=list(target="sql.mssql", signature_comment=FALSE)}
from mtcars
take 3
```
)"

# Check if it can be rendered
invisible(knitr::knit_child(text = src_engine_opts, quiet = TRUE))

cat("````markdown", src_engine_opts, "````", sep = "")

Parameterized PRQL code blocks

The experimental engine option use_glue can be used to insert R variables into PRQL queries.

Characters enclosed in {{ and }} are replaced by the glue::glue() function before being passed to the prql_compile() function.

#| echo: false
#| results: asis

src_glue <- r"(
```r
#| echo: false

library(prqlr)

cyl_min <- 6
derive_or_select <- "derive"
```

```{prql}
#| engine-opts:
#|   use_glue: true

from mtcars
filter cyl > {{cyl_min}}
select {cyl, mpg}
{{derive_or_select}} {mpg_int = math.round 0 mpg}
take 3
```
)"

cat("````markdown", src_glue, "````", sep = "")

The R Markdown file above will be converted to the Markdown file below.

#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_glue, quiet = TRUE),
  "````",
  sep = ""
)

Use query strings in R code blocks

With the experimental use_glue option, the query string can even be defined completely in R side.

#| echo: false
#| results: asis

src_query_from_r <- r"(
```r
#| echo: false

library(prqlr)

prql_query <- "from mtcars
select cyl"
```

```{prql}
#| engine-opts:
#|   use_glue: true

{{prql_query}}
```
)"

cat("````markdown", src_query_from_r, "````", sep = "")

This example outputs the following.

#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_query_from_r, quiet = TRUE),
  "````",
  sep = ""
)

Set special info string to output SQL code blocks

When making SQL code blocks output without a DB connection, the info_string option can be used to control the info string of the output code block. This is convenient when we want to add special attributes that are used in Quarto Markdown files. The default value is "sql".

For example, the following R Markdown file includes a PRQL code block with the info_string option will be converted to...

#| echo: false
#| results: asis

src_info_string <- r"(
```r
#| echo: false

library(prqlr)
```

```{prql}
#| engine-opts:
#|   info_string: '{.sql filename="SQL"}'
from mtcars
take 3
```
)"

cat("````markdown", src_info_string, "````", sep = "")

This Markdown file.

#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_info_string, quiet = TRUE),
  "````",
  sep = ""
)

prqlr documentation built on April 12, 2025, 1:47 a.m.