Connect from JavaScript
Run SQL in the browser and draw charts and maps with the queria package from npm
The queria package on npm runs SQL on Queria datasets inside a web page and draws the results as charts and maps. SQL runs in DuckDB WASM in the browser. Charts and maps show the sources and licenses of the datasets they use.
npm install queria
To draw charts, also install vega, vega-lite, vega-embed and vega-interpreter. To draw maps, also install maplibre-gl. Install them only for the components you use.
The package is in version 0.x. Until 1.0, any release can include breaking changes.
Declaring datasets
Declare each dataset you read by its fully qualified name (owner/name, such as queria/e_stat). In SQL, refer to it by the last part of the name (e_stat.<schema>.<table>). To read two datasets with the same name, give them aliases: { a: "alice/e_stat", b: "bob/e_stat" }.
HTML
Importing queria/elements/define registers the <queria-chart> and <queria-map> elements.
<script type="module">import "queria/elements/define";</script>
<queria-chart
datasets="queria/e_stat"
spec='{"mark": "line", "encoding": {"x": {"field": "year"}, "y": {"field": "population", "type": "quantitative"}}}'
sql="SELECT year, any_value(value) AS population FROM e_stat.ssds.a_municipal_population
WHERE area = '13224' AND cat01 = 'A1101' GROUP BY year ORDER BY year"></queria-chart>
spec is a Vega-Lite specification. The SQL result becomes its data.
With save-image, a “Save image” button appears in the line of sources. It downloads the chart as a 1200 × 630 pixel PNG. The image is made with chartPng() and includes a line with the sources and licenses of the datasets in datasets and the Queria logo. Its colors, font and number format match the chart on the page. In React, use <Chart saveImage />.
Maps
<queria-map
datasets="queria/e_stat"
layer='{"area": "city", "code": "lg_code", "color": "population", "label": "lg_code"}'
sql="SELECT area AS lg_code, any_value(value) AS population
FROM e_stat.ssds.a_municipal_population
WHERE area LIKE '13%' AND year = 2020 AND cat01 = 'A1101' GROUP BY area"></queria-map>
layer sets how rows are placed on the map.
| Field | Description |
|---|---|
area, code |
Place rows by area code. area is prefecture, city or small_area; code is the column with the area code |
lng, lat |
Place rows by position. The longitude and latitude columns |
color |
Column to color by. Numbers go from light (smallest) to dark (largest); other values get one color each |
label |
Column shown on hover |
opacity |
Opacity (0 to 1) |
Include the maplibre-gl CSS in the page. Set the map height with the CSS variable --queria-map-height (400px by default).
React
import { Chart, DataMap, QueriaProvider } from "queria/react";
<QueriaProvider datasets={["queria/e_stat"]}>
<Chart sql={sql} spec={spec} />
<DataMap sql={sql} layer={layer} />
</QueriaProvider>;
Table shows the SQL result as a table, up to 100 rows by default (limit changes it). In HTML, use <queria-table sql="...">; in JavaScript, renderTable(el, rows).
Kpi shows one number large, with its change from the year before under it. It reads the value column from the last row and compares it with the row before, so SQL ordered by year gives the year-over-year change. To compare with another column of the last row, name it in compare.
<Kpi sql={sql} value="population" label="Population" />
Numbers from 10,000 are abbreviated (1.394M, or 139.4万 on Japanese pages); compact={false} writes them in full. When unit is %, the change is shown in points (pt). compareLabel replaces the heading of the change (“vs previous year” by default). In HTML, use <queria-kpi sql="..." value="population">; in JavaScript, renderKpi(el, rows, { value }).
To get only the rows, call useQuery(sql) inside QueriaProvider. Its status is loading, done or error, and it has rows when done.
JavaScript
import { connect, renderChart } from "queria";
const q = connect({ datasets: ["queria/e_stat"] });
const rows = await q.sql(sql);
await renderChart(el, spec, rows);
connect() returns a connection with sql() and sources(). sql() returns the result as an array of rows and attaches the datasets the SQL references the first time they are used. sources() returns the dataset information used to show sources. Draw maps with renderMap(el, layer, rows).
To put a value into an SQL string, quote it as an SQL literal with lit().
import { lit } from "queria";
const rows = await q.sql(`SELECT year, value FROM e_stat.ssds.a_municipal_population WHERE area = ${lit(code)}`);
SQL that calls ST_* functions loads the DuckDB spatial extension the first time it runs.
Exporting images
chartPng() turns a chart into a PNG image, for example to post on social media. The image is 1200 × 630 pixels on a solid background, with a line of sources and licenses and the Queria logo under the chart.
import { chartPng, connect } from "queria";
const q = connect({ datasets: ["queria/e_stat"] });
const blob = await chartPng(spec, await q.sql(sql), { sources: await q.sources(sql) });
Without sources, the image is the chart alone, with no line of sources and no logo. The other options are:
| Option | Description |
|---|---|
theme |
light (default) or dark |
highlight |
Values to highlight in purple, as in renderChart() |
width, height |
Size of the image in CSS pixels, including the line of sources (600 × 315 by default) |
scale |
Pixels per CSS pixel (2 by default) |
chartSvg() takes the same options and returns the image as an SVG string. It also runs in Node, so you can make images at build time or on a server. The text in the image uses the fonts installed where it is drawn.
Private datasets
To read private datasets, pass a data token (a string starting with qd_) or a function that returns one: connect({ datasets, token }). The token is sent only to the Queria API and to the hosts of the declared datasets.
Do not put an API token (qk_) in the HTML or JavaScript of a public page. Anyone who opens the page can read it.
