DuckCon #7
Building local-first analytics apps with SQLRooms
Ilya Boyandin · DuckCon #7
8
sections
15
slides
01 / The idea
- Framing question: a single DuckDB file that contains both the data and the full analytical workspace around it.
- The talk positions DuckDB as an analytical runtime, not just a query engine, for local-first analytics apps.
- Ilya Boyandin walks through how this idea led him to build SQLRooms.
More detail
Opening thesis for the talk: bundling data plus application state into one portable DuckDB file.
02 / SQLRooms overview
- SQLRooms is an open-source framework for building human + agent collaborative analytics apps on top of DuckDB.
- Backed by Foursquare but not owned by it: it was donated to the OpenJS Foundation.
- Emphasizes local analytics with no backend required, owning your own data, and offline use.
- Privacy-preserving AI integration: agents write and run SQL and generate insights without sharing data with model providers.
More detail
Core pitch: local-first, privacy-preserving, modular architecture with modern UI components.
03 / DuckDB integration
- Multiple connector types: DuckDB-WASM (WebAssembly), MotherDuck, and a native/embedded DuckDB server (e.g. via Python).
- Built-in UI for running queries, browsing the schema, and managing tables and results.
- A query workbench lets users add files and explore data directly against DuckDB.
More detail
Flexible backends let the same app run fully in-browser or against a server-side DuckDB.
04 / UI, layout and artifacts
- Rich UI and layout primitives: collapsible resizable panels, tabs, and a dashboard grid.
- Layout ties into a modular state-persistence system for the whole application.
- Artifacts are pluggable and composable: dashboards, pivot tables, notebooks, documents, SQL queries, HTML apps, Python, canvas, and an app builder.
- Ships with default artifact components, but you can supply your own custom ones.
More detail
Everything an analytics app needs for composition, all bound to a modular app state model.
05 / Where it's used
- Started with Flowmap.city, created before Ilya joined Foursquare.
- At Foursquare it powers FSQ Spatial Desktop, a general-purpose geospatial visualization and analysis tool, plus other internal apps.
- Also adopted by other companies and applications such as Transcality, Cosmograph.app, and ChordShell.
- The concept originated in the geospatial space but is now spreading to other domains.
More detail
From geospatial roots to a general-purpose analytics app framework.
06 / The SQLRooms CLI
- Experimental CLI installable with the uv package manager: `uv tool install sqlrooms`.
- Run `sqlrooms ./my-project.db` to open a single project file that is a DuckDB database.
- That file stores both the data and the application state, making the whole project portable and shareable.
- Goal: let people use SQLRooms as a tool without needing programming skills.
More detail
The workspace-in-a-file idea realized: data plus state in one DuckDB database you can hand to someone else.
07 / Building artifacts in the app
- Loading a project opens a UI to add data and create artifacts, with a 'New Worksheet' launcher for all artifact types.
- Build Mosaic-based dashboards by hand, or ask the built-in AI assistant to generate charts and insights.
- Create Notion-like block documents mixing charts and text, with AI help writing the analysis.
- Use Pyodide cells to run Python, bridged to the host DuckDB instance to fetch data, or the free-form HTML app builder to embed custom apps.
More detail
Demo shows an AI assistant analyzing a cars dataset: MPG distributions, horsepower-vs-MPG, and regional differences, all saved inside the single project file.
08 / Vision
- The aim: DuckDB-native analytical apps that are lightweight, portable, local-first, inspectable, and reproducible.
- Everything (artifacts and data) is saved as part of one shareable file.
- Hope is that these apps feel as easy, portable, and local-first as DuckDB itself.
- Learn more at sqlrooms.org.
More detail
Closing thesis and links: sqlrooms.org and linkedin.com/in/ilyabo/.
Key Takeaways
- SQLRooms is an open-source React toolkit for building local-first, data-centric analytics apps on top of DuckDB, donated to the OpenJS Foundation and supported by Foursquare.
- It treats DuckDB as an analytical runtime: one DuckDB file can hold both the data and the entire application state, making a whole workspace portable and shareable.
- Connectors span DuckDB-WASM, MotherDuck, and native/embedded DuckDB, so apps can run fully in-browser or against a server.
- The framework provides composable UI (panels, tabs, dashboard grid) and a pluggable artifact system: dashboards, pivot tables, notebooks, documents, SQL, Python, HTML apps, and more.
- Privacy-preserving AI assistants can write SQL and generate insights locally without shipping your data to model providers.
- An experimental uv-installable CLI (`sqlrooms ./my-project.db`) aims to make the tool usable without programming skills.
- The vision: DuckDB-native apps that are lightweight, portable, local-first, inspectable, and reproducible, just like DuckDB itself.