NAV

Fork me on GitHub

Introduction

Build Status Coverage Status Join the chat at https://gitter.im/Vincit/objection.js

Objection.js is an ORM for Node.js that aims to stay out of your way and make it as easy as possible to use the full power of SQL and the underlying database engine while keeping magic to a minimum.

Objection.js is built on an SQL query builder called knex. All databases supported by knex are supported by objection.js. SQLite3, Postgres and MySQL are thoroughly tested.

What objection.js gives you:

What objection.js doesn’t give you:

Objection.js uses Promises and coding practices that make it ready for the future. We use Well known OOP techniques and ES2015 classes and inheritance in the codebase. You can use things like async/await using node “>=7.6.0” or alternatively with a transpiler such as Babel. Check out our ES2015 and ESNext example projects.

Installation

npm install knex objection

You also need to install one of the following depending on the database you want to use:

npm install pg
npm install sqlite3
npm install mysql
npm install mysql2
npm install mariasql

You can use the next tag to install an alpha/beta/RC version:

npm install objection@next

Objection.js can be installed using npm.

Getting started

Install example project:

git clone git@github.com:Vincit/objection.js.git objection
cd objection/examples/express-es6
npm install
# We use knex for migrations in this example.
npm install knex -g
knex migrate:latest
npm start

If installing the example project seems like too much work, here is a simple standalone example. Just copy this into a file and run it:

// run the following command to install:
// npm install objection knex sqlite3

const { Model } = require('objection');
const Knex = require('knex');

// Initialize knex.
const knex = Knex({
  client: 'sqlite3',
  useNullAsDefault: true,
  connection: {
    filename: 'example.db'
  }
});

// Give the knex object to objection.
Model.knex(knex);

// Person model.
class Person extends Model {
  static get tableName() {
    return 'persons';
  }

  static get relationMappings() {
    return {
      children: {
        relation: Model.HasManyRelation,
        modelClass: Person,
        join: {
          from: 'persons.id',
          to: 'persons.parentId'
        }
      }
    };
  }
}

async function createSchema() {
  // Create database schema. You should use knex migration files to do this. We
  // create it here for simplicity.
  await knex.schema.createTableIfNotExists('persons', table => {
    table.increments('id').primary();
    table.integer('parentId').references('persons.id');
    table.string('firstName');
  });
}

async function main() {
  // Create some people.
  const sylvester = await Person.query().insertGraph({
    firstName: 'Sylvester',

    children: [
      {
        firstName: 'Sage'
      },
      {
        firstName: 'Sophia'
      }
    ]
  });

  console.log('created:', sylvester);

  // Fetch all people named Sylvester and sort them by id.
  // Load `children` relation eagerly.
  const sylvesters = await Person.query()
    .where('firstName', 'Sylvester')
    .eager('children')
    .orderBy('id');

  console.log('sylvesters:', sylvesters);
}

createSchema().then(() => main()).catch(console.error);

To use objection.js all you need to do is initialize knex and give the created object to objection.js using Model.knex(knex). Doing this installs the knex connection globally for all models (even the ones that have not been created yet). If you need to use multiple databases check out our multi-tenancy recipe.

The next step is to create some migrations and models and start using objection.js. The best way to get started is to check out the example project. The express example project is a simple express server. The client.js file contains a bunch of http requests for you to start playing with the REST API.

We also have an ESNext version of the example project that uses Babel for ESNext –> ES2015 transpiling and a typescript version.

Also check out our API reference and recipe book.

Models

A working model with minimal amount of code:

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

class MinimalModel extends Model {
  static get tableName() {
    return 'someTableName';
  }
}

module.exports = MinimalModel;

ESNext:

import { Model } from 'objection';

export default class MinimalModel extends Model {
  static tableName = 'someTableName';
}

Model with custom methods, json schema validation and relations. This model is used in the examples:

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

class Person extends Model {

  // Table name is the only required property.
  static get tableName() {
    return 'persons';
  }

  // Each model must have a column (or a set of columns) that uniquely
  // identifies the rows. The column(s) can be specified using the `idColumn`
  // property. `idColumn` returns `id` by default and doesn't need to be
  // specified unless the model's primary key is something else.
  static get idColumn() {
    return 'id';
  }

  // Methods can be defined for model classes just as you would for
  // any javascript class. If you want to include the result of these
  // method in the output json, see `virtualAttributes`.
  fullName() {
    return this.firstName + ' ' + this.lastName;
  }

  // Optional JSON schema. This is not the database schema!
  // Nothing is generated based on this. This is only used
  // for input validation. Whenever a model instance is created
  // either explicitly or implicitly it is checked against this schema.
  // http://json-schema.org/.
  static get jsonSchema () {
    return {
      type: 'object',
      required: ['firstName', 'lastName'],

      properties: {
        id: {type: 'integer'},
        parentId: {type: ['integer', 'null']},
        firstName: {type: 'string', minLength: 1, maxLength: 255},
        lastName: {type: 'string', minLength: 1, maxLength: 255},
        age: {type: 'number'},

        // Properties defined as objects or arrays are
        // automatically converted to JSON strings when
        // writing to database and back to objects and arrays
        // when reading from database. To override this
        // behaviour, you can override the
        // Person.jsonAttributes property.
        address: {
          type: 'object',
          properties: {
            street: {type: 'string'},
            city: {type: 'string'},
            zipCode: {type: 'string'}
          }
        }
      }
    };
  }

  // This object defines the relations to other models.
  static get relationMappings() {
    // Import models here to prevent require loops.
    const Animal = require('./Animal');
    const Movie = require('./Movie');

    return {
      pets: {
        relation: Model.HasManyRelation,
        // The related model. This can be either a Model
        // subclass constructor or an absolute file path
        // to a module that exports one.
        modelClass: Animal,
        join: {
          from: 'persons.id',
          to: 'animals.ownerId'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: Movie,
        join: {
          from: 'persons.id',
          // ManyToMany relation needs the `through` object
          // to describe the join table.
          through: {
            // If you have a model class for the join table
            // you need to specify it like this:
            // modelClass: PersonMovie,
            from: 'persons_movies.personId',
            to: 'persons_movies.movieId'
          },
          to: 'movies.id'
        }
      },

      children: {
        relation: Model.HasManyRelation,
        modelClass: Person,
        join: {
          from: 'persons.id',
          to: 'persons.parentId'
        }
      },

      parent: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'persons.parentId',
          to: 'persons.id'
        }
      }
    };
  }
}

ESNext:

class Person extends Model {
  // Table name is the only required property.
  static tableName = 'persons';

  // Each model must have a column (or a set of columns) that uniquely
  // identifies the rows. The colum(s) can be specified using the `idColumn`
  // property. `idColumn` returns `id` by default and doesn't need to be
  // specified unless the model's primary key is something else.
  static idColumn = 'id';

  // Methods can be defined for model classes just as you would for
  // any javascript class. If you want to include the result of these
  // method in the output json, see `virtualAttributes`.
  fullName() {
    return this.firstName + ' ' + this.lastName;
  }

  // Optional JSON schema. This is not the database schema!
  // Nothing is generated based on this. This is only used
  // for input validation. Whenever a model instance is created
  // either explicitly or implicitly it is checked against this schema.
  // http://json-schema.org/.
  static jsonSchema = {
    type: 'object',
    required: ['firstName', 'lastName'],

    properties: {
      id: {type: 'integer'},
      parentId: {type: ['integer', 'null']},
      firstName: {type: 'string', minLength: 1, maxLength: 255},
      lastName: {type: 'string', minLength: 1, maxLength: 255},
      age: {type: 'number'},

      // Properties defined as objects or arrays are
      // automatically converted to JSON strings when
      // writing to database and back to objects and arrays
      // when reading from database. To override this
      // behaviour, you can override the
      // Person.jsonAttributes property.
      address: {
        type: 'object',
        properties: {
          street: {type: 'string'},
          city: {type: 'string'},
          zipCode: {type: 'string'}
        }
      }
    }
  };

  // This object defines the relations to other models.
  static relationMappings = {
    pets: {
      relation: Model.HasManyRelation,
      // The related model. This can be either a Model
      // subclass constructor or an absolute file path
      // to a module that exports one. We use the file
      // path version here to prevent require loops.
      modelClass: __dirname + '/Animal',
      join: {
        from: 'persons.id',
        to: 'animals.ownerId'
      }
    },

    movies: {
      relation: Model.ManyToManyRelation,
      modelClass: __dirname + '/Movie',
      join: {
        from: 'persons.id',
        // ManyToMany relation needs the `through` object
        // to describe the join table.
        through: {
          // If you have a model class for the join table
          // you need to specify it like this:
          // modelClass: PersonMovie,
          from: 'persons_movies.personId',
          to: 'persons_movies.movieId'
        },
        to: 'movies.id'
      }
    },

    children: {
      relation: Model.HasManyRelation,
      modelClass: Person,
      join: {
        from: 'persons.id',
        to: 'persons.parentId'
      }
    },

    parent: {
      relation: Model.BelongsToOneRelation,
      modelClass: Person,
      join: {
        from: 'persons.parentId',
        to: 'persons.id'
      }
    }
  };
}

Models are created by inheriting from the Model class. A Model subclass represents a database table and instances of that class represent table rows. A Model class can define relationships (aka. relations, associations) to other models using the static relationMappings property.

In objection, all configuration is done through Model classes and there is no global configuration or state. This allows you to create isolated components and for example to use multiple different databases with different configurations in one app. Most of the time you want the same configuration for all models and a good pattern is to create a BaseModel superclass and inherit all your models from that. You can then add all shared configuration to BaseModel. See the Reference –> Model –> Static properties section for all available configuration options.

Models can optionally define a jsonSchema object that is used for input validation. Every time a Model instance is created, it is validated against the jsonSchema. Note that Model instances are implicitly created whenever you call insert(obj), insertGraph(obj), patch(obj) or any other method that takes model properties (no validation is done when reading from the database).

Each model must have an identifier column. The identifier column name can be set using the idColumn property. idColumn defaults to "id". If your table’s identifier is something else, you need to set idColumn. Composite id can be set by giving an array of column names. Composite keys are first class citizens in objection.

Relations

BelongsToOneRelation: Use this relation when the source model has the foreign key

class Animal extends Model {
  static tableName = 'animals';

  static relationMappings = {
    owner: {
      relation: Model.BelongsToOneRelation,
      modelClass: Person,
      join: {
        from: 'animals.ownerId',
        to: 'persons.id'
      }
    }
  }
}

HasManyRelation: Use this relation when the related model has the foreign key

class Person extends Model {
  static tableName = 'persons';

  static relationMappings = {
    animals: {
      relation: Model.HasManyRelation,
      modelClass: Animal,
      join: {
        from: 'persons.id',
        to: 'animals.ownerId'
      }
    }
  }
}

HasOneRelation: Just like HasManyRelation but for one related row

class Person extends Model {
  static tableName = 'persons';

  static relationMappings = {
    animal: {
      relation: Model.HasOneRelation,
      modelClass: Animal,
      join: {
        from: 'persons.id',
        to: 'animals.ownerId'
      }
    }
  }
}

ManyToManyRelation: Use this relation when the model is related to a list of other models through a join table

class Person extends Model {
  static tableName = 'persons';

  static relationMappings = {
    movies: {
      relation: Model.ManyToManyRelation,
      modelClass: Movie,
      join: {
        from: 'persons.id',
        through: {
          // persons_movies is the join table.
          from: 'persons_movies.personId',
          to: 'persons_movies.movieId'
        },
        to: 'movies.id'
      }
    }
  }
}

HasOneThroughRelation: Use this relation when the model is related to a single model through a join table

class Person extends Model {
  static tableName = 'persons';

  static relationMappings = {
    movie: {
      relation: Model.HasOneThroughRelation,
      modelClass: Movie,
      join: {
        from: 'persons.id',
        through: {
          // persons_movies is the join table.
          from: 'persons_movies.personId',
          to: 'persons_movies.movieId'
        },
        to: 'movies.id'
      }
    }
  }
}

Solutions to require loops

class Person extends Model {
  static get tableName() {
    return 'persons';
  }

  static get relationMappings() {
    // Solution 1:
    //
    // relationMappings getter is accessed lazily when you execute your first query
    // that needs it. Therefore if you `require` your models inside the getter, you
    // don't end up with a require loop. Note that only one end of the relation needs
    // to be required like this, not both. `relationMappings` can also be a method or
    // a thunk if you prefer those instead of getters.
    const Animal = require('./Animal');

    return {
      pets: {
        relation: Model.HasManyRelation,
        modelClass: Animal,
        join: {
          from: 'persons.id',
          to: 'animals.ownerId'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        // Solution 2:
        //
        // Absolute file path to a module that exports the model class. This is similar
        // to solution 1, but objection calls `require` under the hood. The downside here
        // is that you need to give an absolute file path because of the way `require` works.
        modelClass: `${__dirname}/Movie`,
        join: {
          from: 'persons.id',
          through: {
            // persons_movies is the join table.
            from: 'persons_movies.personId',
            to: 'persons_movies.movieId'
          },
          to: 'movies.id'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        // Solution 3:
        //
        // Use only a module name and define a `modelPaths` property for your model (or a superclass
        // of your model). Search for `modelPaths` from the docs for more info.
        modelClass: 'Movie',
        join: {
          from: 'persons.id',
          through: {
            from: 'persons_movies.personId',
            to: 'persons_movies.movieId'
          },
          to: 'movies.id'
        }
      }
    };
  }
}

We already went through how to create relationships (aka. relations, associations) in the models section’s examples but here’s a list of all the available relation types in a nicely searchable place. See this API doc section for full documentation of the relation mapping parameters.

Relationships are a very basic concept in relational databases and if you aren’t familiar with it, you should spend some time googling it first. Basically there are three ways to create a relationship between two tables A and B:

  1. A table has a column that holds the B table’s id. This relationship is called a BelongsToOneRelation in objection. We can say that A belongs to one B.

  2. B table has a column that holds the A table’s id. This relationship is called a HasManyRelation in objection. We can say that A has many B’s.

  3. C table has columns for both A and B tables’ identifiers. This relationship is called ManyToManyRelation in objection. Each row in C joins one A with one B. Therefore an A row can be related to multiple B rows and a B row can be related to multiple A rows.

While relations are usually created between the primary key of one table and a foreign key reference of another table, objection has no such limitations. You can create relationship using any two columns (or any sets of columns). You can even create relation using values nested deep inside json columns.

If you have used other ORM’s you may notice that objection’s relationMappings are really verbose. There are couple of reasons for that:

  1. For a new user, this style underlines what is happening, and which columns and tables are involved.

  2. You only need defined relations once. Writing a couple of lines more for clarity shouldn’t impact your productivity.

Vocabulary for the relation descriptions:

Require loops

Require loops (circular dependencies, circular requires) are a very common problem when defining relations. Whenever a module A requires or imports module B that immediately (synchronously) requires or imports module A, you create a require loop that node.js or objection cannot solve automatically. A require loop usually leads to the other imported value to be an empty object which causes all kinds of problems. Objection attempts to detect these situations and mention the words require loop in the thrown error. Objection offers multiple solutions to this problem. See the circular dependency solutions examples in this section. In addition to objection’s solutions, you can always organize your code so that such loops are not created.

Query examples

The Person model used in the examples is defined here.

All queries are started with one of the Model methods query, $query or $relatedQuery. All these methods return a QueryBuilder instance that can be used just like a knex QueryBuilder.

Table queries

Each model class inherits the static query method from the Model base class. Use query to create queries to the table the model class represents. The return value of the query method is an instance of QueryBuilder that has all the methods a knex QueryBuilder has and more.

Fetch queries

Fetch all people from the database:

const people = await Person.query();

console.log(people[0] instanceof Person); // --> true
console.log('there are', people.length, 'People in total');
select "people".* from "people"

The return value of the query method is an instance of QueryBuilder that has all the methods a knex QueryBuilder has. Here is a simple example that uses some of them:

const middleAgedJennifers = await Person
  .query()
  .where('age', '>', 40)
  .andWhere('age', '<', 60)
  .andWhere('firstName', 'Jennifer')
  .orderBy('lastName')

console.log('The last name of the first middle aged Jennifer is');
console.log(middleAgedJennifers[0].lastName);
select "persons".* from "persons"
where "age" > 40
and "age" < 60
and "firstName" = 'Jennifer'
order by "lastName" asc

In addition to knex methods, the QueryBuilder has a lot of helpers for dealing with relations like the joinRelation method:

const people = await Person
  .query()
  .select('parent:parent.name as grandParentName')
  .joinRelation('parent.parent');

console.log(people[0].grandParentName)
select "parent:parent"."firstName" as "grandParentName"
from "persons"
inner join "persons" as "parent" on "parent"."id" = "persons"."parentId"
inner join "persons" as "parent:parent" on "parent:parent"."id" = "parent"."parentId"

The next example shows how easy it is to build complex queries:

const people = await Person
  .query()
  .select('persons.*', 'Parent.firstName as parentFirstName')
  .join('persons as parent', 'persons.parentId', 'parent.id')
  .where('persons.age', '<', Person.query().avg('persons.age'))
  .whereExists(Animal.query().select(1).where('persons.id', ref('animals.ownerId')))
  .orderBy('persons.lastName');

console.log(people[0].parentFirstName);
select "persons".*, "parent"."firstName" as "parentFirstName"
from "persons"
inner join "persons" as "parent" on "persons"."parentId" = "parent"."id"
where "persons"."age" < (select avg("persons"."age") from "persons")
and exists (select 1 from "animals" where "persons"."id" = "animals"."ownerId")
order by "persons"."lastName" asc

Fetch queries can be created simply by calling Model.query() and chaining query builder methods for the returned QueryBuilder instance. The query is executed by calling the then method, which converts the query into a Promise.

Insert queries

const jennifer = await Person
  .query()
  .insert({firstName: 'Jennifer', lastName: 'Lawrence'})

console.log(jennifer instanceof Person); // --> true
console.log(jennifer.firstName); // --> 'Jennifer'
console.log(jennifer.fullName()); // --> 'Jennifer Lawrence'
insert into "persons" ("firstName", "lastName") values ('Jennifer', 'Lawrence')

Insert queries are created by chaining the insert method to the query. See the insertGraph method for inserting object graphs.

Update queries

const numUpdated = await Person.query()
  .patch({lastName: 'Dinosaur'})
  .where('age', '>', 60)

console.log('all people over 60 years old are now dinosaurs');
console.log(numUpdated, 'people were updated');
update "persons" set "lastName" = 'Dinosaur' where "age" > 60
const updatedPerson = await Person
  .query()
  .patchAndFetchById(246, {lastName: 'Updated'});

console.log(updatedPerson.lastName); // --> Updated.
update "persons" set "lastName" = 'Updated' where "id" = 246
select "persons".* from "persons" where "id" = 246

Update queries are created by chaining the update or patch method to the query. The patch and update methods return the number of updated rows. If you want the freshly updated model as a result you can use the helper method patchAndFetchById and updateAndFetchById. On postgresql you can simply chain .returning('*') or take a look at this recipe for more ideas.

Delete queries

const numDeleted = await Person
  .query()
  .delete()
  .where(raw('lower("firstName")'), 'like', '%ennif%');

console.log(numDeleted, 'people were deleted');
delete from "persons" where lower("firstName") like '%ennif%'

Delete queries are created by chaining the delete method to the query. NOTE: The return value of the query will be the number of deleted rows. If you’re using Postgres take a look at this recipe if you’d like the deleted rows to be returned as Model instances.

Relation queries

While the static query method can be used to create a query to a whole table $relatedQuery method can be used to query a single relation. $relatedQuery returns an instance of QueryBuilder just like the query method.

Fetch queries

// `person` is an instance of `Person` model.
const pets = await person
  .$relatedQuery('pets')
  .where('species', 'dog')
  .orderBy('name');

console.log(person.pets === pets); // --> true
console.log(pets[0] instanceof Animal); // --> true
select "animals".* from "animals"
where "species" = 'dog'
and "animals"."ownerId" = 1
order by "name" asc

Simply call $relatedQuery('pets') for a model instance to fetch a relation for it. The relation name is given as the only argument. The return value is a QueryBuilder so you once again have all the query methods at your disposal. In many cases it’s more convenient to use eager loading to fetch relations. $relatedQuery is better when you only need one relation and you need to filter the query extensively.

By default the fetched related models are assigned to the parent model to a property by the same name as the relation. For example in our person.$relatedQuery('pets') example query, the return value would be assigned to person.pets. This behaviour can be modified using relatedFindQueryMutates. Also check out $setRelated and $appendRelated helpers.

Insert queries

Add a pet for a person:

// `person` is an instance of `Person` model.
const fluffy = await person
  .$relatedQuery('pets')
  .insert({name: 'Fluffy'});

console.log(person.pets.indexOf(fluffy) !== -1); // --> true
insert into "animals" ("name", "ownerId") values ('Fluffy', 1)

If you want to write columns to the join table of a many-to-many relation you first need to specify the columns in the extra array of the through object in relationMappings (see the examples behind the link). For example, if you specified an array extra: ['awesomeness'] in relationMappings then awesomeness is written to the join table in the following example:

// `person` is an instance of `Person` model.
const movie = await person
  .$relatedQuery('movies')
  .insert({name: 'The room', awesomeness: 9001});

console.log('best movie ever was added');
insert into "movies" ("name") values ('The room')
insert into "persons_movies" ("movieId", "personId", "awesomeness") values (14, 25, 9001)

Chain the insert method to the $relatedQuery call to insert a related object for a model instance. The query inserts a new object to the related table and updates the needed tables to create the relation. In case of many-to-many relation a row is inserted to the join table etc. Also check out insertGraph method for an alternative way to insert related models.

By default the inserted related models are appended to the parent model to a property by the same name as the relation. For example in our person.$relatedQuery('pets').insert(obj) example query, the return value would be appended to person.pets. This behaviour can be modified using relatedInsertQueryMutates. Also check out the $setRelated and $appendRelated helpers.

Update queries

See the API documentation of update method.

Delete queries

See the API documentation of delete method.

Relate queries

See the API documentation of relate method.

Unrelate queries

See the API documentation of unrelate method.

Eager loading

Fetch the pets relation for all results of a query:

const people = await Person
  .query()
  .eager('pets');

// Each person has the `.pets` property populated with Animal objects related
// through `pets` relation.
console.log(people[0].pets[0].name);
console.log(people[0].pets[0] instanceof Animal); // --> true

Fetch multiple relations on multiple levels:

const people = await Person
  .query()
  .eager('[pets, children.[pets, children]]');

// Each person has the `.pets` property populated with Animal objects related
// through `pets` relation. The `.children` property contains the Person's
// children. Each child also has the `pets` and `children` relations eagerly
// fetched.
console.log(people[0].pets[0].name);
console.log(people[1].children[2].pets[1].name);
console.log(people[1].children[2].children[0].name);

Here’s the previous query using the optional object notation

const people = await Person
  .query()
  .eager({
    pets: true,
    children: {
      pets: true,
      children: true
    }
  });

Fetch one relation recursively:

const people = await Person
  .query()
  .eager('[pets, children.^]');

// The children relation is from Person to Person. If we want to fetch the whole
// descendant tree of a person we can just say "fetch this relation recursively"
// using the `.^` notation.
console.log(people[0].children[0].children[0].children[0].children[0].firstName);

Limit recursion to 3 levels:

const people = await Person
  .query()
  .eager('[pets, children.^3]');

console.log(people[0].children[0].children[0].children[0].firstName);

Relations can be filtered using the modifyEager method:

const people = await Person
  .query()
  .eager('[children.[pets, movies], movies]')
  .modifyEager('children.pets', builder => {
    // Only select pets older than 10 years old for children.
    builder.where('age', '>', 10);
  });

Relations can also be filtered using named filters like this:

const people = await Person
  .query()
  .eager('[pets(orderByName, onlyDogs), children(orderByAge).[pets, children]]', {
    orderByName: (builder) => {
      builder.orderBy('name');
    },
    orderByAge: (builder) => {
      builder.orderBy('age');
    },
    onlyDogs: (builder) => {
      builder.where('species', 'dog');
    }
  });

console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);

Reusable named filters can be defined for models using namedFilters

// Person.js

class Person extends Model {
  static get namedFilters() {
    return {
      orderByAge: (builder) => {
        builder.orderBy('age');
      }
    };
  }
}

// Animal.js

class Animal extends Model {
  static get namedFilters() {
    return {
      orderByName: (builder) => {
        builder.orderBy('name');
      },
      onlyDogs: (builder) => {
        builder.where('species', 'dog');
      }
    };
  }
}

// somewhereElse.js

const people = await Person
  .query()
  .eager('children(orderByAge).[pets(onlyDogs, orderByName), movies]');

console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);

Relations can be aliased using as keyword:

const people = await Person
  .query()
  .eager(`[
    children(orderByAge) as kids .[
      pets(filterDogs) as dogs,
      pets(filterCats) as cats

      movies.[
        actors
      ]
    ]
  ]`);

console.log(people[0].kids[0].dogs[0].name);
console.log(people[0].kids[0].movies[0].id);

Example usage for allowEager in an express route:

expressApp.get('/people', async (req, res, next) => {
  const people = await Person
    .query()
    .allowEager('[pets, children.pets]')
    .eager(req.query.eager);

  res.send(people);
});

Eager loading algorithm can be changed using the eagerAlgorithm method:

const people = await Person
  .query()
  .eagerAlgorithm(Model.JoinEagerAlgorithm)
  .eager('[pets, children.pets]');

You can fetch an arbitrary graph of relations for the results of any query by chaining the eager method. eager takes a relation expression string as a parameter. In addition to making your life easier, eager queries avoid the “select N+1” problem and provide a great performance.

Because the eager expressions are strings (there’s also an optional object notation) they can be easily passed for example as a query parameter of an HTTP request. However, allowing the client to pass expressions like this without any limitations is not very secure. Therefore the QueryBuilder has the allowEager method. allowEager can be used to limit the allowed eager expression to a certain subset.

By giving expression [pets, children.pets] for allowEager the value passed to eager is allowed to be one of:

Examples of expressions that would cause the query to be rejected:

