Val Town Code SearchReturn to Val Town

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

10const dumped = {};
11
12async function database() {
13  const dump = await sqliteDump(dumped);
14  const sqlite = createSqlite();
50app.post("/execute", async (c) => {
51  const { statement } = await c.req.json();
52  const sqlite = await database();
53  const res = await sqlite.execute(statement);
54  console.log(typeof res, res);
57app.post("/batch", async (c) => {
58  const { statements } = await c.req.json();
59  const sqlite = await database();
60  return c.json(sqlite.batch(statements));
61});

4function incrementNumber() {
5  currentNumber += 1;
6  // Here, you would ideally persist the current number to a database or file for long-term storage.
7  return currentNumber;
8}

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

34- [ ] add triggers to sidebar
35- [ ] add upload from SQL, CSV and JSON
36- [ ] add ability to connect to a non-val town Turso database 
37
38

31  interface Register {
32    Lucia: typeof lucia;
33    DatabaseUserAttributes: DatabaseUserAttributes;
34  }
35}
36
37interface DatabaseUserAttributes {
38  username: string;
39}

1import type {
2  Adapter,
3  DatabaseSession,
4  DatabaseUser,
5  RegisteredDatabaseSessionAttributes,
6  RegisteredDatabaseUserAttributes,
7} from "npm:lucia";
8
34  public async getSessionAndUser(
35    sessionId: string,
36  ): Promise<[session: DatabaseSession | null, user: DatabaseUser | null]> {
37    const [databaseSession, databaseUser] = await Promise.all([
38      this.getSession(sessionId),
39      this.getUserFromSessionId(sessionId),
40    ]);
41    return [databaseSession, databaseUser];
42  }
43
44  public async getUserSessions(userId: string): Promise<DatabaseSession[]> {
45    const result = await this.controller.getAll<SessionSchema>(
46      `SELECT * FROM ${this.escapedSessionTableName} WHERE user_id = ?`,
48    );
49    return result.map((val) => {
50      return transformIntoDatabaseSession(val);
51    });
52  }
53
54  public async setSession(databaseSession: DatabaseSession): Promise<void> {
55    const value: SessionSchema = {
56      id: databaseSession.id,
57      user_id: databaseSession.userId,
58      expires_at: Math.floor(databaseSession.expiresAt.getTime() / 1000),
59      ...databaseSession.attributes,
60    };
61    const entries = Object.entries(value).filter(([_, v]) => v !== undefined);
87  }
88
89  private async getSession(sessionId: string): Promise<DatabaseSession | null> {
90    const result = await this.controller.get<SessionSchema>(
91      `SELECT * FROM ${this.escapedSessionTableName} WHERE id = ?`,
93    );
94    if (!result) return null;
95    return transformIntoDatabaseSession(result);
96  }
97
98  private async getUserFromSessionId(sessionId: string): Promise<DatabaseUser | null> {
99    const result = await this.controller.get<UserSchema>(
100      `SELECT ${this.escapedUserTableName}.* FROM ${this.escapedSessionTableName} INNER JOIN ${this.escapedUserTableName} ON ${this.escapedUserTableName}.id = ${this.escapedSessionTableName}.user_id WHERE ${this.escapedSessionTableName}.id = ?`,
102    );
103    if (!result) return null;
104    return transformIntoDatabaseUser(result);
105  }
106}
117}
118
119interface SessionSchema extends RegisteredDatabaseSessionAttributes {
120  id: string;
121  user_id: string;
123}
124
125interface UserSchema extends RegisteredDatabaseUserAttributes {
126  id: string;
127}
128
129function transformIntoDatabaseSession(raw: SessionSchema): DatabaseSession {
130  const { id, user_id: userId, expires_at: expiresAtUnix, ...attributes } = raw;
131  return {
137}
138
139function transformIntoDatabaseUser(raw: UserSchema): DatabaseUser {
140  const { id, ...attributes } = raw;
141  return {

1# listSqliteTables
2
3Returns a flat array of the names of all tables in your Val Town SQLite database.
4
5```ts