Helper Methods

The shortcuts that save a query-builder chain.
Lookups, upserts, counters, column extraction and raw SQL — all on the repository.

Single-Row Lookups

The …OrFail variants throw instead of returning null, which removes a null-check and produces a proper 404 at the boundary.

lookups.ts

await repo.findOne(10);                       // T | null
await repo.findOne(10, { relations: ["roles"] });
await repo.findOneBy({ email: "ada@example.com" });   // T | null
await repo.findBy({ active: true }, { limit: 10, orderBy: "createdAt DESC" });
await repo.first({ status: "draft" });        // T | null
await repo.last("createdAt");                 // T | null, defaults to "id"
await repo.random();                          // T | null
await repo.exists({ email: "ada@example.com" });      // boolean

await repo.findOrFail(10);                    // T - throws if absent
await repo.firstOrFail({ title: "Second" });  // T - throws if absent

not-found.ts

import { StabilizeError } from "stabilize-orm";

try {
  const user = await repo.findOrFail(id);
} catch (error) {
  if (error instanceof StabilizeError && error.code === "NOT_FOUND_ERROR") {
    return res.status(404).json({ error: "Not found" });
  }
  throw error;
}

Create or Update

Two idempotent writes for the common "get it, or make it" case. Both return the row, so the caller does not need to know which branch ran.

upsert-helpers.ts

// Find by conditions, or create using conditions + defaults
await repo.firstOrCreate({ email: "ada@example.com" }, { name: "Ada" });

// Update if present, create if not
await repo.updateOrCreate({ email: "ada@example.com" }, { name: "Ada L" });

Counters and Toggles

Each applies the change in SQL and returns the updated row, so you see the committed value rather than computing it yourself — no read-modify-write race.

counters.ts

await repo.increment(1, "views");        // views = views + 1
await repo.increment(1, "views", 5);     // views = views + 5
await repo.decrement(1, "stock", 2);     // stock = stock - 2
await repo.toggle(1, "active");          // flips a boolean

Reading Columns

When you want a list of values rather than entities, these skip the hydration step:

columns.ts

const emails = await repo.pluck("email");            // string[]
const rows = await repo.selectColumns("id", "email"); // Partial<T>[]

Bulk Writes

bulk.ts

// One transaction, applies _upsert per row
await repo.bulkUpsert(rows, ["slug"]);

// Chunked in batches of 100 by default - better for very large inputs
await repo.upsertMany(rows, ["slug"], 500);

// Conditional mass update / delete - returns affected row count
await repo.updateBy({ status: "draft" }, { status: "archived" });
await repo.deleteBy({ userId: 7 });

updateBy() and deleteBy() refuse empty conditions with UNSAFE_QUERY — a guard against a filter that silently became {} and rewrote the whole table. deleteBy() soft-deletes when the model has a soft-delete column, and hard deletes otherwise.

Soft Delete Recovery

The single-row restore is recover(), not restore()

There is no repo.restore(id). The three ways back are recover for one row, restoreBy for a filtered set, and recoverAll for everything.

recover.ts

await repo.recover(5);                       // one row, returns it
await repo.restoreBy({ userId: 1 });         // affected row count
await repo.recoverAll();                     // every soft-deleted row

// Reading deleted rows
await repo.findDeleted().execute(db.client); // only trashed
await repo.withTrashed().where("id = ?", 5).execute(db.client);  // all rows

restoreBy() needs a soft-delete column and throws RECOVER_ERROR without one, as does recoverAll(). findDeleted() throws QUERY_ERROR in the same case, since "only the deleted rows" has no meaning on a model that never soft deletes.

withTrashed() is different: it does not throw and does not check for a soft-delete column at all. The soft-delete filter is added to every other builder entry point — find(), findBy(), findOne() and the rest — and withTrashed() is simply the one that returns a bare builder with no filter attached. On a model without a soft-delete column that is what an ordinary query already returns, so the call is a no-op rather than an error.

Raw SQL and Truncate

raw.ts

// Repository - parameterized read
await repo.rawQuery("SELECT * FROM users WHERE age > ?", [18]);

// ORM client - read and exec
await db.rawQuery("PRAGMA table_info(users)");
await db.rawExec("CREATE TABLE post_tags (post_id INTEGER, tag_id INTEGER)");
// => { affectedRows: number }

await repo.truncate();   // hard DELETE of every row, ignores soft delete

rawExec exists only on the Stabilize client, not on a repository. Both take positional ? placeholders — never interpolate values into the string yourself.

Streaming Over Results

For a job that touches every row, these page internally so the whole table is never in memory at once.

iterate.ts

// Transform each row, collect results
const titles = await repo.map(repo.find(), (post) => post.title);

// Side effect per row, paged at 100
await repo.each(repo.find(), async (post, index) => {
  await indexer.add(post, index);
});

// Callback receives a whole batch, not one row
await repo.eachBatch(repo.find(), async (batch) => {
  await search.bulkIndex(batch);
}, 500);

Both accept a QueryBuilder and clone it, so the builder you pass in is not mutated. The third argument is the page size: each takes pageSize, eachBatch takes batchSize — positionally the same, both defaulting to 100. Pagination is offset-based, so rows inserted during a long run can shift the window.

Utilities

utils.ts

import { generateUUID, sqlDefault } from "stabilize-orm";

generateUUID();   // crypto.randomUUID()

// Use as defaultExpression, not defaultValue
columns: {
  id: { type: DataTypes.UUID, defaultExpression: sqlDefault("gen_random_uuid()") },
}

sqlDefault() marks a value as raw SQL to be emitted verbatim. Note that AutoMigrate emits only defaultValue when it creates a table — a defaultExpression is not included, so supply the default in the migration if the database itself must apply it.