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
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.