Database Example

SQLite with Drizzle ORM: a typed schema, SQL migrations, server-only queries, and a page that reads and writes rows.

npm create giojs@latest my-app -- --db   # a new app
npx create-giojs add db                    # an existing app

pnpm create giojs my-app --db            # a new app
pnpm dlx create-giojs add db               # an existing app

yarn create giojs my-app --db            # a new app
yarn dlx create-giojs add db               # an existing app

bun create giojs my-app --db             # a new app
bunx create-giojs add db                   # an existing app

Run npm run dev and open /notes: the rows come from data/app.db, created and migrated on first use.

Why node:sqlite

The driver is Node's built-in node:sqlite, so there is nothing to compile or download. The native drivers (better-sqlite3, @libsql/client) ship prebuilt binaries for common platforms, but a native addon cannot be bundled into the single worker.js that gio build standalone produces, so they would not survive a standalone or Docker deploy. node:sqlite needs Node 22.16 or newer (the feature sets engines); Node still labels it experimental and prints one warning at startup. Drizzle talks to it through its sqlite-proxy driver.

The schema

export const notes = sqliteTable('notes', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
});

Queries

lib/db.server.ts opens the database, applies pending migrations, and exports db plus the queries the page uses. The .server name keeps it out of client bundles; use it from getServerSideProps, actions and route handlers.

export async function listNotes(): Promise<NoteItem[]> {
  const rows = await db.select().from(schema.notes).orderBy(desc(schema.notes.id)).limit(50);
  return rows.map(note => ({ id: note.id, title: note.title, createdAt: note.createdAt.toISOString() }));
}

export async function createNote(title: string): Promise<void> {
  await db.insert(schema.notes).values({ title });
}

Page props travel to the browser as JSON, so dates become ISO strings. Each worker has one connection: db.transaction() works, but other requests' queries can run inside it while its callback awaits, so keep transactions short.

The page

export const getServerSideProps: GetServerSideProps<Props> = async () => ({
  props: { notes: await listNotes() },
});

export async function action(req: ActionArgs) {
  const value = (await req.formData()).get('title');
  const title = typeof value === 'string' ? value.trim() : '';
  if (title === '' || title.length > MAX_TITLE) {
    return { status: 422, data: { error: 'Write something first.', title } };
  }
  await createNote(title);
  return redirect('/notes');
}

Changing the schema

npm run db:generate   # drizzle-kit writes drizzle/0002_<name>.sql
npm run db:migrate    # optional: apply now, without starting the server

pnpm db:generate      # drizzle-kit writes drizzle/0002_<name>.sql
pnpm db:migrate       # optional: apply now, without starting the server

yarn db:generate      # drizzle-kit writes drizzle/0002_<name>.sql
yarn db:migrate       # optional: apply now, without starting the server

bun run db:generate   # drizzle-kit writes drizzle/0002_<name>.sql
bun run db:migrate    # optional: apply now, without starting the server

Migrations live in drizzle/ (commit them). The server applies pending ones when it first opens the database, inside one BEGIN IMMEDIATE transaction, so several workers starting together wait for each other and a failing migration changes nothing. Applied migrations are recorded in Drizzle's own __drizzle_migrations table. Seed data is a migration too (drizzle/0001_seed.sql), so it runs once per database; write one with npx drizzle-kit generate --custom.

Where the data lives

• data/app.db by default; DATABASE_PATH overrides it. data/ is git-ignored.
• gio.toml lists it in [dev] watch_ignore, so writes never restart the dev server:

[dev]
watch_ignore = ["data/**"]

In a standalone deploy the paths are relative to the deploy folder (its run.mjs starts the server there): copy drizzle/ next to it and keep data/ on persistent storage. The Docker feature does both.