ActiveRecord

Why ActiveRecord?

Over the last 15 years, I’ve found ActiveRecord to be a good mental model for breaking an application into understandable parts. It isn’t perfect. For larger, more complex systems, I’ll often put a service in front of a group of models to coordinate a workflow.

I’ve built frameworks where almost everything hung off ActiveRecord. Then one developer puts an expensive database operation inside a loop, somebody adds another loop, and a few innocent-looking lines bring the application to its knees.

Here’s the sort of thing I mean, in TypeScript-style pseudocode. This illustrates the problem; it is not a db3.ai relationship or reporting API.

• Skip to the practical guide

// Illustrative pseudocode, not the db3.ai API.
for (const user of users) {
	for (const project of await user.getProjects()) {
		await project.runReport();
	}
}

The database is still there

If loading each user’s projects runs a query, and every report runs several more, that little loop can trigger thousands of queries. It’s easy to read the code and miss how much work it asks the database to do.

At that point, the whole concept gets blamed. Out goes ActiveRecord; in come resource services, raw queries, a document database or a different ORM. But moving the same database work behind a different abstraction doesn’t make it cheaper.

As with most software engineering, there is no perfect solution. In my experience, ActiveRecord is a great way to rocket through development while keeping things simple and easy to expand. You just can’t forget that there is a database underneath it.

An abstraction several layers above the database can hide performance problems and make database-specific behaviour harder to handle as a system grows. Raw queries everywhere create a different problem: duplicated logic, repeated conversions and inconsistencies. The best answer is often a compromise.

This isn’t trying to be a full object mapper or remove the need to learn a database. Learning a completely abstract system on top of the database can be more work than learning the database itself. Learn the technology you’re using, then build a light abstraction around it. If the abstraction becomes more complicated than the database, you’ve probably gone too far.

That’s the aim here: abstract enough to keep development fast and consistent, and make the application easy to organise and reason about. Keep a database-level escape hatch for the more complex work. ActiveRecord should help you work with the database, not make you forget it exists.

• Query methods and the toKnex() escape hatch

ActiveRecord, built around fields

In some ways, ActiveField would be a better name for the idea. The useful, reusable chunk is the field datatype: it defines how a value moves through the system. ActiveField describes the principle here, not a separate API.

ActiveRecord brings a named collection of fields together and adds queries and persistence. Each FieldType owns how input becomes an application value, how it is validated, how it is stored and loaded, and how it is presented in JSON or a form. The record coordinates those rules; your endpoints shouldn’t have to repeat them.

That puts the behaviour with the datatype, rather than one particular table. PasswordField hashes a password before storage and keeps it out of JSON. EncryptedJsonField encrypts recoverable secrets, such as API tokens, using the app’s configured security service. Declare the same field type on another model and migrate its column. You don’t have to write the conversion and validation again.

The same idea is useful for nested objects and more document-oriented data. JsonField stores nested objects and arrays today, but treats the JSON as one value: it does not automatically validate child fields. Reusable groups of child fields would take this further, but there isn’t a built-in nested-field schema API yet.

This is particularly useful for builders and dynamic applications, where parts of a system are built by another system. If a datatype carries its own rules, the builder has less model-specific code to generate. Define the behaviour once, then reuse it wherever that kind of data appears.

• Field lifecycle and custom field API

Start with a model

A model represents one database row. Its fields own validation, column names and value conversion. Use model names in your application code: workspace, not workspace_id.

We’ll use a note belonging to a workspace. This page covers the normal data workflow. The linked recipe runs it in an isolated database; the API reference contains the full record and query method signatures.

• Run the workspace notes walkthrough
• ActiveRecord API reference

Before you start

Your application needs an App instance, a configured MariaDB/MySQL connection and the model’s table. Create the App once during boot. Models resolve its database automatically; feature functions should not take an optional database argument.

The framework packages are pre-release. The executable recipe below uses supplied package tarballs. There is not yet a public create-app command or a complete workspace/auth starter.

Use committed migrations for application schemas. Database.install() appears only in the disposable example, not in normal server startup. The recipe lists the exact prerequisites and cleanup behaviour.

• Set up and run the isolated example

Define the fields

