Local Analytics Doesn’t Need a Database Server

TL;DR: From interactive maps to graph exploration, DuckDB makes it possible to analyze data without a database server. Ilya Boyandin shares how this approach led to SQLRooms, a toolkit for building analytics applications that bring data, visualizations, and AI-assisted analysis together on the user’s machine. Quote You should own your data. It can live on your computer, not everything has to live in the Cloud. It can be synced via the cloud, but you decide when to keep it local and when not. — Ilya Boyandin, creator of SQLRooms Ilya Boyandin is a software engineer, the creator of SQLRooms, FlowmapBlue, and Flowmap City. He worked on geospatial data analytics at Unfolded, the company founded by the team behind the open-source data visualization projects deck.gl and kepler.gl, which was later acquired by Foursquare. How DuckDB Entered the Picture Ilya first used DuckDB in Flowmap City, a side project for visualizing mobility flows that he still maintains. Working with a small budget, Ilya wanted to keep Flowmap City easy to operate with minimal infrastructure. DuckDB’s WebAssembly build lets the app run analytical queries in the user’s browser without a database server. Uploaded datasets are stored as Parquet files in S3. When someone opens a project, the app loads the data into DuckDB-Wasm through presigned URLs. As users pan, zoom, or change filters in Flowmap City, DuckDB-Wasm runs SQL queries in the browser to update the map. A single interaction can trigger a number of queries preparing the data for the current viewport: Ilya explains: “We can now do much of the analysis and visualization in the browser. This keeps infrastructure requirements low and can be faster because queries don’t need a round trip to the server.” From a Side Project to SQLRooms Much of Flowmap City’s data-loading and interface code was useful beyond mobility analysis. Ilya extracted those pieces into SQLRooms, which he continues to develop as an open-source project. SQLRooms is a modular toolkit for building DuckDB-powered analytics applications in React. It provides building blocks for loading data, writing SQL, displaying tables, creating visualizations, and integrating AI assistants. Developers can combine these pieces with their own components. These components work together within a Room: a workspace containing datasets, analysis artifacts such as saved queries, charts, maps, and documents, and the application state that connects them. SQLRooms handles query execution through a shared DuckDB connection, so developers can focus on how users explore their data and build on their work. DuckDB can run directly in the browser through WebAssembly or natively on the user’s machine in a desktop application, allowing data analysis without a remote database server. Ilya’s interest in local-first software shaped the framework. “You should own your data. It can live on your computer, not everything has to live in the Cloud. It can be synced via the cloud, but you decide when to keep it local and when not.” Putting SQLRooms to Work at Foursquare At Foursquare, Ilya’s team has been developing Foursquare Spatial Desktop, a desktop application for exploring geospatial data built with SQLRooms. It combines DuckDB’s analytical engine with kepler.gl’s visualization tools. The app builds on the team’s experience with kepler.gl and Foursquare Studio, its cloud-hosted geospatial analytics platform. Ilya explains: “For this particular product, DuckDB embedded into the desktop app is just the perfect fit. It enables analysis workflows which users can execute locally.” SQLite is embeddable too, he says, but it is not column-oriented and was not designed for analytics on larger datasets. Modern laptops have substantial computing power and GPUs well suited to visualization, Ilya adds. “I’m seeing renewed interest in applications that keep data and computation on the user’s own machine.” Dynamic Tiling in Spatial Desktop Spatial Desktop uses deck.gl to render maps, but drawing millions of features at once can slow things down. The app addresses this with dynamic tiling powered by DuckDB’s spatial extension. Map tiles are often prepared in advance for fast loading. Spatial Desktop can also generate tiles from users’ own datasets on demand, using the local DuckDB database, so users can start exploring without preparing a tileset first. For each requested tile, it selects nearby features and takes a random sample to keep dense tiles manageable. It also simplifies lines and polygons according to the zoom level, retaining more detail as the user zooms in. The following simplified query shows the main steps. It assumes the source geometries are already in Web Mercator coordinates (EPSG:3857). The tile width determines both a small buffer around the search area and a simplification tolerance equivalent to roughly half a pixel: WITH tile AS ( SELECT ST_TileEnvelope(z, x, y) AS bounds, (ST_XMax(bounds) - ST_XMin(bounds)) / 256.0 AS meters_per_pixel ), candidates AS ( SELECT f.id, f.geometry, tile.bounds, GREATEST(meters_per_pixel * 0.5, 0.1) AS tolerance FROM features f, tile WHERE ST_Intersects_Extent( f.geometry, ST_Buffer(tile.bounds, meters_per_pixel * 16) ) ), sampled AS ( SELECT * FROM candidates USING SAMPLE reservoir(50000 ROWS) ), encoded AS ( SELECT id, ST_AsMVTGeom( ST_SimplifyPreserveTopology(geometry, tolerance), ST_Extent(bounds) ) AS geom FROM sampled ) SELECT ST_AsMVT(encoded, 'features') AS tile FROM encoded WHERE geom IS NOT NULL; ST_AsMVTGeom converts the simplified geometries to tile coordinates and clips them; ST_AsMVT packages them and their attributes into a Mapbox Vector Tile (MVT). The production implementation also handles invalid geometries, filters out very small lines and polygons, and caches generated tiles. An HTTP server running locally as part of the Electron desktop app serves the tiles requested by the map. Graph Analytics on the Fly with Cosmograph SQLRooms also powers the analytical workspace in Cosmograph, a browser-based graph exploration tool developed by Nikita Rokotyan and Thor Whalen. Ilya helped integrate SQLRooms into the application, which can visualize graphs with millions of edges. Cosmograph has been tested with datasets ranging from tens of thousands of points to several million. DuckDB-Wasm handles data loading and SQL queries in a background worker, helping keep the interface responsive. It lets the app filter and summarize data as users explore, while columnar query results reduce the conversion work needed to prepare data for visualization. When the Agent Writes the SQL SQLRooms also supports AI-assisted analysis. “The combination of SQL and AI agents is actually quite powerful,” Ilya says. “SQL has a long history, there are many documents on the web explaining how to use it, so LLMs are pretty good at generating it.” Users can ask questions in natural language. The assistant generates SQL queries and chart specifications, which the application executes and renders locally. The source dataset can stay on the user’s machine, while database schemas and selected query results can be shared with the AI provider to help answer questions. In the Spatial Desktop example below, the assistant generates SQL to retrieve data from available sources and calculate a combined score for wine-growing suitability across Italy. The resulting map is accompanied by AI-generated regional analysis: Where SQLRooms Goes Next Ilya is extending SQLRooms’ workspace model so users can bring their analysis together in interactive documents. These documents combine text with blocks such as charts or SQL queries, letting users explain their findings alongside the analysis that produced them. Ilya is also exploring other ways to work with these analytical projects. At DuckCon #7 in Amsterdam, he demonstrated a SQLRooms command-line tool with a notebook-style interface. It saves both the data and the analysis artifacts in a single DuckDB database file. The goal is to let users continue the same project through a graphical application or the command line. The CLI design is still evolving. Learn More Ilya’s five-minute talk from DuckCon #7 gives a short tour of this direction. SQLRooms is open source and available at sqlrooms.org. Its examples page is a good place to start. Flowmap City, Foursquare Spatial Desktop, and Cosmograph are free to try. We would like to thank Ilya for the conversation. You can follow his writing on DuckDB and SQLRooms on his website. And if you are reading this, are using DuckDB, and would like to share your story, drop us a line at [email protected]. How DuckDB Entered the Picture From a Side Project to SQLRooms Putting SQLRooms to Work at Foursquare Dynamic Tiling in Spatial Desktop Graph Analytics on the Fly with Cosmograph When the Agent Writes the SQL Where SQLRooms Goes Next Learn More Recent posts Ducks in a Column How JetBrains Built Cross-Database Queries into DataGrip with DuckDB Bringing Local Analytics to the Quant's Desktop