Database

Define the tableCopy Anchor Link

Create src/database/notes.ts:

import { camelCase } from "drizzle-orm/pg-core";

export const site_notes = camelCase.table.withRLS("site_notes", t => ({
  id: t.serial().primaryKey(),
  title: t.varchar({ length: 200 }).notNull(),
  body: t.text().notNull(),
  createdAt: t.timestamp().notNull().defaultNow(),
}));

• Every table is a named export of a file in src/database/. The migration generator reads those exports from the plugin's build output; a table that is not exported there never reaches the database.
• Prefix the table name with the plugin, as site_notes does, so two plugins never claim the same table.
• camelCase.table keeps the TypeScript and column names identical (createdAt). withRLS turns on row-level security, as every core table does.

Generate and apply the migrationCopy Anchor Link

pnpm dev already rebuilt the plugin when you saved. From the workspace root, create the migration and apply it:

bun run db:migrate

db:migrate compares your tables with the last migration, writes a new folder such as migrations/20261024183641_little_the_phantom/ in the app that owns the database (apps/web in a Single App, apps/api in a Monorepo App), applies it and seeds core data. Commit that folder, because production applies the same files. Without pnpm dev running, run pnpm build:plugins first so the generator sees the new table.

Add demo notesCopy Anchor Link

There is no write endpoint yet, so add three notes with SQL. With the Docker database from Setup, run this from the workspace root (the my-vitnode folder):

docker compose -p my-vitnode-vitnode-dev exec -T database psql -U root -d vitnode -c "INSERT INTO site_notes (title, body) VALUES ('Welcome to site notes', 'Short updates for everyone on the site.'), ('Office closed on Friday', 'We are back on Monday at 9:00.'), ('New coffee machine', 'It is in the kitchen. Please descale it on Fridays.');"

It answers INSERT 0 3. database is the PostgreSQL service in the generated docker-compose.yml, and -p my-vitnode-vitnode-dev is the Compose project that docker:dev started it in. If you named your project something else, use the -p value from the docker:dev script in your root package.json.

The notes get the ids 1, 2 and 3 in that order, which later lessons use. With your own PostgreSQL, run the same INSERT in any client connected to POSTGRES_URL. The Permissions lesson adds the endpoint that creates notes properly.

Read the table in the list routeCopy Anchor Link

Replace the handler of src/api/modules/notes/list.route.ts, and its imports. Select only the columns the response schema declares:

import { z } from "@hono/zod-openapi";
import { desc } from "drizzle-orm"; 

import { buildRoute } from "@/api/builders";
import { site_notes } from "@/database/notes"; 

import { zodNote } from "./notes.schema";
import { sampleNotes } from "./sample-notes"; 

export const listNotesRoute = buildRoute({
  route: {
    method: "get",
    path: "/",
    responses: {
      200: {
        content: { "application/json": { schema: z.array(zodNote) } },
        description: "Every note, newest first.",
      },
    },
  },
  handler: c => c.json(sampleNotes, 200), 
  handler: async c => {
    const notes = await c
      .get("db")
      .select({ id: site_notes.id, title: site_notes.title, body: site_notes.body })
      .from(site_notes)
      .orderBy(desc(site_notes.createdAt), desc(site_notes.id));

    return c.json(notes, 200);
  },
});

Read the table in the detail routeCopy Anchor Link

Replace the handler of src/api/modules/notes/show.route.ts the same way. The params and responses stay as they are:

import { eq } from "drizzle-orm"; 

import { site_notes } from "@/database/notes"; 
import { sampleNotes } from "./sample-notes"; 

  handler: async c => {
    const { id } = c.req.valid("param");
    const [note] = await c
      .get("db")
      .select({ id: site_notes.id, title: site_notes.title, body: site_notes.body })
      .from(site_notes)
      .where(eq(site_notes.id, id));
    if (!note) return c.body(null, 404);

    return c.json(note, 200);
  },

Then delete src/api/modules/notes/sample-notes.ts; nothing imports it any more.