# Save related records together or not at all > Use the active database transaction without adding database parameters to every model and feature function. - Package: `@db3.ai/app/db` - Canonical page: [https://db3.ai/docs/cookbook-transactions](https://db3.ai/docs/cookbook-transactions) - Markdown: [https://db3.ai/docs/cookbook-transactions.md](https://db3.ai/docs/cookbook-transactions.md) - Framework source of truth: `packages/app/src/db/README.md` ## 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](https://db3.ai/docs/guide-workspace-notes.md#configure) ### Copy the shipped example ```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. ### Run the lab ```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. ### examples/createNotesTogether.ts ```typescript 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 { 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. - [Queue delivery guarantees](https://db3.ai/docs/queue-overview.md) - [Mail retry boundary](https://db3.ai/docs/mail.md) ## 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. ### tests/db/workspaceNotes.test.ts ```typescript 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. ### Run your copied test and check types ```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](https://db3.ai/docs/active-record-api.md) ## Additional source-backed examples ### examples/KnowledgeNote.ts ```typescript import { ActiveRecord, type FieldBuilder } from '@db3.ai/app/db'; /** A workspace-owned note used by the ActiveRecord guide and executable recipe. */ export class KnowledgeNote extends ActiveRecord { static override table = 'knowledge_notes'; static override requestFillable = ['title', 'body']; /** * Defines storage, validation and transport behaviour for each logical field. * * @param field - Framework field factory. * @returns The note's schema; workspace ownership is assigned by trusted code. */ static override fields(field: FieldBuilder) { return { id: field.ulid(), workspace: field.string({ column: 'workspace_id', required: true, maxLength: 26, index: true }), title: field.string({ required: true, maxLength: 120 }), body: field.text(), createdAt: field.timestamp({ column: 'created_at', auto: 'create' }), }; } declare id: string | null; declare workspace: string | null; declare title: string | null; declare body: string | null; declare createdAt: Date | null; } ``` ### examples/workspaceNotes.ts ```typescript import { KnowledgeNote } from './KnowledgeNote'; /** * Saves allowed request fields under a workspace already authorized by the host. * * @param workspaceId - Trusted workspace identity, never taken from the payload. * @param input - Untrusted form data; only title and body are fillable. * @returns The persisted note, or rejects with RecordValidationError. */ export async function createWorkspaceNote(workspaceId: string, input: unknown): Promise { const note = new KnowledgeNote(); note.setFromRequest(input); note.assign({ workspace: workspaceId }); await note.save(); return note; } /** * Reads a bounded list using logical model fields, not database column names. * * @param workspaceId - Workspace the caller is already allowed to read. * @returns Up to twenty notes, newest first with a stable primary-key tie-break. */ export async function listWorkspaceNotes(workspaceId: string): Promise { return KnowledgeNote.where('workspace', workspaceId) .orderBy('createdAt', 'desc') .orderBy('id', 'desc') .limit(20) .all(); } /** * Updates a note only when it belongs to the caller's authorized workspace. * * @param workspaceId - Trusted workspace identity. * @param noteId - Note identity to look up within that workspace. * @param input - Untrusted editable fields; omitted values remain unchanged. * @returns The saved record; a missing or cross-workspace note is not found. */ export async function updateWorkspaceNote(workspaceId: string, noteId: string, input: unknown): Promise { const note = await KnowledgeNote.where({ workspace: workspaceId, id: noteId }).firstOrFail(); note.setFromRequest(input); await note.save(); return note; } /** * Permanently deletes a note within an already authorized workspace. * * @param workspaceId - Trusted workspace identity. * @param noteId - Note identity scoped to that workspace. * @returns The number of deleted rows; throws when the scoped record is absent. */ export async function deleteWorkspaceNote(workspaceId: string, noteId: string): Promise { const note = await KnowledgeNote.where({ workspace: workspaceId, id: noteId }).firstOrFail(); return note.delete(); } ``` ## Behavioural verification Runs the note workflow against a real disposable SQL database, including validation, workspace isolation, updates, deletion and transaction rollback. - Behaviour test: `packages/app/src/db/tests/examples/workspaceNotes.test.ts` - Repository test command (framework checkout only): `npm test --workspace @platform/app -- src/db/tests/examples/workspaceNotes.test.ts --maxWorkers=1` - In an installed application, use the walkthrough commands instead of this repository test. - Expected outcome: 6 tests pass; the walkthrough matches its checked-in JSON output. - Environment: Node.js; MariaDB/MySQL test account with CREATE/DROP DATABASE permission; package-owned test settings. No application database is used. ## Related documentation - [ActiveRecord](https://db3.ai/docs/active-record.md): Define your fields once. Create, validate, query and save records without repeating database conversion in every endpoint. - [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. - [Queue](https://db3.ai/docs/queue-overview.md): Create durable background jobs, run named workers, understand every attempt, compose chains and batches, and recover failures through one complete service guide. ## Guidance for AI tools Use the documented public import `@db3.ai/app/db` and its exported types. Prefer the source-backed examples and behavioural outcomes above over invented APIs or source-relative internal imports.