1import type { LibSQLDatabase } from "../deps/drizzle.ts";
2import type * as schema from "./schema";
3
4export type DBType = LibSQLDatabase<typeof schema>;
13## A Few Honkin' Notes
14
15### database migrations need love and care 🫂
16
17Not totally sure how to generate and apply drizzle migrations yet!
5 * For this reason, we need to manually create the users table.
6 */
7export const migrateDatabase = async () => {
8 await sqlite.execute(`
9CREATE TABLE IF NOT EXISTS \`users\` (
6import usersApi from "./app/users.ts";
7import { db } from "./db/client.ts";
8import { migrateDatabase } from "./db/migrate.ts";
9
10// Migrate database on startup to make sure the proper table exists to store users
11// NOTE - Please comment on the template project if you have ideas on how to do migrations more cleanly on Valtown
12await migrateDatabase();
13
14const app = new Hono<AppType>();
15
16/**
17 * Middleware for setting up the database and storing it on the Hono app's context
18 */
19app.use(async (c, next) => {
1// Import drizzle deps with npm: specifier because exports don't get picked up properly by typescript when using esm.sh
2import { drizzle, type LibSQLDatabase } from "npm:drizzle-orm@0.39.3/libsql";
3import { and, eq, desc, relations, sql } from "npm:drizzle-orm@0.39.3";
4import { integer, sqliteTable, text } from "npm:drizzle-orm@0.39.3/sqlite-core";
5
6export { drizzle };
7export type { LibSQLDatabase };
8export { and, eq, desc, relations, sql };
9export { integer, sqliteTable, text };
5
6/**
7 * The typed database client, allowing us to construct sql queries and
8 * use Drizzle's ORM features against the ValTown sqlite database
9 */
10export const db: DBType = drizzle(sqlite as any, {
9- The **client-side entrypoint** is `/frontend/index.html`, which in turn imports `/frontend/index.tsx`, which in turn imports the React app from `/frontend/components/App.tsx`.
10
11[React Hono Example](https://www.val.town/x/stevekrouse/reactHonoExample) is a fuller featured example project, with a SQLite database table, queries, client-side CSS and a favicon, and some shared code that runs on both client and server.
1import { sqlite } from "https://esm.town/v/std/sqlite";
2
3// Initialize the database schema if not exists
4async function initializeDatabase() {
5 try {
6 // Create the table with explicit column types
32 }
33 } catch (error) {
34 console.error("Database initialization error:", error);
35 }
36}
100// Main request handler
101export default async function(req: Request): Promise<Response> {
102 // Initialize database on every request (quick operation if already created)
103 await initializeDatabase();
104
105 const url = new URL(req.url);
140 }
141
142 // Insert the new speed into the database
143 await sqlite.execute({
144 sql: `INSERT INTO train_speeds (speed) VALUES (:speed)`,
515
516 try {
517 // Initialize database for game storage
518 const db = await sqlite(`./games-db-v${SCHEMA_VERSION}.sqlite`);
519
1518 };
1519
1520 // Save to database
1521 await db.execute(
1522 `INSERT INTO games (id, name, description, code, version, created, updated)
1544 };
1545
1546 // Save to database
1547 await db.execute(
1548 `UPDATE games
1617 }
1618 } catch (error) {
1619 return new Response(JSON.stringify({ error: "Database error" }), {
1620 status: 500,
1621 headers: { "Content-Type": "application/json" }
1634 });
1635 } catch (error) {
1636 return new Response(JSON.stringify({ error: "Database error" }), {
1637 status: 500,
1638 headers: { "Content-Type": "application/json" }
4
5* `index.ts` - this is the **entrypoint** for this whole project
6* `database/` - this contains the code for interfacing with the app's SQLite database table
7
8## Hono
26## CRUD API Routes
27
28This app has two CRUD API routes: for reading and inserting into the messages table. They both speak JSON, which is standard. They import their functions from `/backend/database/queries.ts`. These routes are called from the React app to refresh and update data.
29
30## Errors