Save related records together or not at all

Use the active database transaction without adding database parameters to every model and feature function.

On this page

Prepare the notes lab

Use the Workspace notes test-only MariaDB account and copied examples. The lab creates its own database and never wraps a production schema change in a transaction.

SQL setup and permissions

bash

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

Force the second write to fail

Run the lab and expect rollbackPreservedCount: true. The first proposed note has a valid title; the second is invalid. When validation rejects, the earlier insert rolls back.

The original pre-transaction note remains. Repair the invalid title in your copied example and assert both new notes persist after commit.

bash

npx tsx examples/runWorkspaceNotes.ts

Enter the connection scope once

Start with app().db.knex.transaction(), then run the work inside ActiveRecord.withDb(transaction, callback). Ordinary feature functions keep using model APIs and resolve the scoped connection.

Construct and query the records inside the scope. An existing record already bound to another connection is not magically rebound. Await every operation before returning from the transaction callback.

import { ActiveRecord } from '@db3.ai/app/db';
import { app } from '@db3.ai/app/server';
import { createWorkspaceNote } from './workspaceNotes';
import type { KnowledgeNote } from './KnowledgeNote';

/**
 * Saves a group of notes atomically using the active application's database.
 *
 * Records are constructed inside the transaction scope. An error rolls back
 * all writes; existing records bound to another connection are not rebound.
 *
 * @param workspaceId - Workspace the host has authorized for creation.
 * @param inputs - Note inputs; each goes through the same filling and validation.
 * @returns Persisted notes after the transaction commits successfully.
 */
export async function createNotesTogether(workspaceId: string, inputs: unknown[]): Promise<KnowledgeNote[]> {
	return app().db.knex.transaction(transaction => ActiveRecord.withDb(transaction, async () => {
		const notes: KnowledgeNote[] = [];
		for (const input of inputs) {
			notes.push(await createWorkspaceNote(workspaceId, input));
		}
		return notes;
	}));
}

Let failure reach the transaction

An error must reject the callback for rollback. Do not catch validation/database errors inside it and return success unless committing the earlier work is intentional.

Treat SQL constraints as the final authority for concurrent writes. Application checks can race. Decide whether a retry is safe and keep it bounded; a transaction does not make arbitrary external calls duplicate-safe.

Database atomicity stops at the database

Sending email, charging a card, dispatching through a separate queue connection or calling an AI provider is not rolled back with these model writes. Avoid slow external work while holding locks.

For a required post-commit handoff, use a deliberately designed outbox/reconciliation workflow. This framework recipe does not claim a transactional Queue outbox or exactly-once delivery.

Testing

Create a tests/db directory and save the test below as workspaceNotes.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 { readFileSync } from 'node:fs';
import { afterAll, beforeAll, describe, expect, it } from 'vitest';
import { RecordNotFoundError, RecordValidationError } from '@db3.ai/app/db';
import { createGeneratedTestDatabase, type GeneratedTestDatabase } from '@db3.ai/app/db/test/db';
import { App } from '@db3.ai/app/server';
import { KnowledgeNote } from '../../examples/KnowledgeNote';
import { createWorkspaceNote, deleteWorkspaceNote, listWorkspaceNotes, updateWorkspaceNote } from '../../examples/workspaceNotes';
import { runWorkspaceNotes } from '../../examples/runWorkspaceNotes';
import { createNotesTogether } from '../../examples/createNotesTogether';

describe('ActiveRecord documentation examples', () => {
	it('runs the complete disposable guide and matches its published output', async () => {
		const expected = JSON.parse(readFileSync(new URL('../../examples/outputs/workspace-notes.json', import.meta.url), 'utf8'));
		expect(await runWorkspaceNotes()).toEqual(expected);
	});
});

