Reactive SQL

[bayre]

Hi Mike,

Do you think it might be possible to build reactivity into SQL cells? Or, more specifically, into DuckDB SQL cells. I don't use Hex, but from the documentation it looks like they have something similar. I think this could be tremendously powerful from a data perspective.

The way I'd see it working is something like this:

• Any SQL cell (regardless of database connection) has an output id and a materialize (or similar) boolean attribute
• If materialize then the output gets loaded into a local DuckDB instance as a table
• A dependency graph operates within the local DuckDB instance to ensure that all cells which reference it remain up to date with new materializations
• A user can then reactively query data from Postgres → join local against local data in DuckDB → derive (n) outputs and produce (n) visualisations

I appreciate that it's probably nowhere near as simple as that (!)

Cheers,

Ben

[mbostock]

Thanks for the feedback! Everything is reactive in Observable Notebooks, so you can definitely do this. For example, you can create a SQL cell that queries a PostgreSQL database and outputs to a named variable, and then you can create a DuckDBClient instance to transform the data using DuckDB-Wasm on the client.

Here’s a screenshot of doing the same in Desktop (though using both DuckDB on the “backend”, still running locally thanks to Desktop’s new integrated database connectors, and DuckDB-Wasm in the browser).

The first SQL cell uses DuckDB and outputs an aapl variable. This is then used to define a DuckDBClient in JavaScript on the client. And the last SQL cell then uses DuckDB-Wasm (via the var:db database, which corresponds to the db variable) to do additional querying.

[bayre]

Thanks Mike. This looks great. I can't yet see the database or output options on SQL cells in the Desktop app, but I'll keep an eye on the releases.

Where DuckDB-Wasm is concerned I do still think there's something to be won from materialising objects as tables / views in the browser. If I wanted to do further analysis on groups in your example above I guess I'd have to establish a new DuckDBClient, like so?

const db2 = DuckDBClient({groups});

-- database: var:db2
SELECT * FROM groups ORDER BY "SUM(Volume)" DESC LIMIT 5

In a perfect world it would be possible to set the original DuckDBClient to do something like output=materialize:groups, at which point the client would wrap the original query in create or replace table ${name} as ${query}; select * from ${name};. The hard part would then be to keep track of the cells downstream of these materialisations and update them if the tables upstream were updated.

[mbostock]

Ah, I understand better now, thank you — you want to create derived tables/views that are defined reactively, and then be able to select from them!

I’d like to support this somehow natively within SQL cells, but for now you can define the view in a JavaScript cell like so:

const top_days = "__top_days";

await db.sql([`
  CREATE OR REPLACE VIEW ${top_days} AS
  SELECT *
  FROM aapl
  ORDER BY Volume DESC
  LIMIT ${limit}
`]);

Then, in a SQL cell you can use query_table (DuckDB is awesome) to refer to your dynamic view:

SELECT strftime(Date, '%a'), COUNT(*)
FROM query_table(${top_days})
GROUP BY 1
ORDER BY 2 DESC

Since your view is defined reactively (in this case referencing a dynamic limit defined elsewhere in the notebook), the view will update dynamically, and then since its name (top_days) is also defined reactively, and query referencing that name will likewise re-run reactively. (You just have to be careful to use query_table(${top_days}) to query the table, since if you query the __top_days table directly, it’ll work but it won’t be reactive.)

Demo video:

Screen.Recording.2025-08-19.at.7.31.07.AM.mov

I expect to release Notebook Kit and Desktop 1.1.0 this week with these features. (I also filed duckdb/duckdb-wasm#2081 which will be needed to support CREATE VIEW statements with dynamic variables in SQL cells.)

[bayre]

This is superb! Thanks for taking the time. I'll look forward to experimenting further with the new release.

I'll keep tabs on the DuckDB-Wasm discussion too. If this functionality were supported in SQL cells directly then Observable Desktop would easily supplant the DuckDB UI for a lot of my work.

[mbostock]

I think there’s a way we can build this into SQL cells (and we can workaround the DuckDB-Wasm limitation linked above). Perhaps you’d have this SQL cell output to top_days:

SELECT *
FROM aapl
ORDER BY Volume DESC
LIMIT ${limit}

And then in another SQL cell you could use View(top_days) to select from the above query as a view:

SELECT strftime(Date, '%a'), COUNT(*)
FROM ${View(top_days)}
GROUP BY 1
ORDER BY 2 DESC

Under the hood, View(top_days) would get translated into query_table(?) and lazily construct the corresponding view. So it’d work the same as the demo above, just a little more ergonomic.

[bayre]

So you'd pass the resolved SQL query for top_days into query_table, thereby avoiding the need to materialise any views in the database itself? Sounds like a great solution if it works.

[mbostock]

It would implicitly CREATE VIEW if you referenced View(top_days) from another SQL cell; it wouldn’t be inlining/duplicating the SELECT query, but it’s effectively equivalent, and it wouldn’t materialize the view.

[bayre]

Hi @mbostock. I've been experimenting with this a bit since you added the output option and I've found a workflow that meets most of my needs. There are two parts to it:

1. A persistent DuckDB client. The current DuckDB client in Notebooks 2.0 is great, but the ephemeral nature of it makes it hard to get the full benefits of a local DuckDB instance. Namely extensions, authentication to cloud services, additional database connections and shared views/tables. I've created yet another DuckDB client, but this one includes both a persistent server and a client to interact with it. In this way it's a bit more like the MotherDuck model, albeit I'm currently passing json between the two rather than a binary serialisation.
2. A ref function that performs minimal SQL injection. In essence select * from ${ref({aapl})} is replaced with select * from query_table(aapl) by my DuckDBClient at runtime, allowing queries to be reactive to upstream cells.

The combination of a fully fledged DuckDB instance and Observable's functionality is every bit as good as I'd hoped and I'd love to see it supported natively if you think it's worth pursuing.