A grammar of graphics for SQL

Thomas Lin Pedersen

25th September 2026

Hi everyone

Thank you so much for coming today

I’m here to tell you all I can about ggsql:

Our implementation of the grammar of graphics for SQL

Why am I speaking to you?

  • Software engineer at Posit, PBC
  • If you have created graphics in R, my code has probably been involved
  • I’m creating something new and exciting

I work for Posit which, back in the days were known as RStudio

I’ve done that since 2018

I’ve been using R since my first year at the university (KVL) which was back in 2005

I haven’t aged a day since then but I’ve grown more wise

My main focus has for many years been graphics and data visualization

But why am I speaking to you today? Because I’m creating something new and exciting

I’ve been kinda blessed in this regard

I’ve always loved creating packages for others to use

Now it’s my full time job, which is rare in Open Source

So, I’ve been prolific when it comes to R packages

And, while some are more niche than others, they are all exciting

Many packages in some way ties into ggplot2

If you are unfamiliar, it is a very popular visualization framework

Has reigned supreme in R for 20 years or so

and one of my claims to fame is that I’ve maintained ggplot2 for 8 years now

this is both a privilege and a burden

Popularity begets constraints

And cost/benefit wise you can’t justify anything but incremental polish

But, thinking about visualization frameworks, syntax, theory, etc every day for 8 years+ does provide some very unique insights

+ SQL =

So, we’ve taken all of that insight and freed ourselves from the constraint of compatibility by moving to a language historically devoid of visual output

and that is what ggsql is

Why am I speaking to you?

So, why am I talking to you?

Because ggsql is very exciting, both to develop, but hopefully also as a user

So, the rest of the talk will be about this

I’ll talk a bit about the “Why?” which is somewhat broader than pure ggplot2 escapism

I’ll show you what it looks like

And I’ll touch on the future for the project

Why ggsql?

So, why ggsql?

I think we were mentally bracing ourselves for that question when we unveiled it

But it never came (much)

So I guess the idea is somewhat as good as we thought

Still, I’d like to take you through some of our thoughts so you can better understand what we are trying to achieve

Why ggsql?

  • SQL is hugely influential and still heavily used
  • Many SQL-first or SQL-only data scientists and analysts
  • They are massively under-served in tooling and visualization
  • GoG and SQL just fits so good together that it would be criminal not to

So part of this ties into a broader focus expansion at Posit

We are closely tied to R, but our mission is about Data Science in general

And we recognize that many people working with data, be they scientists or analysts are predominantly using SQL

And honestly, we think we can do better, tooling wise, than what currently exist

We want to embrace SQL in a big way

But honestly, the gg and the sql just fits together so well, so even ignoring the above, once you recognize that you kinda have to build it

A bit about SQL

  • One of the oldest programming language still in use
  • Based on Relational Algebra (same theoretical backbone as dplyr)
  • ANSI SQL (the standard) has been joined by many dialects
  • DECLARATIVE
  • TABULAR

I’m not going to dive to much into SQL today

SQL users here potentially knows it better than me

R users probably knows enough about it

But, just to sum up some key points

It’s old - almost as old as C

It has a strong theoretical foundation (shared with dplyr)

It is one standard with many implementations

A bit about grammar of graphics

  • A theoretical deconstruction of standard plots into core parts
  • A theory with many implementations (ggplot2, Vega, D3)
  • DECLARATIVE
  • TABULAR

So, I’m going to spend a bit more time on the grammar of graphics

If you know ggplot2 this might be rehashing but chances are that you’ve used ggplot2 without fully diving into it’s theoretical backbone

So, the grammar of graphics is a theory defining a set of semantics making it possible to describe plots

Much like relational algebra does for data manipulation

Developed by Leland Wilkinson in 2001

Refined as a base for syntax by Hadley Wickham

A bit about grammar of graphics

The original grammar diverts slightly from this, but we’ll stick with Hadleys version

Often presented as a layer of semantics though this is a simplification

The reality is that many of these terms interact in complex ways

Let’s walk through them

A bit about grammar of graphics

  • Raw data you want to plot
  • In tabular form, one record per row (tidy format)

In general the grammar concerns tabular data

One observation per row, variables as columns

No data encoded into column names (no time-10, time-20, time-30 columns)

A bit about grammar of graphics

  • How columns relate to visual aesthetics
  • How columns relate to statistical properties

Different from assignment

Not “price is x”

But “x relates to price”

A scale handles the “relates” part (coming up)

A bit about grammar of graphics

  • Transformation of observation into derived statistics
  • E.g. binning for a histogram

Sometimes you just show the raw data (e.g. in a scatterplot)

Many times you don’t

The effectiveness of plots is often that they derive valuable statistics from data and show that

stats I think, have been one of the more confusing part of ggplot2 for many users

You generally don’t have to think about it because it is being applied automatically

A histogram layer knows you want a binned count

But the grammar separates the: what if you want the binned count but don’t care much for rectangles in your plot

A bit about grammar of graphics

  • Mapping of measured values to the aesthetic domain
  • Definition of mapping function

As talked about, scales define the conversion function between data and visual representation

  • input type and range
  • scale type
  • (transformation)
  • output type and range

A bit about grammar of graphics

  • How to visually represent data
    • point
    • rectangle
    • area

Usually what we think of when plotting because it is what we see

Room for radicalism vs pragmatism here

