Better support for DuckDB extensions

[Fil]

It would be great to be able to install/load DuckDB extensions as simply as:

---
sql:
  install: [json, fts, icu, spatial, vss]
  tables:
    taxis: nyc-taxis.parquet
---

(This means we'll need to extend the shape of the sql front matter option, see #1054)

related: #1007; #1508

Ref.: documentation for loading extensions in duckdb-wasm.

[Fil]

We should also enable autoloading, I guess. And we should copy the extension files to the local cache — not load it directly from the extensions.duckdb.org domain.

[jaanli]

Yes @Fil this would be amazing - we mainly use this extension: https://github.com/duckdb/duckdb_vss

do you have a sense of what it would take to support this?

I’m free the next month to help with our team @onefact because I am giving a talk at the @duckdb conference:

https://duckdb.org/2024/08/15/duckcon5.html

I would love to help on this and demo it in August!

[Fil]

The main blocker currently is the fact that duckdb-wasm’s stable release does not support extensions, and we've had to clamp its version to 1.28.0 because the whole ecosystem of duckdb & arrow is moving fast and breaking things.

If you want to live on the edge, you just need to edit the src/npm.ts file in two places, to replace "1.28.0" by "latest" or your version of choice. Then empty the _npm cache, and you should be good to go. (You can immediately see the difference if you load a parquet file, because the parquet format now requires an extension, which is autoloaded from extensions.duckdb.org.)

The next step for us will be to self-host all the known extensions, rather than load them from extensions.duckdb.org, so that the site is self-contained and can work offline. The syntactic sugar I'm imagining in the description of this issue might then only be necessary for non-autoloading extension (or maybe we leave it to the user to explicitly load the necessary extensions within any query that needs one).

[Fil]

I recently added an example here
https://observablehq.observablehq.cloud/pangea/party/duckdb-spatial

it's not very rich feature-wise, but it shows that it is technically possible