In addition to the eager method, relations can be fetched using the loadRelated and $loadRelated methods.

By default eager loading is done using multiple separate queries (for details see this blog post). You can choose to use a join based eager loading algorithm that only performs one single query to fetch the whole eager tree. You can select which algorithm to use per query using eagerAlgorithm method or per model by setting the defaultEagerAlgorithm property. All algorithms have their strengths and weaknesses, which are discussed in detail here.

Graph inserts

// The return value of `insertGraph` is the input graph converted into model instances.
// Inserted objects have ids added to them and related rows have foreign keys set, but
// no other columns get fetched from the database. You can use `insertGraphAndFetch`
// for that.
const graph = await Person
  .query()
  .insertGraph({
    firstName: 'Sylvester',
    lastName: 'Stallone',

    children: [{
      firstName: 'Sage',
      lastName: 'Stallone',

      pets: [{
        name: 'Fluffy',
        species: 'dog'
      }]
    }]
  });

The query above will insert ‘Sylvester’, 'Sage’ and 'Fluffy’ into db and create relationships between them as defined in the relationMappings of the models. Technically insertGraph builds a dependency graph from the object graph and inserts the models that don’t depend on any other models until the whole graph is inserted.

If you need to refer to the same model in multiple places you can use the special properties #id and #ref like this:

await Person
  .query()
  .insertGraph([{
    firstName: 'Jennifer',
    lastName: 'Lawrence',

    movies: [{
      "#id": 'silverLiningsPlaybook'
      name: 'Silver Linings Playbook',
      duration: 122
    }]
  }, {
    firstName: 'Bradley',
    lastName: 'Cooper',

    movies: [{
      "#ref": 'silverLiningsPlaybook'
    }]
  }]);

