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.

Blog posts and tutorials:

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 objection = require('objection');
const Model = objection.Model;
const Knex = require('knex');

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

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

// Create database schema. You should use knex migration files to do this. We
// create it here for simplicity.
const schemaPromise = knex.schema.createTableIfNotExists('Person', table => {
  table.increments('id').primary();
  table.string('firstName');
});

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

schemaPromise.then(() => {

  // Create a person.
  return Person.query().insert({firstName: 'Sylvester'});

}).then(person => {

  console.log('created:', person.firstName, 'id:', person.id);
  // Fetch all people named Sylvester.
  return Person.query().where('firstName', 'Sylvester');

}).then(sylvesters => {

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

});

To use objection.js all you need to do is initialize knex and give the connection to objection.js using Model.knex(knex). Doing this installs the knex connection globally for all models. 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 example-requests.sh file contains a bunch of curl commands for you to start playing with the REST API.

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

Also check out our API reference and recipe book.

Blog posts and tutorials:

Models

A working model with minimal amount of code:

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

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:

class Person extends Model {

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

  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 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() {
    // 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: 'Person.id',
          to: 'Animal.ownerId'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: Movie,
        join: {
          from: 'Person.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: 'Person_Movie.personId',
            to: 'Person_Movie.movieId'
          },
          to: 'Movie.id'
        }
      },

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

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

ESNext:

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

  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 validation. Whenever a model instance is created
  // 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: 'Person.id',
        to: 'Animal.ownerId'
      }
    },

    movies: {
      relation: Model.ManyToManyRelation,
      modelClass: __dirname + '/Movie',
      join: {
        from: 'Person.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: 'Person_Movie.personId',
          to: 'Person_Movie.movieId'
        },
        to: 'Movie.id'
      }
    },

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

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

Models are created by inheriting from the Model base class.

Relations

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

class Animal extends Model {
  static relationMappings = {
    owner: {
      relation: Model.BelongsToOneRelation,
      modelClass: Person,
      join: {
        from: 'animal.ownerId',
        to: 'person.id'
      }
    }
  }
}

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

class Person extends Model {
  static relationMappings = {
    animals: {
      relation: Model.HasManyRelation,
      modelClass: Animal,
      join: {
        from: 'person.id',
        to: 'animal.ownerId'
      }
    }
  }
}

HasOneRelation: Just like HasManyRelation but for one related row

