179```
180โโโ backend/
181โ โโโ database/
182โ โ โโโ migrations.ts # Schema definitions
183โ โ โโโ queries.ts # DB query functions
239 ```
240
241### Database Patterns
242- Run migrations on startup or comment out for performance
243- Change table names when modifying schemas rather than altering
2import { Client } from "npm:@notionhq/client";
3import { blobKeyForDemoCache } from "../../shared/utils/blobKeyForDemoCache.ts";
4import { getRelatedPagesFromDatabase } from "../controllers/relatedPages.controller.ts";
5
6// Initialize Notion client
11export default async function (interval: Interval) {
12 // this cron runs every minute
13 // it saves a blob for every page in the "Glancer demos" database
14 // that holds the Notion data and markup needed for every demo
15 // this way, to build a demo in the browser (ie., when /demo/:id is called),
18 const twoDaysAgo = new Date(Date.now() - 48 * 60 * 60 * 1000).toISOString();
19 try {
20 const pages = await notion.databases.query({
21 database_id: Deno.env.get("GLANCE_DEMOS_DB_ID"),
22 filter: {
23 // only get recently edited demos; we don't need to keep stale demos in the cache
29 });
30 console.log("pages to cache: ", pages.results.length);
31 // for each page in the demo database, save a blob
32 for (const page of pages.results) {
33 // remove properties that are not used in the demo and don't need to be saved to the blob
48 page.properties = scrubbed;
49
50 // get "Glancer content" database pages added to the demo page
51 const relatedPages = await getRelatedPagesFromDatabase(page.id);
52
53 // use the same blob key that /views/demo/demo.ts calls to build the page
7});
8
9export async function getRelatedPagesFromDatabase(pageId: string) {
10 try {
11 const response = await notion.databases.query({
12 database_id: Deno.env.get("GLANCE_CONTENT_DB_ID"),
13 filter: {
14 property: "Glancer demos", // the property in the Glancer content database that connects to the /demo
15 relation: {
16 contains: pageId, // the Glancer demos property holds the /demo page id to which it is related
25 });
26 // loop through the response and attach a "page_contents" object to each page
27 // page_content is the markdown in Notion in each "Glancer content" database page
28 for (const page of response.results) {
29 page.page_content = await getPageContents(page.id);
189```
190โโโ backend/
191โ โโโ database/
192โ โ โโโ migrations.ts # Schema definitions
193โ โ โโโ queries.ts # DB query functions
248 ```
249
250### Database Patterns
251- Run migrations on startup or comment out for performance
252- Change table names when modifying schemas rather than altering
315
316### SQL Operations
317- **execute-sql** / **sqlite-query**: Execute a SQL query against a SQLite database
318- **execute-sql-batch** / **sqlite-exec**: Execute multiple SQL statements against a SQLite database
319
320## Creating Val Town Projects
97```
98โโโ backend/
99โ โโโ database/
100โ โ โโโ migrations.ts
101โ โ โโโ queries.ts
229#### SQL Operations
23021. **execute-sql** / **sqlite-query**
231 - Description: Execute a SQL query against a SQLite database
232 - Arguments: `statement` (string)
233 - Returns: Query results
234
23522. **execute-sql-batch** / **sqlite-exec**
236 - Description: Execute multiple SQL statements against a SQLite database
237 - Arguments: `statements` (array of strings), Optional: `mode` (string: "read" or "write", default: "read")
238 - Returns: Results of the batch execution
9 server.tool(
10 "execute-sql",
11 "Execute a SQL query against a SQLite database",
12 {
13 statement: z.string().describe("SQL statement to execute"),
36 server.tool(
37 "execute-sql-batch",
38 "Execute multiple SQL statements against a SQLite database",
39 {
40 statements: z.array(z.string()).describe("Array of SQL statements to execute"),
67 server.tool(
68 "sqlite-query",
69 "Execute a SQL query against a SQLite database",
70 {
71 statement: z.string().describe("SQL statement to execute"),
94 server.tool(
95 "sqlite-exec",
96 "Execute multiple SQL statements against a SQLite database",
97 {
98 statements: z.array(z.string()).describe("Array of SQL statements to execute"),
172```
173โโโ backend/
174โ โโโ database/
175โ โ โโโ migrations.ts # Schema definitions
176โ โ โโโ queries.ts # DB query functions
234- Handle API calls properly with proper error catching
235
236### Database Patterns
237- Run migrations on startup or comment out for performance
238- Change table names when modifying schemas rather than altering
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
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).
5} from "https://esm.town/v/std/utils/index.ts";
6import { Hono } from "npm:hono";
7import { getMessages, insertMessage } from "./database/queries.ts";
8
9const app = new Hono();