The query above will insert only one movie (the 'Silver Linings Playbook’) but both 'Jennifer’ and 'Bradley’ will have the movie related to them through the many-to-many relation movies. The #id can be any string. There are no format or length requirements for them. It is quite easy to create circular dependencies using #id and #ref. Luckily insertGraph detects them and rejects the query with a clear error message.

You can refer to the properties of other models anywhere in the graph using expressions of format #ref{<id>.<property>} as long as the reference doesn’t create a circular dependency. For example:

await Person
  .query()
  .insertGraph([{
    "#id": 'jenniLaw',
    firstName: 'Jennifer',
    lastName: 'Lawrence',

    pets: [{
      name: "I am the dog of #ref{jenniLaw.firstName} whose id is #ref{jenniLaw.id}",
      species: 'dog'
    }]
  }]);

The query above will insert a pet named I am the dog of Jennifer whose id is 523 for Jennifer. If #ref{} is used within a string, the references are replaced with the referred values inside the string. If the reference string contains nothing but the reference, the referred value is copied to it’s place preserving its type.

Existing rows can be related to newly inserted rows by using the relate option. relate can be true in which case all models in the graph that have an identifier get related. relate can also be an array of relation paths like ['children', 'children.movies.actors'] in which case only objects in those paths get related even if they have an idetifier.

await Person
  .query()
  .insertGraph([{
    firstName: 'Jennifer',
    lastName: 'Lawrence',

    movies: [{
      id: 2636
    }]
  }], {
    relate: true
  });

The query above would create a new person Jennifer Lawrence and add an existing movie (id = 2636) to its movies relation. The next query would do the same:

await Person
  .query()
  .insertGraph([{
    firstName: 'Jennifer',
    lastName: 'Lawrence',

    movies: [{
      id: 2636
    }]
  }], {
    relate: [
      'movies'
    ]
  });

If you need to mix inserts and relates inside a single relation, you can use the special property #dbRef

await Person
  .query()
  .insertGraph([{
    firstName: 'Jennifer',
    lastName: 'Lawrence',

    movies: [{
      "#dbRef": 2636
    }, {
      // This will be inserted with an id.
      id: 100,
      name: 'New movie'
    }]
  }]);

Arbitrary relation graphs can be inserted using the insertGraph method. This is best explained using examples, so check them out ➔.

See the allowInsert method if you need to limit which relations can be inserted using insertGraph method to avoid security issues. allowInsert works like allowEager.

If you are using Postgres the inserts are done in batches for maximum performance. On other databases the rows need to be inserted one at a time. This is because postgresql is the only database engine that returns the identifiers of all inserted rows and not just the first or the last one.

insertGraph operation is not atomic by default! You need to start a transaction and pass it to the query using any of the supported ways. See the section about transactions for more information.

You can read more about graph inserts from this blog post.

Graph upserts

For the following examples, assume this is the content of the database:

[{
  id: 1,
  firstName: 'Jennifer',
  lastName: 'Aniston',

  // This is a BelongsToOneRelation
  parent: {
    id: 2,
    firstName: 'Nancy',
    lastName: 'Dow'
  },

  // This is a HasManyRelation
  pets: [{
    id: 1,
    name: 'Doggo',
    species: 'Dog',
  }, {
    id: 2,
    name: 'Kat',
    species: 'Cat',
  }],

  // This is a ManyToManyRelation
  movies: [{
    id: 1,
    name: 'Horrible Bosses',

    reviews: [{
      id: 1,
      title: 'Meh',
      stars: 3,
      text: 'Meh'
    }]
  }, {
    id: 2
    name: 'Wanderlust',

    reviews: [{
      id: 2,
      title: 'Brilliant',
      stars: 5,
      text: 'Makes me want to travel'
    }]
  }]
}]

By default upsertGraph method updates the objects that have an id, inserts objects that don’t have an id and deletes all objects that are not present. Off course the delete only applies to relations and not the root. Here’s a basic example:

// The return value of `upsertGraph` is the input graph converted into model instances.
// Inserted objects have ids added to them related rows have foreign keys set but no other
// columns get fetched from the database. You can use `upsertGraphAndFetch` for that.
const graph = await Person
  .query()
  .upsertGraph({
    // This updates the `Jennifer Aniston` person since the id property is present.
    id: 1,
    firstName: 'Jonnifer',

    parent: {
      // This also gets updated since the id property is present. If no id was given
      // here, Nancy Dow would get deleted, a new Person John Aniston would
      // get inserted and related to Jennifer.
      id: 2,
      firstName: 'John',
      lastName: 'Aniston'
    },

    // Notice that Kat the Cat is not listed in `pets`. It will get deleted.
    pets: [{
      // Jennifer just got a new pet. Insert it and relate it to Jennifer. Notice
      // that there is no id!
      name: 'Wolfgang',
      species: 'Dog'
    }, {
      // It turns out Doggo is a cat. Update it.
      id: 1,
      species: 'Cat',
    }],

    // Notice that Wanderlust is missing from the list. It will get deleted.
    // It is also worth mentioning that the Wanderlust's `reviews` or any
    // other relations are NOT recursively deleted (unless you have
    // defined `ON DELETE CASCADE` or other hooks in the db).
    movies: [{
      id: 1,

      // Upsert graphs can be arbitrarily deep. This modifies the
      // reviews of "Horrible Bosses".
      reviews: [{
        // Update a review.
        id: 1,
        stars: 2,
        text: 'Even more Meh'
      }, {
        // And insert another one.
        stars: 5,
        title: 'Loved it',
        text: 'Best movie ever'
      }, {
        // And insert a third one.
        stars: 4,
        title: '4 / 5',
        text: 'Would see again'
      }]
    }]
  });

By giving relate: true and/or unrelate: true options as the second argument, you can change the behaviour so that instead of inserting and deleting rows, they are related and/or unrelated. Rows with no id still get inserted, but rows that have an id and are not currently related, get related.

const options = {
  relate: true,
  unrelate: true
};

await Person
  .query()
  .upsertGraph({
    // This updates the `Jennifer Aniston` person since the id property is present.
    id: 1,
    firstName: 'Jonnifer',

    // Unrelate the parent. This doesn't delete it.
    parent: null,

    // Notice that Kat the Cat is not listed in `pets`. It will get unrelated.
    pets: [{
      // Jennifer just got a new pet. Insert it and relate it to Jennifer. Notice
      // that there is no id!
      name: 'Wolfgang',
      species: 'Dog'
    }, {
      // It turns out Doggo is a cat. Update it.
      id: 1,
      species: 'Cat',
    }],

    // Notice that Wanderlust is missing from the list. It will get unrelated.
    movies: [{
      id: 1,

      // Upsert graphs can be arbitrarily deep. This modifies the
      // reviews of "Horrible Bosses".
      reviews: [{
        // Update a review.
        id: 1,
        stars: 2,
        text: 'Even more Meh'
      }, {
        // And insert another one.
        stars: 5,
        title: 'Loved it',
        text: 'Best movie ever'
      }]
    }, {
      // This is some existing movie that isn't currently related to Jennifer.
      // It will get related.
      id: 1253
    }]
  }, options);

relate and unrelate (and all other options) can also be lists of relation paths. In that case the option is only applied for the listed relations.

const options = {
  // Only enable `unrelate` functionality for these two paths.
  unrelate: ['pets', 'movies.reviews'],
  // Only enable `relate` functionality for 'movies' relation.
  relate: ['movies'],
  // Disable deleting for movies.
  noDelete: ['movies']
};

await Person
  .query()
  .upsertGraph({
    id: 1,

    // This gets deleted since `unrelate` list doesn't have 'parent' in it
    // and deleting is the default behaviour.
    parent: null,

    // Notice that Kat the Cat is not listed in `pets`. It will get unrelated.
    pets: [{
      // It turns out Doggo is a cat. Update it.
      id: 1,
      species: 'Cat'
    }],

    // Notice that Wanderlust is missing from the list. It will NOT get unrelated
    // or deleted since `unrelate` list doesn't contain `movies` and `noDelete`
    // list does.
    movies: [{
      id: 1,

      // Upsert graphs can be arbitrarily deep. This modifies the
      // reviews of "Horrible Bosses".
      reviews: [{
        // Update a review.
        id: 1,
        stars: 2,
        text: 'Even more Meh'
      }, {
        // And insert another one.
        stars: 5,
        title: 'Loved it',
        text: 'Best movie ever'
      }]
    }, {
      // This is some existing movie that isn't currently related to Jennifer.
      // It will get related.
      id: 1253
    }]
  }, options);

You can disable updates, inserts, deletes etc. for the whole upsertGraph operation or for individual relations by using the noUpdate, noInsert, noDelete etc. options. See UpsertGraphOptions docs for more info.

Arbitrary relation graphs can be upserted (insert + update + delete) using the upsertGraph method. This is best explained using examples, so check them out ➔.

By default upsertGraph method updates the objects that have an id, inserts objects that don’t have an id and deletes all objects that are not present. This functionality can be modified in many ways by providing UpsertGraphOptions object as the second argument.

The upsertGraph method works a little different than the other update and patch methods. When using upsertGraph any where or having methods are ignored. The models are updated based on the id properties in the graph. This is also clarified in the examples.

upsertGraph uses insertGraph under the hood for inserts. That means that you can insert object graphs for relations and use all insertGraph features like #ref references.

upsertGraph operation is not atomic by default! You need to start a transaction and pass it to the query using any of the supported ways. See the section about transactions for more information.

See the allowUpsert method if you need to limit which relations can be modified using upsertGraph method to avoid security issues. allowUpsert works like allowInsert.

Transactions

There are two ways to work with transactions in objection:

  1. Passing around a transaction object
  2. Binding models to a transaction

Passing around a transaction object

const { transaction } = require('objection');
// You can access `knex` instance anywhere you want.
// One way is to get it through any model.
const knex = Person.knex();

try {
  const scrappy = await transaction(knex, async (trx) => {
    const jennifer = await Person
      .query(trx)
      .insert({firstName: 'Jennifer', lastName: 'Lawrence'});

    const scrappy = await jennifer
      .$relatedQuery('pets', trx)
      .insert({name: 'Scrappy'});

    return scrappy;
  });
} catch (err) {
  console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
}

Alternatively transaction.start can be used.

const { transaction } = require('objection');
// You can access `knex` instance anywhere you want.
// One way is to get it through any model.
const knex = Person.knex();

let trx;
try {
  trx = await transaction.start(knex);

  const jennifer = await Person
    .query(trx)
    .insert({firstName: 'Jennifer', lastName: 'Lawrence'});

  const scrappy = await jennifer
    .$relatedQuery('pets', trx)
    .insert({name: 'Scrappy'});

  await trx.commit();
} catch (err) {
  await trx.rollback();

  console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
}

Note that you can pass either a normal knex instance or a transaction to query, $relatedQuery etc. allowing you to build helper functions and services that can be used with or without a transaction. When a transaction is not wanted, just pass in the normal knex instance:

// `db` can be either a transaction or a knex instance or even
// `null` or `undefined` if you have globally set the knex
// instance using `Model.knex(knex)`.
async function insertPersonAndPet(person, pet, db) {
  const person = await Person
    .query(db)
    .insert(person);

  return person
    .$relatedQuery('pets', db)
    .insert(pet);
}

// All following 3 ways to call insertPersonAndPet work:

// 1.
const trx = await transaction.start(Person.knex());
await insertPersonAndPet(person, pet, trx);
await trx.commit();

// 2.
await insertPersonAndPet(person, pet, trx);

// 3.
await insertPersonAndPet(person, pet);

A transaction is started by calling objection.transaction method. You need to pass a knex instance as the first argument. If you don’t have the knex instance otherwise available you can always access it through any Model using Model.knex() provided that you have set the knex instance globally using Model.knex(knex) at some point.

The second argument is a callback that gets passed a transaction object. The transaction object is actually just a knex transaction object and you can start the transaction just as well using knex.transaction function. You then need to pass the transaction to all queries you want to execute in that transaction. query, $query and $relatedQuery accept a transaction as their last argument.

The transaction is committed if the promise returned from the callback is resolved successfully. If the returned Promise is rejected or an error is thrown inside the callback the transaction is rolled back.

Another way to start a trasnsaction is the transaction.start function. See the examples.

Transactions in javascript are a bit of a PITA if you are used to threaded frameworks and languages like java. In those a single chain of operations (for example a single request) is handled in a dedicated thread. Transactions are usually started for the whole thread and every database operation you perform after the start automatically takes part in the transaction because they can access the thread local transaction and the framework can be sure that no other chain of operations (no other request) uses the same transaction.

In javascript there are no threads. We need to explicitly take care that our operations are executed in the correct transaction. Based on our experience the most transparent and least error-prone way to do this is to explicitly pass a transaction object to each operation explicitly.

Binding models to a transaction

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

try {
  const scrappy = await transaction(Person, Animal, async (Person, Animal) => {
    // Person and Animal inside this function are bound to a newly
    // created transaction. The Person and Animal outside this function
    // are not! Even if you do `require('./models/Person')` inside this
    // function and start a query using the required `Person` it will
    // NOT take part in the transaction. Only the actual objects passed
    // to this function are bound to the transaction.

    await Person
      .query()
      .insert({firstName: 'Jennifer', lastName: 'Lawrence'});

    return Animal
      .query()
      .insert({name: 'Scrappy'});
  });
} catch (err) {
  console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
}

You only need to give the transaction function the model classes you use explicitly. All the related model classes are implicitly bound to the same transaction:

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

try {
  const scrappy = await transaction(Person, async (Person) => {
    const jennifer = await Person
      .query()
      .insert({firstName: 'Jennifer', lastName: 'Lawrence'})

    // This creates a query using the `Animal` model class but we
    // don't need to give `Animal` as one of the arguments to the
    // transaction function because `jennifer` is an instance of
    // the `Person` that is bound to a transaction.
    return jennifer
      .$relatedQuery('pets')
      .insert({name: 'Scrappy'});
  });
} catch (err) {
  console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
}

The only way you can mess up with the transactions is if you explicitly start a query using a model class that is not bound to the transaction:

const { transaction } = require('objection');
const Person = require('./models/Person');
const Animal = require('./models/Animal');

await transaction(Person, async (BoundPerson) => {
  // This will be executed inside the transaction.
  const jennifer = await BoundPerson
    .query()
    .insert({firstName: 'Jennifer', lastName: 'Lawrence'});

  // OH NO! This query is executed outside the transaction
  // since the `Animal` class is not bound to the transaction.
  await Animal
    .query()
    .insert({name: 'Scrappy'});

  // OH NO! This query is executed outside the transaction
  // since the `Person` class is not bound to the transaction.
  // BoundPerson !== Person.
  await Person
    .query()
    .insert({firstName: 'Bradley'});
});

The transaction object is always passed as the last argument to the callback:

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

await transaction(Person, async (Person, trx) => {
  // `trx` is the knex transaction object.
  // It can be passed to `transacting`, `query` etc.
  // methods, or used as a knex query builder.

  const jennifer = await trx('persons').insert({firstName: 'Jennifer', lastName: 'Lawrence'});
  const scrappy = await Animal.query(trx).insert({name: 'Scrappy'});
  const fluffy = await Animal.query().transacting(trx).insert({name: 'Fluffy'});

  return {
    jennifer,
    scrappy,
    fluffy
  };
});

The second way to use transactions avoids passing around a transaction object by “binding” model classes to a transaction. You pass all models you want to bind as arguments to the objection.transaction method and as the last argument you provide a callback that receives copies of the models that have been bound to a newly started transaction. All queries started through the bound copies take part in the transaction and you don’t need to pass around a transaction object. Note that the models passed to the callback are actual copies of the models passed as arguments to objection.transaction and starting a query through any other object will not be executed inside a transaction.

Originally we advertised this way of doing transactions as a remedy to the transaction passing plaque but it has turned out to be pretty error-prone. This approach is handy for single inline functions that do a handful of operations, but becomes tricky when you have to call services and helper methods that also perform database queries. To get the helpers and service functions to participate in the transaction you need to pass around the bound copies of the model classes. If you require the same models in the helpers and start queries through them, they will not be executed in the transaction since the required models are not the bound copies, but the original models from which the copies were taken.

Documents

The address property of the Person model is defined as an object in the Person.jsonSchema:

const jennifer = await Person
  .query()
  .insert({
    firstName: 'Jennifer',
    lastName: 'Lawrence',
    age: 24,
    address: {
      street: 'Somestreet 10',
      zipCode: '123456',
      city: 'Tampere'
    }
  });

const jenniferFromDb = await Person
  .query()
  .findById(jennifer.id);

console.log(jennifer.address.city); // --> Tampere
console.log(jenniferFromDb.address.city); // --> Tampere

Objection.js makes it easy to store non-flat documents as table rows. All properties of a model that are marked as objects or arrays in the model’s jsonSchema are automatically converted to JSON strings in the database and back to objects when read from the database. The database columns for the object properties can be normal text columns. Postgresql has the json and jsonb data types that can be used instead for better performance and possibility to write queries to the documents. If you don’t want to use jsonSchema you can mark properties as objects using the jsonAttributes Model property.

Validation

All these will trigger the validation:

Person.fromJson({firstName: 'jennifer', lastName: 'Lawrence'});
await Person.query().insert({firstName: 'jennifer', lastName: 'Lawrence'});
await Person.query().update({firstName: 'jennifer', lastName: 'Lawrence'}).where('id', 10);
// Patch operation ignores the `required` property of the schema and only validates the
// given properties. This allows a subset of model's properties to be updated.
await Person.query().patch({age: 24}).where('age', '<', 24);

Validation errors provide detailed error message:

try {
  await Person.query().insert({firstName: 'jennifer'});
} catch (err) {
  console.log(err instanceof objection.ValidationError); // --> true
  console.log(err.data); // --> {lastName: [{message: 'required property missing', ...}]}
}

Error parameters returned by ValidationError could be used to provide custom error messages:

try {
  await Person.query().insert({firstName: 'jennifer'});
} catch (err) {
  let lastNameErrors = err.data.lastName;

  for (let i = 0; i < lastNameErrors.length; ++i) {
    let lastNameError = lastNameErrors[i];

    if (lastNameError.keyword === "required") {
      console.log('This field is required!');
    } else if (lastNameError.keyword === "minLength") {
      console.log('Must be longer than ' + lastNameError.params.limit)
    } else {
      console.log(lastNameError.message); // Fallback to default error message
    }
  }
}

JSON schema validation can be enabled by setting the jsonSchema property of a model class. The validation is ran each time a Model instance is created.

You rarely need to call $validate method explicitly, but you can do it when needed. If validation fails a ValidationError will be thrown. Since we use Promises, this usually means that a promise will be rejected with an instance of ValidationError.

See the recipe book for instructions if you want to use some other validation library.

Plugins

A curated list of plugins and modules for objection. Only plugins that follow the best practices are accepted on this list. Other modules like plugins for other frameworks and things that cannot be implemented following the best practices are an exception to this rule. If you are a developer or otherwise know of a good plugin/module for objection, please create a pull request or an issue to get it added to this list.

3rd party plugins

Other 3rd party modules

Plugin development best practices

Mixin is just a function that takes a class and returns an extended subclass.

function SomeMixin(Model) {
  // The returned class should have no name.
  return class extends Model {
    // Your modifications.
  };
}

Mixins can be then applied like this:

class Person extends SomeMixin(Model) {

}

This doesn’t work since mixins never modify the input:

// This does absolutely nothing.
SomeMixin(Model);

// Doesn't work!
class Person extends Model {

}

Multiple mixins:

class Person extends SomeMixin(SomeOtherMixin(Model)) {

}

There are a couple of helpers in objection main module for applying multiple mixins.

const { mixin, Model } = require('objection');

class Person extends mixin(Model, [
  SomeMixin,
  SomeOtherMixin,
  EvenMoreMixins,
  LolSoManyMixins,
  ImAMixinWithOptions({foo: 'bar'})
]) {

}
const { compose, Model } = require('objection');

const mixins = compose(
  SomeMixin,
  SomeOtherMixin,
  EvenMoreMixins,
  LolSoManyMixins,
  ImAMixinWithOptions({foo: 'bar'})
);

class Person extends mixins(Model) {

}

Mixins can also be used as decorators:

@SomeMixin
@MixinWithOptions({foo: 'bar'})
class Person extends Model {

}

When possible, objection.js plugins should be implemented as class mixins. A mixin is simply a function that takes a class as an argument and returns a subclass. Plugins should avoid modifying objection.Model, objection.QueryBuilder or any other global variables directly. See the example plugin for more info. There is also another example that should be followed if your plugin takes options or configuration parameters.

Contribution guide

Issues

You can use github issues to request features and file bug reports. An issue is also a good place to ask questions. We are happy to help out if you have reached a dead end, but please try to solve the problem yourself first. The gitter chat is also a good place to ask for help.

When creating an issue there are couple of things you need to remember:

  1. Update to the latest version of objection if possible and see if the problem remains. If updating is not an option you can still request critical bug fixes for older versions.

  2. Describe your problem. What is happening and what you expect to happen.

  3. Provide enough information about the problem for others to reproduce it. The fastest way to get your bug fixed or problem solved is to create a simple standalone app or a test case that demonstrates your problem. If that’s too much work then at least provide the code that fails with enough context and any possible stack traces. Please bear in mind that objection has hundreds of tests and if you run into a problem, say with insert function, it probably doesn’t mean that insert is totally and completely broken, but some small part of it you are using is. That’s why enough context is necessary.

Pull requests

If you have found a bug or want to add a feature, pull requests are always welcome! It’s better to create an issue first to open a discussion if the feature is something that should be added to objection. In case of bugfixes it’s also a good idea to open an issue indicating that you are working on a fix.

For a pull request to get merged it needs to have the following things:

  1. A good description of what the PR fixes or adds. You can just add a link to the corresponding issue.

  2. Test(s) that verifies the fix/feature. It’s possible to create a PR without tests and ask for someone else to write them but in that case it may take a long time or forever until someone finds time to do it. Untested code will never get merged!

  3. For features you also need to write documentation.

Development guide

Development setup

clone

git clone git@github.com:<your-account>/objection.js.git objection

create users and databases

psql -U postgres -c "CREATE USER objection SUPERUSER"
psql -U postgres -c "CREATE DATABASE objection_test"
mysql -u root -e "CREATE USER objection"
mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO objection"
mysql -u root -e "CREATE DATABASE objection_test"
  1. Fork objection in github

  2. Clone objection

  3. Install MySQL and PostgreSQL

  4. Create test users and databases

  5. Run npm test in objection’s root to see if everything works.

Develop

Code and tests need to be written in ES2015 subset supported by node 6.0.0. The best way to make sure of this is to develop with the correct node version. nvm is a great tool for swapping between node versions. prettier is used to format the code. Remember to run npm run prettier before committing code.

Recipe book

Raw queries

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

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

console.log(childAgeSums[0].childAgeSum);
const childAgeSums = await Person
  .query()
  .select(raw('coalesce(sum(??), 0) as ??', ['age', 'childAgeSum']))
  .groupBy('parentId');

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

To mix raw SQL with queries, use the raw function from the main module or the raw method of any Model subclass. The only difference between these two is that the raw function from the main module doesn’t depend on knex where as Model.raw() will throw if the model doesn’t have a knex instance installed. Both of these functions work just like the knex’s raw method. And of course you can just use knex.raw().

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

JSON queries

import { ref } from 'objection';

await Person
  .query()
  .select([
    'id',
    ref('jsonColumn:details.name').castText().as('name'),
    ref('jsonColumn:details.age').castInt().as('age')
  ])
  .join('animals', ref('persons.jsonColumn:details.name').castText(), '=', ref('animals.name'))
  .where('age', '>', ref('animals.jsonData:details.ageLimit'));

Individual json fields can be updated like this:

await Person
  .query()
  .patch({
    'jsonColumn:details.name': 'Jennifer',
    'jsonColumn:details.age': 29
  });

You can use the ref function from the main module to refer to json columns in queries. There is also a bunch of query building methods that have Json in their names. Check them out too.

See FieldExpression for more information about how to refer to json fields.

Json queries currently only work with postgres.

Custom id column

class Person extends Model {
  static get idColumn() {
    return 'person_id';
  }
}

ESNext:

class Person extends Model {
  static idColumn = 'person_id';
}

Name of the identifier column can be changed by setting the static idColumn property of a model class. Composite key can be defined by using an array of column names.

Custom validation

Additional validation:

class Person extends Model {
  $beforeInsert() {
    if (this.id) {
      throw new objection.ValidationError({
        message: 'identifier should not be defined before insert',
        type: 'MyCustomError',
        data: someObjectWithSomeData
      });
    }
  }
}

Modifying the Ajv based jsonSchema validation:

const AjvValidator = require('objection').AjvValidator;

class Model {
  static createValidator() {
    return new AjvValidator({
      onCreateAjv: (ajv) => {
        // Here you can modify the `Ajv` instance.
      },
      options: {
        allErrors: true,
        validateSchema: false,
        ownProperties: true,
        v5: true
      }
    });
  }
}

Replace jsonSchema validation with any other validation scheme by implementing a custom Validator:

// MyCustomValidator.js

const Validator = require('objection').Validator;

class MyCustomValidator extends Validator {
  validate(args) {
    // The model instance. May be empty at this point.
    const model = args.model;

    // The properties to validate. After validation these values will
    // be merged into `model` by objection.
    const json = args.json;

    // `ModelOptions` object. If your custom validator sets default
    // values or has the concept of required properties, you need to
    // check the `opt.patch` boolean. If it is true we are validating
    // a patch object (an object with a subset of model's properties).
    const opt = args.options;

    // A context object shared between the validation methods. A new
    // object is created for each validation operation. You can store
    // whatever you need in this object.
    const ctx = args.ctx;

    // Do your validation here and throw any exception if the
    // validation fails.
    doSomeValidationAndThrowIfFails(json);

    // You need to return the (possibly modified) json.
    return json;
  }

  beforeValidate(args) {
    // Takes the same arguments as `validate`. Usually there is no need
    // to override this.
    return super.beforeValidate(args);
  }

  afterValidate(args) {
    // Takes the same arguments as `validate`. Usually there is no need
    // to override this.
    return super.afterValidate(args);
  }
}

// BaseModel.js

const Model = require('objection').Model;

// Override the `createValidator` method of a `Model` to use the
// custom validator.
class BaseModel extends Model {
  static createValidator() {
    return new MyCustomValidator();
  }
}

If you want to use the json schema validation but add some custom validation on top of it you can override the $beforeValidate or $afterValidate method.

If you need to do validation on insert or update you can throw exceptions from the $beforeInsert and $beforeUpdate methods.

If you don’t want to use the built-in json schema validation, you can just ignore the jsonSchema property. It is completely optional. If you want to use some other validation library you need to implement a custom Validator (see the example).

Snake case to camel case conversion

Conversion in knex:

const Knex = require('knex');
const { knexSnakeCaseMappers } = require('objection');

const knex = Knex({
  client: 'postgres',

  connection: {
    host: '127.0.0.1',
    user: 'objection',
    database: 'objection_test'
  }

  // Merge `postProcessResponse` and `wrapIdentifier` mappers.
  ...knexSnakeCaseMappers()
});

Conversion in objection:

const { Model, snakeCaseMappers } = require('objection');

class Person extends Model {
  static get columnNameMappers() {
    return snakeCaseMappers();
  }
}

ESNext:

import { Model, snakeCaseMappers } from 'objection';

class Person extends Model {
  static columnNameMappers = snakeCaseMappers();
}

You may want to use snake_cased names in database and camelCased names in code. There are two ways to achieve this:

  1. Conversion in knex using knexSnakeCaseMappers. When the conversion is done on knex level everything is converted to camel case including properties and identifiers in relationMappings and queries. knexSnakeCaseMappers use knex’s postProcessResponse and wrapIdentifier hooks.

  2. Conversion in objection using snakeCaseMappers. When the conversion is done on objection level only database columns of the returned rows (model instances) are convered to camel case. You still need to use snake case in relationMappings and queries. Note that insert, patch, update and their variants still take objects in camel case. The reasoning is that objects passed to those methods usually come from the client that also uses camel case.

Paging

const result = await Person
  .query()
  .where('age', '>', 20)
  .page(5, 100);

console.log(result.results.length); // --> 100
console.log(result.total); // --> 3341

Any query can be paged using the page or range method.

Subqueries

You can use functions:

const peopleOlderThanAverage = await Person
  .query()
  .where('age', '>', builder => {
    builder.avg('age').from('persons');
  });

console.log(peopleOlderThanAverage);

Or QueryBuilders:

const peopleOlderThanAverage = await Person
  .query()
  .where('age', '>', Person.query().avg('age'));

console.log(peopleOlderThanAverage);

Subqueries can be written just like in knex: by passing a function in place of a value. A bunch of query building methods accept a function. See the knex.js documentation or just try it out. A function is accepted in most places you would expect. You can also pass QueryBuilder instances or knex queries instead of functions.

Joins

Normal knex-style join:

const people = await Person
  .query()
  .select('persons.*', 'parent.firstName as parentName')
  .join('persons as parent', 'persons.parentId', 'parent.id');

console.log(people[0].parentName);

joinRelation helper for joining relation graphs:

const people = await Person
  .query()
  .select('parent:parent.name as grandParentName')
  .joinRelation('parent.parent');

console.log(people[0].grandParentName);

Again, do as you would with a knex query builder. Objection also has helpers like the joinRelation method family.

PostgreSQL “returning” tricks

Insert and return a Model instance in 1 query:

const jennifer = await Person
  .query()
  .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
  .returning('*');

console.log(jennifer.createdAt); // NOW()-ish
console.log(jennifer.id); // Sequence ID

Update a single row by ID and return the updated Model instance in 1 query:

const jennifer = await Person
  .query()
  .patch({firstName: 'Jenn', lastName: 'Lawrence'})
  .where('id', 1234)
  .returning('*');

console.log(jennifer.updatedAt); // NOW()-ish
console.log(jennifer.firstName); // "Jenn"

Patch a Model instance and receive DB updates to Model instance in 1 query:

const updateJennifer = await jennifer
  .$query()
  .patch({firstName: 'J.', lastName: 'Lawrence'})
  .returning('*');

console.log(updateJennifer.updatedAt); // NOW()-ish
console.log(updateJennifer.firstName); // "J."

Delete all Persons named Jennifer and return the deleted rows as Model instances in 1 query:

const deletedJennifers = await Person
  .query()
  .delete()
  .where({firstName: 'Jennifer'})
  .returning('*');

console.log(deletedJennifers.length); // However many Jennifers there were
console.log(deletedJennifers[0].lastName); // Maybe "Lawrence"

Delete all of Jennifer’s dogs and return the deleted Model instances in 1 query:

const jennsDeletedDogs = await jennifer
  .$relatedQuery('pets')
  .delete()
  .where({'species': 'dog'})
  .returning('*');

console.log(jennsDeletedDogs.length); // However many dogs Jennifer had
console.log(jennsDeletedDogs[0].name); // Maybe "Fido"

Because PostgreSQL (and some others) support returning('*') chaining, you can actually insert a row, or update / patch / delete (an) existing row(s), and receive the affected row(s) as Model instances in a single query, thus improving efficiency. See the examples for more clarity.

Polymorphic associations

class Issue extends Model {
  static get relationMappings() {
    return {
      comments: {
        relation: Model.HasManyRelation,
        modelClass: Comment,
        filter: {commentableType: 'Issue'},
        beforeInsert(model) {
          model.commentableType = 'Issue';
        },
        join: {
          from: 'Issue.id',
          to: 'Comment.commentableId'
        }
      }
    };
  }
}

class PullRequest extends Model {
  static get relationMappings() {
    return {
      comments: {
        relation: Model.HasManyRelation,
        modelClass: Comment,
        filter: {commentableType: 'PullRequest'},
        beforeInsert(model) {
          model.commentableType = 'PullRequest';
        },
        join: {
          from: 'PullRequest.id',
          to: 'Comment.commentableId'
        }
      }
    };
  }
}

The {commentableType: 'Type'} filter adds a WHERE "commentableType" = 'Type' clause to the relation fetch query. The beforeInsert hook takes care of setting the type on insert.

Let’s assume we have tables Comment, Issue and PullRequest. Both Issue and PullRequest can have a list of comments. Comment has a column commentableId to hold the foreign key and commentableType to hold the related model type. Check out the first example for how to create relations for this setup ➔

This kind of associations don’t have referential integrity and should be avoided if possible. Instead, consider using the exclusive arc table pattern discussed here.

Timestamps

class Person extends Model {
  $beforeInsert() {
    this.created_at = new Date().toISOString();
  }

  $beforeUpdate() {
    this.updated_at = new Date().toISOString();
  }
}

You can implement the $beforeInsert and $beforeUpdate methods to set the timestamps. If you want to do this for all your models, you can simply create common base class that implements these methods.

Custom query builder

const QueryBuilder = require('objection').QueryBuilder;

class MyQueryBuilder extends QueryBuilder {
  // Some custom method.
  upsert(model) {
    if (model.id) {
      return this.update(model).where('id', model.id);
    } else {
      return this.insert(model);
    }
  }
}

class Person extends Model {
  static get QueryBuilder() {
    return MyQueryBuilder;
  }
}

Now you can do this:

await Person.query().upsert(person);

You can extend the QueryBuilder returned by Model.query(), modelInstance.$relatedQuery() and modelInstance.$query() methods by setting the model class’s static QueryBuilder.

If you want to set the custom query builder for all model classes you can just set the QueryBuilder property of the Model base class. A cleaner option would be to create your own Model subclass, set its QueryBuilder property and inherit all your models from the custom Model class.

Multi-tenancy

By default, the examples guide you to setup the database connection by setting the knex object of the Model base class. This doesn’t fly if you want to select the database based on the request as it sets the connection globally. There are (at least) two patterns for dealing with this kind of setup:

Model binding pattern

app.use((req, res, next) => {
  // Function that parses the tenant id from path, header, query parameter etc.
  // and returns an instance of knex. You should cache the knex instances and
  // not create a new one for each query.
  const knex = getDatabaseForRequest(req);

  req.models = {
    Person: Person.bindKnex(knex),
    Movie: Movie.bindKnex(knex),
    Animal: Animal.bindKnex(knex)
  };

  next();
});

app.get('/people', async (req, res) => {
  const people = await req.models.Person
    .query()
    .findById(req.params.id);

  res.send(people);
});

If you have a different database for each tenant, a useful pattern is to add a middleware that adds the models to req.models hash and then always use the models through req.models instead of requiring them directly. What bindKnex method actually does is that it creates an anonymous subclass of the model class and sets its knex connection. That way the database connection doesn’t change for the other requests that are currently being executed.

Database passing pattern

app.use((req, res, next) => {
  // Function that parses the tenant id from path, header, query parameter etc.
  // and returns an instance of knex. You should cache the knex instances and
  // not create a new one for each query.
  req.knex = getDatabaseForRequest(req);
  next();
});

app.get('/people', async (req, res) => {
  const people = await Person
    .query(req.knex)
    .findById(req.params.id);

  res.send(people)
});

Another option is to add the knex instance to the request using a middleware and not bind models at all (not even using Model.knex()). The knex instance can be passed to query, $query, and $relatedQuery methods as the last argument. This pattern forces you to design your services and helper methods in a way that you always need to pass in a knex instance. A great thing about this is that you can pass a transaction object instead. (the knex/objection transaction object is a query builder just like the normal knex instance). This gives you a fine grained control over your transactions.

SQL clause precedence and parentheses

await Person
  .query()
  .where('id', 1)
  .where(builder => {
    builder.where('foo', 2).orWhere('bar', 3);
  });

SQL:

select * from "persons" where "id" = 1 and ("foo" = 2 or "bar" = 3)

You can add parentheses to queries by passing a function to the where method.

Default values

class Person extends Model {
  static get jsonSchema() {
    return {
      type: 'object',
      properties: {
        gender: {
          type: 'string',
          enum: ['Male', 'Female', 'Other'],
          default: 'Female'
        }
      }
    };
  }
}

You can set the default values for properties using the default property in jsonSchema.

Composite keys

Specifying a composite primary key for a model:

class Person extends Model {
  static get idColumn() {
    return ['firstName', 'lastName', 'dateOfBirth'];
  }
}

Specifying a relation using a composite primary key and a composite foreign key:

class Person extends Model {
  static get tableName() {
    return 'persons';
  }

  static get relationMappings() {
    return {
      pets: {
        relation: Model.BelongsToOneRelation,
        modelClass: Animal,
        join: {
          from: [
            'persons.firstName',
            'persons.lastName',
            'persons.dateOfBirth'
          ],
          to: [
            'animals.ownerFirstName',
            'animals.ownerLastName',
            'animals.ownerDateOfBirth'
          ]
        }
      }
    };
  }
};

Compound keys are fully supported. Just give an array of columns where you would normally give a single column name. Composite primary key can be specified by setting an array of column names to the idColumn of a model class.

Here’s a list of methods that may help working with composite keys:

Getting count of relations

Let’s say you have a Tweet model and a Like model. Tweet has a HasManyRelation named likers to Like table. Now let’s assume you’d like to fetch a list of Tweets and get the number of likes for each of them without fetching the actual Like rows. This cannot be easily achieved using eager because of the way the queries are optimized (you can read more here). You can leverage SQL’s subqueries and the relatedQuery helper:

const tweets = await Tweet
  .query()
  .select(
    'Tweet.*',
    Tweet.relatedQuery('likers').count().as('numberOfLikes')
  );

console.log(tweets[4].numberOfLikes);

The generated SQL is something like this:

select "Tweet".*, (select count(*) from "Like" where "Like"."tweetId" = "Tweet"."id") as "numberOfLikes" from "Tweet"

Naturally you can add as many subquery selects as you like. For example you could also get the count of retweets in the same query. relatedQuery method works with all relations and not just HasManyRelation.

Error handling

An example error handler function that handles all possible errors. This example uses the objection-db-errors library. Note that you should never send the errors directly to the client as they may contains SQL and other information that reveals too much about the inner workings of your app.

const {
  ValidationError,
  NotFoundError
} = require('objection');

const {
  DBError,
  ConstraintViolationError,
  UniqueViolationError,
  NotNullViolationError,
  ForeignKeyViolationError,
  CheckViolationError,
  DataError
} = require('objection-db-errors');

// In this example `res` is an express response object.
function errorHandler(err, res) {
  if (err instanceof ValidationError) {
    switch (err.type) {
      case 'ModelValidation':
        res.status(400).send({
          message: err.message,
          type: 'ModelValidation',
          data: err.data
        });
        break;
      case 'RelationExpression':
        res.status(400).send({
          message: err.message,
          type: 'InvalidRelationExpression',
          data: {}
        });
        break;
      case 'UnallowedRelation':
        res.status(400).send({
          message: err.message,
          type: 'UnallowedRelation',
          data: {}
        });
        break;
      case 'InvalidGraph':
        res.status(400).send({
          message: err.message,
          type: 'InvalidGraph',
          data: {}
        });
        break;
      default:
        res.status(400).send({
          message: err.message,
          type: 'UnknownValidationError',
          data: {}
        });
        break;
    }
  } else if (err instanceof NotFoundError) {
    res.status(404).send({
      message: err.message,
      type: 'NotFound',
      data: {}
    });
  } else if (err instanceof UniqueViolationError) {
    res.status(409).send({
      message: err.message,
      type: 'UniqueViolation',
      data: {
        columns: err.columns,
        table: err.table,
        constraint: err.constraint
      }
    });
  } else if (err instanceof NotNullViolationError) {
    res.status(400).send({
      message: err.message,
      type: 'NotNullViolation',
      data: {
        column: err.column,
        table: err.table,
      }
    });
  } else if (err instanceof ForeignKeyViolationError) {
    res.status(409).send({
      message: err.message,
      type: 'ForeignKeyViolation',
      data: {
        table: err.table,
        constraint: err.constraint
      }
    });
  } else if (err instanceof CheckViolationError) {
    res.status(400).send({
      message: err.message,
      type: 'CheckViolation',
      data: {
        table: err.table,
        constraint: err.constraint
      }
    });
  } else if (err instanceof DataError) {
    res.status(400).send({
      message: err.message,
      type: 'InvalidData',
      data: {}
    });
  } else if (err instanceof DBError) {
    res.status(500).send({
      message: err.message,
      type: 'UnknownDatabaseError',
      data: {}
    });
  } else {
    res.status(500).send({
      message: err.message,
      type: 'UnknownError',
      data: {}
    });
  }
}

Objection throws four kinds of errors:

  1. ValidationError when an input that could come from the outside world is invalid. These inputs include model instances and POJOs, eager expressions object graphs etc. ValidationError has a type property that can be used to distinguish between the different error types.

  2. NotFoundError when throwIfNotFound was called for a query and no results were found.

  3. Database errors (unique violation error etc.) are thrown by the database client libraries and the error types depend on the library. You can use the objection-db-errors plugin to handle these.

  4. A basic javascript Error when a programming or logic error is detected. In these cases there is nothing the users can do and the only correct way to handle the error is to send a 500 response to the user and to fix the program.

See the example error handler that handles each error type.

Indexing PostgreSQL JSONB columns

Good reading on the subject:

General Inverted Indexes a.k.a. GIN

This is the index type which makes all JSONB set operations fast. All isSuperset / isSubset / hasKeys / hasValues etc. queries can use this index to speed ’em up. Usually this is the index you want and it may take around 30% extra space on the DB server.

If one likes to use only the subset/superset operators with faster and smaller index one can give an extra path_ops parameter when creating the index: “The path_ops index supports only the search path operator @> (see below), but produces a smaller and faster index for these kinds of searches.”. According to Marco Nenciarini’s post the speed up can be over 600% compared to full GIN index and the size of the index is reduced from ~30% -> ~20%.

Full GIN index to speed up all type of json queries:

.raw('CREATE INDEX on ?? USING GIN (??)', ['Hero', 'details'])

Partial GIN index to speed up all subset / superset type of json queries:

.raw('CREATE INDEX on ?? USING GIN (?? jsonb_path_ops)', ['Place', 'details'])

Index on Expression

Another type of index one may use for JSONB field is to create an expression index for example for a certain JSON field inside a column.

You might want to use these if you are using lots of .where(ref('jsonColumn:details.name').castText(), 'marilyn') type of queries, which cannot be sped up with GIN index.

Use of these indexes are more limited, but they are also somewhat faster than using GIN and querying e.g. { field: value } with subset operator. GIN indices also takes a lot of space in compared to expression index for certain field. So if you want to make just certain query to go extra fast you may consider using index on expression.

An expression index referring an internal details.name attribute of an object stored in jsonColumn:

.raw("CREATE INDEX on ?? ((??#>>'{details,name}'))", ['Hero', 'jsonColumn'])

Complete Migration Example and Created Tables / Indexes

Complete example how to try out different index choices.

Migration:

exports.up = (knex) => {
  return knex.schema
    .createTable('Hero', (table) => {
      table.increments('id').primary();
      table.string('name');
      table.jsonb('details');
      table.integer('homeId').unsigned()
        .references('id').inTable('Place');
    })
    .raw(
      'CREATE INDEX on ?? USING GIN (??)',
      ['Hero', 'details']
    )
    .raw(
      "CREATE INDEX on ?? ((??#>>'{type}'))",
      ['Hero', 'details']
    )
    .createTable('Place', (table) => {
      table.increments('id').primary();
      table.string('name');
      table.jsonb('details');
    })
    .raw(
      'CREATE INDEX on ?? USING GIN (?? jsonb_path_ops)',
      ['Place', 'details']
    );
};

Results following schema:

objection-jsonb-example=# \d "Hero"
            Table "public.Hero"
 Column  |          Type
---------+------------------------
 id      | integer
 name    | character varying(255)
 details | jsonb
 homeId  | integer
Indexes:
    "Hero_pkey" PRIMARY KEY, btree (id)
    "Hero_details_idx" gin (details)
    "Hero_expr_idx" btree ((details #>> '{type}'::text[]))

objection-jsonb-example=# \d "Place"
           Table "public.Place"
 Column  |          Type
---------+------------------------
 id      | integer
 name    | character varying(255)
 details | jsonb
Indexes:
    "Place_pkey" PRIMARY KEY, btree (id)
    "Place_details_idx" gin (details jsonb_path_ops)

Expression index is used for example for following query:

explain select * from "Hero" where details#>>'{type}' = 'Hero';

                           QUERY PLAN
----------------------------------------------------------------
 Index Scan using "Hero_expr_idx" on "Hero"
   Index Cond: ((details #>> '{type}'::text[]) = 'Hero'::text)

Ternary relationships

Assume we have the following Models:

  1. user (id, first_name, last_name)
  2. group (id, name)
  3. permission (id, label)
  4. user_group_permission (user_id, group_id, permission_id, extra_attribute)

Here’s how you could create your models:

// User.js
const { Model } = require("objection");

class User extends Model {
  static get tableName() { return "user"; }
  static get relationMappings() {
    return {
      groups: {
        relation: Model.ManyToManyRelation,
        modelClass: require("./Group"),
        join: {
          from: "user.id",
          through: {
            from: "user_group_permission.user_id",
            extra: ["extra_attribute"],
            to: "user_group_permission.group_id"
          },
          to: "group.id"
        }
      },
      permissions: {
        relation: Model.ManyToManyRelation,
        modelClass: require("./Permission"),
        join: {
          from: "user.id",
          through: {
            from: "user_group_permission.user_id",
            extra: ["extra_attribute"],
            to: "user_group_permission.permission_id"
          },
          to: "permission.id"
        }
      }
    };
  }
}

module.exports = User;
// Group.js
const { Model } = require("objection");

class Group extends Model {
  static get tableName() { return "group"; }
  static get relationMappings() {
    return {
      users: {
        relation: Model.ManyToManyRelation,
        modelClass: require("./User"),
        join: {
          from: "group.id",
          through: {
            from: "user_group_permission.group_id",
            extra: ["extra_attribute"],
            to: "user_group_permission.user_id"
          },
          to: "user.id"
        }
      },
      permissions: {
        relation: Model.ManyToManyRelation,
        modelClass: require("./Permission"),
        join: {
          from: "group.id",
          through: {
            from: "user_group_permission.group_id",
            extra: ["extra_attribute"],
            to: "user_group_permission.permission_id"
          },
          to: "permission.id"
        }
      }
    };
  }
}

module.exports = Group;
// Permission.js
const { Model } = require("objection");

class Permission extends Model {
  static get tableName() { return "permission"; }
  static get relationMappings() {
    return {
      users: {
        relation: Model.ManyToManyRelation,
        modelClass: require("./User"),
        join: {
          from: "permission.id",
          through: {
            from: "user_group_permission.permission_id",
            extra: ["extra_attribute"],
            to: "user_group_permission.user_id"
          },
          to: "user.id"
        }
      },
      groups: {
        relation: Model.ManyToManyRelation,
        modelClass: require("./Group"),
        join: {
          from: "permission.id",
          through: {
            from: "user_group_permission.permission_id",
            extra: ["extra_attribute"],
            to: "user_group_permission.group_id"
          },
          to: "group.id"
        }
      }
    };
  }
}

module.exports = Permission;
// UserGroupPermission.js
const { Model } = require("objection");

class UserGroupPermission extends Model {
  static get tableName() { return "user_group_permission"; }
  static get idColumn() { return ["user_id", "group_id", "permission_id"]; }
  static get relationMappings() {
    return {
      user: {
        relation: Model.BelongsToOneRelation,
        modelClass: require("./User"),
        join: {
          from: "user_group_permission.user_id",
          extra: ["extra_attribute"],
          to: "user.id"
        }
      },
      group: {
        relation: Model.BelongsToOneRelation,
        modelClass: require("./Group"),
        join: {
          from: "user_group_permission.group_id",
          extra: ["extra_attribute"],
          to: "group.id"
        }
      },
      permission: {
        relation: Model.BelongsToOneRelation,
        modelClass: require("./Permission"),
        join: {
          from: "user_group_permission.permission_id",
          extra: ["extra_attribute"],
          to: "permission.id"
        }
      }
    };
  }
}

module.exports = UserGroupPermission;

Here’s how you can query your models:

UserGroupPermission
  .query()
  .select(
    "first_name",
    "last_name",
    "label",
    "extra_attribute"
  )
  .joinRelation("[user, permission]")
  .where("group_id", "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx");
/*
{
  first_name: ... ,
  last_name: ... ,
  label: ... ,
  extra_attribute: ...
}
*/
UserGroupPermission
  .query()
  .eager("[user, permission]")
  .where("group_id", "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx");
/*
{
  user: {
    first_name: ... ,
    last_name: ...
  },
  group: {
    name: ...
  },
  permission: {
    label: ...
  },
  extra_attribute: ...
}
*/

Read more about ternary relationships on this issue.

API reference

NOTE: Everything not mentioned in the API documentation is considered private implementation and shouldn’t be relied upon. Private implemementation can change without any notice even between patch versions. Public API described here follows semantic versioning.

The main module

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

The main module is what you get when you import objection. It has a bunch of properties that are all documented elsewhere in the API docs.

Properties

Model

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

The model base class.

transaction

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

The transaction function.

ref

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

The ref helper function.

raw

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

The raw helper function.

lit

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

The lit helper function.

mixin

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

The mixin helper for applying plugins. See the examples behind this link.

compose

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

The compose helper for applying plugins. See the examples behind this link.

lodash

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

Lodash utility library used internally by objection.

Promise

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

Bluebird promise library used internally by objection.

knexSnakeCaseMappers

const { knexSnakeCaseMappers } = require('objection');
const Knex = require('knex');

const knex = Knex({
  client: 'postgres',

  connection: {
    host: '127.0.0.1',
    user: 'objection',
    database: 'objection_test'
  }

  // Merge `postProcessResponse` and `wrapIdentifier` mappers.
  ...knexSnakeCaseMappers()
});

For older nodes:

const Knex = require('knex');
const knexSnakeCaseMappers = require('objection').knexSnakeCaseMappers;

const knex = Knex(Object.assign({
  client: 'postgres',

  connection: {
    host: '127.0.0.1',
    user: 'objection',
    database: 'objection_test'
  }
}, knexSnakeCaseMappers()));

Documented here.

knexIdentifierMapping

const { knexIdentifierMapping } = require('objection');
const Knex = require('knex');

const knex = Knex({
  client: 'postgres',

  connection: {
    host: '127.0.0.1',
    user: 'objection',
    database: 'objection_test'
  }

  // Merge `postProcessResponse` and `wrapIdentifier` mappers.
  ...knexIdentifierMapping({
    MyId: 'id',
    MyProp: 'prop',
    MyAnotherProp: 'anotherProp'
  })
});

Note that you can pretty easily define the conversions in some static property of your model. In this example we have added a property column to jsonSchema and use that to create the mapping object.

const { knexIdentifierMapping } = require('objection');
const Knex = require('knex');
const path = require('path')
const fs = require('fs');

// Path to your model folder.
const MODELS_PATH = path.join(__dirname, 'models');

const knex = Knex({
  client: 'postgres',

  connection: {
    host: '127.0.0.1',
    user: 'objection',
    database: 'objection_test'
  }

  // Go through all models and add conversions using the custom property
  // `column` in json schema.
  ...knexIdentifierMapping(fs.readdirSync(MODELS_PATH)
    .filter(it => it.endsWith('.js'))
    .map(it => require(path.join(MODELS_PATH, it)))
    .reduce((mapping, modelClass) => {
      const properties = modelClass.jsonSchema.properties;
      return Object.keys(properties).reduce((mapping, propName) => {
        mapping[properties[propName].column] = propName;
        return mapping;
      }, mapping);
    }, {});
  )
});

For older nodes:

const Knex = require('knex');
const knexSnakeCaseMappers = require('objection').knexSnakeCaseMappers;

const knex = Knex(Object.assign({
  client: 'postgres',

  connection: {
    host: '127.0.0.1',
    user: 'objection',
    database: 'objection_test'
  }
}, knexIdentifierMapping({
  MyId: 'id',
  MyProp: 'prop',
  MyAnotherProp: 'anotherProp'
})));

Like knexSnakeCaseMappers, but can be used to make an arbitrary static mapping between column names and property names. In the examples, you would have identifiers MyId, MyProp and MyAnotherProp in the database and you would like to map them into id, prop and anotherProp in the code.

snakeCaseMappers

const { Model, snakeCaseMappers } = require('objection');

class Person extends Model {
  static get columnNameMappers() {
    return snakeCaseMappers();
  }
}

ESNext:

import { Model, snakeCaseMappers } from 'objection';

class Person extends Model {
  static columnNameMappers = snakeCaseMappers();
}

Documented here.

QueryBuilder

Query builder for Models.

This class is a wrapper around knex QueryBuilder. QueryBuilder has all the methods a knex QueryBuilder has and more. While knex QueryBuilder returns plain javascript objects, QueryBuilder returns Model subclass instances.

QueryBuilder is thenable, meaning that it can be used like a promise. You can return query builder from a then method of a promise and it gets chained just like a normal promise would.

The query is executed when one of its promise methods then(), catch(), map(), bind() or return() is called.

Static methods

forClass

var builder = QueryBuilder.forClass(modelClass);

Create QueryBuilder for a Model subclass. You rarely need to call this. Query builders are created using the Model.query() and other query methods.

Arguments
Argument Type Description
modelClass Model A Model class constructor
Return value
Type Description
QueryBuilder The created query builder

Global query building helpers

ref

const ref = require('objection').ref;
import { ref } from 'objection';

await Model.query()
  .select([
    'id',
    ref('Model.jsonColumn:details.name').castText().as('name'),
    ref('Model.jsonColumn:details.age').castInt().as('age')
  ])
  .join('OtherModel', ref('Model.jsonColumn:details.name').castText(), '=', ref('OtherModel.name'))
  .where('age', '>', ref('OtherModel.ageLimit'));

Factory function that returns a ReferenceBuilder instance, that makes it easier to refer to tables, columns, json attributes etc. ReferenceBuilder can also be used to type cast and alias the references.

See FieldExpression for more information about how to refer to json fields.

lit

import { lit, ref } from 'objection';

await Model
  .query()
  .where(ref('Model.jsonColumn:details'), '=', lit({name: 'Jennifer', age: 29}))

await Model
  .query()
  .insert({
    numbers: lit([1, 2, 3]).asArray().castTo('real[]')
  })

Factory function that returns a LiteralBuilder instance. LiteralBuilder helps build literals of different types.

raw

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

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

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

Factory function that returns a RawBuilder instance. RawBuilder is a wrapper for knex raw query that doesn’t depend on knex. Instances of RawBuilder are converted to knex raw instances lazily when the query is executed.

Query building methods

findById

const builder = queryBuilder.findById(id);
const person = await Person.query().findById(1);

Composite key:

const person = await Person.query().findById([1, '10']);
Arguments
Argument Type Description
id any| Array.<any>
Return value
Type Description
QueryBuilder this query builder for chaining.

findByIds

const builder = queryBuilder.findByIds([id1, id2]);
const [person1, person2] = await Person.query().findByIds([1, 2]);

Composite key:

const [person1, person2] = await Person.query().findByIds([[1, '10'], [2, '10']]);
Arguments
Argument Type Description
id any|any[] A List of identifiers.
Return value
Type Description
QueryBuilder this query builder for chaining.

findOne

const builder = queryBuilder.findOne(...whereArgs);
const person = await Person.query().findOne({firstName: 'Jennifer', lastName: 'Lawrence'});
const person = await Person.query().findOne('age', '>', 20);
const person = await Person.query().findOne(raw('random() < 0.5'));

Shorthand for where(...whereArgs).first().

Arguments
Argument Type Description
whereArgs …any Anything the where method accepts.
Return value
Type Description
QueryBuilder this query builder for chaining.

insert

const builder = queryBuilder.insert(modelsOrObjects);
const jennifer = await Person
  .query()
  .insert({firstName: 'Jennifer', lastName: 'Lawrence'});

console.log(jennifer.id);

Batch insert (Only works on Postgres):

const actors = await someMovie
  .$relatedQuery('actors')
  .insert([
    {firstName: 'Jennifer', lastName: 'Lawrence'},
    {firstName: 'Bradley', lastName: 'Cooper'}
  ]);

console.log(actors[0].firstName);
console.log(actors[1].firstName);

You can also give raw expressions and subqueries as values like this:

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

await Person
  .query()
  .insert({
    age: Person.query().avg('age'),
    firstName: raw("'Jenni' || 'fer'")
  });

Fields marked as extras for many-to-many relations in relationMappings are automatically written to the join table instead of the target table. The someExtra field in the following example is written to the join table if the extra array of the relation mapping contains the string 'someExtra'.

const jennifer = await someMovie
  .$relatedQuery('actors')
  .insert({
    firstName: 'Jennifer',
    lastName: 'Lawrence',
    someExtra: "I'll be written to the join table"
  });

console.log(jennifer.someExtra);

Creates an insert query.

The inserted objects are validated against the model’s jsonSchema. If validation fails the Promise is rejected with a ValidationError.

NOTE: The return value of the insert query only contains the properties given to the insert method plus the identifier. This is because we don’t make an additional fetch query after the insert. Using postgres you can chain returning('*') to the query to get all properties - see this recipe for some examples. If you use returning(['only', 'some', 'props']) note that the result object will still contain the input properies plus the properties listed in returning. On other databases you can use the insertAndFetch method.

Batch inserts only work on Postgres because Postgres is the only database engine that returns the identifiers of all inserted rows. knex supports batch inserts on other databases also, but you only get the id of the first (or last) inserted object as a result. If you need batch insert on other databases you can use knex directly through YourModel.knexQuery().

Arguments
Argument Type Description
modelsOrObjects Object|Model|Object[]|Model[]; Objects to insert
Return value
Type Description
QueryBuilder this query builder for chaining.

insertAndFetch

const builder = queryBuilder.insertAndFetch(modelsOrObjects);

Just like insert but also fetches the model afterwards.

Note that on postgresql you can just chain returning('*') to the normal insert method to get the same result without an additional query. See this recipe for some examples.

Arguments
Argument Type Description
modelsOrObjects Object|Model|Object[]|Model[]; Objects to insert
Return value
Type Description
QueryBuilder this query builder for chaining.

insertGraph

const builder = queryBuilder.insertGraph(graph, options);

See the section about graph inserts.

Arguments
Argument Type Description
graph Object|Model|Object[]|Model[]; Objects to insert
graph InsertGraphOptions Optional options.
Return value
Type Description
QueryBuilder this query builder for chaining.

insertGraphAndFetch

Exactly like insertGraph but also fetches the graph from the db after insert.

insertWithRelated

Alias for insertGraph.

insertWithRelatedAndFetch

Alias for insertGraphAndFetch.

update

const builder = queryBuilder.update(modelOrObject);
const numberOfAffectedRows = await Person
  .query()
  .update({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .where('id', 134);

console.log(numberOfAffectedRows);

You can also give raw expressions, subqueries and ref() as values like this:

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

await Person
  .query()
  .update({
    firstName: raw("'Jenni' || 'fer'"),
    lastName: 'Lawrence',
    age: Person.query().avg('age'),
    oldLastName: ref('lastName') // same as knex.raw('??', ['lastName'])
  });

Updating single value inside json column and referring attributes inside json columns (only with postgres) etc.:

await Person
  .query()
  .update({
    lastName: ref('someJsonColumn:mother.lastName').castText(),
    'detailsJsonColumn:address.street': 'Elm street'
  });

Creates an update query.

The update object is validated against the model’s jsonSchema. If validation fails the Promise is rejected with a ValidationError.

This method is meant for updating whole objects with all required properties. If you want to update a subset of properties use the patch method.

NOTE: The return value of the query will be the number of affected rows. If you want to update a single row and retrieve the updated row as a result, you may want to use the updateAndFetchById method or take a look at this recipe if you’re using Postgres.

Arguments
Argument Type Description
modelOrObject Object|Model The update object
Return value
Type Description
QueryBuilder this query builder for chaining.

updateAndFetchById

const builder = queryBuilder.updateAndFetchById(id, modelOrObject);
const updatedModel = await Person
  .query()
  .updateAndFetchById(134, {firstName: 'Jennifer', lastName: 'Lawrence', age: 24});

console.log(updatedModel.firstName);

You can also give raw expressions and subqueries as values like this:

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

await Person
  .query()
  .updateAndFetchById(134, {
    firstName: raw("'Jenni' || 'fer'"),
    lastName: 'Lawrence',
    age: Person.query().avg('age')
  });

Updates a single model by id and fetches it from the database afterwards.

The update object is validated against the model’s jsonSchema. If validation fails the Promise is rejected with a ValidationError.

This method is meant for updating whole objects with all required properties. If you want to update a subset of properties use the patchAndFetchById method.

NOTE: On postgresql you can just chain first() and returning('*') to the normal update method to get the same result without an additional query. See this recipe for some examples.

Arguments
Argument Type Description
id string|number Identifier of the model to update
modelOrObject Object|Model The update object
Return value
Type Description
QueryBuilder this query builder for chaining.

updateAndFetch

const builder = queryBuilder.updateAndFetch(modelOrObject);
const updatedModel = await person
  .$query()
  .updateAndFetch({firstName: 'Jennifer', lastName: 'Lawrence', age: 24});

console.log(updatedModel.firstName);

You can also give raw expressions and subqueries as values like this:

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

await person
  .$query()
  .updateAndFetch({
    firstName: raw("'Jenni' || 'fer'"),
    lastName: 'Lawrence',
    age: Person.query().avg('age')
  });

Updates a single model and fetches it from the database afterwards. This only works with instance queries started with $query() method.

The update object is validated against the model’s jsonSchema. If validation fails the Promise is rejected with a ValidationError.

This method is meant for updating whole objects with all required properties. If you want to update a subset of properties use the patchAndFetch method.

NOTE: On postgresql you can just chain first() and returning('*') to the normal update method to get the same result without an additional query. See this recipe for some examples.

Arguments
Argument Type Description
modelOrObject Object|Model The update object
Return value
Type Description
QueryBuilder this query builder for chaining.

upsertGraph

const builder = queryBuilder.upsertGraph(modelOrObject, options);

See the section about graph upserts

Arguments
Argument Type Description
graph Object|Model|Object[]|Model[]; Graph to upsert.
options UpsertGraphOptions Optional options.
Return value
Type Description
QueryBuilder this query builder for chaining.

upsertGraphAndFetch

Exactly like upsertGraph but also fetches the graph from the db after the upsert operation.

patch

const builder = queryBuilder.patch(modelOrObject);
const numberOfAffectedRows = await Person
  .query()
  .patch({age: 24})
  .where('id', 134);

console.log(numberOfAffectedRows);

You can also give raw expressions, subqueries and ref() as values and FieldExpressions as keys:

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

await Person
  .query()
  .patch({
    age: Person.query().avg('age'),
    // You can use knex.raw instead of `raw()` if
    // you prefer.
    firstName: raw("'Jenni' || 'fer'"),
    oldLastName: ref('lastName'),
    // This updates a value nested deep inside a
    // json column `detailsJsonColumn`.
    'detailsJsonColumn:address.street': 'Elm street'
  });

Creates a patch query.

The patch object is validated against the model’s jsonSchema but the required property of the jsonSchema is ignored. This way the properties in the patch object are still validated but an error isn’t thrown if the patch object doesn’t contain all required properties.

Values specified using field expressions and literals are not validated.

If validation fails the Promise is rejected with a ValidationError.

NOTE: The return value of the query will be the number of affected rows. If you want to patch a single row and retrieve the patched row as a result, you may want to use the patchAndFetchById method or take a look at this recipe if you’re using Postgres.

Arguments
Argument Type Description
modelOrObject Object|Model The patch object
Return value
Type Description
QueryBuilder this query builder for chaining.

patchAndFetchById

const builder = queryBuilder.patchAndFetchById(id, modelOrObject);
const updatedModel = await Person
  .query()
  .patchAndFetchById(134, {age: 24});

console.log(updatedModel.firstName);

You can also give raw expressions and subqueries as values like this:

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

await Person
  .query()
  .patchAndFetchById(134, {
    age: Person.query().avg('age'),
    firstName: raw("'Jenni' || 'fer'")
  });

Patches a single model by id and fetches it from the database afterwards.

The patch object is validated against the model’s jsonSchema but the required property of the jsonSchema is ignored. This way the properties in the patch object are still validated but an error isn’t thrown if the patch object doesn’t contain all required properties.

If validation fails the Promise is rejected with a ValidationError.

NOTE: On postgresql you can just chain first() and returning('*') to the normal patch method to get the same result without an additional query. See this recipe for some examples.

Arguments
Argument Type Description
id string|number Identifier of the model to update
modelOrObject Object|Model The patch object
Return value
Type Description
QueryBuilder this query builder for chaining.

patchAndFetch

const builder = queryBuilder.patchAndFetch(modelOrObject);
const updatedModel = await person
  .$query()
  .patchAndFetch({age: 24});

console.log(updatedModel.firstName);

You can also give raw expressions and subqueries as values like this:

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

await person
  .$query()
  .patchAndFetch({
    age: Person.query().avg('age'),
    firstName: raw("'Jenni' || 'fer'")
  });

Patches a single model and fetches it from the database afterwards. This only works with instance queries started with $query() method.

The patch object is validated against the model’s jsonSchema but the required property of the jsonSchema is ignored. This way the properties in the patch object are still validated but an error isn’t thrown if the patch object doesn’t contain all required properties.

If validation fails the Promise is rejected with a ValidationError.

NOTE: On postgresql you can just chain first() and returning('*') to the normal patch method to get the same result without an additional query. See this recipe for some examples.

Arguments
Argument Type Description
modelOrObject Object|Model The patch object
Return value
Type Description
QueryBuilder this query builder for chaining.

delete

const builder = queryBuilder.delete();
const numberOfDeletedRows = await Person
  .query()
  .delete()
  .where('age', '>', 100)

console.log('removed', numberOfDeletedRows, 'people');

Creates a delete query.

The return value of the query will be the number of deleted rows. if you’re using Postgres and want to get the deleted rows, take a look at this recipe.

Return value
Type Description
QueryBuilder this query builder for chaining.

deleteById

const builder = queryBuilder.deleteById(id);
const numberOfDeletedRows = await Person
  .query()
  .deleteById(1)

console.log('removed', numberOfDeletedRows, 'people');

Composite key:

const numberOfDeletedRows = await Person
  .query()
  .deleteById([10, '20', 46]);

console.log('removed', numberOfDeletedRows, 'people');

Deletes a model by id.

The return value of the query will be the number of deleted rows. if you’re using Postgres and want to get the deleted rows, take a look at this recipe.

Arguments
Argument Type Description
id any|any[]
Return value
Type Description
QueryBuilder this query builder for chaining.

relate

const builder = queryBuilder.relate(ids);
const person = await Person
  .query()
  .findById(123);

const numRelatedRows = await person.$relatedQuery('movies').relate(50);
console.log('movie 50 is now related to person 123 through `movies` relation');

Relate multiple (only works with postgres)

const numRelatedRows = await person
  .$relatedQuery('movies')
  .relate([50, 60, 70]);

console.log(`${numRelatedRows} rows were related`);

Composite key

const numRelatedRows = await person
  .$relatedQuery('movies')
  .relate({foo: 50, bar: 20, baz: 10});

console.log(`${numRelatedRows} rows were related`);

Fields marked as extras for many-to-many relations in relationMappings are automatically written to the join table. The someExtra field in the following example is written to the join table if the extra array of the relation mapping contains the string 'someExtra'.

const numRelatedRows = await someMovie
  .$relatedQuery('actors')
  .relate({
    id: 50,
    someExtra: "I'll be written to the join table"
  });

console.log(`${numRelatedRows} rows were related`);

Relates an existing model to another model.

This method doesn’t create a new instance but only updates the foreign keys and in the case of many-to-many relation, creates a join row to the join table.

On Postgres multiple models can be related by giving an array of identifiers.

Arguments
Argument Type Description
ids number|string|Array|Object Identifier(s) of the model(s) to relate
Return value
Type Description
QueryBuilder this query builder for chaining.

unrelate

const builder = queryBuilder.unrelate();
const person = await Person
  .query()
  .findById(123)

const numUnrelatedRows = await person.$relatedQuery('movies')
  .unrelate()
  .where('id', 50);

console.log('movie 50 is no longer related to person 123 through `movies` relation');

Removes a connection between two models.

Doesn’t delete the models. Only removes the connection. For ManyToMany relations this deletes the join column from the join table. For other relation types this sets the join columns to null.

Note that, unlike for relate, you shouldn’t pass arguments for the unrelate method. Use unrelate like delete and filter the rows using the returned query builder.

Return value
Type Description
QueryBuilder this query builder for chaining.

alias

const builder = queryBuilder.alias(alias);
await Person
  .query()
  .alias('p')
  .where('p.id', 1)
  .join('persons as parent', 'parent.id', 'p.parentId')

Give an alias for the table to be used in the query.

Arguments
Argument Type Description
alias string Table alias for the query.
Return value
Type Description
QueryBuilder this query builder for chaining.

increment

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

decrement

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

select

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

forUpdate

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

forShare

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

timeout

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

as

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

columns

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

column

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

from

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

fromJS

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

into

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

with

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

withSchema

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

table

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

distinct

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

join

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

joinRaw

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

innerJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

leftJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

leftOuterJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

rightJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

rightOuterJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

outerJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

fullOuterJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

crossJoin

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

joinRelation

Joins a set of relations described by relationExpression. See the examples for more info.

const builder = queryBuilder.joinRelation(relationExpression, opt);

Join one relation:

await Person
  .query()
  .joinRelation('pets')
  .where('pets.species', 'dog');

Give an alias for a single relation:

await Person
  .query()
  .joinRelation('pets', {alias: 'p'})
  .where('p.species', 'dog');

Join two relations:

await Person
  .query()
  .joinRelation('[pets, parent]')
  .where('pets.species', 'dog')
  .where('parent.name', 'Arnold');

Join two multiple and nested relations. Note that when referring to nested relations : must be used as a separator instead of .. This limitation comes from the way knex parses table references.

await Person
  .query()
  .select('persons.id', 'parent:parent.name as grandParentName')
  .joinRelation('[pets, parent.[pets, parent]]')
  .where('parent:pets.species', 'dog');

Give aliases for a bunch of relations:

await Person
  .query()
  .select('persons.id', 'pr:pr.name as grandParentName')
  .joinRelation('[pets, parent.[pets, parent]]', {
    aliases: {
      parent: 'pr',
      pets: 'pt'
    }
  })
  .where('pr:pt.species', 'dog');
Arguments
Argument Type Description
relationExpression RelationExpression An expression describing which relations to join.
opt object Optional options. See the examples.
Return value
Type Description
QueryBuilder this query builder for chaining.

innerJoinRelation

Alias for joinRelation.

outerJoinRelation

Outer join version of the joinRelation method.

leftJoinRelation

Left join version of the joinRelation method.

leftOuterJoinRelation

Left outer join version of the joinRelation method.

rightJoinRelation

Right join version of the joinRelation method.

rightOuterJoinRelation

Left outer join version of the joinRelation method.

fullOuterJoinRelation

Full outer join version of the joinRelation method.

where

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

andWhere

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhere

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereNot

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereNot

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereRaw

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereWrapped

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

havingWrapped

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereRaw

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereExists

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereExists

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereNotExists

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereNotExists

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereIn

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereIn

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereNotIn

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereNotIn

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereNull

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereNull

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereNotNull

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereNotNull

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereBetween

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereNotBetween

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereBetween

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereNotBetween

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

groupBy

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

groupByRaw

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orderBy

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orderByRaw

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

union

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

unionAll

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

having

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

havingRaw

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orHaving

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

orHavingRaw

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

offset

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

limit

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

count

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

countDistinct

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

min

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

max

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

sum

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

avg

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

avgDistinct

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

debug

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

returning

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

truncate

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

connection

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

modify

See knex documentation

Arguments
Argument Type Description
fn function|string The modify callback function, receiving the builder as its first argument, followed by the optional arguments. If a string is provided, the call is redirected to applyFilter instead.
*arguments The optional arguments passed to the modify function
Return value
Type Description
QueryBuilder this query builder for chaining.

applyFilter

Applies named filters to the query builder.

Arguments
Argument Type Description
filter string The name of the filter, as found in namedFilters.
*arguments When providing multiple arguments, all provided named filters will be applied.
Return value
Type Description
QueryBuilder this query builder for chaining.

columnInfo

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereComposite

const builder = queryBuilder.whereComposite(columns, operator, values);
builder.whereComposite(['id', 'name'], '=', [1, 'Jennifer']);

This method also works with a single column - value pair:

builder.whereComposite('id', 1);

where for (possibly) composite keys.

Return value
Type Description
QueryBuilder this query builder for chaining.

whereInComposite

const builder = queryBuilder.whereInComposite(columns, values);
builder.whereInComposite(['a', 'b'], [[1, 2], [3, 4], [1, 4]]);
builder.whereInComposite('a', [[1], [3], [1]]);
builder.whereInComposite('a', [1, 3, 1]);
builder.whereInComposite(['a', 'b'], SomeModel.query().select('a', 'b'));

whereIn for (possibly) composite keys.

Return value
Type Description
QueryBuilder this query builder for chaining.

whereJsonSupersetOf

var builder = queryBuilder.whereJsonSupersetOf(fieldExpression, jsonObjectOrFieldExpression);
const people = await Person
  .query()
  .whereJsonSupersetOf('additionalData:myDogs', 'additionalData:dogsAtHome');

// These people have all or some of their dogs at home. Person might have some
// additional dogs in their custody since myDogs is supreset of dogsAtHome.

const people = await Person
  .query()
  .whereJsonSupersetOf('additionalData:myDogs[0]', { name: "peter"});

// These people's first dog name is "peter", but the dog might have
// additional attributes as well.

Object and array are always their own supersets.

For arrays this means that left side matches if it has all the elements listed in the right hand side. e.g.

[1,2,3] isSuperSetOf [2] => true
[1,2,3] isSuperSetOf [2,1,3] => true
[1,2,3] isSuperSetOf [2,null] => false
[1,2,3] isSuperSetOf [] => true

The not variants with jsonb operators behave in a way that they won’t match rows, which don’t have the referred json key referred in field expression. e.g. for table

 id |    jsonObject
----+--------------------------
  1 | {}
  2 | NULL
  3 | {"a": 1}
  4 | {"a": 1, "b": 2}
  5 | {"a": ['3'], "b": ['3']}

query:

builder.whereJsonNotEquals("jsonObject:a", "jsonObject:b")

Returns only the row 4 which has keys a and b and a != b, but it won’t return any rows which does not have jsonObject.a or jsonObject.b.

Where left hand json field reference is a superset of the right hand json value or reference.

Arguments
Argument Type Description
fieldExpression FieldExpression Reference to column / json field, which is tested for being a superset
jsonObjectOrFieldExpression Object|Array|FieldExpression To which to compare
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonSupersetOf

See whereJsonSupersetOf

whereJsonNotSupersetOf

See whereJsonSupersetOf

orWhereJsonNotSupersetOf

See whereJsonSupersetOf

whereJsonSubsetOf

const builder = queryBuilder.whereJsonSubsetOf(fieldExpression, jsonObjectOrFieldExpression);

Where left hand json field reference is a subset of the right hand json value or reference.

Object and array are always their own subsets.

See whereJsonSupersetOf

Arguments
Argument Type Description
fieldExpression FieldExpression Reference to column / json field, which is tested for being a superset
jsonObjectOrFieldExpression Object|Array|FieldExpression To which to compare
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonSubsetOf

See whereJsonSubsetOf

whereJsonNotSubsetOf

See whereJsonSubsetOf

orWhereJsonNotSubsetOf

See whereJsonSubsetOf

whereJsonIsArray

const builder = queryBuilder.whereJsonIsArray(fieldExpression);

Where json field reference is an array.

Arguments
Argument Type Description
fieldExpression FieldExpression
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonIsArray

See whereJsonIsArray

whereJsonNotArray

See whereJsonIsArray

orWhereJsonNotArray

See whereJsonIsArray

whereJsonIsObject

const builder = queryBuilder.whereJsonIsObject(fieldExpression);

Where json field reference is an object.

Arguments
Argument Type Description
fieldExpression FieldExpression
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonIsObject

See whereJsonIsObject

whereJsonNotObject

See whereJsonIsObject

orWhereJsonNotObject

See whereJsonIsObject

whereJsonHasAny

const builder = queryBuilder.whereJsonHasAny(fieldExpression, keys);

Where any of given strings is found from json object key(s) or array items.

Arguments
Argument Type Description
fieldExpression FieldExpression
keys string|string[] Strings that are looked from object or array
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonHasAny

See whereJsonHasAny

whereJsonHasAll

const builder = queryBuilder.whereJsonHasAll(fieldExpression, keys);

Where all of given strings are found from json object key(s) or array items.

Arguments
Argument Type Description
fieldExpression FieldExpression
keys string|string[] Strings that are looked from object or array
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonHasAll

See whereJsonHasAll

Other instance methods

context

const builder = queryBuilder.context(queryContext);

You can set the context like this:

await Person
  .query()
  .context({something: 'hello'});

and access the context like this:

const context = builder.context();

You can set any data to the context object. You can also register QueryBuilder lifecycle methods for all queries that share the context:

Person
  .query()
  .context({
    runBefore: (result, builder) => {
      return result;
    },
    runAfter: (result, builder) => {
      return result;
    },
    onBuild: builder => {}
  });

For example the eager method causes multiple queries to be executed from a single query builder. If you wanted to make all of them use the same schema you could write this:

Person
  .query()
  .eager('[movies, children.movies]')
  .context({
    onBuild: builder => {
      builder.withSchema('someSchema');
    }
  });

Sets/gets the query context.

Some query builder methods create more than one query. The query context is an object that is shared with all queries started by a query builder.

The context is also passed to $beforeInsert, $afterInsert, $beforeUpdate, $afterUpdate, $beforeDelete, $afterDelete and $afterGet calls that the query creates.

In addition to properties added using this method (and mergeContext) the query context object always has a transaction property that holds the active transaction. If there is no active transaction the transaction property contains the normal knex instance. In both cases the value can be passed anywhere where a transaction object can be passed so you never need to check for the existence of the transaction property.

See the methods runBefore, onBuild and runAfter for more information about the hooks.

Arguments
Argument Type Description
queryContext Object The query context object
Return value
Type Description
QueryBuilder this query builder for chaining.

mergeContext

const builder = queryBuilder.mergeContext(queryContext);

Merges values into the query context.

This method is like context but instead of replacing the whole context this method merges the objects.

Arguments
Argument Type Description
queryContext Object The object to merge into the query context.
Return value
Type Description
QueryBuilder this query builder for chaining.

tableNameFor

const tableName = queryBuilder.tableNameFor(modelClass);

Returns the table name for a given model class for the query. Usually the table name can be fetched through Model.tableName but if the source table has been changed for example using the QueryBuilder#table method tableNameFor will return the correct value.

Arguments
Argument Type Description
modelClass function A model class.
Return value
Type Description
string The source table (or view) name for modelClass.

tableRefFor

const tableRef = queryBuilder.tableRefFor(modelClass);

Returns the name that should be used to refer to the modelClass’s table in the query. Usually a table can be referred to using its name, but tableRefFor can return a different value for example in case an alias has been given.

Arguments
Argument Type Description
modelClass function A model class.
Return value
Type Description
string The name that should be used to refer to a table in the query.

reject

const builder = queryBuilder.reject(reason);

Skips the database query and “fakes” an error result.

Arguments
Argument Type Description
reson The rejection reason
Return value
Type Description
QueryBuilder this query builder for chaining.

resolve

const builder = queryBuilder.resolve(value);

Skips the database query and “fakes” a result.

Arguments
Argument Type Description
value The resolve value
Return value
Type Description
QueryBuilder this query builder for chaining.

isExecutable

const executable = queryBuilder.isExecutable();

Returns false if this query will never be executed.

This may be true in multiple cases:

  1. The query is explicitly resolved or rejected using the resolve or reject methods.
  2. The query starts a different query when it is executed.
Return value
Type Description
boolean false if the query will never be executed.

isFind

const isFind = queryBuilder.isFind();

Returns true if the query is read-only.

Return value
Type Description
boolean true if the query is read-only.

isInsert

const isInsert = queryBuilder.isInsert();

Returns true if the query performs an insert operation.

Return value
Type Description
boolean true if the query performs an insert operation.

isUpdate

const isUpdate = queryBuilder.isUpdate();

Returns true if the query performs an update operation.

Return value
Type Description
boolean true if the query performs an update operation.

isDelete

const isDelete = queryBuilder.isDelete();

Returns true if the query performs a delete operation.

Return value
Type Description
boolean true if the query performs a delete operation.

isRelate

const isRelate = queryBuilder.isRelate();

Returns true if the query performs a relate operation.

Return value
Type Description
boolean true if the query performs a relate operation.

isUnrelate

const isUnrelate = queryBuilder.isUnrelate();

Returns true if the query performs an unrelate operation.

Return value
Type Description
boolean true if the query performs an unrelate operation.

hasWheres

const hasWheres = queryBuilder.hasWheres();

Returns true if the query contains where statements.

Return value
Type Description
boolean true if the query contains where statements.

hasSelects

const hasSelects = queryBuilder.hasSelects();

Returns true if the query contains any specific select staments, such as: 'select', 'columns', 'column', 'distinct', 'count', 'countDistinct', 'min', 'max', 'sum', 'sumDistinct', 'avg', 'avgDistinct'

Return value
Type Description
boolean true if the query contains any specific select staments.

hasEager

const hasEager = queryBuilder.hasEager();

Returns true if the query defines any eager expressions.

Return value
Type Description
boolean true if the query defines any eager expressions.

has

const has = queryBuilder.has(selector);
console.log(Person.query().range(0, 4).has('range'));

Returns true if the query defines an operation that matches the given selector.

Arguments
Argument Type Description
selector string|regexp A name or regular expression to match all defined operations against.
Return value
Type Description
boolean true if the query defines an operation that matches the given selector.

clear

queryBuilder.clear(selector);
console.log(Person.query().orderBy('firstName').clear('orderBy').has('orderBy'));

Removes all operations in the query that match the given selector.

Arguments
Argument Type Description
selector string|regexp A name or regular expression to match all operations that are to be removed against.
Return value
Type Description
QueryBuilder this query builder for chaining.

runBefore

const builder = queryBuilder.runBefore(runBefore);
const query = Person.query();

query
  .runBefore(async result => {
    console.log('hello 1');

    await Promise.delay(10);

    console.log('hello 2');
    return result
  })
  .runBefore(result => {
    console.log('hello 3');
    return result
  });

await query;
// --> hello 1
// --> hello 2
// --> hello 3

Registers a function to be called before the database query when the builder is executed. Multiple functions can be chained like then methods of a promise.

Arguments
Argument Type Description
runBefore function(result, QueryBuilder) The function to be executed. This function can be async. Note that it needs to return the result used for further processing in the chain of calls.
Return value
Type Description
QueryBuilder this query builder for chaining.

onBuild

const builder = queryBuilder.onBuild(onBuild);
const query = Person.query();

query
 .onBuild(builder => {
   builder.where('id', 1);
 })
 .onBuild(builder => {
   builder.orWhere('id', 2);
 });

Functions registered with this method are called each time the query is built into an SQL string. This method is ran after runBefore methods but before runAfter methods.

If you need to modify the SQL query at query build time, this is the place to do it. You shouldn’t modify the query in any of the run methods.

Unlike the run methods (runAfter, runBefore etc.) these must be synchronous. Also you should not register any run methods from these. You should only call the query building methods of the builder provided as a parameter.

Arguments
Argument Type Description
onBuild function(QueryBuilder) The function to be executed.
Return value
Type Description
QueryBuilder this query builder for chaining.

onBuildKnex

const builder = queryBuilder.onBuildKnex(onBuildKnex);
const query = Person.query();

query
 .onBuildKnex((knexBuilder, objectionBuilder) => {
   knexBuilder.where('id', 1);
 });

Functions registered with this method are called each time the query is built into an SQL string. This method is ran after onBuild methods but before runAfter methods.

If you need to modify the SQL query at query build time, this is the place to do it in addition to onBuild. The only difference between onBuildKnex and onBuild is that in onBuild you can modify the objection’s query builder. In onBuildKnex the objection builder has been compiled into a knex query builder and any modifications to the objection builder will be ignored.

Unlike the run methods (runAfter, runBefore etc.) these must be synchronous. Also you should not register any run methods from these. You should only call the query building methods of the knexBuilder provided as a parameter.

WARNING: You should never call any query building (or any other mutating) method on the objectionBuilder in this function. If you do, those calls will get ignored. At this point the query builder has been compiled into a knex query builder and you should only modify that. You can call non mutating methods like hasSelects, hasWheres etc. on the objection builder.

Arguments
Argument Type Description
onBuildKnex function(KnexQueryBuilder, QueryBuilder) The function to be executed.
Return value
Type Description
QueryBuilder this query builder for chaining.

runAfter

const builder = queryBuilder.runAfter(runAfter);
const query = Person.query();

query
 .runAfter(async (models, queryBuilder) => {
   return models;
 })
 .runAfter(async (models, queryBuilder) => {
   models.push(Person.fromJson({firstName: 'Jennifer'}));
   return models;
 });

const models = await query;

Registers a function to be called when the builder is executed.

These functions are executed as the last thing before any promise handlers registered using the then method. Multiple functions can be chained like then methods of a promise.

Arguments
Argument Type Description
runAfter function(result, QueryBuilder) The function to be executed. This function can be async. Note that it needs to return the result used for further processing in the chain of calls.
Return value
Type Description
QueryBuilder this query builder for chaining.

onError

const builder = queryBuilder.onError(onError);
const query = Person.query();

query
 .onError(async (error, queryBuilder) => {
   // Handle `SomeError` but let other errors go through.
   if (error instanceof SomeError) {
     // This will cause the query to be resolved with an object
     // instead of throwing an error.
     return {error: 'some error occurred'};
   } else {
     return Promise.reject(error);
   }
 })
 .where('age', > 30);

Registers an error handler. Just like catch but doesn’t execute the query.

Arguments
Argument Type Description
onError function(Error, QueryBuilder) The function to be executed on error.
Return value
Type Description
QueryBuilder this query builder for chaining.

eagerAlgorithm

const builder = queryBuilder.eagerAlgorithm(algo);
const people = await Person
  .query()
  .eagerAlgorithm(Person.JoinEagerAlgorithm)
  .eager('[pets, children]')

Select the eager loading algorithm for the query. See comparison between the available algorithms here.

Arguments
Argument Type Description
algo EagerAlgorithm The eager loading algorithm to use. One of Model.JoinEagerAlgorithm, Model.WhereInEagerAlgorithm and Model.NaiveEagerAlgorithm.
Return value
Type Description
QueryBuilder this query builder for chaining.

eagerOptions

const builder = queryBuilder.eagerOptions(options);
const people = await Person
  .query()
  .eagerOptions({joinOperation: 'innerJoin'})
  .eager('[pets, children]')

Sets options for the eager query.

Arguments
Argument Type Description
options EagerOptions Options to set.s
Return value
Type Description
QueryBuilder this query builder for chaining.

eager

const builder = queryBuilder.eager(relationExpression, filters);
// Fetch `children` relation for each result Person and `pets` and `movies`
// relations for all the children.
const people = await Person
  .query()
  .eager('children.[pets, movies]');

console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);

Relations can be filtered by giving named filter functions as arguments to the relations:

const people = await Person
  .query()
  .eager('children(orderByAge).[pets(onlyDogs, orderByName), movies]', {
    orderByAge: (builder) => {
      builder.orderBy('age');
    },
    orderByName: (builder) => {
      builder.orderBy('name');
    },
    onlyDogs: (builder) => {
      builder.where('species', 'dog');
    }
  });

console.log(people[0].children[0].pets[0].name);
cconsole.log(people[0].children[0].movies[0].id);

Reusable named filters can be defined for a model class using namedFilters

class Person extends Model {
  static get namedFilters() {
    return {
      orderByAge: (builder) => {
        builder.orderBy('age');
      }
    };
  }
}

class Animal extends Model {
  static get namedFilters() {
    return {
      orderByName: (builder) => {
        builder.orderBy('name');
      },
      onlyDogs: (builder) => {
        builder.where('species', 'dog');
      }
    };
  }
}

const people = await Person
  .query()
  .eager('children(orderByAge).[pets(onlyDogs, orderByName), movies]');

console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);

Filters can also be registered using the modifyEager method:

const people = await Person
  .query()
  .eager('children.[pets, movies]')
  .modifyEager('children', builder => {
    // Order children by age.
    builder.orderBy('age');
  })
  .modifyEager('children.[pets, movies]', builder => {
    // Only select `pets` and `movies` whose id > 10 for the children.
    builder.where('id', '>', 10);
  })
  .modifyEager('children.movies]', builder => {
    // Only select 100 first movies for the children.
    builder.limit(100);
  });

console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);

Relations can be given aliases using the as keyword:

const people = await Person
  .query()
  .eager(`[
    children(orderByAge) as kids .[
      pets(filterDogs) as dogs,
      pets(filterCats) as cats

      movies.[
        actors
      ]
    ]
  ]`);

console.log(people[0].kids[0].dogs[0].name);
console.log(people[0].kids[0].movies[0].id);

The eager queries are optimized to avoid the N + 1 query problem. Consider this query:

const people = await Person
  .query()
  .where('id', 1)
  .eager('children.children');

console.log(people[0].children.length); // --> 10
console.log(people[0].children[9].children.length); // --> 10

The person has 10 children and they all have 10 children. The query above will return 100 database rows but will generate only three database queries when using WhereInEagerAlgorithm and only one query when using JoinEagerAlgorithm.

The loading algorithm can be changed using the eagerAlgorithm method:

const people = await Person
  .query()
  .where('id', 1)
  .eagerAlgorithm(Person.JoinEagerAlgorithm)
  .eager('[movies, children.pets]')
  .where('movies.name', 'like', '%terminator%')
  .where('children:pets.species', 'dog');

console.log(people);

Fetch relations eagerly for the result rows.

See the eager queries section for more examples and RelationExpression for more info on the relation expression language.

You can choose the way objection performs the eager loading by using eagerAlgorithm method on a query builder or by setting the defaultEagerAlgorithm property of a model. The three algorithms currently available are Model.WhereInEagerAlgorithm (the default) Model.JoinEagerAlgorithm and Model.NaiveEagerAlgorithm. All three have their strengths and weaknesses. We will go through the main differences below. You can always see the executed SQL by calling the debug method for the query builder.

WhereInEagerAlgorithm

This algorithm uses multiple queries to fetch the related objects. Objection performs one query per level in the eager tree. For example only two additional queries will be created for eager expression children.children no matter how many children the model has or how many children each of the children have. This algorithm is explained in detail in this blog post.

Limitations:

JoinEagerAlgorithm

This algorithm uses joins to fetch the whole eager tree using one single query. This allows you to reference the relations in the root query (see the last example). The related tables can be referred using the relation name. Nested relations must be separated by : character (dot is not used because of the way knex parses identifiers).

When this algorithm is used, information schema queries are executed to get table column names. They are done only once for each table during the lifetime of the process and then cached.

Limitations:

NaiveEagerAlgorithm

This algorithm naively fetches the relations using a separate query for each model. For example relation expression children.children will cause 111 queries to be performed assuming a result set of 10 each having 10 children each having 10 children. For small result sets this doesn’t matter. The clear benefit of this algorithm is that there are no limitations. You can use offset, limit, min, max etc. in modifyEager. You can for example fetch only the youngest child for each parent.

Performance differences

WhereInEagerAlgorithm performs more queries than JoinEagerAlgorithm which can cause a significant delay especially if the round trip time to the database server is significant. On the other hand the result from WhereInEagerAlgorithm is trivial to parse into a tree structure while the result of JoinEagerAlgorithm needs some complex parsing which can lead to a significant performance decrease. Which method is faster depends heavily on the query and the environment. You should select the algorithm that makes your code cleaner and only consider performance if you have an actual measured real-life problem. Don’t optimize prematurely! NaiveEagerAlgorithm is by far the slowest. It should only be used for cases where performance doesn’t matter and when it is the only option to get the job done.

Arguments
Argument Type Description
relationExpression string|RelationExpression The eager expression
filters Object<string, function(QueryBuilder)> The named filter functions for the expression
Return value
Type Description
QueryBuilder this query builder for chaining.

joinEager

Shorthand for eagerAlgorithm(Model.JoinEagerAlgorithm).eager(expr).

naiveEager

Shorthand for eagerAlgorithm(Model.NaiveEagerAlgorithm).eager(expr).

mergeEager

The following queries are equivalent

Person
  .query()
  .eager('[children.pets, movies]')
Person
  .query()
  .eager('children')
  .mergeEager('children.pets')
  .mergeEager('movies')
Person
  .query()
  .eager('children.pets')
  .mergeEager('movies')
Person
  .query()
  .mergeEager('children.pets')
  .mergeEager('movies')

Just like eager but instead of replacing query builder’s eager expression this method merges the given expression to the existing expression.

Arguments
Argument Type Description
relationExpression string|RelationExpression The eager expression
filters Object<string, function(QueryBuilder)> The named filter functions for the expression
Return value
Type Description
QueryBuilder this query builder for chaining.

mergeJoinEager

Shorthand for eagerAlgorithm(Model.JoinEagerAlgorithm).mergeEager(expr).

mergeNaiveEager

Shorthand for eagerAlgorithm(Model.NaiveEagerAlgorithm).mergeEager(expr).

allowEager

const builder = queryBuilder.allowEager(relationExpression);
Person
  .query()
  .allowEager('[children.pets, movies]')
  .eager(req.query.eager)

Sets the allowed eager expression.

Any subset of the allowed expression is accepted by eager method. For example setting the allowed expression to a.b.c expressions a, a.b and a.b.c are accepted by eager method. Setting any other expression will reject the query and cause the promise error handlers to be called.

This method is useful when the eager expression comes from an untrusted source like query parameters of a http request.

Arguments
Argument Type Description
relationExpression string|RelationExpression The allowed eager expression
Return value
Type Description
QueryBuilder this query builder for chaining.

mergeAllowEager

The following queries are equivalent

Person
  .query()
  .allowEager('[children.pets, movies]')
Person
  .query()
  .allowEager('children')
  .mergeAllowEager('children.pets')
  .mergeAllowEager('movies')
Person
  .query()
  .allowEager('children.pets')
  .mergeAllowEager('movies')
Person
  .query()
  .mergeAllowEager('children.pets')
  .mergeAllowEager('movies')

Just like allowEager but instead of replacing query builder’s allowEager expression this method merges the given expression to the existing expression.

Arguments
Argument Type Description
relationExpression string|RelationExpression The allowed eager expression
Return value
Type Description
QueryBuilder this query builder for chaining.

modifyEager

const builder = queryBuilder.modifyEager(pathExpression, modifier);

Can be used to modify the eager queries.

The pathExpression is a relation expression that specifies the queries for which the modifier is given.

The following query would filter out the children’s pets that are <= 10 years old:

Person
  .query()
  .eager('[children.[pets, movies], movies]')
  .modifyEager('children.pets', builder => {
    builder.where('age', '>', 10);
  })

The path expression can have multiple targets. The next example sorts both the pets and movies of the children by id:

Person
  .query()
  .eager('[children.[pets, movies], movies]')
  .modifyEager('children.[pets, movies]', builder => {
    builder.orderBy('id');
  })

This example only selects movies whose name contains the word ‘Predator’:

Person
  .query()
  .eager('[children.[pets, movies], movies]')
  .modifyEager('[children.movies, movies]', builder => {
    builder.where('name', 'like', '%Predator%');
  })
Arguments
Argument Type Description
pathExpression string|RelationExpression Expression that specifies the queries for which to give the filter.
modifier function(QueryBuilder The modifier function.
Return value
Type Description
QueryBuilder this query builder for chaining.

filterEager

Alias for modifyEager.

allowInsert

const builder = queryBuilder.allowInsert(relationExpression);
const insertedPerson = await Person
  .query()
  .allowInsert('[children.pets, movies]')
  .insertGraph({
    firstName: 'Sylvester',
    children: [{
      firstName: 'Sage',
      pets: [{
        name: 'Fluffy'
        species: 'dog'
      }, {
        name: 'Scrappy',
        species: 'dog'
      }]
    }]
  })

Sets the allowed tree of relations to insert using insertGraph method.

If the model tree given to the insertGraph method isn’t a subtree of the given expression, the query is rejected.

See methods eager, allowEager, RelationExpression and the section about eager queries for more information on relation expressions.

Arguments
Argument Type Description
relationExpression string|RelationExpression The allowed eager expression
Return value
Type Description
QueryBuilder this query builder for chaining.

allowUpsert

Just like allowInsert but this one works with upsertGraph.

castTo

const builder = queryBuilder.castTo(ModelClass);

The following example creates a query through Person, joins a bunch of relations, selects only the related Animal’s columns and returns the results as Animal instances instead of Person instances.

const animals = await Person
  .query()
  .joinRelation('children.children.pets')
  .select('children:children:pets.*')
  .castTo(Animal);

If your result rows represent no actual model, you can use objection.Model

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

const models = await Person
  .query()
  .joinRelation('children.pets')
  .select([
    'children:pets.id as animalId',
    'children.firstName as childFirstName'
  ])
  .castTo(Model);

Sets the model class of the result rows.

Return value
Type Description
ModelClass The model class of the result rows.
Return value
Type Description
QueryBuilder this query builder for chaining.

modelClass

const modelClass = queryBuilder.modelClass();

Gets the Model subclass this builder is bound to.

Return value
Type Description
Model The Model subclass this builder is bound to

toString

const sql = queryBuilder.toString();

Returns the SQL string suitable for logging input but not for execution, via Knex’s toString(). This method should not be used to create queries for database execution because it makes no guarantees about escaping bindings properly.

Note: In the current release, if the query builder attempts to execute multiple queries or throw any exception whatsoever, no error will throw and instead the following string is returned:

This query cannot be built synchronously. Consider using debug() method instead.

Later versions of Objection may introduce a native way to retrieve an executable SQL statement, or handle this behavior differently. If you need executable SQL, you can consider the unstable/private API this.build().toSQL(), which is the native Knex method that can provide formatted bindings.

Return value
Type Description
string The SQL this query builder will build, or This query cannot be built synchronously. Consider using debug() method instead. if an exception is thrown

toSql

const sql = queryBuilder.toSql();

An alias for toSql().

Note: The behavior of Objection’s toSql() is different from Knex’s toSql() (see above). This method may be deprecated soon.

Return value
Type Description
string The SQL this query builder will build, or This query cannot be built synchronously. Consider using debug() method instead. if an exception is thrown

skipUndefined

const builder = queryBuilder.skipUndefined();

If this method is called for a builder then undefined values passed to the query builder methods don’t cause an exception but are ignored instead.

For example the following query will return all Person rows if req.query.firstName is undefined.

Person
  .query()
  .skipUndefined()
  .where('firstName', req.query.firstName)
Return value
Type Description
QueryBuilder this query builder for chaining

transacting

const builder = queryBuilder.transacting(transaction);

Sets the transaction for a query.

Arguments
Argument Type Description
transaction object A transaction object
Return value
Type Description
QueryBuilder this query builder for chaining

clone

const clone = queryBuilder.clone();

Create a clone of this builder.

Type Description
QueryBuilder Clone of the query builder

execute

const promise = queryBuilder.execute();

Executes the query and returns a Promise.

Return value
Type Description
Promise Promise the will be resolved with the result of the query.

then

const promise = queryBuilder.then(successHandler, errorHandler);

Executes the query and returns a Promise.

Arguments
Argument Type Default Description
successHandler function identity Promise success handler
errorHandler function identity Promise error handler
Return value
Type Description
Promise Promise the will be resolved with the result of the query.

map

const promise = queryBuilder.map(mapper);

Executes the query and calls map(mapper) for the returned promise.

Arguments
Argument Type Default Description
mapper function identity Mapper function
Return value
Type Description
Promise Promise the will be resolved with the result of the query.

reduce

const promise = queryBuilder.reduce(reducer, initialValue);

Executes the query and calls reduce(reducer, initialValue) for the returned promise.

Arguments
Argument Type Default Description
reducer function undefined Reducer function
initialValue any first element of the reduced collection First arg for the

reducer function

Return value
Type Description
Promise Promise the will be resolved with the result of the query.

catch

const promise = queryBuilder.catch(errorHandler);

Executes the query and calls catch(errorHandler) for the returned promise.

Arguments
Argument Type Default Description
errorHandler function identity Error handler
Return value
Type Description
Promise Promise the will be resolved with the result of the query.

return

const promise = queryBuilder.return(returnValue);

Executes the query and calls return(returnValue) for the returned promise.

Arguments
Argument Type Default Description
returnValue undefined Return value
Return value
Type Description
Promise Promise the will be resolved with the result of the query.

bind

const promise = queryBuilder.bind(returnValue);

Executes the query and calls bind(context) for the returned promise.

Arguments
Argument Type Default Description
context undefined Bind context
Return value
Type Description
Promise Promise the will be resolved with the result of the query.

asCallback

const promise = queryBuilder.asCallback(callback);

Executes the query and calls asCallback(callback) for the returned promise.

Arguments
Argument Type Default Description
callback function undefined Node style callback
Return value
Type Description
Promise Promise the will be resolved with the result of the query.

nodeify

const promise = queryBuilder.nodeify(callback);

Executes the query and calls nodeify(callback) for the returned promise.

Arguments
Argument Type Default Description
callback function undefined Node style callback
Return value
Type Description
Promise Promise the will be resolved with the result of the query.

resultSize

const promise = queryBuilder.resultSize();
const query = Person
  .query()
  .where('age', '>', 20);

const [total, models] = await Promise.all([
  query.resultSize(),
  query.offset(100).limit(50)
]);

Returns the amount of rows the current query would produce without limit and offset applied. Note that this executes a query (not the one we are building) and returns a Promise.

Return value
Type Description
Promise Promise the will be resolved with the result size.

page

const builder = queryBuilder.page(page, pageSize);
const result = await Person
  .query()
  .where('age', '>', 20)
  .page(5, 100);

console.log(result.results.length); // --> 100
console.log(result.total); // --> 3341

Two queries are performed by this method: the actual query and a query to get the total count.

Mysql has the SQL_CALC_FOUND_ROWS option and FOUND_ROWS() function that can be used to calculate the result size, but according to my tests and the interwebs the performance is significantly worse than just executing a separate count query.

Postgresql has window functions that can be used to get the total count like this select count(*) over () as total. The problem with this is that if the result set is empty, we don’t get the total count either. (If someone can figure out a way around this, a PR is very welcome).

Arguments
Argument Type Description
page number The index of the page to return. The index of the first page is 0.
pageSize number The page size
Return value
Type Description
QueryBuilder this query builder for chaining

range

const builder = queryBuilder.range(start, end);
const result = await Person
  .query()
  .where('age', '>', 20)
  .range(0, 100);

console.log(result.results.length); // --> 101
console.log(result.total); // --> 3341

range can be called without arguments if you want to specify the limit and offset explicitly:

const result = await Person
  .query()
  .where('age', '>', 20)
  .limit(10)
  .range();

console.log(result.results.length); // --> 101
console.log(result.total); // --> 3341

Only returns the given range of results.

Two queries are performed by this method: the actual query and a query to get the total count.

Mysql has the SQL_CALC_FOUND_ROWS option and FOUND_ROWS() function that can be used to calculate the result size, but according to my tests and the interwebs the performance is significantly worse than just executing a separate count query.

Postgresql has window functions that can be used to get the total count like this select count(*) over () as total. The problem with this is that if the result set is empty, we don’t get the total count either. (If someone can figure out a way around this, a PR is very welcome).

Arguments
Argument Type Description
start number The index of the first result (inclusive)
end number The index of the last result (inclusive)
Return value
Type Description
QueryBuilder this query builder for chaining

pluck

const builder = queryBuilder.pluck(propertyName);
const firstNames = await Person
  .query()
  .where('age', '>', 20)
  .pluck('firstName');

console.log(typeof firstNames[0]); // --> string

If the result is an array, plucks a property from each object.

Arguments
Argument Type Description
propertyName string The name of the property to pluck
Return value
Type Description
QueryBuilder this query builder for chaining

first

const builder = queryBuilder.first();
const firstPerson = await Person
  .query()
  .first()

console.log(firstPerson.age);

If the result is an array, selects the first item.

NOTE: This doesn’t add limit 1 to the query by default. You can override the Model.useLimitInFirst property to change this behaviour.

Also see findById and findOne shorthand methods.

Return value
Type Description
QueryBuilder this query builder for chaining

throwIfNotFound

const builder = queryBuilder.throwIfNotFound();
try {
  await Language
    .query()
    .where('name', 'Java')
    .andWhere('isModern', true)
    .throwIfNotFound()
} catch (err) {
  // No results found.
  console.log(err instanceof Language.NotFoundError); // --> true
}

Causes a Model.NotFoundError to be thrown if the query result is empty.

You can replace Model.NotFoundError with your own error by implementing the static Model.createNotFoundError(ctx) method.

Return value
Type Description
QueryBuilder this query builder for chaining

traverse

var builder = queryBuilder.traverse(modelClass, traverser);
const people = await Person
  .query()
  .eager('pets')
  .traverse((model, parentModel, relationName) => {
    delete model.id;
  });

console.log(people[0].id); // --> undefined
console.log(people[0].pets[0].id); // --> undefined
const persons = await Person
  .query()
  .eager('pets')
  .traverse(Animal, (animal, parentModel, relationName) => {
    delete animal.id;
  });

console.log(persons[0].id); // --> 1
console.log(persons[0].pets[0].id); // --> undefined

Traverses through all models in the result, including the eagerly loaded relations.

The optional first parameter can be a constructor. If given, the traverser function is only called for the models of that class.

Arguments
Argument Type Description
modelClass Model The optional model class filter. If given, the traverser function is only called for models of this class.
traverser function(Model, Model, string) The traverser function that is called for each model. The first argument is the model itself. If the model is in a relation of some other model the second argument is the parent model and the third argument is the name of the relation.
Return value
Type Description
QueryBuilder this query builder for chaining

pick

const builder = queryBuilder.pick(modelClass, properties);

There are two ways to call this methods:

Person
  .query()
  .eager('pets').
  .pick(['id', 'name']);

and

Person
  .query()
  .eager('pets')
  .pick(Person, ['id', 'firstName'])
  .pick(Animal, ['id', 'name']);

Pick properties from result models.

The first example goes through all models (including relations) and discards all properties but id and name. The second example also traverses the whole model tree and discards all but id and firstName properties of all Person instances and id and name properties of all Animal instances.

Arguments
Argument Type Description
modelClass Model The optional model class filter
properties string[] The properties to pick
Return value
Type Description
QueryBuilder this query builder for chaining

omit

const builder = queryBuilder.omit(modelClass, properties);

There are two ways to call this methods:

Person
  .query()
  .eager('pets').
  .omit(['parentId', 'ownerId']);

and

Person
  .query()
  .eager('pets')
  .omit(Person, ['parentId', 'age'])
  .omit(Animal, ['ownerId', 'species']);

Omit properties of result models.

The first example goes through all models (including relations) and omits the properties parentId and ownerId. The second example also traverses the whole model tree and omits the properties parentId and age from all Person instances and ownerId and species properties of all Animal instances.

Arguments
Argument Type Description
modelClass Model The optional model class filter
properties string[] The properties to omit
Return value
Type Description
QueryBuilder this query builder for chaining

Model

class Person extends Model {
  // Table name is the only required property.
  static get tableName() {
    return 'persons';
  }

  // Optional JSON schema. This is not the database schema!
  // Nothing is generated based on this. This is only used
  // for validation. Whenever a model instance is created
  // it is checked against this schema.
  // http://json-schema.org/.
  static get jsonSchema () {
    return {
      type: 'object',
      required: ['firstName', 'lastName'],

      properties: {
        id: {type: 'integer'},
        parentId: {type: ['integer', 'null']},
        firstName: {type: 'string', minLength: 1, maxLength: 255},
        lastName: {type: 'string', minLength: 1, maxLength: 255},
        age: {type: 'number'},

        // Properties defined as objects or arrays are
        // automatically converted to JSON strings when
        // writing to database and back to objects and arrays
        // when reading from database. To override this
        // behaviour, you can override the
        // Person.jsonAttributes property.
        address: {
          type: 'object',
          properties: {
            street: {type: 'string'},
            city: {type: 'string'},
            zipCode: {type: 'string'}
          }
        }
      }
    };
  }

  // This object defines the relations to other models.
  static get relationMappings() {
    return {
      pets: {
        relation: Model.HasManyRelation,
        // The related model. This can be either a Model
        // subclass constructor or an absolute file path
        // to a module that exports one. We use the file
        // path version here to prevent require loops.
        modelClass: __dirname + '/Animal',
        join: {
          from: 'persons.id',
          to: 'animals.ownerId'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: __dirname + '/Movie',
        join: {
          from: 'persons.id',
          // ManyToMany relation needs the `through` object
          // to describe the join table.
          through: {
            from: 'persons_movies.actorId',
            to: 'persons_movies.movieId'

            // If you have a model class for the join table
            // you can specify it like this:
            //
            // modelClass: PersonMovie,

            // Columns listed here are automatically joined
            // to the related models on read and written to
            // the join table instead of the related table
            // on insert.
            //
            // extra: ['someExtra']
          },
          to: 'movies.id'
        }
      },

      children: {
        relation: Model.HasManyRelation,
        modelClass: Person,
        join: {
          from: 'persons.id',
          to: 'persons.parentId'
        }
      },

      parent: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'persons.parentId',
          to: 'persons.id'
        }
      }
    };
  }
}

Subclasses of this class represent database tables.

Model lifecycle

For the purposes of this explanation, let’s define three data layouts:

  1. database: The data layout returned by the database.
  2. internal: The data layout of a model instance.
  3. external: The data layout after calling model.toJSON().

Whenever data is converted from one layout to another, converter methods are called:

  1. database -> $parseDatabaseJson -> internal
  2. internal -> $formatDatabaseJson -> database
  3. external -> $parseJson -> internal
  4. internal -> $formatJson -> external

So for example when the results of a query are read from the database the data goes through the $parseDatabaseJson method. When data is written to database it goes through the $formatDatabaseJson method.

Similarly when you give data for a query (for example query().insert(req.body)) or create a model explicitly using Model.fromJson(obj) the $parseJson method is invoked. When you call model.toJSON() or model.$toJson() the $formatJson is called.

Note: Most libraries like express automatically call the toJSON method when you pass the model to methods like response.json(model). You rarely need to call toJSON() or $toJson() explicitly.

By overriding the lifecycle methods, you can have different layouts for the data in database and when exposed to the outside world. See this recipe for an example usage of the lifecycle methods.

All instance methods of models are prefixed with $ letter so that they won’t overlap with database properties. All properties that start with $ are also removed from database and external layouts.

Static properties

tableName

class Person extends Model {
  static get tableName() {
    return 'persons';
  }
}

ESNext:

class Person extends Model {
  static tableName = 'persons';
}

Name of the database table for this model.

Each model must set this.

jsonSchema

class Person extends Model {
  static get jsonSchema() {
    return {
      type: 'object',
      required: ['firstName', 'lastName'],

      properties: {
        id: {type: 'integer'},
        parentId: {type: ['integer', 'null']},
        firstName: {type: 'string', minLength: 1, maxLength: 255},
        lastName: {type: 'string', minLength: 1, maxLength: 255},
        age: {type: 'number'},

        // Properties defined as objects or arrays are
        // automatically converted to JSON strings when
        // writing to database and back to objects and arrays
        // when reading from database. To override this
        // behaviour, you can override the
        // Person.jsonAttributes property.
        address: {
          type: 'object',
          properties: {
            street: {type: 'string'},
            city: {type: 'string'},
            zipCode: {type: 'string'}
          }
        }
      }
    };
  }
}

ESNext:

class Person extends Model {
  static jsonSchema = {
    type: 'object',
    required: ['firstName', 'lastName'],

    properties: {
      id: {type: 'integer'},
      parentId: {type: ['integer', 'null']},
      firstName: {type: 'string', minLength: 1, maxLength: 255},
      lastName: {type: 'string', minLength: 1, maxLength: 255},
      age: {type: 'number'},

      // Properties defined as objects or arrays are
      // automatically converted to JSON strings when
      // writing to database and back to objects and arrays
      // when reading from database. To override this
      // behaviour, you can override the
      // Person.jsonAttributes property.
      address: {
        type: 'object',
        properties: {
          street: {type: 'string'},
          city: {type: 'string'},
          zipCode: {type: 'string'}
        }
      }
    }
  };
}

The optional schema against which the JSON is validated.

The jsonSchema can be dynamically modified in the $beforeValidate method.

Must follow http://json-schema.org specification. If null no validation is done.

Read more:

idColumn

class Person extends Model {
  static get idColumn() {
    return 'some_column_name';
  }
}

ESNext:

class Person extends Model {
  static idColumn = 'some_column_name';
}

Name of the primary key column in the database table.

Composite id can be specified by giving an array of column names.

Defaults to 'id’.

modelPaths

class Person extends Model {
  static get modelPaths() {
    return [__dirname];
  }
}

Using a shared BaseModel superclass:

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

// models/BaseModel.js
class BaseModel extends Model {
  static get modelPaths() {
    return [__dirname];
  }
}

module.exports = {
  BaseModel
};

// models/Person.js
const { BaseModel } = require('./BaseModel');

class Person extends BaseModel {
  ...
}

ESNext:

class Person extends Model {
  static modelPaths = [__dirname];
}

A list of paths from which to search for models for relations.

A model class can be defined for a relation in relationMappings as

  1. A model class constructor
  2. An absolute path to a module that exports a model class
  3. A path relative to one of the paths in modelPaths array.

You probably don’t want to define modelPaths property for each model. Once again we recommend that you create a BaseModel super class for all your models and define shared configuration such as this there.

relationMappings

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

class Person extends Model {
  static get tableName() {
    return 'persons';
  }

  static get relationMappings() {
    return {
      pets: {
        relation: Model.HasManyRelation,
        modelClass: Animal,
        join: {
          from: 'persons.id',
          // Any of the `to` and `from` fields can also be
          // references to nested fields (or arrays of references).
          // Here the relation is created between `persons.id` and
          // `animals.json.details.ownerId` properties. The reference
          // must be casted to the same type as the other key.
          to: ref('animals.json:details.ownerId').castInt()
        }
      },

      father: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'persons.fatherId',
          to: 'persons.id'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: Movie,
        join: {
          from: 'persons.id',
          through: {
            from: 'persons_movies.actorId',
            to: 'persons_movies.movieId'

            // If you have a model class for the join table
            // you can specify it like this:
            //
            // modelClass: PersonMovie,

            // Columns listed here are automatically joined
            // to the related models on read and written to
            // the join table instead of the related table
            // on insert.
            //
            // extra: ['someExtra']
          },
          to: 'movies.id'
        }
      }
    };
  }
}

ESNext:

import { Model, ref } from 'objection';

class Person extends Model {
  static tableName = 'persons';

  static relationMappings = {
    pets: {
      relation: Model.HasManyRelation,
      modelClass: Animal,
      join: {
        from: 'persons.id',
        // Any of the `to` and `from` fields can also be
        // references to nested fields (or arrays of references).
        // Here the relation is created between `persons.id` and
        // `animals.json.details.ownerId` properties. The reference
        // must be casted to the same type as the other key.
        to: ref('animals.json:details.ownerId').castInt()
      }
    },

    father: {
      relation: Model.BelongsToOneRelation,
      modelClass: Person,
      join: {
        from: 'persons.fatherId',
        to: 'persons.id'
      }
    },

    movies: {
      relation: Model.ManyToManyRelation,
      modelClass: Movie,
      join: {
        from: 'persons.id',
        through: {
          from: 'persons_movies.actorId',
          to: 'persons_movies.movieId'

          // If you have a model class for the join table
          // you can specify it like this:
          //
          // modelClass: PersonMovie,

          // Columns listed here are automatically joined
          // to the related models on read and written to
          // the join table instead of the related table
          // on insert.
          //
          // extra: ['someExtra']
        },
        to: 'movies.id'
      }
    }
  };
}

This property defines the relations to other models.

relationMappings is an object (or a function that returns an object) whose keys are relation names and values are RelationMapping instances. The join property in addition to the relation type define how the models are related to one another. The from and to properties of the join object define the database columns through which the models are associated. Note that neither of these columns need to be primary keys. They can be any columns. In fact they can even be fields inside JSON columns (using the ref helper). In the case of ManyToManyRelation also the join table needs to be defined. This is done using the through object.

The modelClass passed to the relation mappings is the class of the related model. It can be one of the following:

  1. A model class constructor
  2. An absolute path to a module that exports a model class
  3. A path relative to one of the paths in modelPaths array.

The file path versions are handy for avoiding require loops.

See RelationMapping

RelationMapping
Property Type Description
relation function The relation type. One of Model.BelongsToOneRelation, Model.HasOneRelation, Model.HasManyRelation and Model.ManyToManyRelation.
modelClass Model|string Constructor of the related model class, an absolute path to a module that exports one or a path relative to modelPaths that exports a model class.
join RelationJoin Describes how the models are related to each other. See RelationJoin.
modify function(QueryBuilder)|string|object Optional modifier for the relation query. If specified as a function, it will be called each time before fetching the relation. If specified as a string, named filter with specified name will be applied each time when fetching the relation. If specified as an object, it will be used as an additional query parameter - e. g. passing {name: 'Jenny’} would additionally narrow fetched rows to the ones with the name 'Jenny’.
filter function(QueryBuilder)|string|object Alias for modify.
beforeInsert function(Model, QueryContext) Optional insert hook that is called for each inserted model instance. This function can be async.
RelationJoin
Property Type Description
from string|ReferenceBuilder|Array The relation column in the owner table. Must be given with the table name. For example persons.id. Composite key can be specified using an array of columns e.g. ['persons.a', 'persons.b']. Note that neither this nor to need to be foreign keys or primary keys. You can join any column to any column. You can even join nested json fields using the ref helper.
to string|ReferenceBuilder|Array The relation column in the related table. Must be given with the table name. For example movies.id. Composite key can be specified using an array of columns e.g. ['movies.a', 'movies.b']. Note that neither this nor from need to be foreign keys or primary keys. You can join any column to any column. You can even join nested json fields using the ref helper.
through RelationThrough Describes the join table if the models are related through one.
RelationThrough
Property Type Description
from string|ReferenceBuilder|Array The column that is joined to from property of the RelationJoin. For example Person_movies.actorId where Person_movies is the join table. Composite key can be specified using an array of columns e.g. ['persons_movies.a', 'persons_movies.b']. You can join nested json fields using the ref helper.
to string|ReferenceBuilder|Array The column that is joined to to property of the RelationJoin. For example Person_movies.movieId where Person_movies is the join table. Composite key can be specified using an array of columns e.g. ['persons_movies.a', 'persons_movies.b']. You can join nested json fields using the ref helper.
modelClass string|ModelClass If you have a model class for the join table, you should specify it here. This is optional so you don’t need to create a model class if you don’t want to.
extra string[]|Object Columns listed here are automatically joined to the related objects when they are fetched and automatically written to the join table instead of the related table on insert. The values can be aliased by providing an object {propertyName: 'columnName', otherPropertyName: 'otherColumnName'} instead of array
beforeInsert function(Model, QueryContext) Optional insert hook that is called for each inserted join table model instance. This function can be async.

jsonAttributes

class Person extends Model {
  static get jsonAttributes() {
    return ['someProp', 'someOtherProp'];
  }
}

ESNext:

class Person extends Model {
  static jsonAttributes = ['someProp', 'someOtherProp'];
}

Properties that should be saved to database as JSON strings.

The properties listed here are serialized to JSON strings upon insertion/update to the database and parsed back to objects when models are read from the database. Combined with the postgresql’s json or jsonb data type, this is a powerful way of representing documents as single database rows.

If this property is left unset all properties declared as objects or arrays in the jsonSchema are implicitly added to this list.

columnNameMappers

const { Model, snakeCaseMappers } = require('objection');

class Person extends Model {
  static get columnNameMappers() {
    return snakeCaseMappers();
  }
}

ESNext

import { Model, snakeCaseMappers } from 'objection';

class Person extends Model {
  static columnNameMappers = snakeCaseMappers();
}

The mapper signature:

class Person extends Model {
  static columnNameMappers = {
    parse(obj) {
      // database --> code
    },

    format(obj) {
      // code --> database
    }
  };
}

The mappers to use to convert column names to property names in code.

relatedFindQueryMutates

class Person extends Model {
  static get relatedFindQueryMutates() {
    return false;
  }
}

If this config is set to false, calling foo.$relatedQuery('bar') doesn’t assign the fetched related models to foo.bar. The default is true.

relatedInsertQueryMutates

class Person extends Model {
  static get relatedInsertQueryMutates() {
    return false;
  }
}

If this config is set to false, calling foo.$relatedQuery('bar').insert(obj) doesn’t append the inserted related model to foo.bar. The default is true.

virtualAttributes

class Person extends Model {
  static get virtualAttributes() {
    return ['fullName', 'isFemale'];
  }

  fullName() {
    return `${this.firstName} ${this.lastName}`;
  }

  get isFemale() {
    return this.gender === 'female';
  }
}

const person = Person.fromJson({
  firstName: 'Jennifer',
  lastName: 'Aniston',
  gender: 'female'
});

// Note that `toJSON` is always called automatically when an object is serialized
// to a JSON string using JSON.stringify. You very rarely need to call `toJSON`
// explicitly. koa, express and all other frameworks I'm aware of use JSON.stringify
// to serialize objects to JSON.
console.log(person.toJSON());
// --> {"firstName": "Jennifer", "lastName": "Aniston", "isFemale": true, "fullName": "Jennifer Aniston"}

ESNext:

class Person extends Model {
  static virtualAttributes = ['fullName', 'isFemale'];

  fullName() {
    return `${this.firstName} ${this.lastName}`;
  }

  get isFemale() {
    return this.gender === 'female';
  }
}

const person = Person.fromJson({
  firstName: 'Jennifer',
  lastName: 'Aniston',
  gender: 'female'
});

// Note that `toJSON` is always called automatically when an object is serialized
// to a JSON string using JSON.stringify. You very rarely need to call `toJSON`
// explicitly. koa, express and all other frameworks I'm aware of use JSON.stringify
// to serialize objects to JSON.
console.log(person.toJSON());
// --> {"firstName": "Jennifer", "lastName": "Aniston", "isFemale": true, "fullName": "Jennifer Aniston"}

Getters and methods listed here are serialized with real properties when toJSON is called.

The virtual values are not written to database. Only the “external” JSON format will contain them.

uidProp

class Person extends Model {
  static get uidProp() {
    return '#id';
  }
}

ESNext:

class Person extends Model {
  static uidProp = '#id';
}

Name of the property used to store a temporary non-db identifier for the model.

NOTE: You cannot use any of the model’s properties as uidProp. For example if your model has a property id, you cannot set uidProp = 'id'.

Defaults to ’#id’.

uidRefProp

class Person extends Model {
  static get uidRefProp() {
    return '#ref';
  }
}

ESNext:

class Person extends Model {
  static uidRefProp = '#ref';
}

Name of the property used to store a reference to a uidProp

NOTE: You cannot use any of the model’s properties as uidRefProp. For example if your model has a property ref, you cannot set uidRefProp = 'ref'.

Defaults to ’#ref’.

dbRefProp

class Person extends Model {
  static get dbRefProp() {
    return '#dbRef';
  }
}

ESNext:

class Person extends Model {
  static dbRefProp = '#dbRef';
}

Name of the property used to point to an existing database row from an insertGraph graph.

NOTE: You cannot use any of the model’s properties as dbRefProp. For example if your model has a property id, you cannot set dbRefProp = 'id'.

Defaults to ’#dbRef’.

propRefRegex

class Person extends Model {
  static get propRefRegex() {
    return /#ref{([^\.]+)\.([^}]+)}/g;
  }
}

ESNext:

class Person extends Model {
  static propRefRegex = /#ref{([^\.]+)\.([^}]+)}/g;
}

Regular expression for parsing a reference to a property.

Defaults to /#ref{([^\.]+)\.([^}]+)}/g.

pickJsonSchemaProperties

class Person extends Model {
  static get pickJsonSchemaProperties() {
    return true;
  }
}

ESNext:

class Person extends Model {
  static pickJsonSchemaProperties = true;
}

If this is true only properties in jsonSchema are picked when inserting or updating a row in the database.

Defaults to false.

defaultEagerAlgorithm

class Person extends Model {
  static get defaultEagerAlgorithm() {
    return Model.WhereInEagerAlgorithm;
  }
}

ESNext:

class Person extends Model {
  static defaultEagerAlgorithm = Model.WhereInEagerAlgorithm;
}

Sets the default eager loading algorithm for this model. Must be either Model.WhereInEagerAlgorithm or Model.JoinEagerAlgorithm.

Defaults to Model.WhereInEagerAlgorithm.

defaultEagerOptions

class Person extends Model {
  static get defaultEagerOptions() {
    return {
      minimize: true,
      separator: '->',
      aliases: {}
    };
  }
}

ESNext:

class Person extends Model {
  static defaultEagerOptions = {
    minimize: true,
    separator: '->',
    aliases: {}
  };
}

Sets the default options for eager loading algorithm. See the possible fields here.

Defaults to {minimize: false, separator: ':', aliases: {}}.

namedFilters

class Movie extends Model {
  static get namedFilters() {
    return {
      goodMovies: (builder) => builder.where('stars', '>', 3),
      orderByName: (builder) => builder.orderBy('name')
    };
  }
}

class Animal extends Model {
  static get namedFilters() {
    return {
      dogs: (builder) => builder.where('species', 'dog')
    };
  }
}

The named filters can be used in any eager query:

Person
  .query()
  .eager('[movies(goodMovies, orderByName).actors, pets(dogs)]')

Named filters that can be used in any eager query and by the applyFilter method.

useLimitInFirst

class Animal extends Model {
  static get useLimitInFirst() {
    return true;
  }
}

If true, limit(1) is added to the query when first() is called. Defaults to false.

QueryBuilder

class Person extends Model {
  static get QueryBuilder() {
    return MyCustomQueryBuilder;
  }
}

ESNext:

class Person extends Model {
  static QueryBuilder = MyCustomQueryBuilder;
}

QueryBuilder subclass to use in query or $query methods.

This constructor is used whenever a query builder is created using query or $query methods. You can override this to use your own QueryBuilder subclass.

Usage example.

Static methods

query

const queryBuilder = Person.query(transactionOrKnex);

Read models from the database:

// Get all rows.
const people = await Person.query();
console.log('there are', people.length, 'people in the database');

// Example of a more complex WHERE clause. This generates:
// SELECT "persons".*
// FROM "persons"
// WHERE ("firstName" = 'Jennifer' AND "age" < 30)
// OR ("firstName" = 'Mark' AND "age" > 30)
const marksAndJennifers = await Person
  .query()
  .where(builder => {
    builder
      .where('firstName', 'Jennifer')
      .where('age', '<', 30);
  })
  .orWhere(builder => {
    builder
      .where('firstName', 'Mark')
      .where('age', '>', 30);
  });

console.log(marksAndJennifers);


// Get a subset of rows and fetch related models
// for each row.
const oldPeople = await Person
  .query()
  .where('age', '>', 60)
  .eager('children.children.movies');

console.log('some old person\'s grand child has appeared in',
  oldPeople[0].children[0].children[0].movies.length,
  'movies');

Insert models to the database:

const sylvester = await Person
  .query()
  .insert({firstName: 'Sylvester', lastName: 'Stallone'});

console.log(sylvester.fullName());
// --> 'Sylvester Stallone'.

// Batch insert. This only works on Postgresql as it is
// the only database that returns the identifiers of
// _all_ inserted rows. If you need to do batch inserts
// on other databases useknex* directly.
// (See .knexQuery() method).
const inserted = await Person
  .query()
  .insert([
    {firstName: 'Arnold', lastName: 'Schwarzenegger'},
    {firstName: 'Sylvester', lastName: 'Stallone'}
  ]);

console.log(inserted[0].fullName()); // --> 'Arnold Schwarzenegger'

update and patch can be used to update models. Only difference between the mentioned methods is that update validates the input objects using the model class’s full jsonSchema and patch ignores the required property of the schema. Use update when you want to update all properties of a model and patch when only a subset should be updated.

const numUpdatedRows = await Person
  .query()
  .update({firstName: 'Jennifer', lastName: 'Lawrence', age: 35})
  .where('id', jennifer.id);

console.log(numUpdatedRows);

// This will throw assuming that `firstName` or `lastName`
// is a required property for a Person.
await Person.query().update({age: 100});

// This will _not_ throw.
await Person
  .query()
  .patch({age: 100});

console.log('Everyone is now 100 years old');

Models can be deleted using the delete method. Naturally the delete query can be chained with any knex* methods:

await Person
  .query()
  .delete()
  .where('age', '>', 90);

console.log('anyone over 90 is now removed from the database');

Creates a query builder for the model’s table.

All query builders are created using this function, including $query, $relatedQuery and relatedQuery. That means you can modify each query by overriding this method for your model class. This is especially useful when combined with the use of onBuild.

See the query examples section for more examples.

Arguments
Argument Type Description
transactionOrKnex object Optional transaction or knex instance for the query. This can be used to specify a transaction or even a different database. for a query. Falsy values are ignored.
Return value
Type Description
QueryBuilder The created query builder

relatedQuery

const queryBuilder = Person.relatedQuery(relationName);

Select count of a relation and the maximum value of another one:

const people = await Person
  .query()
  .select([
    'persons.*',

    Person.relatedQuery('pets')
      .count()
      .where('species', 'dog')
      .as('dogCount'),

    Person.relatedQuery('movies')
      .max('createdAt')
      .as('mostRecentMovieDate')
  ]);

console.log(people[0].id);
console.log(people[0].dogCount)
console.log(people[0].mostRecentMovieDate);

Find models that have at least one item in a relation:

const peopleThatHavePets = await Person
  .query()
  .whereExists(Person.relatedQuery('pets'));

Generates something like this:

select "persons".* from "persons" where exists (select "pets".* from "animals" as "pets" where "pets"."ownerId" = "persons"."id")

Creates a subquery to a relation.

This query can only be used as a subquery and therefore there is no need to ever pass a transaction or a knex instance to it. It will always inherit its parent query’s transaction because it is compiled and executed as a part of the parent query.

Arguments
Argument Type Description
relationName string The name of the relation to create subquery for.
Return value
Type Description
QueryBuilder The created query builder

knex

Get:

const knex = Person.knex();

Set:

let knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: 'database.db'
  }
});

Model.knex(knex);
knex = Model.knex();

Get/Set the knex instance for this model class.

Subclasses inherit the connection. A system-wide knex instance can thus be set by calling objection.Model.knex(knex). This works even after subclasses have been created.

See bindKnex method if you want to use multiple databases in the same application.

raw

Shortcut for Person.knex().raw(...args)

fn

Shortcut for Person.knex().fn

knexQuery

Shortcut for Person.knex().table(Person.tableName)

bindKnex

Example:

const knex1 = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: 'database1.db'
  }
});

