Software engineer, available for freelance work! • he/him/his
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`;
}
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.