StabilizeORM

Helper Methods

Lookups, create-or-update, column helpers, bulk writes, soft delete, iteration, raw SQL and utilities

Lookups

typescript
async findOne(id: number | string, options?: { relations?: string[] }, _client?: DBClient): Promise<T | null>
async findOneBy(conditions: Partial<T>, options?: { relations?: string[] }, _client?: DBClient): Promise<T | null>
async findBy(conditions: Partial<T>, options?: { relations?: string[]; limit?: number; orderBy?: string }, _client?: DBClient): Promise<T[]>
async findOrFail(id: number | string, options?: { relations?: string[] }, _client?: DBClient): Promise<T>
async firstOrFail(conditions?: Partial<T>, options?: { relations?: string[] }, _client?: DBClient): Promise<T>
async first(conditions?: Partial<T>): Promise<T | null>
async last(field?: string): Promise<T | null>
async random(): Promise<T | null>
async exists(conditions?: Partial<T>): Promise<boolean>

Options:

  • relations Names of relations to load with the row.
  • limit findBy() only. Maximum rows to return.
  • orderBy findBy() only. Column to order by.
  • field last() only. Column to order by, defaulting to "id".

findOrFail() and firstOrFail() throw a StabilizeError with code NOT_FOUND_ERROR when nothing matches.

example/lookups.ts
const user = await userRepo.findOne(1, { relations: ["posts"] });
const byEmail = await userRepo.findOneBy({ email: "alice@example.com" });
const admins = await userRepo.findBy({ role: "admin" }, { limit: 10, orderBy: "name" });
const newest = await userRepo.last("createdAt");
const lucky = await userRepo.random();
const hasAny = await userRepo.exists({ status: "active" });

firstOrCreate() / updateOrCreate()

typescript
async firstOrCreate(conditions: Partial<T>, defaults?: Partial<T>, _client?: DBClient): Promise<T>
async updateOrCreate(conditions: Partial<T>, updates: Partial<T>, _client?: DBClient): Promise<T>

firstOrCreate() returns the matching row, creating it from defaults when none exists. updateOrCreate() returns the matching row, creating or updating it from updates.

example/first-or-create.ts
const tag = await tagRepo.firstOrCreate(
  { name: "orm" },
  { name: "orm", color: "blue" }
);


const setting = await settingRepo.updateOrCreate(
  { key: "theme" },
  { key: "theme", value: "dark" }
);

Column and value helpers

typescript
async pluck<K extends keyof T>(column: K): Promise<any[]>
async selectColumns(...columns: (keyof T)[]): Promise<Partial<T>[]>
async toggle(id: number | string, field: string, _client?: DBClient): Promise<T>
async increment(id: number | string, field: string, amount?: number, _client?: DBClient): Promise<T>
async decrement(id: number | string, field: string, amount?: number, _client?: DBClient): Promise<T>

pluck() returns one column as a flat array. selectColumns() returns partial rows containing only the named columns.

toggle(), increment() and decrement() apply the change in SQL and return the updated row. amount defaults to 1.

example/column-helpers.ts
const names = await userRepo.pluck("name");
const slim = await userRepo.selectColumns("id", "name");


const toggled = await userRepo.toggle(1, "active");
const bumped = await userRepo.increment(1, "logins", 1);
const lowered = await userRepo.decrement(1, "credits", 5);

Bulk writes

typescript
async bulkUpsert(entities: Partial<T>[], keys: string[], _client?: DBClient): Promise<T[]>
async upsertMany(entities: Partial<T>[], keys: string[], batchSize?: number, _client?: DBClient): Promise<T[]>
async updateBy(conditions: Partial<T>, updates: Partial<T>, _client?: DBClient): Promise<number>
async deleteBy(conditions: Partial<T>, _client?: DBClient): Promise<number>

bulkUpsert() writes every entity in one transaction. upsertMany() does the same in batches; batchSize defaults to 100. The keys argument names the columns that decide whether a row is matched and updated or inserted.

