Raw queries

To mix raw SQL with queries, use the raw function from the main module. raw works just like the knex's raw method (opens new window) but in addition, supports objection queries, raw, ref, val and all other objection types. You can also use knex.raw() (opens new window).

raw is handy when you want to mix SQL in objection queries, but if you want to fire off a completely custom query, you need to use knex.raw (opens new window).

There are also some helper methods such as whereRaw in the QueryBuilder.

Examples

const { raw } = require('objection');
const ageToAdd = 10;

await Person.query().patch({
  age: raw('age + ?', ageToAdd),
});

const { raw } = require('objection');

const childAgeSums = await Person.query()
  .select(raw('coalesce(sum(??), 0)', 'age').as('childAgeSum'))
  .where(
    raw(`?? || ' ' || ??`, 'firstName', 'lastName'),
    'Arnold Schwarzenegger'
  )
  .orderBy(raw('random()'));

console.log(childAgeSums[0].childAgeSum);

Also see the fn helper for calling SQL functions. The following example is equivalent the previous one.

const { fn, ref } = require('objection');

const childAgeSums = await Person.query()
  .select(fn.coalesce(fn.sum(ref('age')), 0).as('childAgeSum'))
  .where(
    fn.concat(ref('firstName'), ' ', ref('lastName')),
    'Arnold Schwarzenegger'
  )
  .orderBy(fn('random'));

console.log(childAgeSums[0].childAgeSum);

Binding arguments can be other raw instances, QueryBuilders or pretty much anything you can think of.

const { raw, ref } = require('objection');

const people = await Person
  .query()
  .alias('p')
  .select(raw('array(?) as childIds', [
    Person.query()
      .select('id')
      .where('id', ref('p.parentId'))
  ]);

console.log('child identifiers:', people[0].childIds)

Completely custom raw query using knex:

const knex = Person.knex();
await knex.raw('SELECT 1');