Database tables

This page is for module authors whose module stores data. It walks through adding a table, checking that its two dialects match, adding columns to another module's table and committing the migrations your test fixtures need. The rules behind each step are in Module Kit database helpers.

Add a table

Write the schema twice, as plain Drizzle tables: once for SQLite and once for PostgreSQL. Both files export the same tables with the same table, column and index names.

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

export const things = sqliteTable('things', {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
})

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

export const things = pgTable('things', {
  id: serial().primaryKey(),
  name: text().notNull(),
})

Register the files with registerDatabaseTable, as in the Setup checklist. Omit name, so every exported table is registered.

Check that the dialects match

Every package that owns a schema has a test/schema-parity.test.ts. Add a case for each schema file you add. expectSchemaParity compares tables, columns, indexes and foreign keys:

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))

A Module Kit test also fails when a .sqlite.ts schema has no .postgresql.ts twin.

Add columns to another module's table

To add columns to a table another module owns, such as users, export a table of the same name with only your columns. The columns arrive in every host app that installs both modules. Nothing happens when the owner is absent.

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

export const users = sqliteTable('users', {
  nickname: text(),
})

Ship a users.postgresql.ts too, and register both in setup():

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

extendDatabaseTable changes only the generated schema. Say on your module's page that host apps must generate and commit a migration. Database extensions explains the model.

Generate test fixture migrations

A test fixture that touches the database needs committed migrations for its complete module configuration, including every module that extends its tables. Prepare the test fixture first, so .nuxt/hub/db/schema.mjs includes every registered extension. Then generate, from the repository root with an absolute path:

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

Commit the SQL, the Drizzle snapshot and the migration journal together.

Next steps

• Testing – run the suite against those test fixtures.
• Database extensions – how columns from several modules merge into one table.