sqlite.js

// https://github.com/sql-js/sql.js/issues/284
const SQLite = await (async () => {
  const exports = {};
  const response = await fetch(import.meta.resolve("npm:sql.js/dist/sql-wasm.js"));
  new Function("exports", await response.text())(exports);
  return exports.Module({locateFile: (name) => import.meta.resolve("npm:sql.js/dist/") + name});
})();

export default SQLite;

export class SQLiteDatabaseClient {
  constructor(db) {
    Object.defineProperties(this, {
      _db: {value: db}
    });
  }
  static async open(source) {
    return new SQLiteDatabaseClient(new SQLite.Database(await load(await source)));
  }
  async query(query, params) {
    return await exec(this._db, query, params);
  }
  async queryRow(query, params) {
    return (await this.query(query, params))[0] || null;
  }
  async explain(query, params) {
    const rows = await this.query(`EXPLAIN QUERY PLAN ${query}`, params);
    return element("pre", {className: "observablehq--inspect"}, [text(rows.map((row) => row.detail).join("\n"))]);
  }
  async describeTables({schema} = {}) {
    return this.query(
      `SELECT NULLIF(schema, 'main') AS schema, name FROM pragma_table_list() WHERE type = 'table'${
        schema == null ? "" : " AND schema = ?"
      } AND name NOT LIKE 'sqlite_%' ORDER BY schema, name`,
      schema == null ? [] : [schema]
    );
  }
  async describeColumns({schema, table} = {}) {
    if (table == null) throw new Error("missing table");
    const rows = await this.query(
      `SELECT name, type, "notnull" FROM pragma_table_info(?${schema == null ? "" : ", ?"}) ORDER BY cid`,
      schema == null ? [table] : [table, schema]
    );
    if (!rows.length) throw new Error(`table not found: ${table}`);
    return rows.map(({name, type, notnull}) => ({
      name,
      type: sqliteType(type),
      databaseType: type,
      nullable: !notnull
    }));
  }
  async describe(object) {
    const rows = await (object === undefined
      ? this.query("SELECT name FROM sqlite_master WHERE type = 'table'")
      : this.query("SELECT * FROM pragma_table_info(?)", [object]));
    if (!rows.length) throw new Error("Not found");
    const {columns} = rows;
    return element("table", {value: rows}, [
      element("thead", [
        element(
          "tr",
          columns.map((c) => element("th", [text(c)]))
        )
      ]),
      element(
        "tbody",
        rows.map((r) =>
          element(
            "tr",
            columns.map((c) => element("td", [text(r[c])]))
          )
        )
      )
    ]);
  }
  async sql() {
    return this.query(...this.queryTag.apply(this, arguments));
  }
  queryTag(strings, ...params) {
    return [strings.join("?"), params];
  }
  export() {
    return this._db.export(this._db);
  }
}

Object.defineProperty(SQLiteDatabaseClient.prototype, "dialect", {
  value: "sqlite"
});

// https://www.sqlite.org/datatype3.html
function sqliteType(type) {
  switch (type) {
    case "NULL":
      return "null";
    case "INT":
    case "INTEGER":
    case "TINYINT":
    case "SMALLINT":
    case "MEDIUMINT":
    case "BIGINT":
    case "UNSIGNED BIG INT":
    case "INT2":
    case "INT8":
      return "integer";
    case "TEXT":
    case "CLOB":
      return "string";
    case "REAL":
    case "DOUBLE":
    case "DOUBLE PRECISION":
    case "FLOAT":
    case "NUMERIC":
      return "number";
    case "BLOB":
      return "buffer";
    case "DATE":
    case "DATETIME":
      return "string"; // TODO convert strings to Date instances in sql.js
    default:
      return /^(?:(?:(?:VARYING|NATIVE) )?CHARACTER|(?:N|VAR|NVAR)CHAR)\(/.test(type)
        ? "string"
        : /^(?:DECIMAL|NUMERIC)\(/.test(type)
        ? "number"
        : "other";
  }
}

function load(source) {
  return typeof source === "string"
    ? fetch(source).then(load)
    : source && typeof source.arrayBuffer === "function" // Response, Blob, FileAttachment
    ? source.arrayBuffer().then(load)
    : source instanceof ArrayBuffer
    ? new Uint8Array(source)
    : source;
}

async function exec(db, query, params) {
  const [result] = await db.exec(query, params);
  if (!result) return [];
  const {columns, values} = result;
  const rows = values.map((row) => Object.fromEntries(row.map((value, i) => [columns[i], value])));
  rows.columns = columns;
  return rows;
}

function element(name, props, children) {
  if (arguments.length === 2) (children = props), (props = undefined);
  const element = document.createElement(name);
  if (props !== undefined) for (const p in props) element[p] = props[p];
  if (children !== undefined) for (const c of children) element.appendChild(c);
  return element;
}

function text(value) {
  return document.createTextNode(value);
}