Front-end duckdb sql support

[jcalfee]

Description

I'm so impressed you pulled it off with duckdb-mvp. What a great fit for the largely read-only reporting use cases. So, what about exposing the powerful SQL language (over the cached data) in a more dynamic way: expose this to the front end.

What problem would this solve?

Efficient scalable (runs in the browser) collaborative prototyping reducing the need to to deploy and making it possible to collaborate with non-developers but those willing to work with SQL.

How should it work?

Not sure exactly, but to make this generic the source and storage for the SQL should be generic so evidence.dev does not have to deal with this. The user may want to use localStorage, sessionStorage, etc. or encode / decode the SQL in a URL for end-users to pass around, plugged into a API, etc.. Could there be a simple textarea plugin and error reporting div that hooks into the runtime and reporting engine / and change events working both after deployment and during npm run dev?

I, as a developer, could really encourage people in our community to learn (or use) the advanced SQL features in duckdb by setting this up for them with plenty of cached data, and providing starter SQL and help. A larger audience can field test their prototypes and send it back to me for more perminate report integration.

[jcalfee]

Maybe they could edit an entire markdown page wiki style. This way they could combine data-sources and even get into charting. It could be partial page so they see only what they need and not so much they get discourage or into trouble.

[mcrascal]

I'm so impressed you pulled it off with duckdb-mvp. What a great fit for the largely read-only reporting use cases. So, what about exposing the powerful SQL language (over the cached data) in a more dynamic way: expose this to the front end.

Queries can be modified by the front-end, and are re-executed in the client using duck db's web assembly distribution.

Here are the docs on filters: https://docs.evidence.dev/core-concepts/filters/

And here are the docs on how to interact with the query engine from a custom component: https://docs.evidence.dev/components/custom-components/component-queries/

It is currently undocumented, and subject to change, but we also offer a SQL Client component, which you can invoke in your pages, e.g. <SqlConsole initialQuery='select 100 as number' name='queryResults' ... />

Is that what you're thinking of?

[jcalfee]

Nice, I see it was very little code to pulls schema metadata and put some simple code-insight in there. Could the SqlConsole make use of dynamic queries to support embedded filter variables (e.g. ${inputs.my_input})?