Val Town Code SearchReturn to Val Town

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

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# SQLite Dump Util
2
3A utility function that generates SQL statements to dump the data and schema of tables in a SQLite database.
4
5## Usage