Give the model a table name and a fields() method. field.ulid() creates a primary key in memory, before the first save. Required strings are trimmed and validated. Timestamp fields keep Date values in the backend and convert them for JSON.

The declare properties tell TypeScript about the accessors installed by ActiveRecord. They do not set runtime values. Keep their types aligned with the field definitions.

Only title and body are request-fillable. Ownership comes from trusted application code. A fillable list prevents mass assignment; it does not authenticate the caller or prove workspace membership.

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

Create and save a note

Construct a record, fill the permitted request fields, assign trusted ownership, then call save(). A new record is inserted. A loaded record is updated when you save it again.

KnowledgeNote.create() also creates an unsaved record. Unlike Laravel’s create(), it does not insert a row. Always call save() when you want persistence.

The functions below assume the host has already authorized workspaceId. Do not pass a workspace from the request body as that trusted argument. Unknown and non-fillable payload keys are ignored; use request validation when your API must reject them.

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<KnowledgeNote> {
	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<KnowledgeNote[]> {
	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<KnowledgeNote> {
	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<number> {
	const note = await KnowledgeNote.where({ workspace: workspaceId, id: noteId }).firstOrFail();
	return note.delete();
}

Read the records you need

where() builds a query; all() executes it and returns model instances. first() returns one record or null. firstOrFail() throws RecordNotFoundError. findByPk(id) is a useful unscoped lookup, but it is not a tenant access check.

For a workspace-owned resource, include both workspace and record identity when reading, editing or deleting. The example uses the same scoped lookup for updates and deletes, so another workspace’s ID is treated as not found.

Bound list queries. The example returns at most twenty records, ordered by createdAt and then id. Use count() for a database count. select() takes logical field names; treat partially selected records as read-only views, not complete records to edit.

• Filters, selection, ordering and query results

Handle validation failures

save() validates before writing. A blank or overlong title throws RecordValidationError; error.errors contains the field errors. validate() lets you check earlier and returns a boolean. Read getErrors() or getFieldErrors(name) afterwards.

An HTTP adapter can map validation failure to 422 and a missing scoped record to 404. Return only the field, message and code your UI needs. Error objects can contain submitted values, so don’t serialize the entire error into logs or API responses.

Field conversion is intentionally permissive: for example, a string field can coerce a number. Validate the raw request first when the endpoint needs strict input types. Field validation remains the final check before persistence.

• Record validation and error inspection methods
• Request validation service

Update or delete a note

Load the scoped record, apply the supplied editable fields, then save it. Omitted fields keep their existing values. isDirty() tells you whether values have changed; it is not an optimistic-locking or revision check.

delete() permanently removes this example’s row. If you need undo, use a model with softDeletes enabled and a deletedAt timestamp field. withTrashed(), onlyTrashed(), restore() and forceDelete() are available in the reference; this recipe does not exercise soft deletion.

Concurrent edits need an application policy. Saving a record does not automatically detect that someone else changed the same field.

• Deletion, soft-delete and dirty-state APIs

Return JSON without rebuilding the record

Use note.toJSON() for field-owned transport values. A timestamp becomes a string; hidden fields stay out of the result. toAppData() keeps backend values such as Date objects, while also respecting hidden fields.

Conversion is not authorization. Return the whole record only if the caller may see every visible field. Otherwise choose a permitted response shape at the endpoint.

Save several records together

Open a transaction through app().db.knex, then run the work inside ActiveRecord.withDb(). The feature functions keep using the normal model API. If any save fails, the transaction rolls back.

Create or load participating records inside that scope. Records already bound to a connection are not automatically moved into the transaction. Keep provider calls and other slow external work outside the database transaction.

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

What this guide covers

The tested path covers model fields, ULIDs, safe request filling, inserts, scoped queries, validation failures, updates, JSON, hard deletion and rollback. It also explains App setup and the boundary between this lab and application migrations.

Advanced reference: query operators, dirty state, soft deletion, request maps, field metadata, forms, custom fields and lower-level connection/hydration controls. These are reference coverage, not a claim that every advanced workflow has a walkthrough.

Still to write: relationship loading and projections, a migration-first HTTP API, cursor pagination, and conflict-aware editing. Cross-service recipes are tracked under Solve a problem.

• Full ActiveRecord and query API
• Solve a problem: recipes and planned walkthroughs