7It's currently super limited (no pagination, editing data, data-type specific viewers), and is just a couple dozens lines of code over a couple different vals. Forks encouraged! Just comment on the val if you add any features that you want to share.
8
9To use it on your own Val Town SQLite database, [fork it](https://www.val.town/v/stevekrouse/sqlite_admin/fork) to your account.
10
11It uses [basic authentication](https://www.val.town/v/pomdtr/basicAuth) with your [Val Town API Token](https://www.val.town/settings/api) as the password (leave the username field blank).
24In any other platform, I would have just given up and moved on. But Val Town is different. I was confident that I could address this issue in userspace, without having to wait for the platform to implement it.
25
26Val Town allows you to run a val on a cron schedule, so I wrote a val that would fetch all the vals from the API, and store them as a sqlite table (did I mention that every user get it's own sqlite database ?).
27
28```ts
68## Isolating the Vals Table
69
70There was still a challenge to overcome: the table was part of my account database, and I didn't want to give everyone access to it (there are some sensitive tables in there).
71
72One way to solve this issue would be to publish a stripped-down api that only allows a few predefined queries. But that would be boring, and I wanted to give users the full power of SQL.
88```
89
90Since val.town account can be created with a val.email address, you can create an infinite number of accounts (and thus sqlite databases) using this trick.
91
92So say hello to the [sqlite account](https://val.town/u/sqlite), which is a separate account that only contains the `vals` table.
7
8- [val.town](https://www.val.town/) - Infrastructure
9- [SQLite](https://www.sqlite.org/index.html) - Database
10- [Deno](https://deno.com/) - Runtime
11- [Pushover](https://pushover.net/) - Notifications
11 }
12
13 createIntrospector(db: kysely.Kysely<any>): kysely.DatabaseIntrospector {
14 return new kysely.SqliteIntrospector(db);
15 }
50}
51
52export class VtConnection implements kysely.DatabaseConnection {
53 async executeQuery<R>(compiledQuery: kysely.CompiledQuery): Promise<kysely.QueryResult<R>> {
54 const result = await sqlite.execute({
30- [ ] add triggers to sidebar
31- [ ] add upload from SQL, CSV and JSON
32- [ ] add ability to connect to a non-val town Turso database
33- [x] fix wonky sidebar separator height problem (thanks to @stevekrouse)
34- [x] make result tables scrollable
1# Searching valtown via sql, jq, jo
2
3In a recent discussion, a useful command line pipeline was shared for querying a database and processing the results. The original command looked like this:
4
5```bash
7```
8
9This command effectively queries a database for records matching certain criteria and processes the results. However, there are several ways this pipeline can be improved for efficiency and clarity.
10
11## Recommendations for Improvement
34}
35
36export async function setupDatabase() {
37 await createTable();
38 const docs = await fetchJSON(thisWebURL());
5## Architecture
6
7This version of the site still uses Notion to store the data and [NoteForms](https://noteforms.com) for the form to get your submission into Notion. I intend to cut Notion out of the equation shortly by building our own HTML form that writes data directly to my sqlite database.
8
9* This val is the router for the application
10* @stevekrouse/getDocs pulls the date me docs from my sqlite database.
11* @stevekrouse/dateme_notion_sync syncs my data from Notion to my sqlite database every 10 minutes
12
13## Todos
30- [ ] add triggers to sidebar
31- [ ] add upload from SQL, CSV and JSON
32- [ ] add ability to connect to a non-val town Turso database
33- [x] fix wonky sidebar separator height problem (thanks to @stevekrouse)
34- [x] make result tables scrollable
1import { sqlite } from "https://esm.town/v/std/sqlite?v=4";
2import { parseNotionDateDoc } from "https://esm.town/v/stevekrouse/date_me_doc_helpers";
3import { dateMeNotionDatabase } from "https://esm.town/v/stevekrouse/dateMeNotionDatabase";
4
5function jsonifyDoc(doc) {
28 await sqlite.execute("delete from DateMeDocs");
29
30 const docs = (await dateMeNotionDatabase)
31 .map(parseNotionDateDoc)
32 .map(jsonifyDoc)