meta

sqliteUniverse: Make SQLite Queries Against Multiple Endpoints in Deno (Val Town) (Part 1) Prerequisite Knowledge Val Town-Hosted SQLite 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): POST /execute { "statement": { "sql": "SELECT * FROM some_table", "args": [] } } Output: { "rows": [[1, "first", 1709942400], [2, "second", 1709942401]], "columns": ["id", "name", "lastModified"] } POST /batch { "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"] }, ] SQLite in Wasm 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 Example 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" ] } ] Dump Tool 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 Putting it All Together 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 sqliteUniverse is an @std/sqlite compatible interface that determines where a table should route to based on different patterns Table Name 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 Default options.interfaces.patterns : 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 Other Available Patterns: 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 Overriding Default Options 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 : 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 } ] Part 2 Since Val Town currently has a character limit for val readmes, this will have to continue in Part 2 !

Forked from postpostscript/authMiddleware

moduleHighlightValueLink: Link to a Val With a Value or Method's Code Highlighted Examples: import { moduleHighlightValueLink, getRedirectUrl } from "https://esm.town/v/postpostscript/moduleHighlightValueLink"; console.log(await moduleHighlightValueLink("@std/email", "email")) // https://val.town/v/std/email#L6-42 console.log(await moduleHighlightValueLink("@postpostscript/moduleHighlightValueLink", "moduleHighlightValueLink")) // https://val.town/v/postpostscript/moduleHighlightValueLink#L6-20 // get URL you can imbed in an iframe console.log(getRedirectUrl("@postpostscript/moduleHighlightValueLink", "getRedirectUrl", true)); // https://postpostscript-modulehighlightvaluelink.web.val.run/?embed=1&module=%40postpostscript%2FmoduleHighlightValueLink&name=getRedirectUrl Iframe example: import { htmlResponse } from "https://esm.town/v/postpostscript/html"; import { getRedirectUrl } from "https://esm.town/v/postpostscript/moduleHighlightValueLink"; export default async function(req: Request): Promise<Response> { return htmlResponse` <iframe src="${getRedirectUrl(import.meta.url, "default", true)}" width="100%" height="100%"> </iframe> `; }

An interactive, runnable TypeScript val by postpostscript

Auth for Val Town I've developed a collection of vals that lets you sign into HTTP vals who use its middleware (help me name this?) Example Projects You have an HTTP val that is just for you, because it accesses your sqlite or blob storage or any other sensitive function. Install the @postpostscript/authMiddleware.authMiddlewareCookie middleware for zero-config authentication that makes executing the endpoint exclusive to you. Example: @postpostscript/authIdExampleCookiePrivate You have an HTTP val and you want to gate a feature behind a login form while letting anyone access the site. Use that same middleware but disable the token issuer ( iss ) requirement and set the optional option to true . Example: @postpostscript/authIdExampleComments You have an API and you want to lock it down, with specific scopes giving you access to specific endpoints. Use the authMiddlewareToken middleware with the additional middleware @postpostscript/pathAsScope . Example: @postpostscript/apiProxy @postpostscript/sqliteExplorerApp : a fork of @nbbaier/sqliteExplorerApp gated behind authMiddlewareCookie Make a cool project with this? Let me know and I'll add it to the list! How it Works Every user who wants to use this system is required to set up a JWKS endpoint at @handle/jwks (Instructions: @postpostscript/jwks ). This is the foundation for trusted communication between code executed by different Val Town accounts, and is what enables this system Anyone who wants to sign in must fork the @postpostscript/authId val which provides the sign in mechanism just for you . You can modify this to your liking as long as it provides the same outputs (redirecting back to the Client with token and clientToken query string params) with the same inputs (a Client Token) If I want to require authentication for my HTTP val, I need to use middleware to protect the endpoint(s). The simplest of that is @postpostscript/authMiddleware.authMiddlewareCookie , which will provide a login form for the endpoint and pass through the request once a trusted token is stored in the AUTH_ID_TOKEN cookie. By default, the val's owner is the only one who can sign into it. Here's an example JWT Payload that provides access to @example/exampleApp : { "jti": "c63a41c9-13d0-4424-b5b4-4a8ec6002610", "aud": [ "@example/exampleApp", "@postpostscript/authId" ], "scope": "@postpostscript/authId/id", "iss": "@postpostscript/authId", "sub": "@postpostscript/authId", "clientTokenId": "12abb6d0-6c88-4d2f-bb56-88f79a2b4503", "iat": 1709694976, "exp": 1709698576 } This, once the token is verified against the @postpostscript/jwks endpoint , says: I received this auth token from @postpostscript ( iss : @postpostscript/authId ) I have been given permission to act as them ( scope : @postpostscript/authId/id ) I was authenticated at the request of a trusted client ( clientTokenId : 12abb6d0-6c88-4d2f-bb56-88f79a2b4503 ) @example/exampleApp is the only val that should accept this token ( aud : @example/exampleApp ) I have 1 hour to use this token, after that it will not work ( iat and exp ) Here's how I got to this token I navigated to https://example-exampleapp.web.val.run/ I entered my Val Town username (@postpostscript) I clicked "Go to Your Sign In Page" and was redirected to https://postpostscript-authid.web.val.run/?clientToken=[...] Since I hadn't verified my identity on my sign in page in an hour, I needed to do that by clicking "Send Sign In Link to My Email" and clicking the link in that email. Now the @postpostscript/authId endpoint knew I was @postpostscript! Next I reviewed the list of scopes @example/exampleApp was requesting. In this case, it was only the required scope of @postpostscript/authId/id , the one proving I am me. In other apps, I may see other scopes that the apps have requested that I can optionally accept I clicked Provide Access to @example/exampleApp and was redirected to https://example-exampleapp.web.val.run/?token=[...]&clientToken[...]. If the app is happy with those tokens, I am now authenticated. If it were locked down to only be available to @example, I'd see an error here Next Steps Try it out, give me feedback, and help out with improvements if you'd like! Right now @handle/authId is the only trusted sign-in provider but that's just for simplicity, there could be @handle/authIdV2 or @handle/iamme or @handle/whatever , as long as the outputs are the same I have started working on a permissioned callback system (run someone else's code as you without needing to fork anything) that uses this, but I'm not 100% sure yet how to guarantee that code you have whitelisted has not changed. Either way, that should be coming in the near future! Let me know if you have any ideas for that or anything else! Glossary Scope A permission that has been granted to a token, for example @example/blog/deleteComment or @example/apiProxy/request/v1/*:GET . Vals can self-describe their scopes like this: export const SCOPES = { deleteComment: "lets you delete a comment", "request/v1/*": "makes an API request as you", } Client Token A token @postpostscript/authMiddleware.authMiddlewareCookie generates to represent a sign-in request. It is passed to a user's sign-in page and then passed back afterwards to ensure both parties trust each other Out of the box, @postpostscript/jwks.verify enforces the maximum number of times a token can be validated when the field maxUses is present in its payload. Client Tokens use this to ensure that 1 sign-in request = 1 sign-in attempt, successful or not JWT (JSON Web Token) A key giving permission to the token-holder to do an action JWKS (JSON Web Key Sets) A standard for the public verification of JWTs

