Database extensions

Your app needs one more column on a table a module owns, such as a locale on users. A module may need one too, such as the role that Authorization adds to users. Neither can edit the owner's schema. After this page you can decide where to declare the column, how to keep it working on SQLite and PostgreSQL, and which migration to commit.

Which one do I use?

Terms

• Owning module. The module that registers a table and ships its base schema file.
• Extension file. A file that adds columns to a table it does not own, exported under the table's name.
• Migration. A generated SQL (Structured Query Language) file that changes the database schema. You commit one whenever a module adds or changes tables.
• Dialect. The database type a schema is written for: SQLite or PostgreSQL.
• Drizzle. The TypeScript object-relational mapper (ORM) that modules use to define tables and generate migrations.

How it works

Module Kit lets one module own a Drizzle table while others extend it, without the owner knowing:

• The owning module registers a plain Drizzle schema file with registerDatabaseTable.
• Other modules add columns with extendDatabaseTable.
• The host app adds columns from its own server/db/extend.<dialect>.ts.

All three work on the generated NuxtHub schema. They change the schema, not the database.

Registering a table

The owning module ships a schema file and registers it. Omit name: every table export of the file is registered.

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

Pass name only to restrict which tables are registered. Auth does this, because which of its tables exist depends on the enabled features.

Extending a table

An extension file exports a table definition, or a columns object, under the table's name. It is merged into every registered table it has a matching export for, and only those. When the owner is not installed, nothing matches and the call has no effect.

From a module:

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

// src/runtime/db/users.sqlite.ts
import { sqliteTable, text } from 'drizzle-orm/sqlite-core'

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

From the host app, one file can extend several tables. Only the export that matches the table being built is used:

// server/db/extend.sqlite.ts
import { text } from 'drizzle-orm/sqlite-core'
import { defineTableColumns } from '@frigear-nu/module-kit/utils'

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

Columns are merged in this order. A column that appears again replaces the earlier one.

1. The base schema file of the owning module.
2. Files registered with extendDatabaseTable, in registration order.
3. For each Nuxt layer, from the layers the app extends towards the host app itself: the shared server/db/extend.<dialect>.ts.

The host app generates and commits its own migration for what it adds.

One schema file per dialect

NuxtHub resolves schema files for the host app's dialect, sqlite or postgresql. Every schema and extension file (schema.sqlite.ts, users.sqlite.ts, server/db/extend.sqlite.ts, ...) ships with a .postgresql.ts twin next to it. The twin exports the same tables with the same table, column and index names.

Runtime code must work on both dialects. Do not use db.run, db.batch, PRAGMA, rowid or SQLite-only SQL without a PostgreSQL branch.

A test keeps the twins in sync. Every package that owns a schema has test/schema-parity.test.ts:

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

expectSchemaParity compares tables, columns, indexes and foreign keys. For columns it checks the Drizzle data type, not null, primary key, unique, defaults, $defaultFn and $onUpdateFn.

• Add a case when you add a schema file.
• A Module Kit test fails when a .sqlite.ts schema has no .postgresql.ts twin.

Migrations are committed history

Extensions only change the generated schema. So every app with a database needs a migration for them, including every test fixture.

• Generate migrations from the app's complete module configuration, including every extending module.
• Commit the SQL, the Drizzle snapshot and the migration journal together. They are versioned schema history, not build artifacts.
• Keep existing history intact.

After changing a schema or an extension, check each test fixture that has committed migrations. Run this from the repository root, with an absolute --cwd:

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

If it creates a migration, the test fixture needs one. Delete any stray migration you do not intend to commit.

A module that adds routes using requireAuth or requireUser needs the auth_user_tokens migration in every test fixture that includes it.

Common mistakes

Next steps

• Database – set up the database and generate your first migration in an app.
• Database tables – add a table to a module step by step.
• Testing – boot database-backed test fixtures.