const knex2 = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: 'database2.db'
  }
});

SomeModel.knex(null);

const BoundModel1 = SomeModel.bindKnex(knex1);
const BoundModel2 = SomeModel.bindKnex(knex2);

// Throws since the knex instance is null.
await SomeModel.query();

// Works.
const models = await BoundModel1.query();

console.log(models[0] instanceof SomeModel); // --> true
console.log(models[0] instanceof BoundModel1); // --> true

// Works.
const models = await BoundModel2.query();

console.log(models[0] instanceof SomeModel); // --> true
console.log(models[0] instanceof BoundModel2); // --> true

Creates an anonymous subclass of this class that is bound to the given knex.

This method can be used to bind a Model subclass to multiple databases for example in a multi-tenant system. See the multi tenancy recipe for more info.

Arguments
Argument Type Description
knex Knex knex connection to bind to
Return value
Type Description
function The create model subclass constructor

bindTransaction

const { transaction } = require('objection');
const Person = require('./models/Person');

await transaction(Person.knex(), async (trx) => {
  const TransactingPerson =  Person.bindTransaction(trx);

  await TransactingPerson
    .query()
    .insert({firstName: 'Jennifer'});

  return TransactingPerson
    .query()
    .patch({lastName: 'Lawrence'})
    .where('id', jennifer.id);
});

