# Change the schema without losing the data

> Generate a reviewable migration, apply it and check that models, files and the database agree.

- Package: `@db3.ai/app/db/migrations`
- Canonical page: [https://db3.ai/docs/migrations](https://db3.ai/docs/migrations)
- Markdown: [https://db3.ai/docs/migrations.md](https://db3.ai/docs/migrations.md)
- Framework source of truth: `packages/app/src/db/README.md`

<a id="setup"></a>

## Use a disposable MariaDB database

Complete Installation and its local MariaDB/test-account setup, then copy the database examples. Set the test-only DB_* values in `.env`; unset DATABASE_URL if it points somewhere else. The account must create/drop generated test databases.

Generated model migrations currently support MariaDB only. The runner creates a temporary directory and database and removes both in `finally`. It never applies changes to an existing app.

- [Install and configure MariaDB](https://db3.ai/docs/installation.md)

### Copy the shipped example

```bash
mkdir -p examples
cp -R node_modules/@db3.ai/app/src/db/examples/. examples/
```

<a id="run"></a>

## Generate, fail safely and repair

Expect initialGenerated, unsafeBlocked, blockedPreservedSource, repairedGenerated, matches, titlePreserved and categoryNullable all true. pendingBefore and applied are both 1.

The example starts with one saved note. Adding a required category without a default is blocked. Changing the proposal to nullable generates a safe migration, applies it and preserves the note.

### Run the lab

```bash
npx tsx examples/runNoteMigrations.ts
```

<a id="manager"></a>

## One manager owns the workflow

`DatabaseMigrationManager` takes the complete app model registry, an app database connection, MariaDB dialect, absolute migration/snapshot paths and optional ledger policy. This is infrastructure configuration, not an optional db parameter threaded through feature services.

The three classes in the lab represent successive versions of the same source model. Never register all three together. Your app changes one model in source and uses the same configured manager from its CLI.

### examples/runNoteMigrations.ts

```typescript
import { mkdtemp, readFile, readdir, rm } from 'node:fs/promises';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { pathToFileURL } from 'node:url';
import { ActiveRecord, DatabaseMigrationManager, mariaDbDialect, type FieldBuilder } from '@db3.ai/app/db';
import { createGeneratedTestDatabase } from '@db3.ai/app/db/test/db';

/** Initial version of a disposable note table, before a schema change. */
class InitialNote extends ActiveRecord {
	static override table = 'migration_guide_notes';
	/** Defines the initial committed schema. */
	static override fields(field: FieldBuilder) { return { id: field.ulid(), title: field.string({ required: true }) }; }
}

/** Unsafe proposal: existing rows have no value for this required column. */
class RequiredCategoryNote extends InitialNote {
	/** Adds a required field without pretending existing data has a value. */
	static override fields(field: FieldBuilder) { return { ...super.fields(field), category: field.string({ required: true }) }; }
}

/** Repaired additive change that can be deployed before backfilling existing rows. */
class OptionalCategoryNote extends InitialNote {
	/** Adds a nullable field safe for the existing note. */
	static override fields(field: FieldBuilder) { return { ...super.fields(field), category: field.string() }; }
}

/**
 * Generates, applies and checks migrations against an owned disposable database.
 *
 * The three model classes represent successive source revisions, not models an
 * application should register together. Production apps commit reviewed files;
 * this lab discards only its temporary migration directory and test database.
 *
 * @returns Generation, blocked-change, recovery and persisted-data outcomes.
 */
export async function runNoteMigrations() {
	const database = await createGeneratedTestDatabase('migrations_guide');
	const directory = await mkdtemp(join(tmpdir(), 'db3-migrations-guide-'));
	const snapshotFile = join(directory, 'schema.snapshot.json');
	const migrationsDirectory = join(directory, 'migrations');
	/** Creates the manager for one simulated model-source revision. */
	const managerFor = (Model: typeof InitialNote, second: number) => new DatabaseMigrationManager({ db: database.db, models: [Model], dialect: mariaDbDialect, migrationsDirectory, snapshotFile, environment: 'test', now: () => new Date(Date.UTC(2026, 8, 5, 12, 0, second)) });
	try {
		const initial = managerFor(InitialNote, 0);
		const first = await initial.makeMigration({ name: 'initial_notes' });
		await initial.migrate();
		await ActiveRecord.withDb(database.db, () => InitialNote.create({ title: 'Keep this note' }).save());
		const snapshotBefore = await readFile(snapshotFile, 'utf8');
		const filesBefore = await readdir(migrationsDirectory);
		const unsafe = await managerFor(RequiredCategoryNote, 1).makeMigration({ name: 'required_category' });
		const blockedPreservedSource = snapshotBefore === await readFile(snapshotFile, 'utf8') && filesBefore.length === (await readdir(migrationsDirectory)).length;
		const repaired = managerFor(OptionalCategoryNote, 2);
		const second = await repaired.makeMigration({ name: 'optional_category' });
		const pendingBefore = (await repaired.status()).migrations.pending.length;
		const applied = await repaired.migrate();
		const checked = await repaired.check();
		const row = await ActiveRecord.withDb(database.db, () => OptionalCategoryNote.query().firstOrFail());
		return { initialGenerated: first.generated, unsafeBlocked: unsafe.blocked, blockedPreservedSource, repairedGenerated: second.generated, pendingBefore, applied: applied.applied.length, matches: checked.matches, titlePreserved: row.get('title') === 'Keep this note', categoryNullable: row.get('category') === null };
	} finally {
		try { await database.destroy(); } finally { await rm(directory, { recursive: true, force: true }); }
	}
}

if (process.argv[1] && import.meta.url === pathToFileURL(process.argv[1]).href) console.log(JSON.stringify(await runNoteMigrations(), null, 2));
```

<a id="commands"></a>

## Use the generated app commands

In the Notes starter, edit the model, then run `npm run db:make:migration -- add_note_category`. Review the generated file and snapshot before applying anything. Run `npm run db:migrate`, then `npm run db:check`.

Generation compares models to the committed snapshot, not your memory of the live database. It writes frozen source; migration execution applies committed files and does not regenerate models. Check requires snapshot/model agreement, no pending/missing files and compatible live schema.

These npm scripts invoke the shared framework CLI: `db3 db:make-migration`, `db3 db:migrate` and `db3 db:check`. The generation script also type-checks your app first. `server/cli.config.ts` registers `databaseCommands` from `@db3.ai/app/db/commands` and supplies the app factory; the framework owns execution and cleanup. Each action has its own file under the database service’s `commands/` directory and uses `app().db.migrations`, configured with `dbOptions: { migrations: { models } }`. The framework locates committed migrations in `server/database/migrations/` and the snapshot at `server/database/schema.snapshot.json` under the app root. No separate `migrations.ts` settings file or migration path configuration is needed. Run `npx db3 --help` from the app root to list registered commands.

- [Starter schema-change workflow](https://db3.ai/docs/starter-app.md)
- [Manager methods and results](https://db3.ai/docs/migrations-api.md)

<a id="review"></a>

## Review generated changes

Safe generation includes nullable columns, required columns with static defaults, ordinary non-unique indexes and supported static-default changes. Data-dependent constraints, unsupported type changes and unsafe required columns are blocked for explicit review.

For a new required field on existing rows, consider add nullable, deploy compatible code, backfill deliberately, then add the requirement in a reviewed forward migration. Do not invent a default that changes product meaning just to satisfy the generator.

Generation uses a source lock and is refused in production. `allowDestructive` deliberately promotes certain removals; it is not a general approval of every risky change. Never use it casually on customer data.

<a id="deploy"></a>

## Commit schema inputs and apply them once

Commit migration files and the desired snapshot with the model change. API/worker startup must not run install, sync or generation. Deployment applies reviewed files through the migration ledger/lock, then runs check.

Never edit an already applied migration. Add a new forward migration. MariaDB DDL is not generally transactionally reversible; a failed migration may leave partial schema changes requiring inspection and recovery.

Extra live tables/columns can remain compatible for additive blue/green releases, but missing required schema or mismatched indexes fails check. Baseline adoption is a narrow existing-schema operation, not a way to mark arbitrary migrations complete.

- [Full migration status and baseline options](https://db3.ai/docs/migrations-api.md)

<a id="testing"></a>

## Testing

Create a `tests/db` directory and save the test below as `runNoteMigrations.test.ts`. It imports the example you copied into `examples/`. Keep the same folder layout so the relative import resolves.

Run from the application root with the development dependencies from Installation. These are consumer tests, not commands that assume a framework checkout. This database lab needs the same test-only SQL credentials when run through Vitest.

### tests/db/runNoteMigrations.test.ts

```typescript
import { expect, it } from 'vitest';
import { runNoteMigrations } from '../../examples/runNoteMigrations';

it('generates, applies, blocks unsafe changes and recovers with an additive migration', async () => {
	expect(await runNoteMigrations()).toEqual({ initialGenerated: true, unsafeBlocked: true, blockedPreservedSource: true, repairedGenerated: true, pendingBefore: 1, applied: 1, matches: true, titlePreserved: true, categoryNullable: true });
});
```

<a id="run-tests"></a>

## Test migrations against real existing data

The copied test creates a real row before the change, proves blocked generation leaves both snapshot and file count unchanged, then checks the repaired migration preserves that row.

Add a fixture representing the real data shape before deployment. A successful fresh install alone does not prove an upgrade is safe. Backups and restore verification remain application/deployment work.

### Run your copied test and check types

```bash
npx vitest run tests/db/runNoteMigrations.test.ts
npx tsc --noEmit --target ES2022 --module ESNext --moduleResolution Bundler --types node --skipLibCheck examples/*.ts
```

<a id="coverage"></a>

## Coverage and limits

Taught/tested: source generation, apply, status/pending, check, unsafe required-column refusal, unchanged source on refusal, nullable recovery and data preservation. Existing migration tests cover locks, defaults, indexes, baseline and production-generation refusal.

A production deployment, backfill, destructive rollback, database backup and non-MariaDB migration are not proved here. `Database.install()` is a disposable bootstrap utility, not the deployment workflow.

- [Migration API](https://db3.ai/docs/migrations-api.md)
- [Model your data](https://db3.ai/docs/guide-model-data.md)

## Behavioural verification
Generates/applies initial and additive migrations, preserves an existing note and refuses an unsafe required column without changing source.
- Behaviour test: `packages/app/src/db/tests/examples/runNoteMigrations.test.ts`
- Repository test command (framework checkout only): `npm run test:service --workspace packages/app -- db --maxWorkers=1`
- In an installed application, use the walkthrough commands instead of this repository test.
- Expected outcome: The guide test passes against the real framework components.
- Environment: Node.js 24 with MariaDB and CREATE/DROP test-database permission. Only owned temporary migration files and generated SQL database.

## Related documentation
- [Migrations API reference](https://db3.ai/docs/migrations-api.md): Current emitted signatures and options for @db3.ai/app/db/migrations.
- [Create your app](https://db3.ai/docs/starter-app.md): Create an account, save a private note and summarise it with AI. Start with working application code you can change.
- [Keep conversion in the field](https://db3.ai/docs/fields.md): Define a reusable value once, from input and validation through storage and public output.
- [Take a note from input to stored data](https://db3.ai/docs/guide-model-data.md): Choose fields, protect ownership, persist the model and return its public shape.