Seafowl database client
Observable database client for Seafowlhttps://seafowl.io that follows the DatabaseClient specificationhttps://observablehq.com/@observablehq/database-client-specification.
sha256 = require('https://cdnjs.cloudflare.com/ajax/libs/js-sha256/0.9.0/sha256.js')
async function querySeafowl(sql, host = "demo.seafowl.io") {
let query = sql.trim();
let queryHash = sha256(query);
// Append a random extension (.csv) that Seafowl discards but that makes
// Cloudflare treat the query response as a static cacheable asset.
let response = await fetch(`${host}/q/${queryHash}.csv`, {
headers: {
"X-Seafowl-Query": encodeURIComponent(query)
},
mode: "cors",
method: "GET"
});
if (!response.ok) throw new Error(response.status);
let response_t = await response.text();
console.log(response_t);
return response_t ? response_t.trim().split("\n").map(JSON.parse) : [];
}
function quote(q) {
if (typeof q === 'string' || q instanceof String) {
return `'${q.replaceAll("'", "''")}'`;
}
return q;
}
function getObservableType(dfType) {
if (dfType.includes("Utf")) return "string";
if (dfType.includes("Int64")) return "bigint"; // TODO check if this is actually a bigint
if (dfType.includes("Int")) return "integer";
if (dfType.includes("Float")) return "number";
if (dfType.includes("Boolean")) return "boolean";
if (dfType.includes("Date")) return "other"; // We don't deserialize dates from the JSON
return "other";
}
class SeafowlDatabase {
constructor({
host = "https://demo.seafowl.io",
}) {
this.host = host;
}
async sql(strings, ...args) {
console.log(strings);
console.log(args);
const result = [strings[0]];
args.forEach((val, i) => {
result.push(quote(val), strings[i + 1]);
});
const interpolatedQuery = result.join("");
console.log(interpolatedQuery);
return await querySeafowl(interpolatedQuery, this.host);
}
async describeTables(schema, database) {
let clauses = [];
let args = [];
let queryFragments = ["SELECT table_catalog AS database, table_schema AS schema, table_name AS name FROM information_schema.tables"];
if (schema) {
args.push(schema);
clauses.push("table_schema = ");
}
if (database) {
args.push(database);
clauses.push("table_catalog = ");
}
if (clauses.length) {
queryFragments[0] += " WHERE " + clauses[0];
clauses.slice(1).forEach((clause) => {
queryFragments.push(" AND " + clause);
});
}
console.log(queryFragments);
return await this.sql(queryFragments, ...args);
}
async describeColumns({
table,
schema = "public",
database = "default"
}) {
console.log(table);
let args = [table, schema, database];
let results = await this.sql`SELECT column_name AS name, data_type AS \"databaseType\", is_nullable AS nullable FROM information_schema.columns WHERE table_name = ${table} AND table_schema = ${schema} AND table_catalog = ${database} ORDER BY ordinal_position ASC`;
// Map databaseType to Observable's type
results.forEach(col => col.type = getObservableType(col.databaseType));
return results;
}
}
database = new SeafowlDatabase({host: "https://demo.seafowl.io"})
testQuoting = database.sql`SELECT ${1}`
testDescribeTables = database.describeTables()
testDescribeTablesInSchema = database.describeTables("public", null)
testDescribeTablesInSchemaAndDb = database.describeTables("public", "default")
testDescribeColumns = database.describeColumns({table: "supply_chains_copy"});
database
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.