updateBy() and deleteBy() return the number of affected rows. Both throw a StabilizeError with code UNSAFE_QUERY when conditions is empty.updateBy() auto-sets updatedAt when the model has timestamps, and auto-advances an optimistic-lock column. deleteBy() soft-deletes when the model has a soft-delete column, and hard deletes otherwise.

example/bulk-writes.ts
const saved = await userRepo.bulkUpsert(
  [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }],
  ["id"]
);


const many = await userRepo.upsertMany(rows, ["email"], 250);


const updated = await userRepo.updateBy({ status: "new" }, { status: "active" });
const removed = await userRepo.deleteBy({ status: "archived" });

Soft delete

typescript
async recover(id: number | string, _client?: DBClient): Promise<T>
async recoverAll(_client?: DBClient): Promise<number>
async restoreBy(conditions: Partial<T>, _client?: DBClient): Promise<number>
async truncate(_client?: DBClient): Promise<void>
findDeleted(): QueryBuilder<T>
withTrashed(): QueryBuilder<T>

recover() restores one row by primary key. recoverAll() and restoreBy() return the number of rows restored. truncate() performs a hard DELETE of every row.

findDeleted() returns only trashed rows and withTrashed() includes them. Both return a QueryBuilder. In a model without a soft-delete column, restoreBy() throws code RECOVER_ERROR and findDeleted() throws code QUERY_ERROR. withTrashed() never throws, because it builds a bare QueryBuilder with no soft-delete predicate. It also attaches no relation loader, so .withRelations() on a withTrashed() builder does not eager-load anything — use find() for that.

example/soft-delete.ts
const restored = await userRepo.recover(1);
const count = await userRepo.restoreBy({ status: "archived" });


const trashed = await userRepo.findDeleted().execute(orm.client);
const everything = await userRepo.withTrashed().execute(orm.client);


await userRepo.truncate();

Iteration

typescript
async map<R>(query: QueryBuilder<T>, transform: (item: T) => R): Promise<R[]>
async each(query: QueryBuilder<T>, callback: (item: T, index: number) => void | Promise<void>, pageSize?: number): Promise<void>
async eachBatch(query: QueryBuilder<T>, callback: (batch: T[]) => void | Promise<void>, batchSize?: number): Promise<void>

each() and eachBatch() clone the builder — the passed builder is not mutated — and paginate by offset/limit. pageSize and batchSize both default to 100.

example/iteration.ts
const names = await userRepo.map(userRepo.find(), (u) => u.name);


await userRepo.each(userRepo.find(), async (user, index) => {
  await sync(user, index);
}, 50);


await userRepo.eachBatch(userRepo.find(), async (batch) => {
  await bulkIndex(batch);
}, 200);

Raw SQL

typescript
// Repository
async rawQuery<R = T>(query: string, params?: any[]): Promise<R[]>


// Stabilize
async rawQuery<T = any>(query: string, params?: any[]): Promise<T[]>
async rawExec(query: string, params?: any[]): Promise<{ affectedRows: number }>

rawExec() exists only on the Stabilize class, not on the Repository. Both rawQuery() forms return an array of rows.

example/raw-sql.ts
const rows = await userRepo.rawQuery("SELECT * FROM users WHERE age > ?", [18]);


const all = await orm.rawQuery("SELECT * FROM users");
const { affectedRows } = await orm.rawExec(
  "UPDATE users SET active = 0 WHERE lastLogin < ?",
  [oneYearAgo]
);

Utilities

Module-level exports, not repository methods.

typescript
export function generateUUID(): string            // returns crypto.randomUUID()
export function sqlDefault(sql: string): DefaultExpression   // { sql: string }

sqlDefault() is used as a column's defaultExpression, not defaultValue.

example/utilities.ts
import { generateUUID, sqlDefault } from "stabilize-orm";


const id = generateUUID();


const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.UUID, defaultExpression: sqlDefault("gen_random_uuid()") },
  },
});

Note that autoMigrate emits only defaultValue when creating a table — a defaultExpression is not included.