ggsql (GitHub Repo)
ggsql is a SQL extension that lets you write data queries and visualization specifications in a single composable syntax, eliminating context switches to Python or R.
What: ggsql extends SQL with grammar-of-graphics style visualization commands (VISUALISE, DRAW, SCALE, LABEL) that let analysts query databases and define charts in one go, currently targeting DuckDB/SQLite with Vegalite output.
Why it matters: For SQL-native analysts, this removes the friction of extracting data then switching to another language just to understand what you're looking at. The composable syntax also makes it easy for AI agents to generate queries that humans can immediately validate.
Takeaway: Try the web playground to see SQL-integrated visualization without installing anything, or check the documentation for interactive examples.
Deep dive
- SQL extension that combines data retrieval with visualization specifications in a single query language
- Based on the Grammar of Graphics (popularized by ggplot2 in R), offering composable syntax that scales from simple to complex visualizations
- Designed to eliminate context switching for SQL analysts who otherwise need to export data and switch to Python/R just to create charts
- Queries use familiar SQL SELECT statements followed by VISUALISE clauses specifying aesthetic mappings (x, y, color), then DRAW, SCALE, and LABEL commands
- Pre-alpha status with core architecture complete, approaching alpha release
- Currently focuses on DuckDB/SQLite database support with Vegalite as the output format
- Future development will add more database readers and output format writers beyond the current stack
- Compiles to WebAssembly enabling browser-based usage without installation via their playground
- Syntax deliberately designed to be readable and writable by both humans and AI agents, making validation straightforward
- Documentation includes interactive examples for hands-on learning
Decoder
- Grammar of Graphics: A framework for building visualizations compositionally by specifying data mappings, geometric shapes, scales, and other layers rather than using pre-made chart types
- ggplot2: A popular R visualization library based on Grammar of Graphics that inspired ggsql's design
- Vegalite: A high-level grammar for creating interactive visualizations, often compiled to JavaScript
- DuckDB: An embedded analytical database designed for fast queries on columnar data
- WASM (WebAssembly): A binary instruction format that allows compiled code to run in web browsers at near-native speed
Original article
ggsql allows developers to write queries that combine SQL data retrieval with visualization specifications in a single, composable syntax.