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---
14
15## Others
16* `getAllVideos` Lists all the videos in the database
17* `backfillVideo` Takes a video object and puts it in the DB. Useful if you're iterating over to...you guessed it, backfill content.
18