Alias for bindKnex.

fromJson

const person = Person.fromJson(json, opt);

Creates a model instance from a JSON object.

The object is checked against jsonSchema and an exception is thrown on failure.

Arguments
Argument Type Description
json Object The JSON object from which to create the model.
opt ModelOptions Update options.
Return value
Type Description
Model The created model instance

fromDatabaseJson

const person = Person.fromDatabaseJson(row);

Creates a model instance from a JSON object in database format.

Arguments
Argument Type Description
row Object A database row.
Return value
Type Description
Model The created model instance

createValidator

class BaseModel extends Model {
  static createValidator() {
    return new MyCustomValidator();
  }
}

Sharing the same validator between model classes is also possible:

const validator = new MyCustomValidator();

class BaseModel extends Model {
  static createValidator() {
    return validator;
  }
}

The default implementation:

const AjvValidator = require('objection').AjvValidator;

class Model {
  static createValidator() {
    return new AjvValidator({
      onCreateAjv: (ajv) => {
        // Here you can modify the `Ajv` instance.
      },
      options: {
        allErrors: true,
        validateSchema: false,
        ownProperties: true,
        v5: true
      }
    });
  }
}

Creates an instance of a Validator that is used to do all validation related stuff. This method is called only once per model class.

You can override this method to return an instance of your custom validator. The custom validator doesn’t need to be based on the jsonSchema. It can be anything at all as long as it implements the Validator interface.

