Trending

SQLite Explorer

View and interact with your Val Town SQLite data. It's based off Steve's excellent SQLite Admin val, adding the ability to run SQLite queries directly in the interface. This new version has a revised UI and that's heavily inspired by LibSQL Studio by invisal. This is now more an SPA, with tables, queries and results showing up on the same page.

image.webp

Install

Install the latest stable version (v37) by forking this val that imports & exports it in your account:

Install v37

Authentication

SQLite Explorer basic authentication with your Val Town API Token as the password (leave the username field blank).

Todos / Plans

  • fix wonky sidebar separator height problem
  • improve error handling
  • improve table formatting
  • make result tables scrollable
  • add codemirror
  • add loading indication to the run button (initial version shipped)
  • add ability to favorite queries
  • add saving of last query run for a table (started)
  • add visible output for non-query statements
  • add schema viewing
  • add export to SQL, CSV, and JSON (CSV and JSON helper functions written in this val)
  • add refresh to table list sidebar after CREATE/DROP/ALTER statements
  • add automatic execution of initial select query on double click
  • add listener for cmd+enter to submit query
  • add views to the sidebar
Readme
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/** @jsxImportSource https://esm.sh/hono@latest/jsx **/
import { resetStyle } from "https://esm.town/v/nbbaier/resetStyle";
import {
EditorSection,
MockTable,
Separator,
Table,
TablesList,
} from "https://esm.town/v/nbbaier/sqliteStudioComponents";
import { RunSVG, TableSVG } from "https://esm.town/v/nbbaier/sqliteStudioSVGs";
import { sqliteStyle } from "https://esm.town/v/nbbaier/sqliteStyle";
import { basicAuth } from "https://esm.town/v/pomdtr/basicAuth";
import { sqlite } from "https://esm.town/v/std/sqlite";
import { Hono } from "npm:hono";
import type { FC } from "npm:hono/jsx";
import { jsxRenderer } from "npm:hono/jsx-renderer";
const HTML: FC = ({ children }) => {
return (
<html>
<head>
<title>SQLite Explorer</title>
<link rel="preconnect" href="https://fonts.googleapis.com" />
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin />
<link
href="https://fonts.googleapis.com/css2?family=Fira+Code:wght@300..700&family=Source+Sans+3:ital,wght@0,200..900;1,200..900&display=swap"
rel="stylesheet"
/>
<style
dangerouslySetInnerHTML={{ __html: resetStyle }}
/>
<style
dangerouslySetInnerHTML={{ __html: sqliteStyle }}
/>
<script src="https://unpkg.com/htmx.org@1.9.9/dist/htmx.min.js"></script>
<script src="https://unpkg.com/hyperscript.org@0.9.12"></script>
<script type="module" src="https://esm.town/v/nbbaier/resizeScript" />
<script type="module" src="https://esm.town/v/nbbaier/tableSelectScript" />
</head>
<body _="
on keydown[event.metaKey and key is 'Enter'] log 'command + enter' then send submitForm to #sql-editor
">
<div class="root-container">
<header>
<h1>sqlite explorer</h1>
</header>
{children}
</div>
</body>
</html>

Val Town AI Readme Writer

This val provides a class ReadmeWriter for generating readmes for vals with OpenAI. It can both draft readmes and update them directly

PRs welcome! See Todos below for some ideas I have.

Usage

To draft a readme for a given code, use the draftReadme method:

import { ReadmeWriter } from "https://esm.town/v/nbbaier/readmeGPT";

const readmeWriter = new ReadmeWriter({});
const val = "https://www.val.town/v/:username/:valname";

const generatedReadme = await readmeWriter.draftReadme(val);

To write and update a readme for a given code, use the writeReadme method:

import { ReadmeWriter } from "https://esm.town/v/nbbaier/readmeGPT";

const readmeWriter = new ReadmeWriter({});
const val = "https://www.val.town/v/:username/:valname";

const successMessage = await readmeWriter.writeReadme(val);

API Reference

Class: ReadmeWriter

The ReadmeWriter class represents a utility for generating and updating README files.

Constructor

Creates an instance of the ReadmeWriter class.

Parameters:
  • model (optional): The model to be used for generating the readme. Defaults to "gpt-3.5-turbo".
  • apiKey (optional): An OpenAI API key. Defaults to Deno.env.get("OPENAI_API_KEY").

Methods

  • draftReadme(val: string): Promise<string>: Generates a readme for the given val.

    • Parameters:

      • val: URL of the code repository.
    • Returns:

      • A promise that resolves to the generated readme.
  • writeReadme(val: string): Promise<string>: Generates and updates a readme for the given val.

    • Parameters:

      • val: URL of the code repository.
    • Returns:

      • A promise that resolves to a success message if the update is successful.

Todos

  • Additional options to pass to the OpenAI model
  • Ability to pass more instructions to the prompt to modify how the readme is constructed
Readme
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import { type WriterOptions } from "https://esm.town/v/nbbaier/WriterOptions";
import { fetch } from "https://esm.town/v/std/fetch?v=4";
import OpenAI, { type ClientOptions } from "npm:openai";
export class ReadmeWriter {
model: string;
openai: OpenAI;
apiKey: string;
valtownKey: string;
constructor(options: WriterOptions) {
const { model, ...openaiOptions } = options;
this.model = model ? model : "gpt-3.5-turbo";
this.openai = new OpenAI(openaiOptions);
this.valtownKey = Deno.env.get("valtown");
}
private createPrompt(code: string, userPrompt?: string) {
return `
You are an AI assistant that writes documentation for code. You output readmes
in GitHub flavored markdown. Usage sections should include a single code snippet
that a user can copy and paste. Never return anything other than documentation for
the code you are provided.
${userPrompt}
Take the below code and return a markdown readme:
${code}
`;
}
private async getVal(username: string, valName: string) {
try {
const res = await fetch(`https://api.val.town/v1/alias/${username}/${valName}`, {
method: "GET",
headers: {
"accept": "*/*",
"Content-Type": "application/json",
"Authorization": `Bearer ${this.valtownKey}`,
},
});
const { id, code } = await res.json();
return { id, code };
} catch (error) {
throw new Error("Error getting val code: " + error.message);
}
}
private async performOpenAICall(prompt: string) {
try {

This is a deno/valtown port in progress of https://github.com/tarasglek/scrape2md

License: MIT

Handy script to scrape various data sources into markdown. Intended to feed llms in https://chatcraft.org

Usage: https://taras-scrape2md.web.val.run/$YOUR_URL

TODO

https://chatcraft.org/api/share/tarasglek/IDYChVAilfePgVZb_T5pH POST from browser

Metadata for use with https://github.com/tarasglek/valtown2js:

{
  "typeCheck": false,
  "mappings": {
    "https://esm.sh/linkedom": {
      "name": "linkedom",
      "version": "^0.16.8"
    }
  },
  "package": {
    "name": "scrape2md",
    "version": "1.0.0",
    "devDependencies": {
      "@types/turndown": "^5.0.4"
    }
  }
}
Readme
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import { marked } from "https://deno.land/x/marked/mod.ts";
import { DOMParser } from "https://esm.sh/linkedom";
import { isProbablyReaderable, Readability } from "npm:@mozilla/readability@^0.5.0";
import TurndownService from "npm:turndown@^7.1.2";
import { getSubtitles } from "npm:youtube-captions-scraper@^2.0.1";
function getYoutubeVideoID(url: URL): string | null {
const regExp = /(?:youtube\.com\/(?:[^/]+\/.+\/|(?:v|e(?:mbed)?)\/|.*[?&]v=)|youtu\.be\/)([^"&?/\s]{11})/i;
const match = url.href.match(regExp);
return match ? match[1] : null;
}
function response(message: string, contentType = "text/markdown"): Response {
const headers = new Headers();
headers.set("Access-Control-Allow-Origin", "*"); // Allow all origins
headers.set("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS");
headers.set("Access-Control-Allow-Headers", "Content-Type, Authorization");
headers.set("Access-Control-Max-Age", "86400"); // 24 hours
headers.set("Content-Type", contentType);
return new Response(message, {
status: 200,
headers: headers,
});
}
function err(msg: string): Response {
// Create the error message as a JSON string
const errorMessage = JSON.stringify({
error: {
message: msg,
code: 400,
},
});
// Use the response function to create and return the Response object
// with the error message and the "application/json" content type
return response(errorMessage, "application/json");
}
export default async function(req: Request): Promise<Response> {
const myurl = new URL(req.url);
const pathname = myurl.pathname.substring(1) + myurl.search;
if (!pathname.startsWith("http")) {
return new Response(null, {
status: 301,
headers: { "Location": "https://www.val.town/v/taras/scrape2md" },
});
}
const url = new URL(pathname);

Utilities for Classless CSS

Forked from Paul Kinlan's Classless CSS Demo

Usage

For projects that should be pretty in an unopinionated way

import { randomStyle } from "https://esm.town/v/stevekrouse/classless_css";

export default async function(req: Request): Promise<Response> {
  return new Response(`<h1>Welcome to Val Town!</h1>${randomStyle}`, {
    headers: {
      "Content-Type": "text/html",
    },
  });
}
Readme
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
import frameworks from "https://esm.sh/gh/PaulKinlan/classless-css-demo@1a68518805/src/frameworks.ts";
import { randomize } from "https://esm.town/v/stevekrouse/randomize";
delete frameworks[""];
function absolutePath(url: string) {
if (url.startsWith("http")) return url;
else return `https://classless-css-demo.deno.dev` + url;
}
export const random = randomize(Object.entries(frameworks))[0][1];
export const randomURL = absolutePath(random.cssUrl);
export const randomStyle = `<link rel="stylesheet" href="${randomURL}">`;
export default function(req: Response) {
const url = new URL(req.url);
if (url.pathname === "/") {
// return a list of all the framework names as iframes
return new Response(
"<h1>Fork of Paul Kinlan's Classless CSS</h1>" + Object.entries(frameworks)
.map(
([name, data]) =>
`<div><iframe width="90%" style="padding:10px" src="https://classless-css-demo.deno.dev${data.htmlUrl}"></iframe></div>`)
.join(""),
{
headers: {
"Content-Type": "text/html",
},
},
);
}
const name = url.pathname.replace("/", "");
const framework = frameworks[name] || random;
const frameworkURL = absolutePath(framework.cssUrl);
const frameworkStyle = `<link rel="stylesheet" href="${frameworkURL}">`;
return new Response(`<h1>${framework.name} Classless Style!</h1>${frameworkStyle}`, {
headers: {
"Content-Type": "text/html",
},
});
}

Val Town Blob Storage - https://docs.val.town/std/blob

Readme
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import { API_URL } from "https://esm.town/v/std/API_URL";
import { ValTownBlobError } from "https://esm.town/v/std/ValTownBlobError";
import { ValTownBlobNotFoundError } from "https://esm.town/v/std/ValTownBlobNotFoundError";
export const blob = {
get: get,
set: set,
copy: copy,
move: move,
list: list,
delete: delete_,
getJSON: getJSON,
setJSON: setJSON,
};
async function list(prefix?: string): Promise<{ key: string; size: number; lastModified: string }[]> {
let querystring = prefix ? `?prefix=${encodeURIComponent(prefix)}` : "";
const res = await fetch(`${API_URL}/v1/blob${querystring}`, {
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
});
if (!res.ok) {
const body = await res.text();
throw new ValTownBlobError(body ? body : "Error listing blobs");
}
return res.json();
}
async function delete_(key: string) {
const res = await fetch(`${API_URL}/v1/blob/${encodeURIComponent(key)}`, {
method: "DELETE",
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
});
if (!res.ok) {
const body = await res.text();
throw new ValTownBlobError(body ? body : "Error deleting blob");
}
}
async function get(key: string) {
const res = await fetch(`${API_URL}/v1/blob/${encodeURIComponent(key)}`, {
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
});
if (res.status === 404) {
throw new ValTownBlobNotFoundError();

SQLite Table Export Utils

This allows for a val.town-hosted SQLite table to be exported as:

  • JSON (Record<string, unknown>[])
  • Arrow IPC (Uint8Array)
  • TODO: Others?

This can then be used by a HTTP endpoint, like so:

import { exportSQLiteTable, SQLiteTableExportFormat } from "https://esm.town/v/rlesser/sqliteTableExportUtils";

export default async function(req: Request): Promise<Response> {
  const tableName = new URL(req.url).searchParams.get("table");
  if (!tableName) {
    return new Response("Table name is required", { status: 400 });
  }
  const format = (new URL(req.url).searchParams.get("format") || "arrowIPC") as SQLiteTableExportFormat;
  const data = await exportSQLiteTable(tableName, format);
  if (data instanceof Uint8Array) {
    return new Response(data, {
      headers: { "Content-Type": "application/octet-stream" },
    });
  } else {
    return Response.json(data);
  }
}

TODO

  • Specify limit and offset of export, for pagination
  • Smart assessment of if the export is going to be over the val.town limit of 10MB, adjust to paginated of so.
  • Support other export formats.

PRs welcome!

Readme
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import { sqlite } from "https://esm.town/v/std/sqlite";
import { type ResultSet } from "npm:@libsql/client";
import { DataType, Float64, Int32, Table, tableToIPC, Utf8, vectorFromArray } from "npm:apache-arrow";
// Function to map SQLite data types to Apache Arrow data types
function sqliteTypeToArrowType(sqliteType: string): DataType {
switch (sqliteType.toUpperCase()) {
case "INTEGER":
return new Int32();
case "TEXT":
return new Utf8();
case "REAL":
return new Float64();
// Add more mappings as needed
default:
return new Utf8(); // Default or throw error for unsupported types
}
}
export type SQLiteTableExportFormat = "json" | "arrowIPC";
export async function exportSQLiteTable(
tableName: string,
format: SQLiteTableExportFormat,
limit: number | null,
offset: number | null,
): Promise<Record<string, unknown>[] | Uint8Array> {
// Read data from the SQLite table
const data = await sqlite.execute(`
SELECT * FROM ${tableName} LIMIT ${limit || -1} OFFSET ${offset || 0}
`) as ResultSet;
const rows = data.rows;
// Dynamically read the schema of the table
const schemaInfo = await sqlite.execute(`PRAGMA table_info(${tableName})`) as ResultSet;
const columns = schemaInfo.rows.map(col => ({ name: String(col[1]), type: sqliteTypeToArrowType(String(col[2])) }));
// If json, output this right away
if (format == "json") {
return rows.map(r => Object.fromEntries(columns.map((c, idx) => [c.name, r[idx]])));
}
// Convert each column to an Arrow Vector
const tableData = {};
columns.forEach((column, i) => {
const data = rows.map(row => row[i]);
tableData[column.name] = vectorFromArray(data, column.type);
});
// Create an Apache Arrow Table using the dynamically determined schema and vectors

Blob Admin

This is a lightweight Blob Admin interface to view and debug your Blob data.

Screenshot 2023-12-13 at 12.51.53.png

To use it on your own Val Town Blob Storage, fork it to your account.

It uses basic authentication with your Val Town API Token as the password (leave the username field blank).

TODO

  • /new - render a page to write a new blob key and value
    • or new blob by file upload
  • /edit/:blob - render a page to edit a blob (prefilled with the existing content)
    • json validation when the existing content is json
      • checkbox to disable that
  • /delete/:blob - delete a blob and render success
Readme
Fork
1
2
3
4
5
6
7
8
9
10
11
12
import { basicAuth } from "https://esm.town/v/pomdtr/basicAuth";
import { blob_admin_blob } from "https://esm.town/v/stevekrouse/blob_admin_blob";
import { blob_admin_home } from "https://esm.town/v/stevekrouse/blob_admin_home";
import { Hono } from "npm:hono@3.9.2";
const app = new Hono();
app.get("/", async (c) => c.html(await blob_admin_home()));
app.get("/new", (c) => c.html("TODO: New Blob"));
app.get("/:blob", async (c) => c.html(await blob_admin_blob(c.req.param("blob"))));
app.get("/edit/:blob", async (c) => c.html(`TODO: Edit ${c.req.param("blob")}`));
app.get("/delete/:blob", async (c) => c.html(`TODO: Delete ${c.req.param("blob")}`));
export default basicAuth(app.fetch);

Sentry Crons monitoring for scheduled functions in val.town

Use this val to monitor your schedule jobs for any issues and report them directly to Sentry Crons.

How to configure

  1. After forking, find the DSN for your Sentry project by going to Settings > Projects > Select your project >Settings > Client Keys (DSN).
  2. Add your DSN to as a SENTRY_DSN in your val.town environment variables.
  3. Change the monitorConfig.schedule.value Crontab expression to match your job run.
  4. Replace the run() function with your scheduled function.
  5. Alter any other monitor config properties to better fit your scheduled function. Learn more.

Benefits

This val will automatically create and set up a Cron monitor in Sentry, and report all of your runs as check-ins. If your job ever misses a run or fails, Sentry will notify you.

CleanShot 2024-02-05 at 13.56.32@2x.png

Any errors that occur in your scheduled function will be reported directly into Sentry:

CleanShot 2024-02-05 at 13.53.11@2x.png

Readme
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
import * as Sentry from "https://deno.land/x/sentry/index.mjs";
Sentry.init({
dsn: Deno.env.get("SENTRY_DSN"),
});
const monitorSlug = "sentry-val-town-example";
const monitorConfig = {
schedule: {
type: "crontab",
value: "15 * * * *",
},
checkinMargin: 2,
maxRuntime: 10,
timezone: "UTC",
};
// implement your cron job run here
const run = async (interval: Interval) => {
const delay = ms => new Promise(res => setTimeout(res, ms));
console.log(`Last run at ${interval.lastRunAt}`);
console.log("Starting 2s simulated job...");
await delay(2000);
console.log("Done");
// throw new Error("Any errors will be reported to Sentry");
};
export default async function(interval: Interval) {
try {
await Sentry.withMonitor(
monitorSlug,
() => run(interval),
monitorConfig,
);
} catch (err) {
Sentry.captureException(err);
throw err;
} finally {
await Sentry.flush(1000);
}
}

SQLite Dump Util

A utility function that generates SQL statements to dump the data and schema of tables in a SQLite database.

Usage

This example specifically dumps the users table and logs the output:

import { sqliteDump } from "https://esm.town/v/nbbaier/sqliteDump";

const dump = await sqliteDump(["users"]);
console.log(dump)

You can optionally specify a callback to handle the result. The example below dumps the users table and emails it using std/email.

import { email } from "https://esm.town/v/std/email";
import { sqliteDump } from "https://esm.town/v/nbbaier/sqliteDump";

await sqliteDump(["users"], async (res) => {
  await email({ text: res });
});

Function Signature

function sqliteDump(tables?: string[], callback?: ((result: string) => string | void | Promise<void>) | undefined): Promise<string | void>

Parameters

  • tables: (Optional) Array of table names to include in the dump. If not provided, all tables will be included.
  • callback: (Optional) An (potentially async) callback function to process the dump result. The callback receives the dump string as its argument.
Readme
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import { email } from "https://esm.town/v/std/email";
import { sqlite } from "https://esm.town/v/std/sqlite";
function generateInsertStatements(
tableName: string,
columns: string[],
rows: string[][],
): string[] {
const columnNames = columns.join(", ");
let insertStatements = [];
for (const row of rows) {
const values = row.map(v => `'${typeof v === "string" ? v.replace(/'/g, "%27") : v}'`).join(", ");
const insertStatement = `INSERT INTO ${tableName} (${columnNames}) VALUES (${values});`;
insertStatements.push(insertStatement);
}
return insertStatements;
}
export async function sqliteDump(
tables?: string[],
callback?: (result: string) => string | void | Promise<void>,
) {
const dumpTables = tables !== undefined
? tables
: ((
await sqlite.execute(
`select name, type from sqlite_schema where type = 'table'`,
)
).rows.map((row) => row[0]) as string[]);
let statements: string[] = [];
for (const table of dumpTables) {
const schemaQuery = await sqlite.execute(
`SELECT name, sql FROM sqlite_schema WHERE name = '${table}'`,
);
const tableSchema = schemaQuery.rows[0][1] as string;
const createStatement = tableSchema.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
const { columns, rows } = await sqlite.execute(`select * from ${table} where id = 89`);
const insertStatements = generateInsertStatements(table, columns, rows);
statements.push(createStatement, ...insertStatements);
}
if (callback === undefined) {
callback = (result) => {

Prune a val's versions.

Useful if you want to delete a bunch of versions.

All versions before keep_since that aren't in keep_versions will be deleted!!!

You can run it without passing commit to see a preview of what will happen.

Example

await prune_val("abcdefg", [3,6,8], 12, true);

Could output

Val: untitled_peachTakin, Current Version: 15
Deleting Versions: [ 1, 2, 4, 5, 7, 8, 9, 10, 11 ]
Deleting Version 1
Deleted
Deleting Version 2
Deleted
Deleting Version 4
Deleted
Deleting Version 5
Deleted
Deleting Version 6
Deleted
Deleting Version 7
Version already deleted, skipping
Deleting Version 8
Deleted
Deleting Version 9
Deleted
Deleting Version 10
Deleted
Deleting Version 11
Deleted
Readme
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
export async function prune_val(
val_id: string,
keep_versions: number[],
keep_since: number,
commit = false,
): Promise<void> {
const resp = await fetch(`https://api.val.town/v1/vals/${val_id}`, {
headers: {
Authorization: "Bearer " + Deno.env.get("valtown"),
},
});
if (resp.status !== 200) {
console.error("Error fetching val:", resp);
return;
}
const val = await resp.json();
console.log(`Val: ${val.name}, Current Version: ${val.version}`);
const versions_to_delete = [];
for (let i = 1; i <= val.version; i++) {
if (i >= keep_since) {
break;
}
if (keep_versions.indexOf(i) == -1) {
versions_to_delete.push(i);
}
}
console.log("Deleting Versions:", versions_to_delete);
for (const v of versions_to_delete) {
console.log(`Deleting Version ${v}`);
const resp = await fetch(
`https://api.val.town/v1/vals/${val_id}/versions/${v}`,
{
headers: {
Authorization: "Bearer " + Deno.env.get("valtown"),
},
},
);
switch (resp.status) {
case 200: {
if (commit) {
const resp = await fetch(
`https://api.val.town/v1/vals/${val_id}/versions/${v}`,
{
method: "DELETE",
headers: {
Authorization: "Bearer " + Deno.env.get("valtown"),
},
},