class Person extends Model {
  static relationMappings = {
    animals: {
      relation: Model.HasOneRelation,
      modelClass: Animal,
      join: {
        from: 'person.id',
        to: 'animal.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 relationMappings = {
    movies: {
      relation: Model.ManyToManyRelation,
      modelClass: Movie,
      join: {
        from: 'Person.id',
        through: {
          // Person_Movie is the join table.
          from: 'Person_Movie.personId',
          to: 'Person_Movie.movieId'
        },
        to: 'Movie.id'
      }
    }
  }
}

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

class Person extends Model {
  static relationMappings = {
    movie: {
      relation: Model.HasOneThroughRelation,
      modelClass: Movie,
      join: {
        from: 'Person.id',
        through: {
          // Person_Movie is the join table.
          from: 'Person_Movie.personId',
          to: 'Person_Movie.movieId'
        },
        to: 'Movie.id'
      }
    }
  }
}

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

Vocabulary for the relation descriptions:

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:

Person
  .query()
  .then(people => {
    console.log(people[0] instanceof Person); // --> true
    console.log('there are', people.length, 'People in total');
  })
  .catch(err => {
    console.log('oh noes');
  });
select * from "Person"

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:

Person
  .query()
  .where('age', '>', 40)
  .andWhere('age', '<', 60)
  .andWhere('firstName', 'Jennifer')
  .orderBy('lastName')
  .then(middleAgedJennifers => {
    console.log('The last name of the first middle aged Jennifer is');
    console.log(middleAgedJennifers[0].lastName);
  });
select * from "Person"
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:

Person
  .query()
  .select('parent:parent.name as grandParentName')
  .joinRelation('parent.parent')
  .then(people => {
    console.log(people[0].grandParentName)
  });
select "parent:parent"."firstName" as "grandParentName"
from "Person"
inner join "Person" as "parent" on "parent"."id" = "Person"."parentId"
inner join "Person" as "parent:parent" on "parent:parent"."id" = "parent"."parentId"

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

Person
  .query()
  .select('Person.*', 'Parent.firstName as parentFirstName')
  .join('Person as Parent', 'Person.parentId', 'Parent.id')
  .where('Person.age', '<', Person.query().avg('Person.age'))
  .whereExists(Animal.query().select(1).whereRef('Person.id', 'Animal.ownerId'))
  .orderBy('Person.lastName')
  .then(people => {
    console.log(people[0].parentFirstName);
  });
select "Person".*, "Parent"."firstName" as "parentFirstName"
from "Person"
inner join "Person" as "Parent" on "Person"."parentId" = "Parent"."id"
where "Person"."age" < (select avg("Person"."age") from "Person")
and exists (select 1 from "Animal" where "Person"."id" = "Animal"."ownerId")
order by "Person"."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

Person
  .query()
  .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
  .then(jennifer => {
    console.log(jennifer instanceof Person); // --> true
    console.log(jennifer.firstName); // --> 'Jennifer'
    console.log(jennifer.fullName()); // --> 'Jennifer Lawrence'
  })
  .catch(err => {
    console.log('oh noes');
  });
insert into "Person" ("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

Person
  .query()
  .patch({lastName: 'Dinosaur'})
  .where('age', '>', 60)
  .then(numUpdated => {
    console.log('all people over 60 years old are now dinosaurs');
    console.log(numUpdated, 'people were updated');
  })
  .catch(err => {
    console.log(err.stack);
  });
update "Person" set "lastName" = 'Dinosaur' where "age" > 60
Person
  .query()
  .patchAndFetchById(246, {lastName: 'Updated'})
  .then(updated => {
    console.log(updated.lastName); // --> Updated.
  })
  .catch(err => {
    console.log(err.stack);
  });
update "Person" set "lastName" = 'Updated' where "id" = 246
select * from "Person" 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

Person
  .query()
  .delete()
  .where(Person.raw('lower("firstName")'), 'like', '%ennif%')
  .then(numDeleted => {
    console.log(numDeleted, 'people were deleted');
  })
  .catch(err => {
    console.log(err.stack);
  });
delete from "Person" 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.
person
  .$relatedQuery('pets')
  .where('species', 'dog')
  .orderBy('name')
  .then(pets => {
    console.log(person.pets === pets); // --> true
    console.log(pets[0] instanceof Animal); // --> true
  });
select * from "Animal"
where "species" = 'dog'
and "Animal"."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.

Insert queries

Add a pet for a person:

// `person` is an instance of `Person` model.
person
  .$relatedQuery('pets')
  .insert({name: 'Fluffy'})
  .then(fluffy => {
    console.log(person.pets.indexOf(fluffy) !== -1); // --> true
  });
insert into "Animal" ("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.
person
  .$relatedQuery('movies')
  .insert({name: 'The room', awesomeness: 9001})
  .then(movie => {
    console.log('best movie ever was added');
  });
insert into "Movie" ("name") values ('The room')
insert into "Person_Movie" ("movieId", "personId", "awesomeness") values (14, 25, 9001)

Chain the insert method to the $relatedQuery('pets') call to insert a related object for a model instance. The query inserts the 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.

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 queries

Eager loading

Fetch the pets relation for all results of a query:

Person
  .query()
  .eager('pets')
  .then(people => {
    // 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:

Person
  .query()
  .eager('[pets, children.[pets, children]]')
  .then(people => {
    // 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);
  });

Fetch one relation recursively:

Person
  .query()
  .eager('[pets, children.^]')
  .then(people => {
    // 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:

Person
  .query()
  .eager('[pets, children.^3]')
  .then(people => {
    console.log(people[0].children[0].children[0].children[0].firstName);
  });

Relations can be filtered using the modifyEager method:

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:

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');
    }
  })
  .then(people => {
    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

Person
  .query()
  .eager('children(orderByAge).[pets(onlyDogs, orderByName), movies]')
  .then(people => {
    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:

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

      movies.[
        actors
      ]
    ]
  ]`)
  .then(people => {
    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', (req, res, next) => {
  Person
    .query()
    .allowEager('[pets, children.pets]')
    .eager(req.query.eager)
    .then(people => res.send(people))
    .catch(next);
});

Eager loading algorithm can be changed using the eagerAlgorithm method:

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 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

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:

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:

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.

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:

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.
      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
};

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.
      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);

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

The upsertGraph method works a little different than the other update and patch methods. When using upsertGraph any where or having method is 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 allowEager.

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 knex = Person.knex();

objection.transaction(knex, trx => {
  return Person
    .query(trx)
    .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
    .then(jennifer => {
      return jennifer
        .$relatedQuery('pets', trx)
        .insert({name: 'Scrappy'});
    });
}).then(scrappy => {
  console.log('Jennifer and Scrappy were successfully inserted');
}).catch(err => {
  console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
});

ESNext

const knex = Person.knex();

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

    return jennifer
      .$relatedQuery('pets', trx)
      .insert({name: 'Scrappy'});
  });
} catch (err) {
  console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
}

console.log('Jennifer and Scrappy were successfully 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 transation 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)`.
function insertPersonAndPet(person, pet, db) {
  return Person
    .query(db)
    .insert(person)
    .then(person => {
      return person
        .$relatedQuery('pets', db)
        .insert(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.

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

objection.transaction(Person, Animal, (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 tranaction. Only the actual objects passed
  // to this function are bound to the transaction.

  return Person
    .query()
    .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
    .then(() => {
      return Animal
        .query()
        .insert({name: 'Scrappy'});
    });

}).then(scrappy => {
  console.log('Jennifer and Scrappy were successfully inserted');
}).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:

objection.transaction(Person, Person => {

  return Person
    .query()
    .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
    .then(jennifer => {
      // 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'});
    });

}).then(scrappy => {
  console.log('Jennifer and Scrappy were successfully inserted');
}).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 Person = require('./models/Person');
const Animal = require('./models/Animal');

objection.transaction(Person, BoundPerson => {

  // This will be executed inside the transaction.
  return BoundPerson
    .query()
    .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
    .then(jennifer => {
      // OH NO! This query is executed outside the transaction
      // since the `Animal` class is not bound to the transaction.
      return Animal
        .query()
        .insert({name: 'Scrappy'});
    })
    .then(() => {
      // OH NO! This query is executed outside the transaction
      // since the `Person` class is not bound to the transaction.
      // BoundPerson !== Person.
      return Person
        .query()
        .insert({firstName: 'Bradley'});
    });

});

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

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

  var q1 = trx('Person').insert({firstName: 'Jennifer', lastName: 'Lawrence'});
  var q2 = Animal.query(trx).insert({name: 'Scrappy'});
  var q3 = Animal.query().transacting(trx).insert({name: 'Fluffy'});

  return Promise.all([q1, q2, q3]);

}).spread((jennifer, scrappy, fluffy) => {
  console.log('Jennifer, Scrappy and Fluffy were successfully inserted');
}).catch(err => {
  console.log('Something went wrong. Jennifer, Scrappy and Fluffy were not inserted');
});

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:

Person
  .query()
  .insert({
    firstName: 'Jennifer',
    lastName: 'Lawrence',
    age: 24,
    address: {
      street: 'Somestreet 10',
      zipCode: '123456',
      city: 'Tampere'
    }
  })
  .then(jennifer => {
    console.log(jennifer.address.city); // --> Tampere
    return Person.query().where('id', jennifer.id);
  })
  .then(jenniferFromDb => {
    console.log(jenniferFromDb.address.city); // --> Tampere
  })
  .catch(err => {
    console.log('oh noes');
  });

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.

Validation

All these will trigger the validation:

Person.fromJson({firstName: 'jennifer', lastName: 'Lawrence'});
Person.query().insert({firstName: 'jennifer', lastName: 'Lawrence'});
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.
Person.query().patch({age: 24}).where('age', '<', 24);

Validation errors provide detailed error message:

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:

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

List of plugins and modules for objection

A curated list of good 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.

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) {

}

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 needs 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 4.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.

Recipe book

Raw queries

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

Person
  .query()
  .select(raw('coalesce(sum(??), 0) as ??', ['age', 'childAgeSum']))
  .where(raw(`?? || ' ' || ??`, 'firstName', 'lastName'), 'Arnold Schwarzenegger')
  .orderBy(raw('random()'))
  .then(childAgeSums => {
    console.log(childAgeSums[0].childAgeSum);
  });
Person
  .query()
  .select(Person.raw('coalesce(sum(??), 0) as ??', ['age', 'childAgeSum']))
  .groupBy('parentId')
  .then(childAgeSums => {
    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';

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

Individual json fields can be updated like this:

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.

Change id column

class Person extends Model {
  static get idColumn() {
    return '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({
        id: [{
          message: 'identifier should not be defined before insert'
          keyword: null,
          params: null
        }]
      });
    }
  }
}

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, you need to check the `opt.patch` boolean. If it is true
    // we are validating a patch object, 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.
    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).

Map column names to different property names

snake_case/camelCase conversion:

// It's a good idea to memoize the conversion functions.
const snakeCase = _.memoize(_.snakeCase);
const camelCase = _.memoize(_.camelCase);

class Person extends Model {
  // This is called when an object is serialized to database format.
  $formatDatabaseJson(json) {
    json = super.$formatDatabaseJson(json);

    return _.mapKeys(json, (value, key) => {
      return snakeCase(key);
    });
  }

  // This is called when an object is read from database.
  $parseDatabaseJson(json) {
    json = _.mapKeys(json, (value, key) => {
      return camelCase(key);
    });

    return super.$parseDatabaseJson(json);
  }
}

Note that even though column names are mapped when fetching / storing data, one still has to use db column names when writing queries:

await Person.query().insert({ firstName: 'Jennifer' });
let jen = await Person.query().where('first_name', 'Jennifer');
expect(jen.firstName).to.equal('Jennifer');

Sometimes you may want to use for example snake_cased column names in database tables and camelCased property names in code. You can use the functions

to convert data between database and “external” representations.

Paging

Person
  .query()
  .where('age', '>', 20)
  .page(5, 100)
  .then(result => {
    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:

Person
  .query()
  .where('age', '>', builder => {
    builder.avg('age').from('Person');
  })
  .then(peopleOlderThanAverage => {
    console.log(peopleOlderThanAverage);
  });

Or QueryBuilders:

Person
  .query()
  .where('age', '>', Person.query().avg('age'))
  .then(peopleOlderThanAverage => {
    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:

Person
  .query()
  .select('Person.*', 'Parent.firstName as parentName')
  .join('Person as Parent', 'Person.parentId', 'Parent.id')
  .then(people => {
    console.log(people[0].parentName);
  });

joinRelation helper for joining relation graphs:

Person
  .query()
  .select('parent:parent.name as grandParentName')
  .joinRelation('parent.parent')
  .then(people => {
    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:

Person
  .query()
  .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
  .returning('*')
  .then(jennifer => {
    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:

Person
  .query()
  .patch({firstName: 'Jenn', lastName: 'Lawrence'})
  .where('id', 1234)
  .first() // Ensures we're returned a single row in the promise resolution
  .returning('*')
  .then(jennifer => {
    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:

jennifer
  .$query()
  .patch({firstName: 'J.', lastName: 'Lawrence'})
  .first() // Ensures we're returned a single row in the promise resolution
  .returning('*')
  .then(jennifer => {
    console.log(jennifer.updatedAt); // NOW()-ish
    console.log(jennifer.firstName); // "J."
  });

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

Person
  .query()
  .delete()
  .where({firstName: 'Jenn'})
  .returning('*')
  .then(deletedJennifers => {
    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:

jennifer
  .$relatedQuery('pets')
  .delete()
  .where({'species': 'dog'})
  .returning('*')
  .then(jennsDeletedDogs => {
    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'},
        join: {
          from: 'Issue.id',
          to: 'Comment.commentableId'
        }
      }
    };
  }
}

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

The {commentableType: 'Type'} filter adds a WHERE "commentableType" = 'Type' clause to the relation fetch query. It doesn’t automatically set the type when you insert a new comment. You have to set the commentableType manually:

someIssue
  .$relatedQuery('comments')
  .insert({text: 'blaa', commentableType: 'Issue'})
  .then(...)

Creating polymorphic associations isn’t as easy as it could be at the moment, but it can be done using custom filters for relations. 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:

Person.query().upsert(person).then(() => {
  ...
});

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', (req, res) => {
  req.models.Person
    .query()
    .findById(req.params.id)
    .then(people => 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', (req, res) => {
  Person
    .query(req.knex)
    .findById(req.params.id)
    .then(people => 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

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

SQL:

select * from "Person" 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 relationMappings() {
    return {
      pets: {
        relation: Model.BelongsToOneRelation,
        modelClass: Animal,
        join: {
          from: [
            'Person.firstName',
            'Person.lastName',
            'Person.dateOfBirth'
          ],
          to: [
            'Animal.ownerFirstName',
            'Animal.ownerLastName',
            'Animal.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:

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

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 fields that are all documented elsewhere in the API docs. Here’s a list of the fields and links to their docs.

Fields

Model

The model base class.

transaction

The transaction function.

ref

The ref helper function.

raw

The raw helper function.

mixin

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

compose

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

lodash

Lodash utility library used in objection. Useful for plugin developers so that they don’t have to add it as a dependency.

Promise

Bluebird promise library used in objection. Useful for plugin developers so that they don’t have to add it as a dependency.

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;

Factory function that returns a ReferenceBuilder instance, that makes it easier to refer to tables, columns, json attributes and add casting to referred columns wihtout need to use raw queries.

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

import { ref } from 'objection';

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'));

ReferenceBuilder 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(sqlType)

Give custom casting type to which referenced value is casted to.

.castType('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(as)

As format to tell which name will be used for reference for example in .select(ref('age').as('yougness'))

lit() (Not implemented yet)

The same as ref() but allows one to tell in which format certain javascript literal should be passed to database engine.

raw

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

Person
  .query()
  .select(raw('coalesce(sum(??), 0) as ??', ['age', 'childAgeSum']))
  .where(raw(`?? || ' ' || ??`, 'firstName', 'lastName'), 'Arnold Schwarzenegger')
  .orderBy(raw('random()'))
  .then(childAgeSums => {
    console.log(childAgeSums[0].childAgeSum);
  });

Wrapper for knex raw query that doens’t depend on knex. Instances returned by raw are converted to knex raw instances when the query is executed.

Query building methods

findById

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

Composite key:

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

findOne

const builder = queryBuilder.findOne(...whereArgs);
Person.query().findOne({firstName: 'Jennifer', lastName: 'Lawrence'});
Person.query().findOne('age', '>', 20);
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);
Person
  .query()
  .insert({firstName: 'Jennifer', lastName: 'Lawrence'})
  .then(jennifer => {
    console.log(jennifer.id);
  });

Batch insert (Only works on Postgres):

someMovie
  .$relatedQuery('actors')
  .insert([
    {firstName: 'Jennifer', lastName: 'Lawrence'},
    {firstName: 'Bradley', lastName: 'Cooper'}
  ])
  .then(actors => {
    console.log(actors[0].firstName);
    console.log(actors[1].firstName);
  });

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

Person
  .query()
  .insert({
    age: Person.query().avg('age'),
    firstName: Person.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'.

someMovie
  .$relatedQuery('actors')
  .insert({
    firstName: 'Jennifer',
    lastName: 'Lawrence',
    someExtra: "I'll be written to the join table"
  })
  .then(jennifer => {
    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. On other databases you can use the insertAndFetch method.

The batch insert only works 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|Array.<Object>|Array.<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|Array.<Object>|Array.<Model> Objects to insert
Return value
Type Description
QueryBuilder this query builder for chaining.

insertGraph

const builder = queryBuilder.insertGraph(graph);

You can insert any asyclic graph of models like this:

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.

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

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

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

    movies: [{
      "#ref": 'Silver Linings Playbook'
    }]
  }]);

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.

If you need to refer to a model already in the database from a many-to-many relation you can use the special property #dbRef like this:

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

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

    movies: [{
      "#dbRef": 1536
    }, {
      "#dbRef": 6527
    }]
  }]);

You can refer to the properties of other models in the graph using expressions of format #ref{<id>.<property>} for example:

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

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

The query above will insert a pet named I am the dog of Jennifer Lawrence for Jennifer.

Insert models with relations. This method is best explained with examples ➔

See the allowInsert method if you need to limit which relations can be inserted using this method to avoid security issues.

By the way, if you are using Postgres the inserts are done in batches for maximum performance.

Arguments
Argument Type Description
graph Object|Model|Array.<Object>|Array.<Model> Objects to insert
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);
Person
  .query()
  .update({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .where('id', 134)
  .then(numberOfAffectedRows => {
    console.log(numberOfAffectedRows);
  });

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

Person
  .query()
  .update({
    firstName: Person.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.:

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);
Person
  .query()
  .updateAndFetchById(134, {firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .then(updatedModel => {
    console.log(updatedModel.firstName);
  });

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

Person
  .query()
  .updateAndFetchById(134, {
    firstName: Person.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);
person
  .$query()
  .updateAndFetch({firstName: 'Jennifer', lastName: 'Lawrence', age: 24})
  .then(updatedModel => {
    console.log(updatedModel.firstName);
  });

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

person
  .$query()
  .updateAndFetch({
    firstName: Person.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

See the section about graph upserts

Arguments
Argument Type Description
graph Object|Model|Array.<Object>|Array.<Model> Graph to upsert.
Return value
Type Description
QueryBuilder this query builder for chaining.

patch

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

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

Person
  .query()
  .patch({
    age: Person.query().avg('age'),
    firstName: Person.raw("'Jenni' || 'fer'"),
    oldLastName: ref('lastName')
  });

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.

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);
Person
  .query()
  .patchAndFetchById(134, {age: 24})
  .then(updatedModel => {
    console.log(updatedModel.firstName);
  });

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

Person
  .query()
  .patchAndFetchById(134, {
    age: Person.query().avg('age'),
    firstName: Person.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);
person
  .$query()
  .patchAndFetch({age: 24})
  .then(updatedModel => {
    console.log(updatedModel.firstName);
  });

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

person
  .$query()
  .patchAndFetch({
    age: Person.query().avg('age'),
    firstName: Person.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();
Person
  .query()
  .delete()
  .where('age', '>', 100)
  .then(numberOfDeletedRows => {
    console.log('removed', numberOfDeletedRows, 'people');
  });

Creates a delete query.

Return value
Type Description
QueryBuilder this query builder for chaining.

deleteById

const builder = queryBuilder.deleteById(id);
Person
  .query()
  .deleteById(1)
  .then(numberOfDeletedRows => {
    console.log('removed', numberOfDeletedRows, 'people');
  });

Composite key:

Person
  .query()
  .deleteById([10, '20', 46])
  .then(numberOfDeletedRows => {
    console.log('removed', numberOfDeletedRows, 'people');
  });

Deletes a model by id.

Arguments
Argument Type Description
id any|Array.<any>
Return value
Type Description
QueryBuilder this query builder for chaining.

relate

const builder = queryBuilder.relate(ids);
Person
  .query()
  .where('id', 123)
  .first()
  .then(person => {
    return person.$relatedQuery('movies').relate(50);
  })
  .then(() => {
    console.log('movie 50 is now related to person 123 through `movies` relation');
  });

Relate multiple (only works with postgres)

person
  .$relatedQuery('movies')
  .relate([50, 60, 70])
  .then(() => {

  });

Composite key

person
  .$relatedQuery('movies')
  .relate({foo: 50, bar: 20, baz: 10})
  .then(() => {

  });

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'.

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

  });

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();
Person
  .query()
  .where('id', 123)
  .first()
  .then(person => {
    return person.$relatedQuery('movies').unrelate().where('id', 50);
  })
  .then(() => {
    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);
Person
  .query()
  .alias('p')
  .where('p.id', 1)
  .join('Person 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.

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:

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

Give an alias for a single relation:

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

Join two relations:

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.

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

Give aliases for a bunch of relations:

Person
  .query()
  .select('Person.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

Return value
Type Description
QueryBuilder this query builder for chaining.

columnInfo

See knex documentation

Return value
Type Description
QueryBuilder this query builder for chaining.

whereRef

var builder = queryBuilder.whereRef(leftRef, operator, rightRef);
builder.whereRef('Person.id', '=', 'Animal.ownerId');

Compares a column reference to another

Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereRef

var builder = queryBuilder.orWhereRef(leftRef, operator, rightRef);
builder.orWhereRef('Person.id', '=', 'Animal.ownerId');

Compares a column reference to another

Return value
Type Description
QueryBuilder this query builder for chaining.

whereComposite

var 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.

whereJsonEquals

const builder = queryBuilder.whereJsonEquals(fieldExpression, jsonObjectOrFieldExpression);
Person
  .query()
  .whereJsonEquals('additionalData:myDogs', 'additionalData:dogsAtHome')
  .then(people => {
    // oh joy! these people have all their dogs at home!
  });

Person
  .query()
  .whereJsonEquals('additionalData:myDogs[0]', { name: "peter"})
  .then(people => {
    // these people's first dog name is "peter" and the dog has no other
    // attributes, but its name
  });

Where jsonb field reference equals jsonb object or other field reference.

Also supports having field expression in both sides of equality.

Arguments
Argument Type Description
fieldExpression FieldExpression Reference to column / json field
jsonObjectOrFieldExpression Object|Array|FieldExpression Reference to column / json field or json object
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonEquals

See whereJsonEquals

whereJsonNotEquals

See whereJsonEquals

orWhereJsonNotEquals

See whereJsonEquals

whereJsonSupersetOf

var builder = queryBuilder.whereJsonSupersetOf(fieldExpression, jsonObjectOrFieldExpression);
Person
  .query()
  .whereJsonSupersetOf('additionalData:myDogs', 'additionalData:dogsAtHome')
  .then(people => {
    // 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.
  });

Person
  .query()
  .whereJsonSupersetOf('additionalData:myDogs[0]', { name: "peter"})
  .then(people => {
    // 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|Array.<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|Array.<string> Strings that are looked from object or array
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonHasAll

See whereJsonHasAll

whereJsonField

const builder = queryBuilder.whereJsonField(fieldExpression, operator, value);

Where referred json field value casted to same type with value fulfill given operand.

Value may be number, string, null, boolean and referred json field is converted to TEXT, NUMERIC or BOOLEAN sql type for comparison.

If left hand field does not exist rows appear IS null so if one needs to get only rows, which has key and it’s value is null one may use e.g. .whereJsonSupersetOf("column", { field: null }) or check is key exist and then .whereJsonField('column:field', 'IS', null)

For testing against objects or arrays one should see tested with whereJsonEqual, whereJsonSupersetOf and whereJsonSubsetOf methods.

Arguments
Argument Type Description
fieldExpression FieldExpression Expression pointing to certain value
operator string SQL comparator usually <, >, <>, = or !=
value boolean|number|string|null Value to which field is compared to
Return value
Type Description
QueryBuilder this query builder for chaining.

orWhereJsonField

See whereJsonField

Other instance methods

context

const builder = queryBuilder.context(queryContext);

You can set the context like this:

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

and access the context like this:

var 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: (builder) => {},
    runAfter: (builder) => {},
    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.

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.

isFindQuery

const isFindQuery = queryBuilder.isFindQuery();

Returns true if the query is read-only.

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

runBefore

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

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

   return Promise.delay(10).then(() => {
     console.log('hello 2');
   });
 })
 .runBefore(() => {
   console.log('hello 3');
 });

query.then();
// --> 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(any, QueryBuilder) The function to be executed.
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 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.

runAfter

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

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

query.then(models => {
  const jennifer = models[models.length - 1];
});

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(*, QueryBuilder) The function to be executed.
Return value
Type Description
QueryBuilder this query builder for chaining.

eagerAlgorithm

const builder = queryBuilder.eagerAlgorithm(algo);
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.

eager

const builder = queryBuilder.eager(relationExpression, filters);
// Fetch `children` relation for each result Person and `pets` and `movies`
// relations for all the children.
Person
  .query()
  .eager('children.[pets, movies]')
  .then(people => {
    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:

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');
    }
  })
  .then(people => {
    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 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');
      }
    };
  }
}

Person
  .query()
  .eager('children(orderByAge).[pets(onlyDogs, orderByName), movies]')
  .then(people => {
    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:

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);
  })
  .then(people => {
    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:

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

      movies.[
        actors
      ]
    ]
  ]`)
  .then(people => {
    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:

Person
  .query()
  .where('id', 1)
  .eager('children.children')
  .then(people => {
    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:

Person
  .query()
  .where('id', 1)
  .eagerAlgorithm(Person.JoinEagerAlgorithm)
  .eager('[movies, children.pets]')
  .where('movies.name', 'like', '%terminator%')
  .where('children:pets.species', 'dog')
  .then(people => {
    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.

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);
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.

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. If this query builder executes multiple queries, only the first query’s SQL is returned.

Return value
Type Description
string The SQL this query builder will build

toSql

const sql = queryBuilder.toSql();

Returns the SQL string. If this query builder executes multiple queries, only the first query’s SQL is returned.

Return value
Type Description
string The SQL this query builder will build

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.

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);

Promise.all([
  query.resultSize(),
  query.offset(100).limit(50)
]).spread((total, models) => {
  ...
});

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);
Person
  .query()
  .where('age', '>', 20)
  .page(5, 100)
  .then(result => {
    console.log(result.results.length); // --> 100
    console.log(result.total); // --> 3341
  });

Two queries 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
pageSize number The page size
Return value
Type Description
QueryBuilder this query builder for chaining

range

const builder = queryBuilder.range(start, end);
Person
  .query()
  .where('age', '>', 20)
  .range(0, 100)
  .then(result => {
    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:

Person
  .query()
  .where('age', '>', 20)
  .limit(10)
  .range()
  .then(result => {
    console.log(result.results.length); // --> 101
    console.log(result.total); // --> 3341
  });

Only returns the given range of results.

Two queries 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);
Person
  .query()
  .where('age', '>', 20)
  .pluck('firstName')
  .then(firstNames => {
    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();
Person
  .query()
  .first()
  .then(firstPerson => {
    console.log(person.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();
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);
Person
  .query()
  .eager('pets')
  .traverse((model, parentModel, relationName) => {
    delete model.id;
  })
  .then(people => {
    console.log(people[0].id); // --> undefined
    console.log(people[0].pets[0].id); // --> undefined
  });
Person
  .query()
  .eager('pets')
  .traverse(Animal, (animal, parentModel, relationName) => {
    delete animal.id;
  })
  .then(persons => {
    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 Array.<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 Array.<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 'Person';
  }

  // 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: 'Person.id',
          to: 'Animal.ownerId'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: __dirname + '/Movie',
        join: {
          from: 'Person.id',
          // ManyToMany relation needs the `through` object
          // to describe the join table.
          through: {
            from: 'Person_Movie.actorId',
            to: 'Person_Movie.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: 'Movie.id'
        }
      },

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

      parent: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'Person.parentId',
          to: 'Person.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 'Person';
  }
}

ESNext:

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

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];
  }
}

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.

relationMappings

class Person extends Model {
  static get relationMappings() {
    return {
      pets: {
        relation: Model.HasManyRelation,
        modelClass: Animal,
        join: {
          from: 'Person.id',
          to: 'Animal.ownerId'
        }
      },

      father: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'Person.fatherId',
          to: 'Person.id'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: Movie,
        join: {
          from: 'Person.id',
          through: {
            from: 'Person_Movie.actorId',
            to: 'Person_Movie.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: 'Movie.id'
        }
      }
    };
  }
}

ESNext:

class Person extends Model {
  static relationMappings = {
    pets: {
      relation: Model.HasManyRelation,
      modelClass: Animal,
      join: {
        from: 'Person.id',
        to: 'Animal.ownerId'
      }
    },

    father: {
      relation: Model.BelongsToOneRelation,
      modelClass: Person,
      join: {
        from: 'Person.fatherId',
        to: 'Person.id'
      }
    },

    movies: {
      relation: Model.ManyToManyRelation,
      modelClass: Movie,
      join: {
        from: 'Person.id',
        through: {
          from: 'Person_Movie.actorId',
          to: 'Person_Movie.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: 'Movie.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 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) Optional modifier for the relation query. This is called each time the relation is fetched.
filter function(QueryBuilder) Alias for modify.
RelationJoin
Property Type Description
from string|Array.<string> The relation column in the owner table. Must be given with the table name. For example Person.id. Composite key can be specified using an array of columns e.g. ['Person.a', 'Person.b']. Note that neither this nor to need to be foreign keys or primary keys. You can join any column to any column.
to string|Array.<string> The relation column in the related table. Must be given with the table name. For example Movie.id. Composite key can be specified using an array of columns e.g. ['Movie.a', 'Movie.b']. Note that neither this nor from need to be foreign keys or primary keys. You can join any column to any column.
through RelationThrough Describes the join table if the models are related through one.
RelationThrough
Property Type Description
from string|Array.<string> The column that is joined to from property of the RelationJoin. For example Person_Movie.actorId where Person_Movie is the join table. Composite key can be specified using an array of columns e.g. ['Person_Movie.a', 'Person_Movie.b'].
to string|Array.<string> The column that is joined to to property of the RelationJoin. For example Person_Movie.movieId where Person_Movie is the join table. Composite key can be specified using an array of columns e.g. ['Person_Movie.a', 'Person_Movie.b'].
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 Array.<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

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.

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'
});

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'
});

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.

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

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.

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};
  }
}

ESNext:

class Person extends Model {
  static defaultEagerOptions = {minimize: true};
}

Sets the default options for eager loading algorithm. See the possible fields here.

Defaults to null.

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.

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.
Person.query().then(people => {
  console.log('there are', people.length, 'people in the database');
});

// Example of a more complex WHERE clause. This generates:
// SELECT FROM "Person"
// WHERE ("firstName" = 'Jennifer' AND "age" < 30)
// OR ("firstName" = 'Mark' AND "age" > 30)
Person
  .query()
  .where(builder => {
    builder
      .where('firstName', 'Jennifer')
      .where('age', '<', 30);
  })
  .orWhere(builder => {
    builder
      .where('firstName', 'Mark')
      .where('age', '>', 30);
  })
  .then(marksAndJennifers => {
    console.log(marksAndJennifers);
  });

// Get a subset of rows and fetch related models
// for each row.
Person
  .query()
  .where('age', '>', 60)
  .eager('children.children.movies')
  .then(oldPeople => {
    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:

Person.query()
  .insert({firstName: 'Sylvester', lastName: 'Stallone'})
  .then(sylvester => {
    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).
Person
  .query()
  .insert([
    {firstName: 'Arnold', lastName: 'Schwarzenegger'},
    {firstName: 'Sylvester', lastName: 'Stallone'}
  ])
  .then(inserted => {
    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.

Person
  .query()
  .update({firstName: 'Jennifer', lastName: 'Lawrence', age: 35})
  .where('id', jennifer.id)
  .then(numUpdatedRows => {
    console.log(numUpdatedRows);
  });

// This will throw assuming that `firstName` or `lastName`
// is a required property for a Person.
Person.query().update({age: 100});

// This will _not_ throw.
Person
  .query()
  .patch({age: 100})
  .then(() => {
    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:

Person
  .query()
  .delete()
  .where('age', '>', 90)
  .then(() => {
    console.log('anyone over 90 is now removed from the database');
  });

Creates a query builder for the model’s table.

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

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

formatter

Shortcut for Person.knex().client.formatter()

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.
SomeModel.query().then();

// Works.
BoundModel1.query().then(models => {
 console.log(models[0] instanceof SomeModel); // --> true
 console.log(models[0] instanceof BoundModel1); // --> true
});

// Works.
BoundModel2.query().then(models => {
 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 Person = require('./models/Person');

objection.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();
  }
}

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.

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(errorHash) {
    return new MyCustomValidationError(errorHash);
  }
}

The default implementation:

class Model {
  static createValidationError(errorHash) {
    return new this.ValidationError(errorHash);
  }
}

Creates an error thrown when validation fails for a model. You can override this to throw any error you want.

Arguments
Argument Type Description
errorHash Object The failed validations. See ValidationError documentation for details.
Return value
Type Description
Error The created error. Model.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);

Examples:

Person.loadRelated([person1, person2], 'children.pets').then(people => {
  var person1 = people[0];
  var person2 = people[1];
});

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

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');
    }
  })
  .then(people => {
    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
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|Array.<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.

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();

Exports this model as a JSON object.

Return value
Type Description
Object Model as a JSON object.

toJSON

const jsonObj = modelInstance.toJSON();

Exports this model as a JSON object.

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

$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|Array.<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|Array.<string>|Object.<string, boolean> keys to pick
Return value
Type Description
Model this for chaining

$clone

const clone = modelInstance.$clone;

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.

Return value
Type Description
Model Deep clone of this

$query

const queryBuilder = person.$query(transactionOrKnex);

Re-fetch the instance from the database:

person.$query().then(reFetchedPerson => {
  // Note that `person` did not get modified by the fetch.
  console.log(reFetchedPerson);
});

// If you need to refresh the same instance you can do this:
person.$query().then(reFetchedPerson => {
  // Note that `person` did not get modified by the fetch.
  person.$set(reFetchedPerson);
});

// Or this:
person = await person.$query();

Insert a new model to database:

Person.fromJson({firstName: 'Jennifer'}).$query().insert().then(jennifer => {
  console.log(jennifer.id);
});

Patch a model:

person.$query().patch({lastName: 'Cooper'}).then(() => {
  console.log('person updated');
});

Delete a model.

person.$query().delete().then(() => {
  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:

jennifer.$relatedQuery('pets').then(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:

jennifer
  .$relatedQuery('pets')
  .select('Animal.*', 'Person.name as ownerName')
  .where('species', '=', 'dog')
  .orWhere('breed', '=', 'cat')
  .innerJoin('Person', 'Person.id', 'Animal.ownerId')
  .orderBy('Animal.name')
  .then(dogsAndCats => {
    // 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:

jennifer
  .$relatedQuery('pets')
  .insert({species: 'dog', name: 'Fluffy'})
  .then(waldo => {
    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:

jennifer
  .$relatedQuery('pets')
  .relate(fluffy.id)
  .then(() => {
    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.

jennifer
  .$relatedQuery('pets')
  .unrelate()
  .where('id', fluffy.id)
  .then(() => {
    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.

jennifer
  .$relatedQuery('pets')
  .delete()
  .where('species', 'cat')
  .then(() => {
    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.

jennifer
  .$relatedQuery('pets')
  .update({species: 'dog', name: 'Fluffy the great', vaccinated: false})
  .where('id', fluffy.id)
  .then(updatedFluffy => {
    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.
jennifer
  .$relatedQuery('pets')
  .update({vaccinated: true})
  .where('species', 'dog');

// This query will succeed.
jennifer
  .$relatedQuery('pets')
  .patch({vaccinated: true})
  .where('species', 'dog')
  .then(() => {
    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.

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);

Examples:

jennifer.$loadRelated('[pets, children.[pets, father]]').then(jennifer => {
  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:

jennifer
  .$loadRelated('children(orderByAge).[pets(onlyDogs, orderByName), movies]', {
    orderByAge: (builder) => {
      builder.orderBy('age');
    },
    orderByName: (builder) => {
      builder.orderBy('name');
    },
    onlyDogs: (builder) => {
      builder.where('species', 'dog');
    }
  })
  .then(jennifer => {
    console.log(jennifer.children.pets[0]);
  });

Loads related models using a RelationExpression.

Arguments
Argument Type Description
expression string|RelationExpression The relation expression
filters Object.<string, function(QueryBuilder)> Optional named filters
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 {
  $beforeInsert(queryContext) {
    return doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  $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.
    return 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 {
  $afterInsert(queryContext) {
    return doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  $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.
    return 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 {
  $beforeUpdate(opt, queryContext) {
    return doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  $beforeUpdate(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);
  }
}

Note that the 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 {
  $afterUpdate(opt, queryContext) {
    return doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  $afterUpdate(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);
  }
}

Note that the 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 {
  $beforeDelete(queryContext) {
    return doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  $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.
    return 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 {
  $afterDelete(queryContext) {
    return doPossiblyAsyncStuff();
  }
}

The current query’s transaction/knex instance can always be accessed through queryContext.transaction.

class Person extends Model {
  $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.
    return 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. Person.jsonColumnName:details.names[1] would refer to value 'Second' in column Person.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:

Person
  .query()
  .eager('children.[movies.actors.[pets, children], pets]')
  .then(people => {
    // 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.

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(data);

Or

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

throw new ValidationError(data);

The 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.

Error of this class is thrown by default if a model validation fails.

You can replace this error by overriding Model.createValidationError() method.

Property Type Description
statusCode number HTTP status code for interop with express error handlers and other libraries that search for status code from errors.
data Object Dictionary of 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.

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.

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.

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: 'Person.id',
          to: 'Animal.ownerId'
        }
      }
    };
  }
}

const relations = Person.getRelations();

console.log(relations.pets instanceof Model.HasManyRelation); // --> true
console.log(relations.pets.name); // --> pets
console.log(relations.pets.ownerCol); // --> ['id']
console.log(relations.pets.relatedCol); // --> ['ownerId']

Relation is a parsed and normalized instance of a RelationMapping. Relations can be accessed using the getRelations method.

There is a *Prop and *Col version of each key, because models may define conversions between database and external property names. Most of the times these fields hold the same values, but in case of a conversion, the conversion has been applied to them. The properties are arrays because of composite key support.

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.
ownerCol Array<string> The relation column in the ownerModelClass.
ownerProp Array<string> The relation property in the ownerModelClass.
relatedCol Array<string> The relation column in the relatedModelClass.
relatedProp Array<string> The relation property in the relatedModelClass.
joinTable string The name of the join table (only for ManyToMany and HasOneThrough relations).
joinTableOwnerCol Array<string> The join table column pointing to ownerCol (only for ManyToMany and HasOneThrough relations).
joinTableOwnerProp Array<string> The join table property pointing to ownerProp (only for ManyToMany and HasOneThrough relations).
joinTableRelatedCol Array<string> The join table property pointing to relatedCol (only for ManyToMany and HasOneThrough relations).
joinTableRelatedProp Array<string> The join table property pointing to relatedProp (only for ManyToMany and HasOneThrough relations).

Changelog

0.8.9

What’s new

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.