If you want to use the default json schema based AjvValidator but want to modify it, you can use the objection.AjvValidator constructor. See the default implementation example.

If you want to share the same validator instance between multiple models, that’s completely fine too. Simply implement createValidator so that it always returns the same object instead of creating a new one.

Return value
Type Description
Validator The created validator instance

createNotFoundError

class BaseModel extends Model {
  static createNotFoundError(queryContext) {
    return new MyCustomNotFoundError();
  }
}

The default implementation:

class Model {
  static createNotFoundError(queryContext) {
    return new this.NotFoundError();
  }
}

Creates an error thrown by throwIfNotFound() method. You can override this to throw any error you want.

Arguments
Argument Type Description
queryContext Object The context object of query that produced the empty result. See context.
Return value
Type Description
Error The created error. Model.NotFoundError by default.

createValidationError

class BaseModel extends Model {
  static createValidationError({type, message, data}) {
    return new MyCustomValidationError({type, message, data});
  }
}

The default implementation:

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

class Model {
  static createValidationError({type, message, data}) {
    return new ValidationError({type, message, data});
  }
}

Creates an error thrown when validation fails for a model. You can override this to throw any error you want. The errors created by this function don’t have to implement any interface or have the same properties as ValidationError. Objection only throws errors created by this function an never catches them.