describe('workspace note behaviour through public framework imports', () => {
	let database: GeneratedTestDatabase;
	let application: App;
	const workspace = '01ARZ3NDEKTSV4RRFFQ69G5FAV';
	const otherWorkspace = '01ARZ3NDEKTSV4RRFFQ69G5FAW';

	beforeAll(async () => {
		database = await createGeneratedTestDatabase('notes_examples');
		application = new App({ db: database.db });
		await application.db.install(KnowledgeNote);
	});

	afterAll(async () => {
		await application?.close();
		await database?.destroy();
	});

	it('separates in-memory creation from persistence', async () => {
		const note = KnowledgeNote.create({ workspace, title: 'Unsaved note' });
		expect(note.id).toMatch(/^[0-9A-HJKMNP-TV-Z]{26}$/);
		expect(note.isPersisted()).toBe(false);
		expect(await KnowledgeNote.findByPk(note.id)).toBeNull();
		await note.save();
		expect(note.isPersisted()).toBe(true);
		expect(await KnowledgeNote.findByPk(note.id)).toBeInstanceOf(KnowledgeNote);
	});

	it('guards workspace/id, queries logical fields and serializes dates', async () => {
		const note = await createWorkspaceNote(workspace, { id: 'ignored', workspace: otherWorkspace, title: '  Protected note  ' });
		expect(note.workspace).toBe(workspace);
		expect(note.title).toBe('Protected note');
		expect(note.id).not.toBe('ignored');
		expect((await listWorkspaceNotes(workspace)).some(row => row.id === note.id)).toBe(true);
		expect((await listWorkspaceNotes(otherWorkspace)).some(row => row.id === note.id)).toBe(false);
		const row = await KnowledgeNote.where({ workspace, id: note.id }).firstOrFail();
		expect(row.createdAt).toBeInstanceOf(Date);
		expect(typeof row.toJSON().createdAt).toBe('string');
	});

	it('rejects blank and oversized titles without inserting rows', async () => {
		const before = await KnowledgeNote.where('workspace', workspace).count();
		await expect(createWorkspaceNote(workspace, { title: ' ' })).rejects.toBeInstanceOf(RecordValidationError);
		await expect(createWorkspaceNote(workspace, { title: 'x'.repeat(121) })).rejects.toBeInstanceOf(RecordValidationError);
		expect(await KnowledgeNote.where('workspace', workspace).count()).toBe(before);
	});

	it('scopes edits/deletes and preserves omitted fields during updates', async () => {
		const note = await createWorkspaceNote(workspace, { title: 'Edit me', body: 'Keep me' });
		await expect(updateWorkspaceNote(otherWorkspace, note.id!, { title: 'Forbidden' })).rejects.toBeInstanceOf(RecordNotFoundError);
		await expect(deleteWorkspaceNote(otherWorkspace, note.id!)).rejects.toBeInstanceOf(RecordNotFoundError);
		const edited = await updateWorkspaceNote(workspace, note.id!, { title: 'Edited', workspace: otherWorkspace });
		expect(edited.body).toBe('Keep me');
		expect(edited.workspace).toBe(workspace);
		await expect(deleteWorkspaceNote(workspace, note.id!)).resolves.toBe(1);
		expect(await KnowledgeNote.findByPk(note.id)).toBeNull();
	});

	it('rolls back an earlier write when a later record fails validation', async () => {
		const before = await KnowledgeNote.where('workspace', workspace).count();
		await expect(createNotesTogether(workspace, [{ title: 'First' }, { title: '' }])).rejects.toBeInstanceOf(RecordValidationError);
		expect(await KnowledgeNote.where('workspace', workspace).count()).toBe(before);
	});
});

Test rollback and success

The copied SQL tests assert that a later validation failure leaves the count unchanged. Add a successful two-note case when adapting the recipe, and check both notes after the transaction returns.

Never use a framework-owned mock database to prove transaction semantics. Cleanup removes only the generated lab database.

bash

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

Coverage and limits

Taught/tested: scoped ActiveRecord connection, normal feature reuse and rollback after an earlier real insert. Isolation levels, deadlock retries, cross-database work and external outbox delivery are not demonstrated.

Use the exact API for connection escape hatches only when working on framework/test/transaction infrastructure; ordinary app/model APIs should not grow optional database parameters.

ActiveRecord connection API

Behaviour tested Executed by the documentation maintenance gate

What this does

Runs the note workflow against a real disposable SQL database, including validation, workspace isolation, updates, deletion and transaction rollback.

Expected output6 tests pass; the walkthrough matches its checked-in JSON output.

Behaviour testpackages/app/src/db/tests/examples/workspaceNotes.test.ts

This test command requires the framework repository. Use the walkthrough commands in an installed application.

Environment: Node.js; MariaDB/MySQL test account with CREATE/DROP DATABASE permission; package-owned test settings. No application database is used.