Module Kit database helpers

Your module needs a table of its own, or one more column on a table another module owns. It must run on SQLite and PostgreSQL, and its test fixtures need committed migrations. This page is the full reference for module authors. Set up the module first: Overview.

App developers start at Database. Database extensions explains why tables are extended this way.

Helpers

These helpers work on the schema that NuxtHub generates, not on the database.

The calls only register hooks. NuxtHub does the work when it builds the schema, so there is nothing to await. The option and callback types are exported from @frigear-nu/module-kit/utils: DatabaseTableOptions, TableExtensionOptions, DbExtenderFn and TableExtenderFn.

Registering a table

The owning module ships a plain Drizzle schema file and registers it:

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

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  email: text().notNull().unique(),
})

export const userPasskeys = sqliteTable('user_passkeys', {
  id: integer().primaryKey({ autoIncrement: true }),
  userId: integer().notNull().references(() => users.id, { onDelete: 'cascade' }),
})

import { createResolver } from '@nuxt/kit'
import { registerDatabaseTable } from '@frigear-nu/module-kit/utils'

// in setup()
const resolver = createResolver(import.meta.url)

registerDatabaseTable(nuxt, {
  resolver,
  schema: dialect => `./runtime/server/db/schema.${dialect}`,
})

Every export initialised by a table call (sqliteTable(...), pgTable(...)) is registered. Other exports are ignored. All tables of the file are generated into one file, so they can reference each other, as the foreign key above does. The base file decides the table function and the SQL table name of the merged result.

registerDatabaseTable options

name is for restricting only. Auth passes it because which of its tables exist depends on the enabled features:

const tables = ['authUserTokens']
if (withWebAuthn) tables.push('userCredentials')
if (withPasswordReset) tables.push('passwordResets')
if (withMagicLink || withVerifyEmail) tables.push('magicLinks')

registerDatabaseTable(nuxt, {
  name: tables,
  resolver,
  schema: dialect => `./runtime/db/schema.${dialect}`,
})

A name that the file does not export throws. So does a name whose export is not a table call.

One schema file per dialect

NuxtHub resolves schema and file for the host app's dialect, sqlite or postgresql. So every schema and extension file ships twice, as .sqlite.ts and .postgresql.ts. Both export the same tables, with the same table, column and index names.

import { pgTable, text, serial, integer } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial().primaryKey(),
  email: text().notNull().unique(),
})

export const userPasskeys = pgTable('user_passkeys', {
  id: serial().primaryKey(),
  userId: integer().notNull().references(() => users.id, { onDelete: 'cascade' }),
})

Map the types like this, so the Drizzle data types match:

A Module Kit test fails when a .sqlite.ts schema has no .postgresql.ts twin. Keep the two files in step with a test in test/schema-parity.test.ts. expectSchemaParity from @frigear-nu/module-kit/testing throws and lists every difference. It compares tables, columns (Drizzle data type, not null, primary key, unique, defaults, $defaultFn, $onUpdateFn), indexes and foreign keys. compareSchemaDialects returns the same list instead of throwing.

import { it } from 'vitest'
import { expectSchemaParity } from '@frigear-nu/module-kit/testing'
import * as sqlite from '../src/runtime/server/db/schema.sqlite'
import * as postgresql from '../src/runtime/server/db/schema.postgresql'

it('schema.postgresql matches schema.sqlite', () => {
  expectSchemaParity(sqlite, postgresql)
})

Extending a table

Another module adds columns to a table it does not own with extendDatabaseTable. The column file exports, under the table's name, a table call, a plain columns object or a defineTableColumns call:

// in setup()
extendDatabaseTable(nuxt, {
  resolver,
  file: dialect => `./runtime/db/users.${dialect}`,
})

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

export const users = sqliteTable('users', {
  locale: text().default('da'),
})

import { pgTable, text } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  locale: text().default('da'),
})