Return value
Type Description
Error The created error. ValidationError by default.

omitImpl

class Person extends Model {
  omitImpl(obj, prop) {
    delete obj[prop];
  }
}

Omit implementation to use.

The default implementation deletes the property.

loadRelated

const promise = Person.loadRelated(models, expression, filters, transactionOrKnex);

Examples:

const people = await Person.loadRelated([person1, person2], 'children.pets');

const person1 = people[0];
const person2 = people[1];

Relations can be filtered by giving named filter functions as arguments to the relations:

const people = await Person
  .loadRelated([person1, person2], 'children(orderByAge).[pets(onlyDogs, orderByName), movies]', {
    orderByAge: (builder) => {
      builder.orderBy('age');
    },
    orderByName: (builder) => {
      builder.orderBy('name');
    },
    onlyDogs: (builder) => {
      builder.where('species', 'dog');
    }
  });

console.log(people[1].children.pets[0]);

Load related models for a set of models using a RelationExpression.

Arguments
Argument Type Description
models Array.<Model|Object>
expression string|RelationExpression The relation expression
filters Object.<string, function(QueryBuilder)> Optional named filters
transactionOrKnex object Optional transaction or knex instance for the query. This can be used to specify a transaction or even a different database.
Return value
Type Description
QueryBuilder The created query builder

traverse

There are two ways to call this method:

Model.traverse(models, (model, parentModel, relationName) => {
  doSomething(model);
});

and

Model.traverse(Person, models, (person, parentModel, relationName) => {
  doSomethingForPerson(person);
});

Traverses the relation tree of a list of models.

Calls the callback for each related model recursively. The callback is called also for the input models themselves.

In the second example the traverser function is only called for Person instances.

Arguments
Argument Type Description
filterConstructor function If this optional constructor is given, the traverser is only called for models for which model instanceof filterConstructor returns true.
models Model|Model[] The model(s) whose relation trees to traverse.
traverser function(Model, string, string) The traverser function that is called for each model. The first argument is the model itself. If the model is in a relation of some other model the second argument is the parent model and the third argument is the name of the relation.

getRelations

const relations = Person.getRelations();

Returns a Relation object for each relation defined in relationMappings.

This method is mainly useful for plugin developers and for other generic usages.

Return value
Type Description
Object.<string, Relation> Object whose keys are relation names and values are Relation instances.

columnNameToPropertyName

const propertyName = Person.columnNameToPropertyName(columnName);

For example, if you have defined columnNameMappers = snakeCaseMappers() for your model:

const propName = Person.columnNameToPropertyName('foo_bar');
console.log(propName); // --> 'fooBar'

Runs the property through possible columnNameMappers and $parseDatabaseJson hooks to apply any possible conversion for the column name.

Arguments
Argument Type Description
columnName string A column name
Return value
Type Description
string The property name

propertyNameToColumnName

const columnName = Person.propertyNameToColumnName(propertyName);

For example, if you have defined columnNameMappers = snakeCaseMappers() for your model:

const columnName = Person.propertyNameToColumnName('fooBar');
console.log(columnName); // --> 'foo_bar'

Runs the property through possible columnNameMappers and $formatDatabaseJson hooks to apply any possible conversion for the property name.

Arguments
Argument Type Description
propertyName string A property name
Return value
Type Description
string The column name

fetchTableMetadata

const metadata = await Person.fetchTableMetadata(opt);

Fetches and caches the table metadata.

Most of the time objection doesn’t need this metadata, but some methods like joinEager do. This method is called by objection when the metadata is needed. The result is cached and after the first call the cached promise is returned and no queries are executed.

Because objection uses this on demand, the first query that needs this information can have unpredicable performance. If that’s a problem, you can call this method for each of your models during your app’s startup.

If you’ve implemented tableMetadata method to return a custom metadata object, this method doesn’t execute database queries, but returns Promise.resolve(this.tableMetadata()) instead.

Arguments
Argument Type Description
opt TableMetadataFetchOptions Optional options
Return value
Type Description
Promise<TableMetadata> The table metadata object

tableMetadata

const metadata = Person.tableMetadata(opt);

A custom override that uses the property information in jsonSchema.

class Person extends Model {
  static tableMetadata() {
    return {
      columns: Object.keys(this.jsonSchema.properties)
    };
  }
}

Synchronously returns the table metadata object from the cache.

You can override this method to return a custom object if you don’t want objection to use fetchTableMetadata.

See fetchTableMetadata for more information.

Arguments
Argument Type Description
opt TableMetadataOptions Optional options
Return value
Type Description
TableMetadata The table metadata object

Instance methods

$id

console.log(model.$id()); // -> 100
// Sets the id.
model.$id(100);

Composite key

console.log(model.$id()); // -> [100, 20, 30]
// Sets the id.
model.$id([100, 20, 30]);

Returns or sets the identifier of a model instance.

The identifier property does not have to be accessed or set using this method. If the identifier property is known it can be accessed or set just like any other property.

$beforeValidate

class Person extends Model {
  $beforeValidate(jsonSchema, json, opt) {
    return jsonSchema;
  }
}

This is called before validation.

You can add any additional validation to this method. If validation fails, simply throw an exception and the query will be rejected. If you modify the jsonSchema argument and return it, that one will be used to validate the model.

opt.old object contains the old values while json contains the new values if validation is being done for an existing object.

Arguments
Argument Type Description
jsonSchema Object A deep clone of this class’s jsonSchema
json Object The JSON object to be validated
opt ModelOptions Optional options
Return value
Type Description
Object The modified jsonSchema or the input jsonSchema.

$validate

modelInstance.$validate();

Validates the model instance.

Calls $beforeValidate and $afterValidate methods. This method is called automatically from fromJson and $setJson methods. This method can also be called explicitly when needed.

Throws
Type Description
ValidationError If validation fails.

$toDatabaseJson

const row = modelInstance.$toDatabaseJson();

Exports this model as a database JSON object.

This method is called internally to convert a model into a database row.

Return value
Type Description
Object Database row.

$toJson

const jsonObj = modelInstance.$toJson(opt);
const shallowObj = modelInstance.$toJson({shallow: true, virtuals: true});

Exports this model as a JSON object.

Arguments
Argument Type Description
opt ToJsonOptions Optional options
Return value
Type Description
Object Model as a JSON object.

toJSON

const jsonObj = modelInstance.toJSON(opt);
const shallowObj = modelInstance.toJSON({shallow: true, virtuals: true});

Exports this model as a JSON object.

Arguments
Argument Type Description
opt ToJsonOptions Optional options
Return value
Type Description
Object Model as a JSON object.

$afterValidate

class Person extends Model {
  $afterValidate(json, opt) {

  }
}

This is called after successful validation.

You can do further validation here and throw a ValidationError if something goes wrong.

opt.old object contains the old values while json contains the new values if validation is being done for an existing object.

Arguments
Argument Type Description
json Object The JSON object to be validated
opt ModelOptions Optional options

$parseDatabaseJson

class Person extends Model {
  $parseDatabaseJson(json) {
    // Remember to call the super class's implementation.
    json = super.$parseDatabaseJson(json);
    // Do your conversion here.
    return json;
  }
}

This is called when a Model is created from a database JSON object.

Converts the JSON object from the database format to the internal format.

There are a couple of requirements for the implementation:

1. This function must be pure. It should’t have any side effects because it is called from “unexpected” places (for example to determine if your model somehow transforms column names between db and code).

2. This function must be able to handle any subset of model’s properties coming in. You cannot assume that some column is present in the json object as it depends on the select statement. There can also be additional columns because of joins, aliases etc. This method must also be prepared for null values in any property of the json object.

Arguments
Argument Type Description
json Object The JSON object in database format
Return value
Type Description
Object The JSON object in internal format

$formatDatabaseJson

class Person extends Model {
  $formatDatabaseJson(json) {
    // Remember to call the super class's implementation.
    json = super.$formatDatabaseJson(json);
    // Do your conversion here.
    return json;
  }
}

This is called when a Model is converted to database format.

Converts the JSON object from the internal format to the database format.

There are a couple of requirements for the implementation:

1. This function must be pure. It should’t have any side effects because it is called from “unexpected” places (for example to determine if your model somehow transforms column names between db and code).

2. This function must be able to handle any subset of model’s properties coming in. You cannot assume that some property is present in the json object. There can also be additional properties. This method must also be prepared for null values in any property of the json object.

Arguments
Argument Type Description
json Object The JSON object in internal format
Return value
Type Description
Object The JSON object in database format

$parseJson

class Person extends Model {
  $parseJson(json, opt) {
    // Remember to call the super class's implementation.
    json = super.$parseJson(json, opt);
    // Do your conversion here.
    return json;
  }
}

This is called when a Model is created from a JSON object.

Converts the JSON object from the external format to the internal format.

There are a couple of requirements for the implementation:

1. This function must be pure. It should’t have any side effects because it is called from “unexpected” places (for example to determine if your model somehow transforms column names between db and code).

2. This function must be able to handle any subset of model’s properties coming in. You cannot assume that some property is present in the json object. There can also be additional properties. This method must also be prepared for null values in any property of the json object.

Arguments
Argument Type Description
json Object The JSON object in external format
opt ModelOptions Optional options
Return value
Type Description
Object The JSON object in internal format

$formatJson

class Person extends Model {
  $formatJson(json) {
    // Remember to call the super class's implementation.
    json = super.$formatJson(json);
    // Do your conversion here.
    return json;
  }
}

This is called when a Model is converted to JSON.

Converts the JSON object from the internal format to the external format.

There are a couple of requirements for the implementation:

1. This function must be pure. It should’t have any side effects because it is called from “unexpected” places (for example to determine if your model somehow transforms column names between db and code).

2. This function must be able to handle any subset of model’s properties coming in. You cannot assume that some column is present in the json object as it depends on the select statement. There can also be additional columns because of joins, aliases etc. This method must also be prepared for null values in any property of the json object.

Arguments
Argument Type Description
json Object The JSON object in internal format
Return value
Type Description
Object The JSON object in external format

$setJson

modelInstance.$setJson(json, opt);

Sets the values from a JSON object.

Validates the JSON before setting values.

Arguments
Argument Type Description
json Object The JSON object to set
opt ModelOptions Optional options
Return value
Type Description
Model this for chaining

$setDatabaseJson

modelInstance.$setDatabaseJson(json);

Sets the values from a JSON object in database format.

Arguments
Argument Type Description
json Object The JSON object in database format
Return value
Type Description
Model this for chaining

$set

modelInstance.$set(json);

Sets the values from another model or object.

Unlike $setJson, this doesn’t call any $parseJson methods or validate the input. This simply sets each value in the object to this object.

Arguments
Argument Type Description
obj Object
Return value
Type Description
Model this for chaining

$setRelated

modelInstance.$setRelated(relation, relatedModels);
person.$setRelated('parent', parent);
console.log(person.parent);
person.$setRelated('children', children);
console.log(person.children[0]);

Sets related models to a corresponding property in the object.

Arguments
Argument Type Description
relation string|Relation Relation name or a relation instance to set.
relatedModels Model|Model[] Models to set.
Return value
Type Description
Model this for chaining

$appendRelated

modelInstance.$appendRelated(relation, relatedModels);
person.$appendRelated('parent', parent);
console.log(person.parent);
person.$appendRelated('children', child1);
person.$appendRelated('children', child2);

child1 = person.children[person.children.length - 1];
child2 = person.children[person.children.length - 2];

Appends related models to a corresponding property in the object.

Arguments
Argument Type Description
relation string|Relation Relation name or a relation instance to set.
relatedModels Model|Model[] Models to append.
Return value
Type Description
Model this for chaining

$omit

modelInstance.$omit(keys);

Omits a set of properties.