authMiddleware: middleware to protect your HTTP val Todo [ ] Human readable JWT claim validation errors

jwks Required Setup Fork this val Set JWKS Environment Variables Go to https://postpostscript-generatejwksenv.web.val.run/?prefix=JWKS Follow the steps there with the prefix "JWKS" If you want to set the keys up manually, set up env vars JWKS_PRIVATE and JWKS_PUBLIC as JWKS with the algorithm RS512 in this format: { "keys": [ { ... } ] }

An interactive, runnable TypeScript val by postpostscript

An interactive, runnable TypeScript val by postpostscript

provideBlob: Return Response Quickly and Poll for the Expensive Parts Example: See @postpostscript/provideBlobExample for full example. You will need to fork this val to use provideBlob on your projects as it uses @std/blob for storage import { Image } from "https://deno.land/x/imagescript@1.2.17/mod.ts"; import { htmlResponse } from "https://esm.town/v/postpostscript/html"; import { provideBlob } from "https://esm.town/v/postpostscript/provideBlob"; export default async function(req: Request) { const image = provideBlob(async () => { const png = new Image(100, 100); png.drawCircle(50, 50, 50, 100); return png.encode(); }).jsPromise(); return htmlResponse` <div id="image"></div> <script> ${image}.then(blob => { const $img = document.createElement("img") $img.src = URL.createObjectURL(blob) document.getElementById("image").appendChild($img) }) </script> `; }

@postpostscript 's ideas [ ] betaify - fork a val and optionally its dependencies, then kill them all later [ ] import proxy removing or bundling any with .ts or .vue or whatever - in progress if you want to screw with it [ ] sync sqlite between accounts [x] provide read only query access to sqlite: @postpostscript/sqlitePublic [x] backup function that backs up everything on your account (still queryable through @postpostscript/sqliteUniverse): @postpostscript/sqliteBackup [ ] authenticated poll [ ] sign data left on other account to prevent impersonation [x] readme editor ( @postpostscript/readmeManager ) [ ] request wrapper/middleware to serve favicon [x] [multi-account sqlite query @postpostscript/sqliteUniverse [ ] UI to explore public queryable data [ ] val profiler [ ] zachlike game with Vals as the base [ ] choose your own adventure with editor and state stored in URL [ ] new blog post page [ ] doctest runner [ ] recipe manager + auto scheduler -- every week it tells you a list of recipes + a shopping list. you rate what you cook and it gets recommended later if you liked it

An interactive, runnable TypeScript val by postpostscript

An interactive, runnable TypeScript val by postpostscript

An interactive, runnable TypeScript val by postpostscript

sqliteUniverse: make queries against multiple vals or endpoints at the same time! Example: @postpostscript/sqliteUniverseExample Todo [ ] tests‼️ [ ] update to support following syntax: SELECT * FROM "@example/endpoint".someTable or SELECT * FROM "@example/endpoint:::someTable"

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! Examples Example: find my vals that are related to my public tables 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" } ] Example: query against a backup 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 })); Example: query from @std/sqlite and public data simultaneously 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: 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 })); Next Steps 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