A bit about grammar of graphics

  • Which subset(s) of data to show
  • and how…

For people unfamiliar with GoG, this is often the weirder one

Think, small multiples

But can be many non-obvious variations of this

A bit about grammar of graphics

  • How the positional aesthetics relate spatially
    • cartesian
    • polar
    • ternary

How do you put positions onto a flat paper

Hints at an underlying general relationship not described by the grammar which I have deep thoughts about

A bit about grammar of graphics

  • Non-data-driven styling
    • Title font
    • Panel background color
    • Grid line width

A bit lazily “all the rest”

In some sense what gives your plot character

(for better or worse)

SQL and GoG

Declarative

Modular

Composable

Speakable

So, with that out of the way we can return to our

they fit so well together

argument

SQL and GoG

  • These qualities makes for exceptional software
  • They are good for humans
  • They are good for LLMs

Not every tool needs a theoretical foundation but those that have are often superior

When tools align foundationally they work well together

I don’t know what this crazy timeline we’re on will bring.

I do think we will write less code

But I also think that we will read a lot of code

Syntax is as important in this scenario

Demo time

  Data

1
S
species island bill_len bill_dep flipper_len body_mass sex year
Adelie Torgersen 39.1 18.7 181 3750 male 2007
Adelie Torgersen 39.5 17.4 186 3800 female 2007
Adelie Torgersen 40.3 18 195 3250 female 2007
Adelie Torgersen null null null null null 2007
Adelie Torgersen 36.7 19.3 193 3450 female 2007
Adelie Torgersen 39.3 20.6 190 3650 male 2007
Adelie Torgersen 38.9 17.8 181 3625 female 2007
Adelie Torgersen 39.2 19.6 195 4675 male 2007
Adelie Torgersen 34.1 18.1 193 3475 null 2007
Adelie Torgersen 42 20.2 190 4250 null 2007
Adelie Torgersen 37.8 17.1 186 3300 null 2007
Adelie Torgersen 37.8 17.3 180 3700 null 2007
Adelie Torgersen 41.1 17.6 182 3200 female 2007
Adelie Torgersen 38.6 21.2 191 3800 male 2007

  Mappings

1
S

  Statistics

1
S

  Statistics

1
S

  Statistics

1
S

  Scales

1
S

  Geometries

1
S

  Facets

1
S

  Facets

1
S

  Coordinates

1
S

  Coordinates

INSTALL spatial;
1
W

  Theme

TBD, but…

  Theme

1
V

So, what is this witchcraft actually?

  • ggsql is written in pure Rust
  • We currently provide:
    • A CLI
    • A Positron/VS Code extension
    • A Jupyter kernel
    • R and Python bindings
    • A DuckDB extension
    • A WebAssembly binary

ggsql is, in some sense, many things

we have grappled with naming exactly what it is

is it a package

an SQL dialect

A reference implementation of a new derived programming language

maybe the fine print doesn’t matter that much

ggsql in Positron

  • We are leveling up our SQL love in Positrion
  • Positron will be the best place to run SQL
  • ggsql is a big part of that equation

As I hinted at in the beginning, we are aiming a huge spotlight on SQL

We want you to want to use positron for SQL work

By extension, even if ggsql can be used in many ways, we want positron to give the best experience

ggsql in Quarto/Jupyter

  • The Jupyter kernel makes ggsql work automatically
  • The R bindings provide a knitr engine
    • Run R, Python, and ggsql in the same document
    • Share data automatically between the processes

A notebook is probably the easiest entry for now

Just install the kernel and create a quarto document with ggsql code blocks

My data lives in PostgreSQL

Now what?

  • ggsql is built to interact directly with your database
  • Can interact with most through ODBC/ADBC driver support
  • (All) computations are send to the backend
  • Only data needed for plotting is returned

During all this I’ve used built in data or perhaps a csv file here and there

You probably don’t work exclusively with penguin research

We want to meet you were your data is

Bottom line is we don’t want to force yet-another-tool on you

We just want to meet you were you currently are

What’s in the future?

  • gt inspired table formatting
  • patchwork inspired plot composition
  • Interactivity

Questions

…or perhaps a demo of something cool

ggsql

1 / 44
A grammar of graphics for SQL Thomas Lin Pedersen 25th September 2026

  1. Slides

  2. Tools

  3. Close
  • A grammar of graphics...
  • Why am I speaking to you?
  • I’ve been kinda blessed...
  • So, I’ve been prolific...
  • Many packages in...
  • and one of my claims...
  • + SQL = So, we’ve...
  • Why am I speaking to you?
  • Why ggsql? So,...
  • Why ggsql?
  • A bit about SQL
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • A bit about grammar of graphics
  • SQL and GoG
  • SQL and GoG
  • Demo time...
  •   Data
  •   Mappings
  •   Statistics
  •   Statistics
  •   Statistics
  •   Scales
  •   Geometries
  •   Facets
  •   Facets
  •   Coordinates
  •   Coordinates
  •   Theme
  •   Theme
  • So, what is this witchcraft actually?
  • ggsql in Positron
  • ggsql in Quarto/Jupyter
  • My data lives in PostgreSQL
  • We want to meet you...
  • What’s in the future?
  • Questions …or perhaps...
  • f Fullscreen
  • s Speaker View
  • o Slide Overview
  • e PDF Export Mode
  • r Scroll View Mode
  • ? Keyboard Help