const json = person
  .fromJson({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .$omit('lastName')
  .toJSON();

console.log(_.has(json, 'lastName')); // --> false
const json = person
  .fromJson({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .$omit(['lastName'])
  .toJSON();

console.log(_.has(json, 'lastName')); // --> false
const json = person
  .fromJson({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .$omit({lastName: true})
  .toJSON();

console.log(_.has(json, 'lastName')); // --> false

Omits a set of properties.

The selected properties are set to undefined. Note that this is done in-place. Properties are set to undefined instead of deleting them for performance reasons (V8 doesn’t like delete).

If you want to use delete instead of undefining, you can override the omitImpl method.

Arguments
Argument Type Description
keys string|string[]|Object.<string, boolean> keys to omit
Return value
Type Description
Model this for chaining

$pick

modelInstance.$pick(keys);

Omits a set of properties.

const json = person
  .fromJson({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .$pick('firstName', 'age')
  .toJSON();

console.log(_.has(json, 'lastName')); // --> false
const json = person
  .fromJson({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .$pick(['firstName', 'age'])
  .toJSON();

console.log(_.has(json, 'lastName')); // --> false
const json = person
  .fromJson({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .$pick({firstName: true, age: true})
  .toJSON();

console.log(_.has(json, 'lastName')); // --> false

Picks a set of properties.

All other properties but the selected ones are set to undefined. Note that this is done in-place. Properties are set to undefined instead of deleting them for performance reasons (V8 doesn’t like delete).

If you want to use delete instead of undefining, you can override the omitImpl method.

Arguments
Argument Type Description
keys string|string[]|Object.<string, boolean> keys to pick
Return value
Type Description
Model this for chaining

$clone

const clone = modelInstance.$clone(options);
const shallowClone = modelInstance.$clone({shallow: true});

Returns a (deep) copy of this model.

If this object has instances of Model as properties (or arrays of them) they are cloned using their $clone() method. A shallow copy without relations can be created by passing the shallow: true option.

Arguments
Argument Type Description
opt CloneOptions Optional options
Return value
Type Description
Model Deep clone of this

$query

const queryBuilder = person.$query(transactionOrKnex);

Re-fetch the instance from the database:

// If you need to refresh the same instance you can do this:
const reFetchedPerson = await person.$query();

// Note that `person` did not get modified by the fetch.
person.$set(reFetchedPerson);

Insert a new model to database:

const jennifer = await Person.fromJson({firstName: 'Jennifer'}).$query().insert();

console.log(jennifer.id);

Patch a model:

await person.$query().patch({lastName: 'Cooper'});

console.log('person updated');

Delete a model.

await person.$query().delete();

console.log('person deleted');

Creates a query builder for this model instance.

All queries built using the returned builder only affect this instance.

Arguments
Argument Type Description
transactionOrKnex object Optional transaction or knex instance for the query. This can be used to specify a transaction or even a different database for a query. Falsy values are ignored.
Return value
Type Description
QueryBuilder query builder

$relatedQuery

const builder = model.$relatedQuery(relationName, transactionOrKnex);

Fetch all models related to a model through a relation. The fetched models are also stored to the owner model’s property named after the relation (by default):

const pets = await jennifer.$relatedQuery('pets');

console.log('jennifer has', pets.length, 'pets');
console.log(jennifer.pets === pets); // --> true

The related query is just like any other query. All knex methods are available:

const dogsAndCats = await jennifer
  .$relatedQuery('pets')
  .select('animals.*', 'persons.name as ownerName')
  .where('species', '=', 'dog')
  .orWhere('breed', '=', 'cat')
  .innerJoin('persons', 'persons.id', 'animals.ownerId')
  .orderBy('animals.name');

// All the dogs and cats have the owner's name "Jennifer"
// joined as the `ownerName` property.
console.log(dogsAndCats);

This inserts a new model to the database and binds it to the owner model as defined by the relation (by default):

const waldo = await jennifer
  .$relatedQuery('pets')
  .insert({species: 'dog', name: 'Fluffy'});

console.log(waldo.id);

To add an existing model to a relation the relate method can be used. In this example the dog fluffy already exists in the database but it isn’t related to jennifer through the pets relation. We can make the connection like this:

await jennifer
  .$relatedQuery('pets')
  .relate(fluffy.id);

console.log('fluffy is now related to jennifer through pets relation');

The connection can be removed using the unrelate method. Again, this doesn’t delete the related model. Only the connection is removed. For example in the case of ManyToMany relation the join table entries are deleted.

await jennifer
  .$relatedQuery('pets')
  .unrelate()
  .where('id', fluffy.id);

console.log('jennifer no longer has fluffy as a pet');

Related models can be deleted using the delete method. Note that in the case of ManyToManyRelation the join table entries are not deleted. Naturally the delete query can be chained with anyknex* methods.

await jennifer
  .$relatedQuery('pets')
  .delete()
  .where('species', 'cat')

console.log('jennifer no longer has any cats');

update and patch can be used to update related models. Only difference between the mentioned methods is that update validates the input objects using the related model class’s full schema and patch ignores the required property of the schema. Use update when you want to update all properties of a model and patch when only a subset should be updated.

const updatedFluffy = await jennifer
  .$relatedQuery('pets')
  .update({species: 'dog', name: 'Fluffy the great', vaccinated: false})
  .where('id', fluffy.id);

console.log('fluffy\'s new name is', updatedFluffy.name);

// This query will be rejected assuming that `name` or `species`
// is a required property for an Animal.
await jennifer
  .$relatedQuery('pets')
  .update({vaccinated: true})
  .where('species', 'dog');

// This query will succeed.
await jennifer
  .$relatedQuery('pets')
  .patch({vaccinated: true})
  .where('species', 'dog');

console.log('jennifer just got all her dogs vaccinated');

Use this to build a query that only affects the models related to this instance through a relation. By default, any fetched or inserted models are also stored to the owner model’s property named after the relation. See relatedFindQueryMutates or relatedInsertQueryMutates to change this behaviour.

Arguments
Argument Type Description
relationName string The name of the relation to query.
transactionOrKnex object Optional transaction or knex instance for the query. This can be used to specify a transaction or even a different database for a query. Falsy values are ignored.
Return value
Type Description
QueryBuilder A query builder

$loadRelated

const builder = modelInstance.$loadRelated(expression, filters, transactionOrKnex);

Examples:

await jennifer.$loadRelated('[pets, children.[pets, father]]');

console.log('Jennifer has', jennifer.pets.length, 'pets');
console.log('Jennifer has', jennifer.children.length, 'children');
console.log('Jennifer\'s first child has', jennifer.children[0].pets.length, 'pets');
console.log('Jennifer had her first child with', jennifer.children[0].father.name);

Relations can be filtered by giving named filter functions as arguments to the relations:

await jennifer
  .$loadRelated('children(orderByAge).[pets(onlyDogs, orderByName), movies]', {
    orderByAge: (builder) => {
      builder.orderBy('age');
    },
    orderByName: (builder) => {
      builder.orderBy('name');
    },
    onlyDogs: (builder) => {
      builder.where('species', 'dog');
    }
  });

console.log(jennifer.children.pets[0]);

Loads related models using a RelationExpression and assigns them to the target model instances.

Arguments
Argument Type Description
expression string|RelationExpression The relation expression
filters Object.<string, function(QueryBuilder)> Optional named filters
transactionOrKnex object Optional transaction or knex instance for the query. This can be used to specify a transaction or even a different database.
Return value
Type Description
QueryBuilder The created query builder

$traverse

Shortcut for Model.traverse(filterConstructor, this, callback).

$knex

Shortcut for return this.constructor.knex().

$transaction

Shortcut for return this.constructor.knex().

$beforeInsert

class Person extends Model {
  async $beforeInsert(queryContext) {
    await super.$beforeInsert(queryContext);
    await doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  async $beforeInsert(queryContext) {
    await super.$beforeInsert(queryContext);
    // This can always be done even if there is no running transaction. In that
    // case `queryContext.transaction` returns the normal knex instance. This
    // makes sure that the query is not executed outside the original query's
    // transaction.
    await SomeModel
      .query(queryContext.transaction)
      .insert(whatever);
  }
}

Called before a model is inserted into the database.

You can return a promise from this function if you need to do asynchronous stuff. You can also throw an exception to abort the insert and reject the query. This can be useful if you need to do insert specific validation.

Arguments
Argument Type Description
queryContext Object The context object of the insert query. See context.
Return value
Type Description
Promise|*

$afterInsert

class Person extends Model {
  async $afterInsert(queryContext) {
    await super.$afterInsert(queryContext);
    await doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  async $afterInsert(queryContext) {
    await super.$afterInsert(queryContext);
    // This can always be done even if there is no running transaction. In that
    // case `queryContext.transaction` returns the normal knex instance. This
    // makes sure that the query is not executed outside the original query's
    // transaction.
    await SomeModel
      .query(queryContext.transaction)
      .insert(whatever);
  }
}

Called after a model has been inserted into the database.

You can return a promise from this function if you need to do asynchronous stuff.

Arguments
Argument Type Description
queryContext Object The context object of the insert query. See context.
Return value
Type Description
Promise|*

$beforeUpdate

class Person extends Model {
  async $beforeUpdate(opt, queryContext) {
    await super.$beforeUpdate(opt, queryContext);
    await doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  async $beforeUpdate(opt, queryContext) {
    await super.$beforeUpdate(opt, queryContext);
    // This can always be done even if there is no running transaction. In that
    // case `queryContext.transaction` returns the normal knex instance. This
    // makes sure that the query is not executed outside the original query's
    // transaction.
    await SomeModel
      .query(queryContext.transaction)
      .insert(whatever);
  }
}

Note that the opt.old object is only populated for instance queries started with $query:

somePerson
  .$query()
  .update(newValues);

For the following query opt.old is undefined because there is no old object in the javascript side. objection.js doesn’t fetch the old values even if they existed in the database for performance and simplicity reasons.

Person
  .query()
  .update(newValues)
  .where('foo', 'bar');

Called before a model is updated.

You can return a promise from this function if you need to do asynchronous stuff. You can also throw an exception to abort the update and reject the query. This can be useful if you need to do update specific validation.

This method is also called before a model is patched. Therefore all the model’s properties may not exist. You can check if the update operation is a patch by checking the opt.patch boolean.

opt.old object contains the old values while this contains the updated values. The old values are never fetched from the database implicitly. For non-instance queries the opt.old object is undefined. See the examples –>.

Arguments
Argument Type Description
opt ModelOptions Update options.
queryContext Object The context object of the update query. See context.
Return value
Type Description
Promise|*

$afterUpdate

class Person extends Model {
  async $afterUpdate(opt, queryContext) {
    await super.$afterUpdate(opt, queryContext);
    await doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  async $afterUpdate(opt, queryContext) {
    await super.$afterUpdate(opt, queryContext);
    // This can always be done even if there is no running transaction. In that
    // case `queryContext.transaction` returns the normal knex instance. This
    // makes sure that the query is not executed outside the original query's
    // transaction.
    await SomeModel
      .query(queryContext.transaction)
      .insert(whatever);
  }
}

Note that the opt.old object is only populated for instance queries started with $query:

somePerson
  .$query()
  .update(newValues);

For the following query opt.old is undefined because there is no old object in the javascript side. objection.js doesn’t fetch the old values even if they existed in the database for performance and simplicity reasons.

Person
  .query()
  .update(newValues)
  .where('foo', 'bar');

Called after a model is updated.

You can return a promise from this function if you need to do asynchronous stuff.

This method is also called after a model is patched. Therefore all the model’s properties may not exist. You can check if the update operation is a patch by checking the opt.patch boolean.

opt.old object contains the old values while this contains the updated values. The old values are never fetched from the database implicitly. For non-instance queries the opt.old object is undefined. See the examples –>.

Arguments
Argument Type Description
opt ModelOptions Update options.
queryContext Object The context object of the update query. See context.
Return value
Type Description
Promise|*

$beforeDelete

class Person extends Model {
  async $beforeDelete(queryContext) {
    await super.$beforeDelete(queryContext);
    await doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  async $beforeDelete(queryContext) {
    await super.$beforeDelete(queryContext);
    // This can always be done even if there is no running transaction. In that
    // case `queryContext.transaction` returns the normal knex instance. This
    // makes sure that the query is not executed outside the original query's
    // transaction.
    await SomeModel
      .query(queryContext.transaction)
      .insert(whatever);
  }
}

Called before a model is deleted.

You can return a promise from this function if you need to do asynchronous stuff.

Note that this method is only called for instance deletes started with $query() method.

Arguments
Argument Type Description
queryContext Object The context object of the delete query. See context.
Return value
Type Description
Promise|*

$afterDelete

class Person extends Model {
  async $afterDelete(queryContext) {
    await super.$afterDelete(queryContext);
    await doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  async $afterDelete(queryContext) {
    await super.$afterDelete(queryContext);
    // This can always be done even if there is no running transaction. In that
    // case `queryContext.transaction` returns the normal knex instance. This
    // makes sure that the query is not executed outside the original query's
    // transaction.
    await SomeModel
      .query(queryContext.transaction)
      .insert(whatever);
  }
}

Called after a model is deleted.

You can return a promise from this function if you need to do asynchronous stuff.

Note that this method is only called for instance deletes started with $query() method.

Arguments
Argument Type Description
queryContext Object The context object of the delete query. See context.
Return value
Type Description
Promise|*

$afterGet

class Person extends Model {
  $afterGet(queryContext) {
    return doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  $afterGet(queryContext) {
    // This can always be done even if there is no running transaction. In that
    // case `queryContext.transaction` returns the normal knex instance. This
    // makes sure that the query is not executed outside the original query's
    // transaction.
    return SomeModel
      .query(queryContext.transaction)
      .insert(whatever);
  }
}

Called after a model is fetched.

This method is not called for insert, update or delete operations.

You can return a promise from this function if you need to do asynchronous stuff.

Arguments
Argument Type Description
queryContext Object The context object of the get query. See context.
Return value
Type Description
Promise|*

transaction

See the section about transactions

Methods

start

const trx = await transaction.start(Model.knex());

try {
  await doStuff(trx);
  await trx.commit();
} catch (err) {
  await trx.rollback(err);
}

Starts a transaction and returns a transaction object. If you use this method, you must explicitly remember to call trx.commit() or trx.rollback(err).

TransactionObject

This is nothing more than a knex transaction object. It can be used as a knex query builder, it can be passed to objection queries and models can be bound to it

See the section about transactions for more info and examples.

Instance methods

commit

const promise = trx.commit();

Call this method to commit the transaction. This only needs to be called if you use transaction.start() method.

rollback

const promise = trx.rollback(error);

Call this method to rollback the transaction. This only needs to be called if you use transaction.start() method. You need to pass the error to the method as the only argument.

FieldExpression

Field expressions allow one to refer to JSONB fields inside columns.

Syntax: <column reference>[:<json field reference>]

e.g. persons.jsonColumnName:details.names[1] would refer to value 'Second' in column persons.jsonColumnName which has { details: { names: ['First', 'Second', 'Last'] } } object stored in it.

First part <column reference> is compatible with column references used in knex e.g. MyFancyTable.tributeToThBestColumnNameEver.

Second part describes a path to an attribute inside the referred column. It is optional and it always starts with colon which follows directly with first path element. e.g. Table.jsonObjectColumnName:jsonFieldName or Table.jsonArrayColumn:[321].

Syntax supports [<key or index>] and .<key or index> flavors of reference to json keys / array indexes:

e.g. both Table.myColumn:[1][3] and Table.myColumn:1.3 would access correctly both of the following objects [null, [null,null,null, "I was accessed"]] and { "1": { "3" : "I was accessed" } }

Caveats when using special characters in keys:

  1. objectColumn.key This is the most common syntax, good if you are not using dots or square brackets [] in your json object key name.
  2. Keys containing dots objectColumn:[keywith.dots] Column { "keywith.dots" : "I was referred" }
  3. Keys containing square brackets column['[]'] { "[]" : "This is getting ridiculous..." }
  4. Keys containing square brackets and quotes objectColumn:['Double."Quote".[]'] and objectColumn:["Sinlge.'Quote'.[]"] Column { "Double.\"Quote\".[]" : "I was referred", "Sinlge.'Quote'.[]" : "Mee too!" }
  5. Keys containing dots, square brackets, single quotes and double quotes in one json key is not currently supported

RelationExpression

For example an expression children.[movies.actors.[pets, children], pets] represents a tree:

              children
              (Person)
                 |
         -----------------
         |               |
       movies           pets
      (Movie)         (Animal)
         |
       actors
      (Person)
         |
    -----------
    |         |
   pets    children
 (Animal)  (Person)

The model classes are shown in parenthesis.

This class rarely needs to be used directly. The relation expression can be given to a bunch of functions in objection.js. For example:

const people = await Person
  .query()
  .eager('children.[movies.actors.[pets, children], pets]');

// All persons have the given relation tree fetched.
console.log(people[0].children[0].movies[0].actors[0].pets[0].name);

Relation expressions can have arguments. Arguments are listed in parenthesis after the relation names like this:

Person
  .query()
  .eager(`children(arg1, arg2).[movies.actors(arg3), pets]`)

You can spread eager expressions to multiple lines and add whitespace:

Person
  .query()
  .eager(`[
    children.[
      pets,
      movies.actors.[
        pets,
        children
      ]
    ]
  ]`)

Eager expressions can be aliased using as keyword:

Person
  .query()
  .eager(`[
    children as kids.[
      pets(filterDogs) as dogs,
      pets(filterCats) as cats,

      movies.actors.[
        pets,
        children as kids
      ]
    ]
  ]`)

Relation expression is a simple DSL for expressing relation trees.

These are all valid relation expressions:

There are two tokens that have special meaning: * and ^. * means “all relations recursively” and ^ means “this relation recursively”.

For example children.* means “relation children and all its relations, and all their relations and …”. The * token must be used with caution or you will end up fetching your entire database.

Expression parent.^ is equivalent to parent.parent.parent.parent... up to the point a relation no longer has results for the parent relation. The recursion can be limited to certain depth by giving the depth after the ^ character. For example parent.^3 is equal to parent.parent.parent.

Relations can be aliased using the as keyword.

RelationExpression object notation

The string expression in the comment is equivalent to the object expression below it:

// `children`
{
  children: true
}
// `children.movies`
{
  children: {
    movies: true
  }
}
// `[children, pets]`
{
  children: true
  pets: true
}
// `[children.[movies, pets], pets]`
{
  children: {
    movies: true,
    pets: true
  }
  pets: true
}
// `parent.^`
{
  parent: {
    $recursive: true
  }
}
// `parent.^5`
{
  parent: {
    $recursive: 5
  }
}
// `parent.*`
{
  parent: {
    $allRecursive: true
  }
}
// `[children as kids, pets(filterDogs) as dogs]`
{
  kids: {
    $relation: 'children'
  },

  dogs: {
    $relation: 'pets',
    $modify: ['filterDogs']
  }
}

In addition to the string expressions, a more verbose object notation can also be used.

Validator

const Validator = require('objection').Validator;

Usage example:

const Validator = require('objection').Validator;

class MyCustomValidator extends Validator {
  validate(args) {
    // The model instance. May be empty at this point.
    const model = args.model;

    // The properties to validate. After validation these values will
    // be merged into `model` by objection.
    const json = args.json;

    // `ModelOptions` object. If your custom validator sets default
    // values, you need to check the `opt.patch` boolean. If it is true
    // we are validating a patch object and the defaults should not be set.
    const opt = args.options;

    // A context object shared between the validation methods. A new
    // object is created for each validation operation. You can store
    // any data here.
    const ctx = args.ctx;

    // Do your validation here and throw any exception if the
    // validation fails.
    doSomeValidationAndThrowIfFails(json);

    // You need to return the (possibly modified) json.
    return json;
  }

  beforeValidate(args) {
    // Takes the same arguments as `validate`. Usually there is no need
    // to override this.
    return super.beforeValidate(args);
  }

  afterValidate(args) {
    // Takes the same arguments as `validate`. Usually there is no need
    // to override this.
    return super.afterValidate(args);
  }
}

const Model = require('objection').Model;

// Override the `createValidator` method of a `Model` to use the
// custom validator.
class BaseModel extends Model {
  static createValidator() {
    return new MyCustomValidator();
  }
}

Abstract class from which model validators must be inherited. See the example for explanation. Also check out the createValidator method.

AjvValidator

const AjvValidator = require('objection').AjvValidator;

Usage example:

const Model = require('objection').Model;
const AjvValidator = require('objection').AjvValidator;

class BaseModel extends Model {
  static createValidator() {
    return new AjvValidator({
      onCreateAjv: (ajv) => {
        // Here you can modify the `Ajv` instance.
      },
      options: {
        allErrors: true,
        validateSchema: false,
        ownProperties: true,
        v5: true
      }
    });
  }
}

The default Ajv based json schema validator. You can override the createValidator method of Model like in the example to modify the validator.

ValidationError

const ValidationError = require('objection').ValidationError;

throw new ValidationError({type, message, data});

Or

const ValidationError = require('objection').Model.ValidationError;

throw new ValidationError({type, message, data});

If type is "ModelValidation" then data object should follow this pattern:

{
  key1: [{
    message: '...',
    keyword: 'required',
    params: null
  }, {
    message: '...',
    keyword: '...',
    params: {
      ...
    }
  }, ...],

  key2: [{
    message: '...',
    keyword: 'minLength',
    params: {
      limit: 1,
      ...
    }
  }, ...],

  ...
}

For each key, a list of errors is given. Each error contains the default message (as returned by the validator), an optional keyword string to identify the validation rule which didn’t pass and a param object which optionally contains more details about the context of the validation error.

If type is anything else but "ModelValidation", data can be any object that describes the error.

Error of this class is thrown by default if validation of any input fails. By input we mean any data that can come from the outside world, like model instances (or POJOs), relation expressions object graphs etc.

You can replace this error by overriding Model.createValidationError() method.

See the error handling recipe for more info.

Property Type Description
statusCode number HTTP status code for interop with express error handlers and other libraries that search for status code from errors.
type string One of “ModelValidation”, “RelationExpression”, “UnallowedRelation” and “InvalidGraph”. This can be any string for your own custom errors. The listed values are used internally by objection.
data object Any additional data. The content of this property is documented in the example in this section for “ModelValidation” errors.

NotFoundError

const NotFoundError = require('objection').NotFoundError;

throw new NotFoundError(data);

Or

const NotFoundError = require('objection').Model.NotFoundError;

throw new NotFoundError(data);

Error of this class is thrown by default by throwIfNotFound()

You can replace this error by overriding Model.createNotFoundError() method.

See the error handling recipe for more info.

ModelOptions

Property Type Description
patch boolean If true the json is treated as a patch and the required field of the json schema is ignored in the validation. This allows us to create models with a subset of required properties for patch operations.
skipValidation boolean If true the json schema validation is skipped
old object The old values for methods like $beforeUpdate and $beforeValidate.

CloneOptions

Property Type Description
shallow boolean If true, relations are ignored

ToJsonOptions

Property Type Description
shallow boolean If true, relations are ignored. Default is false.
virtuals boolean If false, virtual attributes are omitted from the output. Default is true.

EagerOptions

Property Type Description
minimize boolean If true the aliases of the joined tables and columns in a join based eager loading are minimized. This is sometimes needed because of identifier length limitations of some database engines. objection throws an exception when a query exceeds the length limit. You need to use this only in those cases.
separator string Separator between relations in nested join based eager query. Defaults to :. Dot (.) cannot be used at the moment because of the way knex parses the identifiers.
aliases Object.<string, string> Aliases for relations in a join based eager query. Defaults to an empty object.
joinOperation string Which join type to use ['leftJoin', 'innerJoin', 'rightJoin', ...] or any other knex join method name. Defaults to leftJoin.

UpsertGraphOptions

Property Type Description
relate boolean|string[] If true, relations are related instead of inserted. Relate functionality can be enabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
unrelate boolean|string[] If true, relations are unrelated instead of deleted. Unrelate functionality can be enabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
insertMissing boolean|string[] If true, models that have identifiers and are not found, are inserted. By default this is false and an error is thrown. This functionality can be enabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
update boolean|string[] If true, update operations are performaed instead of patch when altering existing models, affecting the way the data is validated. With update operations, all required fields need to be present in the data provided. This functionality can be enabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
noInsert boolean|string[] If true, no inserts are performed. Inserts can be disabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
noUpdate boolean|string[] If true, no updates are performed. Updates can be disabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
noDelete boolean|string[] If true, no deletes are performed. Deletes can be disabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
noRelate boolean|string[] If true, no relates are performed. Relate operations can be disabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.
noUnrelate boolean|string[] If true, no unrelate operations are performed. Unrelate operations can be disabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.

InsertGraphOptions

Property Type Description
relate boolean|string[] If true, models with an id are related instead of inserted. Relate functionality can be enabled for a subset of relations of the graph by providing a list of relation expressions. See the examples here.

TableMetadataFetchOptions

Property Type Description
table string A custom table name. If not given, Model.tableName is used.
knex knex|Transaction A knex instance or a transaction

TableMetadataOptions

Property Type Description
table string A custom table name. If not given, Model.tableName is used.

TableMetadata

Property Type Description
columns string[] Names of all the columns in a table.

Relation

Note that Relation instances are actually instances of the relation classes used in relationMappings. For example:

class Person extends Model {
  static get relationMappings() {
    return {
      pets: {
        relation: Model.HasManyRelation,
        modelClass: Animal,
        join: {
          from: 'persons.id',
          to: 'animals.ownerId'
        }
      }
    };
  }
}

const relations = Person.getRelations();

console.log(relations.pets instanceof Model.HasManyRelation); // --> true
console.log(relations.pets.name); // --> pets
console.log(relations.pets.ownerProp.cols); // --> ['id']
console.log(relations.pets.relatedProp.cols); // --> ['ownerId']

Relation is a parsed and normalized instance of a RelationMapping. Relations can be accessed using the getRelations method.

Relation holds a RelationProperty instance for each property that is used to create the relationship between two tables.

Relation is actually a base class for all relation types BelongsToOneRelation, HasManyRelation etc. You can use instanceof to determine the type of the relations (see the example on the right). Note that HasOneRelation is a subclass of HasManyRelation and HasOneThroughRelation is a subclass of ManyToManyRelation. Arrange your instanceof checks accordingly.

Property Type Description
name string Name of the relation. For example pets or children.
ownerModelClass function The model class that has defined the relation.
relatedModelClass function The model class of the related objects.
ownerProp RelationProperty The relation property in the ownerModelClass.
relatedProp RelationProperty The relation property in the relatedModelClass.
joinModelClass function The model class representing the join table. This class is automatically generated by Objection if none is provided in the join.through.modelClass setting of the relation mapping, see RelationThrough.
joinTable string The name of the join table (only for ManyToMany and HasOneThrough relations).
joinTableOwnerProp RelationProperty The join table property pointing to ownerProp (only for ManyToMany and HasOneThrough relations).
joinTableRelatedProp RelationProperty The join table property pointing to relatedProp (only for ManyToMany and HasOneThrough relations).

RelationProperty

Represents a property that is used to create relationship between two tables. A single RelationProperty instance can represent composite key. In addition to a table column, A RelationProperty can represent a nested field inside a column (for example a jsonb column).

Properties

Property Type Description
size number The number of columns. In case of composite key, this is greater than one.
modelClass function The model class that owns the property.
props Array<string> The column names converted to “external” format. For example if modelClass defines a snake_case to camelCase conversion, these names are in camelCase. Note that a RelationProperty may actually point to a sub-properties of the columns in case they are of json or some other non-scalar type. This array always contains only the converted column names. Use getProp(obj, idx) method to get the actual value from an object.
cols Array<string> The column names in the database format. For example if modelClass defines a snake_case to camelCase conversion, these names are in snake_case. Note that a RelationProperty may actually point to a sub-properties of the columns in case they are of json or some other non-scalar type. This array always contains only the column names.

Methods

getProp

const value = property.getProp(obj, index);

Gets this property’s index:th value from an object. For example if the property represents a composite key [a, b.d.e, c] and obj is {a: 1, b: {d: {e: 2}}, c: 3} then getProp(obj, 1) would return 2.

setProp

const value = property.setProp(obj, index, value);

Sets this property’s index:th value in an object. For example if the property represents a composite key [a, b.d.e, c] and obj is {a: 1, b: {d: {e: 2}}, c: 3} then setProp(obj, 1, 'foo') would mutate obj into {a: 1, b: {d: {e: 'foo'}}, c: 3}.

fullCol

const col = property.fullCol(builder, index);

Returns the property’s index:th column name with the correct table reference. Something like "Table.column". The first argument must be an objection QueryBuilder instance.

ref

const ref = property.ref(builder, index);

Allows you to do things like this:

const builder = Person.query();
const ref = property.ref(builder, 0);
builder.where(ref, '>', 10);

Returns a ReferenceBuilder instance that points to the index:th column.

patch

property.patch(patchObj, index, value);

Allows you to do things like this:

const builder = Person.query();
const patch = {};
property.patch(patch, 0, 'foo');
builder.patch(patch);

Appends an update operation for the index:th column into patchObj object.

ReferenceBuilder

An instance of this is returned from the ref helper function.

Methods

castText

Cast reference to sql type text.

castInt

Cast reference to sql type integer.

castBigInt

Cast reference to sql type bigint.

castFloat

Cast reference to sql type float.

castDecimal

Cast reference to sql type decimal.

castReal

Cast reference to sql type real.

castBool

Cast reference to sql type boolean.

castType

Give custom type to which referenced value is casted to.

DEPRECATED: Use castTo instead. castType Will be removed in 2.0.

.castType('mytype') --> CAST(?? as mytype)

castTo

Give custom type to which referenced value is casted to.

.castTo('mytype') --> CAST(?? as mytype)

castJson

In addition to other casts wrap reference to_jsonb() function so that final value reference will be json type.

as

Gives an alias for the reference .select(ref('age').as('yougness'))

LiteralBuilder

An instance of this is returned from the lit helper function. If an object is given as a value, it is casted to json by default.

Methods

castText

Cast to sql type text.

castInt

Cast to sql type integer.

castBigInt

Cast to sql type bigint.

castFloat

Cast to sql type float.

castDecimal

Cast to sql type decimal.

castReal

Cast to sql type real.

castBool

Cast to sql type boolean.

castType

Give custom type to which referenced value is casted to.

DEPRECATED: Use castTo instead. castType Will be removed in 2.0.

.castType('mytype') --> CAST(?? as mytype)

castTo

Give custom type to which referenced value is casted to.

.castTo('mytype') --> CAST(?? as mytype)

castJson

Converts the value to json (jsonb in case of postgresql). The default cast type for object values.

castArray

Converts the value to an array literal.

DEPRECATED: Use asArray instead. castArray Will be removed in 2.0.

asArray

Converts the value to an array literal.

lit([1, 2, 3]).asArray() --> ARRAY[?, ?, ?]

Can be used in conjuction with castTo.

lit([1, 2, 3]).asArray().castTo('real[]') -> CAST(ARRAY[?, ?, ?] AS real[])

as

Gives an alias for the reference .select(ref('age').as('yougness'))

Changelog

1.1.9

What’s new

1.1.8

What’s new

1.1.7

What’s new

1.1.6

What’s new

1.1.5

What’s new

1.1.4

What’s new

1.1.3

What’s new

1.1.2

What’s new

1.1.1

What’s new

1.1.0

What’s new

1.0.1

What’s new

1.0.0 🎉

What’s new

Breaking changes

Changes

0.9.4

What’s new

0.9.3

What’s new

0.9.2

What’s new

0.9.1

What’s new

0.9.0

What’s new

Breaking changes

0.8.8

What’s new

0.8.7

What’s new

0.8.6

What’s new

0.8.5

What’s new

0.8.4

What’s new

0.8.3

What’s new

0.8.2

What’s new

0.8.1

What’s new

0.8.0

What’s new

Breaking changes

Old model definition

function Person() {
  Model.apply(this, arguments);
}

Model.extend(Person);

Person.tableName = 'Person';

Person.prototype.fullName = function () {
  return this.firstName + ' ' + this.lastName;
}

// More static and prototype methods.

Easiest way to migrate to class and extends keywords

class Person extends Model {

}

Person.tableName = 'Person';

Person.prototype.fullName = function () {
  return this.firstName + ' ' + this.lastName;
}

// More static and prototype methods.

0.7.12

What’s new

0.7.11

What’s new

0.7.10

What’s new

0.7.9

What’s new

0.7.6

What’s new

0.7.5

What’s new

0.7.4

What’s new

0.7.3

What’s new

0.7.2

What’s new

0.7.1

What’s new

0.7.0

What’s new

Breaking changes

0.6.2

What’s new

0.6.1

What’s new

0.6.0

What’s new

Breaking changes

0.5.5

What’s new

0.5.4

What’s new

0.5.3

What’s new

0.5.1

What’s new

0.5.0

What’s new

0.4.0

What’s new

Breaking changes

There shouldn’t be any major breaking changes. We moved from ES5 to ES7 + babel in this version so there are big changes in the codebase. If something comes up, please open an issue.

There are a few known corner cases that may break:

0.3.3

What’s new

0.3.2

What’s new

0.3.1

What’s new

0.3.0

What’s new

Breaking changes

0.2.8

What’s new

0.2.7

What’s new

0.2.6

What’s new

0.2.5

What’s new

0.2.4

What’s new

0.2.3

What’s new

0.2.2

What’s new

0.2.1

What’s new

0.2.0

What’s new

Breaking changes

0.1.0

First release.