ORM — How to Use

Quick reference for the ORM in config/orm.js. The module exports a db object (often used as dbPromise in the app) that extends the MySQL pool with the functions below.

---

1. query(sql, values, options)

Runs raw SQL. Use for custom queries.

Parameter	Type	Description
sql	string	SQL with ? placeholders
values	array	Values for placeholders
options	object	useReadOnly (default true for SELECT)

Examples

// SELECT (uses read-only pool by default)
const rows = await orm.query("SELECT id, name FROM users WHERE status = ?", [
    "active",
]);

// Force main pool for a SELECT
const rows = await orm.query("SELECT ...", [], { useReadOnly: false });

// INSERT/UPDATE/DELETE (always main pool)
await orm.query("UPDATE users SET last_login = ? WHERE id = ?", [
    new Date(),
    1,
]);

---

2. findOne(table, where, options)

Returns one row or null. Throws Boom.notFound when no row is found and validateNotFound !== false.

Parameter	Type	Description
table	string	Table name
where	object	WHERE conditions (optional)
options	object	select, orderBy, validateNotFound (default true)

Examples

// By simple equality
const user = await orm.findOne("users", { email: "juan@example.com" });

// No conditions (first row; use orderBy to control which)
const first = await orm.findOne("users", {}, { orderBy: { id: "ASC" } });

// Allow null without throwing
const user = await orm.findOne(
    "users",
    { id: 999 },
    { validateNotFound: false },
);
// user === null if not found

// Specific columns
const user = await orm.findOne(
    "users",
    { id: 1 },
    { select: ["id", "name", "email"] },
);

// Order and pick one
const latest = await orm.findOne(
    "users",
    { status: "active" },
    { orderBy: { created_at: "DESC" } },
);

// Operators in where
const user = await orm.findOne("users", {
    status: "active",
    age: { operator: ">", value: 25 },
});

---

3. find(table, where, options)

Returns an array of rows.

Parameter	Type	Description
table	string	Table name
where	object	WHERE conditions (optional)
options	object	select, orderBy, limit

Examples

// All rows
const users = await orm.find("users");

// Simple where
const users = await orm.find("users", { status: "active" });

// Select columns
const users = await orm.find(
    "users",
    { status: "active" },
    { select: ["id", "name"] },
);

// Limit (number)
const users = await orm.find("users", {}, { limit: 10 });

// Limit + offset
const users = await orm.find("users", {}, { limit: { limit: 10, offset: 20 } });

// OrderBy as object (column -> direction)
const users = await orm.find(
    "users",
    {},
    { orderBy: { created_at: "DESC", id: "ASC" } },
);

// OrderBy as array of objects
const users = await orm.find(
    "users",
    {},
    {
        orderBy: [
            { field: "name", direction: "ASC" },
            { field: "id", direction: "DESC" },
        ],
    },
);

// OrderBy as array of column names (default ASC)
const users = await orm.find("users", {}, { orderBy: ["name", "id"] });

// Combined
const users = await orm.find(
    "users",
    { status: "active" },
    {
        select: ["id", "name", "email"],
        orderBy: { age: "DESC" },
        limit: { limit: 10, offset: 0 },
    },
);

---

4. update(table, data, where)

Updates rows. Returns { affectedRows, changedRows }.

Parameter	Type	Description
table	string	Table name
data	object	SET key-value
where	object	WHERE conditions

Examples

// Simple
await orm.update("users", { age: 31 }, { id: 1 });

// Multiple columns
await orm.update(
    "users",
    {
        last_login: new Date(),
        login_count: 5,
    },
    { email: "juan@example.com" },
);

// Set column from another column (raw SQL reference)
await orm.update(
    "zoho_invoices",
    {
        payment_status: "paid",
        paid_amount: { type: "ref", value: "total" },
    },
    { id: 123 },
);
// Generates: paid_amount = total (no placeholder)

// Where with operators
await orm.update(
    "users",
    { status: "inactive" },
    {
        role: "guest",
        last_login: { operator: "<", value: new Date("2020-01-01") },
    },
);

---

5. delete(table, where)

Deletes rows. Where is required. Returns { affectedRows }.

Parameter	Type	Description
table	string	Table name
where	object	WHERE conditions (required)

Examples

await orm.delete("users", { id: 1 });

await orm.delete("users", {
    status: "inactive",
    created_at: { operator: "<", value: new Date("2020-01-01") },
});

