Database

What you'll have afterwards:

• You know when to generate a migration and which files to commit.
• You can add a column of your own to a table a module owns.
• You know what changes when you move from SQLite to PostgreSQL.

Modules bring their own tables, such as users or forms. Your app's database only gets them through a migration you generate and commit. This page explains that step and the database behind it.

Prerequisites

A project created in Installation.

Where the database lives

The starter uses the database of NuxtHub, the module that gives the host app its database and blob storage. Modules define their tables with Drizzle, a TypeScript object-relational mapper (ORM).

Locally the database is a SQLite file, apps/web/.data/db/sqlite.db, opened through the libsql driver. It is set in nuxt.config.ts:

export default defineNuxtConfig({
  hub: {
    db: {
      dialect: 'sqlite',
      driver: 'libsql',
      connection: { url: 'file:./.data/db/sqlite.db' },
    },
  },
})

NuxtHub applies the committed migrations in apps/web/server/db/migrations when you run nuxt dev and nuxt build.

Migrations

A module registers its tables, but that only changes the generated Drizzle schema. The database itself does not change.

Generate and commit a migration whenever a module adds or changes columns:

• after you add a module,
• after you upgrade one,
• after you extend a table yourself.

pnpm db:generate   # generate migration files from schema changes
pnpm db:migrate    # apply migrations to the development database

pnpm db:generate prints No schema changes, nothing to migrate when the database is already up to date.

Add columns to a module's table

A host app adds columns to a table it does not own from its own server/db/extend.<dialect>.ts. Export one table or columns object per table, under the table's name:

import { text } from 'drizzle-orm/sqlite-core'

export const users = { locale: text().default('da') }
export const posts = { summary: text() }

Only the export that matches the table being built is used. The file is merged after every module.

Then run pnpm db:generate and commit the migration. Database extensions explains the details.

SQLite and PostgreSQL

The database type a schema is written for is its dialect: SQLite or PostgreSQL. Every module ships its schema for both. So you can run SQLite or libSQL/Turso locally, and Cloudflare D1, libSQL/Turso or PostgreSQL in production.

The committed migrations are per dialect. The starter keeps them in server/db/migrations/sqlite.

If you extend a table, keep a .postgresql.ts twin next to each .sqlite.ts file:

• It has the same table, column and index names.
• You change both files in the same commit.
• It uses pgTable from drizzle-orm/pg-core and the matching column types.

The full table is in Database extensions.

Write runtime queries with Drizzle so they work on both dialects. Avoid db.run, db.batch, PRAGMA and other SQLite-only SQL.

Next steps

• Deployment – take the app and its database to production.
• Module Kit database – register tables of your own when you write a module.