SQL code blocks, powered by DuckDB 🦆
Framework now includes built-in support for client-side SQL powered by DuckDB. You can use the new SQL code blocks and sql tagged template literal to query data from CSV, TSV, JSON, Apache Arrow, and Apache Parquet files, which can either be static or generated by data loaders.
To use SQL, first register the desired tables in the page’s front matter using the sql option. Each key is a table name, and each value is the path to the corresponding data file. For example, to register a table named gaia from a Parquet file:
---
sql:
gaia: ./lib/gaia-sample.parquet
---To run SQL queries, create a SQL fenced code block (```sql). For example, to query the first 10 rows from the gaia table:
```sql
SELECT * FROM gaia ORDER BY phot_g_mean_mag LIMIT 10
```SQL code blocks render results using Inputs.table:
To refer to the results of a query in JavaScript, use the id directive. (When an id is specified, the table is hidden by default, but you can display it using the display directive.) For example, to refer to the results of the previous query as top10:
```sql id=top10
SELECT * FROM gaia ORDER BY phot_g_mean_mag LIMIT 10
```For dynamic or interactive queries that respond to user input, you can interpolate values into SQL queries using inline expressions ${…}. For example, to show the stars around a given brightness:
```js
const mag = view(Inputs.range([6, 20], {label: "Magnitude"}));
```
```sql
SELECT * FROM gaia WHERE phot_g_mean_mag BETWEEN ${mag - 0.1} AND ${mag + 0.1};
```SQL fenced code blocks are shorthand for the sql tagged template literal. You can invoke the sql tagged template literal directly like so:
const rows = await sql`SELECT * FROM gaia LIMIT 10`;For more, see the new SQL guide.
Other changes
Fix FileAttachment resolution in imported modules. Fix module preloads of transitive imports. Fix a race condition in display where stale values could be displayed after invalidation. Unpin the version of Apache ECharts (from 5.4.3; currently 5.5.0). Use files rather than built-in sample datasets for the default project templates.
New Contributors
- @elevenchars made their first contribution in #991
Full Changelog: v1.1.2...v1.2.0