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)
130but those will have to come later!
131
132Another 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
133
134---