sqliteUniverse: Make SQLite Queries Against Multiple Endpoints in Deno (Val Town) (Part 1)

Val Town hosts SQLite as part of its standard library (@std/sqlite). This makes a fetch request against their closed-source API (using your API token) which returns results in a consistent format. This is great because you can host your own endpoints that work similarly, and reuse code that was only designed in mind for that original hosted interface

The standard format (abridged to important fields):

{
  "statement": {
    "sql": "SELECT * FROM some_table",
    "args": []
  }
}

Output:

{
  "rows": [[1, "first", 1709942400], [2, "second", 1709942401]],
  "columns": ["id", "name", "lastModified"]
}

{
  "statements": [
    {
      "sql": "INSERT INTO some_table VALUES (?, ?, ?)",
      "args": [3, "third", 1709942402]
    },
    {
      "sql": "SELECT * FROM some_table",
      "args": []
    }
  ]
}

Output:

[
  {
    "rows": [],
    "columns": ["id", "name", "lastModified"]
  },
  {
    "rows": [[1, "first", 1709942400], [2, "second", 1709942401], [3, "third", 1709942402]],
    "columns": ["id", "name", "lastModified"]
  },
]

There is a deno package (sqlite) which lets you (among other things) create SQLite databases in-memory using WebAssembly. I've created a Val which wraps this to enable it to be a drop-in replacement for @std/sqlite: @postpostscript/sqliteWasm

Create val
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";

const sqlite = createSqlite();
console.log(sqlite.batch([
  Statement`
    CREATE TABLE test (
      id TEXT PRIMARY KEY,
      value TEXT
    )
  `,
  Statement`
    INSERT INTO test VALUES (
      ${"some-id"},
      ${"some-value"}
    )
  `,
  Statement`
    SELECT * FROM test
  `,
]))

Result:

[
  {
    rows: [],
    rowsAffected: 0,
    columns: []
  },
  {
    rows: [],
    rowsAffected: 1,
    columns: []
  },
  {
    rows: [ [ "some-id", "some-value" ] ],
    rowsAffected: 0,
    columns: [ "id", "value" ]
  }
]

I have modified @nbbaier's great work at @postpostscript/sqliteDump to support dumping from any sqlite interface, whether the standard library's version, over HTTP, or through the above Wasm implementation

All of the above enables:

• Serving a subset of your private data publicly for others to query (Example: @postpostscript/sqlitePublic)
• Backing up your database and querying against that backup (via @postpostscript/sqliteBackup's sqliteFromBlob and sqliteToBlob)

But we can do more..! What if we could query from multiple of these data sources.. at the same time! 😱

sqliteUniverse is an @std/sqlite compatible interface that determines where a table should route to based on different patterns

The actual table name will always come after a "/", with the exception of tables without any endpoint, for example users. Everything before the last "/" is the endpoint name. Endpoint interfaces will be chosen in the following order:

• Exact match in options.interfaces.exact e.g. @std/sqlite/someTable would match options.interfaces.exact["@std/sqlite"]
• Each pattern in options.interfaces.patterns
• options.interfaces.fallback will be called

An error is thrown if none of the above matches AND returns an sqlite interface. If there is a match but the handler returns nothing, it will continue down the list

• patterns.https - /^https:\/\// ( https://example.com/somePath/tableName): fetch from https://example.com/somePath/batch
• patterns.val - /^@/ (@author/name/somePath/tableName): fetch from the val's endpoint, https://author-name.web.val.run/somePath/batch

The following patterns are accessible through import { patterns } from "https://esm.town/v/postpostscript/sqliteUniverse":

• patterns.blob - /^blob:\/\// (blob://backup:sqlite:1709960402936) - import the database from private blob backup:sqlite:1709960402936

The sqliteUniverse export contains defaults insuring no private data will be leaked. If you want to reduce or extend these options, use the sqliteUniverseWithOptions export and pass a modified interfaces option in the first argument:

Examples of how to set options.interfaces.exact, options.interfaces.patterns, and options.interfaces.fallback:

Create val
import { sqliteUniverseWithOptions, patterns, defaultPatterns } from "https://esm.town/v/postpostscript/sqliteUniverse";
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
import { sqliteFromAPI } from "https://esm.town/v/postpostscript/sqliteFromAPI";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";

const sqlite = sqliteUniverseWithOptions({
  interfaces: {
    exact: {
      // `SELECT * FROM "some-endpoint/someTable"` will match
      // `SELECT * FROM "some-endpoint/somePath/someTable"` will NOT match
      "some-endpoint": ({ endpoint, tables }) => {
        const sqlite = createSqlite()
        sqlite.batch([
          Statement`CREATE TABLE someTable (someField TEXT PRIMARY KEY)`,
          Statement`INSERT INTO someTable VALUES (${"some-field"})`
        ])
        return sqlite
      },
    },

    patterns: [
      ...defaultPatterns,
      [
        // shorthand e.g. ~/sqlitePublic -> @postpostscript/sqlitePublic
        /^~\/(\w+)/,
        ({ endpoint, tables, match }) => {
          return sqliteFromAPI(`@postpostscript/${match[1]}`)
        },
      ]
    ],

    fallback({ endpoint, tables }) {
      // if an endpoint is not found, this will be called
      return sqliteFromAPI(`@postpostscript/sqlitePublic`)
    },
  },
})

console.log(await Statement`
  SELECT *
  FROM "some-endpoint/someTable"
  JOIN "~/sqliteVals/vals"
  JOIN authIdExampleComments_comment
  LIMIT 1
`.execute({ sqlite }))

Output:

[
  {
    someField: "some-field",
    id: "aeb70bbb-05fc-403b-8d6a-130c423ecb53",
    name: "discordWelcomedMembers",
    code: "// set at Sun Mar 10 2024 00:32:48 GMT+0000 (Coordinated Universal Time)\n" +
      "export let discordWelcomedM"... 8289 more characters,
    version: 234771,
    privacy: "public",
    public: 1,
    run_start_at: "2024-03-10T00:32:48.978Z",
    run_end_at: "2024-03-10T00:32:48.978Z",
    created_at: "2024-03-10T00:32:48.978Z",
    author_id: "a0bf3b31-15a5-4d5c-880e-4b1e22c9bc18",
    author_username: "stevekrouse",
    username: "postpostscript",
    comment: "test",
    date_added: 1709776325.857
  }
]

Since Val Town currently has a character limit for val readmes, this will have to continue in Part 2!