DuckCon #7

ggsql: A grammar of graphics for SQL

Teun van den Brand · DuckCon #7

sections

slides

ggsqlGrammar of GraphicsDuckDBSQLData Visualizationggplot2

01 / What is ggsql?

Combining the grammar of graphics with SQL

ggsql is an alpha-stage tool that fuses the Grammar of Graphics (a visualization theory) with SQL (a query language).
The idea: keep a small, composable grammar and pair it with a familiar language so plotting becomes a natural extension of querying.
It ships as a DuckDB community extension: install it, then add visualization syntax on top of a normal SQL query.
In the running penguins example, a SELECT is filtered to Biscoe Island and bill length vs bill depth is drawn as a point layer.
From that base you can add trend lines, group by color, and refine the plot, all from within your SQL analysis.

02 / Try it out

No live demos allowed, so the audience is invited to run the interactive example on ggsql.org themselves.
The Try-it-out editor shows a regular query (SELECT * FROM ggsql.penguins WHERE island = 'Biscoe') followed by declarative visualization clauses.
VISUALISE maps bill_len to X, bill_dep to Y, and body_mass to fill; DRAW point renders the scatter; SCALE and LABEL clauses style it.
The result is a fully composed chart titled 'Relationship between bill dimensions in 3 species of penguins'.

03 / Why a new tool

The speaker is a ggplot2 maintainer but wrote his first SQL query only 5-7 months ago.
ggplot2 is the most downloaded R package, more popular than infrastructure staples like Rcpp or jsonlite.
After ~18 years of development, ggplot2 is powerful but locked in by backward compatibility.
ggsql is a fresh start: a carte blanche to try new ideas without that legacy burden.

04 / The theory

The Grammar of Graphics is a theory developed by Leland Wilkinson and published in his book of the same name.
It has many software implementations: ggplot2 (R), plotnine (Python), Vega-Lite, Gadfly (Julia), and Tableau.
Notably absent from that list is SQL, which is exactly the gap ggsql aims to fill.

05 / The ecosystem gap

A typical analytics workflow lives in a database (ideally DuckDB), then ships data out to R or Python.
Wrangling happens in pandas or dplyr, and plotting in plotnine or ggplot2, forcing you to cross an ecosystem gap.
That handoff from SQL/DuckDB into R or Python is inconvenient and interrupts the workflow.
ggsql is positioned on the SQL side so you can get plots straight out of the database without switching tools.

06 / What we avoid

The functional approach (e.g. the miniplot extension's scatter_chart function) works but is rigid: one chart type, few customizations.
Cramming everything into a function creates a giant monolith that is terrible to use, so ggsql avoids it.
ggsql also steers clear of closed-source point-and-click BI tools like Power BI Desktop.
Core belief: code is the primary and better way to express visualization, not clicking through menus.

07 / Shared DNA

Both are declarative: SQL speaks to information structures ('give revenue grouped by month'), the grammar to encodings ('show revenue as bars by month').
Both are compositional from a small core: SQL has SELECT / GROUP BY / WHERE; the grammar has layers, scales, and facets.
Both aim for portable execution: SQL across DuckDB / PostgreSQL / MySQL, the grammar across ggplot2 / Vega-Lite / plotnine.
The main difference is that grammar syntax is not conserved across its execution backends.

08 / How it runs

The user submits a SELECT statement followed by a VISUALISE block.
ggsql splits the two: the SELECT ... FROM part is sent to the database to execute.
The VISUALISE part is kept, and ggsql builds the plot from the returned data.
The output is a finished graphic, with the database doing the data work and ggsql doing the drawing.

09 / Minard demo

Minard's famous chart of Napoleon's 1812 Russian campaign encodes troop counts as line thickness (beige advancing, black retreating).
The simplest ggsql version: VISUALISE long AS X, lat AS Y and DRAW path, speaking the plot into existence.
It is then built up: PARTITION splits the lines, direction becomes stroke color, and survivors map to linewidth, with SCALE clauses tuning colors and line thickness.
A DRAW text layer adds city labels, and the spatial extension enables map projections (Mercator, orthographic) with a filled Europe basemap.
Projections can be flipped and zoomed, finishing with proper title, subtitle, and legend labels.

10 / Works wherever

Distributed as a standalone executable with no bundled R or Python runtime.
Execution is constrained so the risk of running malicious code is minimal.
Integrations include the DuckDB extension, a command-line interface, and a Jupyter kernel.
There is also language support for VS Code and Positron, Quarto, R and Python wrappers, and a WebAssembly build driving the web examples.
The team is exploring LLM assistance (e.g. a query-chat scale) since some SQL knowledge should transfer to ggsql.

ggsql brings the Grammar of Graphics into SQL, letting you plot directly from queries instead of exporting to R or Python.
It ships as a DuckDB community extension where a VISUALISE block is appended to a normal SELECT statement.
The runtime splits work cleanly: the database executes the SELECT, ggsql builds the visualization from the results.
The design deliberately avoids rigid chart functions (like miniplot) and closed-source point-and-click BI tools, betting on code as the way to express charts.
SQL and the grammar of graphics share deep traits: both declarative, compositional from a small core, and portable across backends.
The Minard Napoleon chart shows how plots build up incrementally from a one-line path to projected, labeled maps via the spatial extension.
It runs as a standalone, sandboxed executable with broad integrations (CLI, Jupyter, VS Code/Positron, Wasm) and early LLM-assistance support; still alpha, try it on ggsql.org.