The file is merged only into registered tables it has a matching export for. Other tables are untouched, and so are other exports of the file. So you never need to name the table. When the owning module is not installed, nothing matches and the call does nothing.

Merge order

Columns are merged in this order. A column that appears again replaces the earlier one and moves to the end of the table.

1. The base schema file of the owning module.
2. Files registered with extendDatabaseTable, in registration order.
3. For each Nuxt layer, from the base layers towards the host app: the legacy server/db/<name>.extend.<dialect> file, then the shared server/db/extend.<dialect>.ts. One shared file can extend several tables:
   server/db/extend.sqlite.ts

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

Legacy <name>.extend.<dialect> files with a default export still work. Passing extensionFile replaces the shared file location and turns off the legacy lookup.

Constraints of schema files

• Only top-level exports initialised by a table call are registered. In an extension file, a plain columns object or a single-argument wrapper such as defineTableColumns({ ... }) works too.
• Named imports are carried over and de-duplicated, and a value import wins over type X. Relative paths such as '../types' become absolute. Default and namespace imports are not carried over.
• Top-level helpers, constants and type aliases are not carried over. Declare what a column needs inline, or import it by name. packages/events/src/runtime/server/db/schema.sqlite.ts writes a customType inline this way.
• Indexes and constraints (the third argument of the table call) of the base file are kept. In an extension file they are dropped with a warning, and so is a SQL table name that differs from the base. Put indexes on extension columns in the base file or in a migration.

Hooks

Module Kit uses two build-time hooks. Hook them yourself only to add files by hand.

Generated files

Merged tables are written to .nuxt/module-kit/db/<kebab-case table names>.<dialect>.ts, such as .nuxt/module-kit/db/users-user-passkeys.sqlite.ts. A name over 80 characters is shortened and gets a hash suffix. NuxtHub builds the schema from exactly these files, so open them when a migration looks wrong.

NuxtHub bundles them into .nuxt/hub/db/schema.mjs on every boot and every nuxt prepare. It caches the generated types in .nuxt/cache/nuxthub/db/ and reuses them while the inputs are unchanged, so the bundle takes milliseconds after the first boot. Delete that folder to force a full rebuild.

Migrations and database-backed module tests

A migration is what changes the database. Every app that installs your module needs one, including each test fixture with committed migrations. Fixtures that have them are listed by ls packages/*/test/fixtures/*/server/db/migrations.

Checking and updating a fixture's migrations

Run these steps from the repository root after changing a schema or an extension, for every fixture with committed migrations.

pnpm --filter @frigear-nu/<package> exec nuxt db generate --cwd "$PWD/packages/<package>/test/fixtures/<fixture>"

pnpm --filter @frigear-nu/<package> exec nuxt prepare "$PWD/packages/<package>/test/fixtures/<fixture>"
pnpm --filter @frigear-nu/<package> exec drizzle-kit generate \
  --dialect sqlite \
  --schema "$PWD/packages/<package>/test/fixtures/<fixture>/.nuxt/hub/db/schema.mjs" \
  --out test/fixtures/<fixture>/server/db/migrations/sqlite \
  --name <migration-name>
pnpm --filter @frigear-nu/<package> test

Database-backed fixture requirements

• Configure a test-only session password and a local SQLite or libsql database.
• A fixture that includes a module using requireAuth or requireUser needs the auth_user_tokens migration. The Auth fixtures show it.
• Use dev: true in the @nuxt/test-utils/e2e setup, so NuxtHub applies the fixture's committed migrations at startup. Turning off build-time migrations does not turn these off.
• Remove only the fixture's own .data folder before the suite, and clean it up afterwards.
• Assert a request's HTTP status, with the response body as the message, before its side effects, such as a session cookie. The underlying HTTP or database error then shows.
• Run the suite twice, to check that migrations apply and that runs do not share data. Users and Events have working fixture lifecycles.

Next steps

• Database tables – add a table to your module step by step.
• Testing – boot the database-backed test fixture.
• Module Kit server toolkit – serve the table from your routes.