---

6. exist(table, where)

Returns true if at least one row matches, else false.

Examples

const hasAdmin = await orm.exist("users", { role: "admin" });

const hasRecent = await orm.exist("users", {
    status: "active",
    last_login: { operator: ">", value: new Date("2024-01-01") },
});

---

7. count(table, where)

Returns the number of rows matching where. where is optional.

Examples

const total = await orm.count("users");

const active = await orm.count("users", { status: "active" });

const count = await orm.count("users", {
    status: "active",
    age: { operator: ">=", value: 18 },
});

---

8. insert(table, data, options)

Inserts one row. Returns { insertId, affectedRows }.

Parameter	Type	Description
table	string	Table name
data	object	Column -> value
options	object	insertIgnore (boolean)

Examples

const result = await orm.insert("users", {
    name: "Juan",
    email: "juan@example.com",
    age: 30,
});
console.log(result.insertId);

// Skip duplicate key errors
const result = await orm.insert(
    "users",
    {
        name: "Juan",
        email: "juan@example.com",
    },
    { insertIgnore: true },
);

---

9. insertMultiple(table, data, options)

Inserts one or many rows in a single statement. data can be one object or an array of objects (same keys). Returns { insertId, affectedRows }.

Examples

// Single object (same shape as insert)
await orm.insertMultiple("users", {
    name: "Juan",
    email: "juan@example.com",
    age: 30,
});

// Array of rows
await orm.insertMultiple(
    "users",
    [
        { name: "Juan", email: "juan@example.com", age: 30 },
        { name: "María", email: "maria@example.com", age: 28 },
    ],
    { insertIgnore: true },
);

---

10. insertBatch(table, data, options)

Batch insert: data must be an array of objects with the same keys. Returns { insertId, affectedRows }. Uses backtick-quoted table and column names.

Examples

await orm.insertBatch("users", [
    { name: "Juan", email: "juan@example.com", age: 30 },
    { name: "María", email: "maria@example.com", age: 28 },
]);

await orm.insertBatch("logs", rows, { insertIgnore: true });

---

11. whereBuilder(whereEvaluations)

Helper to build a WHERE string from a list of conditions. Each item: { evaluation, pass, fail }. If evaluation is truthy, pass is appended; otherwise fail (if present).

Example

const where = orm.whereBuilder([
    { evaluation: !!filters.status, pass: "status = 1", fail: "1=1" },
    { evaluation: !!filters.name, pass: 'name LIKE "%x%"', fail: "" },
]);
// Use in raw query; no automatic escaping of values

---

WHERE clause reference

Used by findOne, find, update, delete, exist, count.

Simple equality (AND)

{ id: 1, status: 'active' }
// WHERE id = ? AND status = ?

Operators

{ age: { operator: '>', value: 25 } }
{ name: { operator: 'LIKE', value: '%Juan%' } }
{ status: { operator: 'IN', value: ['active', 'pending'] } }
{ status: { operator: 'NOT IN', value: ['banned'] } }
// Empty IN → 1=0; empty NOT IN → 1=1

Column comparison (no placeholder)

{ paid_amount: { operator: '=', value: { $col: 'total' } } }
// paid_amount = total

$or / $and

{ $or: { id: 1, status: 'active' } }
// WHERE (id = ? OR status = ?)

{ status: 'active', $or: { age: { operator: '>', value: 25 }, name: { operator: 'LIKE', value: '%A%' } } }
// WHERE status = ? AND (age > ? OR name LIKE ?)

{ $and: { age: { operator: '>', value: 18 }, status: 'active' } }
// WHERE (age > ? AND status = ?)

Reserved words

Column names that are reserved (e.g. default) are escaped in orderBy (e.g. `default`).

---

OrderBy formats

• Object: { columnName: 'ASC' | 'DESC' } (default ASC).
• Array of objects: [{ field: 'name', direction: 'ASC' }, ...].
• Array of strings: ['name', 'id'] (ASC).

---

Limit format

• Number: limit: 10 → LIMIT 10.
• Object: limit: { limit: 10, offset: 20 } → LIMIT 10 OFFSET 20.

---

Errors

• Queries: throw Boom.badImplementation on DB errors.
• findOne with validateNotFound: true (default): throws Boom.notFound when no row is found.
• update / insert / insertMultiple / insertBatch: throw if data is empty.
• delete: requires a non-empty where (no global deletes).