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

If you haven't already, check out Part 1 for an overview of this system!

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

console.log(await Statement`
  SELECT v.name AS val, t.name AS "table"
  FROM "https://postpostscript-sqlitevals.web.val.run/vals" v
  INNER JOIN "https://postpostscript-sqlitepublic.web.val.run/sqlite_schema" t
  ON t.name LIKE ('https://postpostscript-sqlitepublic.web.val.run/' || v.name || '%')
  AND t.type = 'table'
  WHERE v.author_username = 'postpostscript'
`.execute({
	sqlite: sqliteUniverse,
}))

Since table names matching /^@/ will let you pass val names in the place of their HTTP endpoints, we can write the query as:

SELECT v.name AS val, t.name AS "table"
FROM "@postpostscript/sqliteVals/vals" v
INNER JOIN "@postpostscript/sqlitePublic/sqlite_schema" t
ON t.name LIKE ('@postpostscript/sqlitePublic/' || v.name || '%')
AND t.type = 'table'
WHERE v.author_username = 'postpostscript'

The result for the latter:

[
  {
    val: "authId",
    table: "@postpostscript/sqlitePublic/authIdExampleComments_comment"
  },
  {
    val: "authIdExampleComments",
    table: "@postpostscript/sqlitePublic/authIdExampleComments_comment"
  }
]

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

const sqlite = sqliteUniverseWithOptions({
  interfaces: {
    patterns: [
      ...defaultPatterns,
      patterns.blob,
    ],
  },
});

console.log(await Statement`
  SELECT *
  FROM "blob://backup:sqlite:1709960402936/someTable"
`.execute({ sqlite }));

Create val
import { sqliteFromBlob } from "https://esm.town/v/postpostscript/sqliteBackup";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { defaultPatterns, sqliteUniverseWithOptions } from "https://esm.town/v/postpostscript/sqliteUniverse";
import { sqlite as sqlitePrivate } from "https://esm.town/v/std/sqlite?v=4";

const sqlite = sqliteUniverseWithOptions({
  interfaces: {
    patterns: defaultPatterns,
    fallback({ endpoint, tables }) {
      return sqlitePrivate
    },
  },
});

console.log(await Statement`
  SELECT t.*, p.*
  FROM privateTable t
  JOIN "@example/sqlitePublic/publicTable" p
`.execute({ sqlite }));

You could also do it like this to make it more explicit:

Create val
const sqlite = sqliteUniverseWithOptions({
  interfaces: {
    exact: {
      "@std/sqlite": sqlitePrivate,
    },
    patterns: defaultPatterns,
  },
});

console.log(await Statement`
  SELECT t.*, p.*
  FROM "@std/sqlite/privateTable" t
  JOIN "@example/sqlitePublic/publicTable" p
`.execute({ sqlite }));

I'd like to make patterns to allow queries against JSON and Val/ESM exports e.g.

SELECT * FROM "json://example.com/example.json"

or

SELECT * FROM "export://@postpostscript/someVal/someExport"

but those will have to come later!

Another necessary feature for querying against larger databases will be to use the WHERE or JOIN conditions when dumping from them, but this will be more complicated

P.S. This has been a super fun project to work on and I hope you find it interesting or inspiring too! Let me know if you find any errors in this post or if you'd like me to expand more on a specific feature, and let me know if you make anything cool with this and I'll make a note of it!

P.P.S (😏) this interface is not 100% stable yet, so I would recommend pinning to specific versions if it not breaking is important to you

P.P.P.S. Want to serve your own public SQLite endpoints? This is currently the simplest example I have of how to do that: @postpostscript/sqlitePublic