Create new tables/views in a database client

[domoritz]

I would like to add a temporary table/view to a database and then query the database after I added the table/view. Because running SQL queries to add a table/view is a side effect, what's the right way in Observable to get the client after the query finished?

Related to https://github.com/observablehq/stdlib/pull/310 https://github.com/observablehq/stdlib/pull/313.

In my client, I added a method client(query, params) that returned the client after the query ran.

[mbostock]

Can you share a concrete example (ideally live code, sample data) for more context? Thanks.

[domoritz]

Here is an example in the old library: https://observablehq.com/d/23408851c5f93aa9

The example may work for you if the last cell with SELECT * FROM foo executes before the cell with SELECT * FROM foo for c.

[domoritz]

What I could do is create a cell like

c2 = {
  await c.query('CREATE OR REPLACE VIEW foo AS SELECT 1');
  return c
}

but I wonder whether there is value in a convenience method that runs a query and returns the client.

[domoritz]

I noticed this difference when moving over https://observablehq.com/@cmudig/introducing-sql-with-duckdb to the standard library client. I made the changes and used the workaround described in https://github.com/observablehq/stdlib/issues/319#issuecomment-1312161939.

[mbostock]

I recommend doing it in the same cell that declares the database client so that all downstream cells see the database already initialized. Like so:

db = {
  const db = await DuckDBClient.of();
  await db.query('CREATE OR REPLACE VIEW foo AS SELECT 1');
  return db;
}

If this is common we can figure out a syntax to incorporate this into the of constructor.

[mbostock]

Closing as working as intended. I can’t think of anything I like better than the above but feel free to share ideas and I’ll reconsider.

[domoritz]

The one thing I am still not happy with (and that my old client didn't do well either), it supporting the SQL cells for adding views and then using these views in subsequent cells. Since Observable defines dependencies between cells explicitly rather than via the order views defined in SQL cells are not necessarily there in a subsequent cell. Not sure whether you have any good ideas for how to address this issue in general (by e.g. having SQL cells also define SQL inputs and outputs similar to JS cells define JS inputs and output).

It would be nice to be able to use SQL cells

Here is a concrete example where I wrote a rather complicated transformation and I would love to use the SQL cell with syntax highlighting for it.

One option I could imagine is a "transform" mode for SQL cells where they don't output a table and don't return values but instead they return the client after the SQL in the cell ran.

[mbostock]

Here’s how I would do it:

https://observablehq.com/@observablehq/derived-views-with-duckdb

The short explanation is: use one cell (moviesInitial) to define the initial database client, then define your side-effect cells (e.g., moviesViews), and lastly define a second cell (movies) which has the same value as the first (the database client) but references all the side-effect cells to guarantee that the side-effect cells get applied first. Then all your queries reference the initialized database client (movies).

[domoritz]

Ah, clever use of comma expressions. I updated the notebook: https://observablehq.com/@cmudig/a2-example-movies-data

[domoritz]

Great, I am happy. I suppose you will make some kind of announcement of the client so I will not mention it on social media yet. Happy to help promote it then you think it's a good time. Thanks for all the great improvements to the client and sql cells!

[mbostock]

Thank you @domoritz! I am writing a notebook at here and will post here when it’s ready:

https://observablehq.com/@observablehq/duckdb