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
64### SQLite in Wasm
65
66There is a [deno package (sqlite)](https://deno.land/x/sqlite@v3.8) 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](https://val.town/v/postpostscript/sqliteWasm)
67
68#### Example
123
124 - Serving a subset of your private data publicly for others to query (Example: [@postpostscript/sqlitePublic](https://val.town/v/postpostscript/sqlitePublic))
125 - Backing up your database and querying against that backup (via @postpostscript/sqliteBackup's [sqliteFromBlob](https://postpostscript-modulehighlightvaluelink.web.val.run/?module=@postpostscript/sqliteBackup&name=sqliteFromBlob) and [sqliteToBlob](https://postpostscript-modulehighlightvaluelink.web.val.run/?module=@postpostscript/sqliteBackup&name=sqliteToBlob))
126
127But we can do more..! What if we could query from multiple of these data sources.. at the same time! 😱
150The following patterns are accessible through `import { patterns } from "https://esm.town/v/postpostscript/sqliteUniverse"`:
151
152- `patterns.blob` - `/^blob:\/\//` (`blob://backup:sqlite:1709960402936`) - import the database from **private** blob `backup:sqlite:1709960402936`
153
154### Overriding Default Options