Queries

Read

Read methods are static and return model instances:

const users = await User.findAll();
const active = await User.findAllBy({active: true});
const user = await User.findOneById(1);
const alice = await User.findOneBy({email: 'alice@example.com'});
const total = await User.count();
const actives = await User.countBy({active: true});

• findAll(options?): every row.
• findAllBy(where, options?): the rows matching the conditions. search() is an alias.
• findOneBy(where): the first matching row, or undefined.
• findOneById(id): the row with ID id, or undefined.
• count() and countBy(where): the number of rows.
• new(): a new empty model instance, same as new User().

Conditions

A condition maps a column to a value (equality) or to an [operator, value] pair:

await User.findAllBy({
  active: true,
  name: ['LIKE', 'A%'],
  id: ['IN', [1, 2, 3]],
  email: ['IS NOT NULL'],
});

Available operators: =, >, <, >=, <=, LIKE, NOT LIKE, IN, NOT IN, plus IS NULL and IS NOT NULL, written without a value. All conditions are combined with AND.

Options

findAll() and findAllBy() accept pagination and sorting options:

await User.findAll({
  limit: 20,
  offset: 40,
  orderBy: {name: 'asc', createdAt: ['desc', 'last']},
});

Sorting takes 'asc' or 'desc', or an [order, nulls] pair to put null values first ('first') or last ('last').

Create, update, delete

const user = User.new().setName('Alice').setEmail('alice@example.com');

const created = await user.create(); // inserts, then reads the row back
await created?.setName('Alice B.').update();
await created?.createOrUpdate(); // update() when getId() is set, create() otherwise
await created?.delete(); // true when the query succeeded

• create() and update() return the row read back from the database.
• update() and delete() target the row whose id equals getId().

Errors

Query methods do not throw when an SQL query fails: the error is logged, prefixed with [ORM] and the table name, and the promise resolves with undefined. So check the result:

const user = await User.findOneById(1);
if (!user) {
  // not found, or the query failed (see the logs)
}

Custom queries

For anything the methods don't cover, KnexInstance.get() gives you Knex. Keep these queries in static methods of the model:

import {KnexInstance, QueryRow, Table} from '@fca.gg/orm';

@Table('users')
export class User extends QueryRow {
  // ... columns ...

  public static async countSignupsSince(date: Date): Promise<number> {
    const [row] = await KnexInstance.get()('users').where('created_at', '>=', date).count('* as count');
    return Number(row.count);
  }
}

It is also how you use a transaction, which the ORM does not handle itself yet:

await KnexInstance.get().transaction(async (trx) => {
  await trx('users').where('id', 1).update({active: false});
  await trx('posts').where('user_id', 1).delete();
});

Model methods (create(), update()…) do not accept a transaction: inside a transaction, write queries with trx.