Creating a quick PDF report from a DuckDB query

Once in a while, we have to create PDF reports from database queries. In the past we used either JasperReports, a heavyweight Java-based reporting engine, or wrote some Python code using a library like FPDF. But about a year ago, a new kid on the block caught my eye: Typst.

Typst is a markup-based typesetting system written in Rust. It has already quite a few templates available, mainly for scientific papers, but also for invoices, CVs, and more. So I did a quick test to see if it can be used to create a PDF report from a DuckDB query. It took me about 30 minutes to get a decent result.

Step 1: DuckDB query with JSON output

duckdb -json -c "SELECT * FROM read_parquet('s3://us-prd-motherduck-open-datasets/netflix/netflix_daily_top_10.parquet');" >data.json

Step 2: Convert it to a PDF using Typst

typst compile --input file=data.json --input title="Daily Top 10" jsontable.typ

It takes around 4 seconds to generate a 394 page PDF looking like this:

report

Here’s the Typst template jsontable.typ, using tada for automatic table layout:

#import "@preview/tada:0.2.0"

// Page styling
#set page(
  paper: "a4",
  flipped: true
)
#set text(
  font: "IBM Plex Sans",
  size: 10pt
)

#let record-data = json(sys.inputs.at("file"))

// Table styling
#show table.cell.where(y: 0): set text(weight: "bold")
#set table(
  fill: (_, y) => if calc.even(y) { rgb("EAF2F5") }
)

#let td = tada.from-records(record-data)

= #sys.inputs.at("title", default: "Data Table")

#tada.to-table(td)

I’m quite happy with the result and plan to use Typst for more complex reports including maps and legends. But maybe someone else creates a general purpose database reporting tool based on Typst first?