syntax highlight quarto output

Question

If I was trying to show some SQL generated by dbplyr in a quarto document is there any way to apply some nice syntax highlighting to the resulting output? For example this minimal quarto doc:

---
title: "sql highlighting"
format: html
---

```{r}
library(dbplyr)

library(dplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, mtcars)
```

```{r}
tbl(con, "mtcars") %>% 
  group_by(cyl) %>% 
  summarise(mpg = mean(mpg, na.rm = TRUE)) %>% 
  arrange(desc(mpg)) %>% 
  show_query()
```

Results in this:

Is there any way to apply syntax highlighting to that?

Using the chunk option `class-output: sql` should result in sql code highlighting! — shafee, Jul 19 '23 at 18:57
Ah I see. But I see that that doesn't work for python sql outputs. And I see that your answer here gives some hints: https://stackoverflow.com/questions/76338850/python-output-highlighting-in-quarto. So then if I want to have proper sql syntax highlighting for a python output I just need to figure out the right lua incantation. Is that right? — boshek, Jul 19 '23 at 22:22
yes for `jupyter` engine, you need to sort this out using Lua filter. — shafee, Jul 20 '23 at 08:56
posted an answer just to get a few things down in one place. thanks for all your help! — boshek, Jul 20 '23 at 15:34

score 0 · Answer 1 · answered Jul 20 '23 at 15:34

So @shafee answered this question in the comments and also provided some pointers for a python version in this answer.

For R you can use class-output:sql which will properly highlight what is returned by dplyr::show_query:

---
title: "sql highlighting"
format: html
---

```{r}
library(dbplyr)

library(dplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, mtcars)
```

```{r}
#| class-output: sql
tbl(con, "mtcars") %>% 
  group_by(cyl) %>% 
  summarise(mpg = mean(mpg, na.rm = TRUE)) %>% 
  arrange(desc(mpg)) %>% 
  show_query()
```

For python (or ibis at least), you need to make use of a lua filter. Adapting @shafee's work I created this lua file:

sql-highlight.lua

function add_sql_class()
    return {
      CodeBlock = function(cb)
        if not cb.classes:includes('sql') then
          cb.classes:insert('sql')
        end
        return cb
      end
    }
  end

  function add_class_to_cb()
    return {
      Div = function(el)
        if el.classes:includes('cell-output') then
          return el:walk(add_sql_class())
        end
      end
    }
  end

  function add_class_to_output()
    return {
      Div = function(el)
        if el.classes:includes('cell') then
          return el:walk(add_class_to_cb())
        end
      end
    }
  end

  function Pandoc(doc)
    if FORMAT == 'revealjs' then
      return doc:walk(add_class_to_output())
    end
  end

And then with ibis you are then able to get some nicer sql syntax highlighting:

---
title: "SQL Highlight"
format: revealjs
jupyter: python3
filters:
  - sql-highlight.lua
---

```{python}
#| echo: false
import os
import urllib.request
from ibis.interactive import *
```


```{python}
#| echo: false
ibis.options.interactive = True
penguins = ex.penguins.fetch()
```


## Generate SQL
```{python}
#| classes: output-highlight
#| echo: true
ibis.show_sql(
  penguins
    .filter(penguins.species == "Adelie")
    .group_by(["island", "year"]) 
    .aggregate(penguins.bill_length_mm.mean())
)
```

which ends up looking like this:

syntax highlight quarto output

1 Answers1

sql-highlight.lua