db = DatasetteClient('https://ds.agarcia.dev/congress/congress-members')
db.info()
db`SELECT * FROM members LIMIT 5`
{
const party = "Democrat";
const after = "2019-01-01";
return db`SELECT * FROM terms WHERE party = ${party} and start > ${after}`;
}
viewof currentCASenators = db.table`
SELECT
members.full_name,
start,
end
FROM terms
LEFT JOIN members ON members.bioguide = terms.member
WHERE state = "CA"
AND type = "sen"
AND end > date('now')`
currentCASenators
viewof empty = db.table`select 1 limit 0;`
empty
db.render(
(rows, columns) => md`
Rows:
~~~js
${JSON.stringify(rows, null, 2)}
~~~
Columns:
~~~js
${JSON.stringify(columns, null, 2)}
~~~
`
)`select * from members where gender = "F" limit 2`
db.table.options({
renderCell: (value, column) => html`<div>${value} (<b>${column}</b>)`
})`SELECT bioguide, first_name, last_name FROM members WHERE first_name LIKE "ALEX%" LIMIT 10;`
db.table.options({
extraColumns: [
{ name: "json", render: row => html`<pre><code>${JSON.stringify(row)}` }
]
})`SELECT bioguide, first_name, last_name FROM members WHERE first_name LIKE "ALEX%" LIMIT 5;`
DatasetteClient('notexist')
DatasetteClient('https://ds.agarcia.dev/congress')
db`select not_exist from members;`
db`select * from not_exist;`
db`select syntax error`
db``
db.table`select not`
DatasetteClient = {
async function DatasetteClient(baseUrl, options = {}) {
const { fetchInit = {} } = options;
// sanity check the baseUrl, make sure it
// points to a Datasette db, not the entire instance
const meta = await fetch(`${baseUrl}.json`, fetchInit)
.then(r => r.json())
.catch(() => null);
if (!meta)
throw Error(
'Error connecting to Datasette instance. Make sure you are passing in a valid URL, and that CORS is enabled on the Datasette instance (with the --cors flag).'
);
if (typeof meta.database !== 'string')
throw Error(
'DatasetteClient url must point to a specific database, not the entire Datasette instance.'
);
async function db(strings, ...values) {
const { sql, params } = parseTemplate(strings, ...values);
const fetchUrl = getFetchURL(baseUrl, sql, params);
const data = await fetch(fetchUrl, fetchInit).then(r => r.json());
return data;
}
db.info = async () => {
return fetch(`${baseUrl}.json`, fetchInit).then(r => r.json());
};
db.dry = (strings, ...values) => {
return parseTemplate(strings, ...values);
};
function tableRenderer(options = {}) {
const { renderCell, extraColumns = [] } = options;
return async function(a, ...b) {
const { sql, params } = parseTemplate(a, ...b);
const fetchUrl = getFetchURL(baseUrl, sql, params, 'objects');
const data = await fetch(fetchUrl, fetchInit).then(r => r.json());
const dsLink = `${baseUrl}?${(() => {
const sp = new URLSearchParams();
sp.append('sql', sql);
for (const key in params) {
sp.append(key, params[key]);
}
return sp.toString();
})()}`;
if (data.ok === false)
return _renderError(data.title, data.status, data.error);
if (data.rows.length === 0)
return Object.assign(html`<div>No results!`, { value: data.rows });
const table = html`
<div style="overflow: auto; max-height: 400px;">
<table>
<thead>
<tr>
${data.columns.map(c => html`<th>${DOM.text(c)}`)}
${extraColumns.map(c => html`<th>${DOM.text(c.name)}`)}
</tr>
</thead>
<tbody>
${data.rows.map(
row =>
html`<tr>
${data.columns.map(column => {
const result = renderCell && renderCell(row[column], column);
return html`<td>${result || DOM.text(row[column])}`;
})}
${extraColumns.map(c => html`<td>${c.render(row)}`)}
</tr>`
)}
</tbody>
</table>
</div>
${_renderTableFooter(
data.rows,
data.columns,
data.truncated,
data.query_ms,
dsLink
)}
`;
const value = Object.assign(data.rows, { columns: data.columns });
return Object.assign(table, { value });
};
}
db.table = tableRenderer();
db.table.options = tableRenderer;
db.render = function(render) {
return async function(a, ...b) {
const { sql, params } = parseTemplate(a, ...b);
const fetchUrl = getFetchURL(baseUrl, sql, params, 'objects');
const data = await fetch(fetchUrl, fetchInit).then(r => r.json());
if (data.ok === false)
return _renderError(data.title, data.status, data.error);
return Object.assign(render(data.rows, data.columns), {
value: Object.assign(data.rows, { columns: data.columns })
});
};
};
return db;
}
return DatasetteClient;
}
function _renderTableRows(rows = [], columns = [], { renderCell } = {}) {
return rows.map(
row =>
html`<tr>${columns.map(column => {
const result = renderCell && renderCell(row[column], column);
return html`<td>${result || DOM.text(row[column])}`;
})}`
);
}
function _renderError(title, status, error) {
return html`<div class="observablehq--error"> <div style="font-size: 1.5rem; font-weight: 700;">${title} (${status})</div><pre>${DOM.text(
error
)}`;
}
function parseTemplate(strings, ...values) {
let sql = "";
const params = {};
let paramsI = 0;
for (const [i, s] of strings.entries()) {
if (i < values.length) {
if (Array.isArray(values[i])) {
const sqlArrayParts = [];
for (const v of values[i]) {
const param = `p${paramsI++}`;
sqlArrayParts.push(`:${param}`);
params[param] = v;
}
sql += `${s}(${sqlArrayParts.join(',')})`;
} else {
const param = `p${paramsI++}`;
sql += `${s}:${param}`;
params[param] = values[i];
}
} else {
sql += s;
}
}
return { sql, params };
}
function getFetchURL(baseUrl, sql, params, _shape = "array") {
const searchParams = new URLSearchParams();
searchParams.append('sql', sql);
for (const key in params) {
searchParams.append(key, params[key]);
}
searchParams.append('_shape', _shape);
return `${baseUrl}.json?${searchParams.toString()}`;
}
function duration(ms) {
if (ms < 1000) return `${Number.parseFloat(ms).toPrecision(3)}ms`;
const seconds = ms / 1000;
return `${Number.parseFloat(seconds).toPrecision(3)} seconds`;
}
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
