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
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
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
5/**
6 * Every Val Town account comes with its own private
7 * [SQLite database](https://www.sqlite.org/) that
8 * is accessible from any of your vals.
9 * ([Docs ↗](https://docs.val.town/std/sqlite))
5/**
6 * Every Val Town account comes with its own private
7 * [SQLite database](https://www.sqlite.org/) that
8 * is accessible from any of your vals.
9 * ([Docs ↗](https://docs.val.town/std/sqlite))
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