Change the schema without losing the data

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

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

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.

npx tsx examples/runNoteMigrations.ts

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.

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

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 are app-owned npm scripts, not global framework executables. The starter already wires them; a custom app supplies its own thin CLI around makeMigration(), migrate() and check().

• Starter schema-change workflow
• Manager methods and results

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.

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

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.

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

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.

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

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
• Model your data