# 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 but in addition, supports objection queries, raw, ref, lit and all other objection types. You can also use knex.raw().

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.

There are also some helper methods such as whereRaw and selectRaw 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');