Effortless eager loading and nested inserts with objection.js

A while back I wrote an introductory post about objection.js, the ORM I created for Node.js (it was called Moron.js back then). Now it's time to dig a little deeper into its features. In this post, I will introduce the nested relation query system of objection.js, which is one of its most powerful features. It allows you to easily load and insert nested relations from/into the database.

Example database

The simple database used in the following examples has three tables Person, Movie and Review. There is a many-to-many relation between Person and Movie that represents actors. Movies can have a list of Reviews and each Review has a Person as a reviewer. For some reason our database also stores family tree data expressed through a parent relation from Person to itself. Here's a pretty picture of our db:

relational-documents-schema

Eager loading

In objection.js you can fetch related models for the results of any query by chaining the eager method to the query. The eager method takes a relation expression string as a parameter. Let's look at a couple of simple examples first to get started. The first example fetches the actors relation for all movies in the result. The examples are written in ES7, but note that ES7 is not a requirement of objection.js. You can use ES6 or ES5 if you want.

let movies = await Movie
  .query()
  .where('name', 'like', '%Terminator%')
  .eager('actors');

console.log(movies[0].actors[0].firstName);
// --> Arnold 

The second example also fetches the movies' reviews:

let movies = await Movie
  .query()
  .where('name', 'like', '%Terminator%')
  .eager('[actors, reviews]');

console.log(movies[0].actors[0].firstName);
// --> Arnold 
console.log(movies[0].reviews[0].title);
// --> Great movie

Nested relations can be fetched using a dot notation. In the next example we also fetch the actors' parent and other movies:

let movies = await Movie
  .query()
  .eager('[actors.[parent, movies], reviews.reviewer]');

console.log(movies[0].actors[0].parent.firstName);
// --> Gustav 
console.log(movies[0].actors[0].movies[2].name);
// --> Predator 
console.log(movies[0].reviews[0].reviewer.firstName);
// --> Some

The nested eager queries avoid the N + 1 selects problem by using where in queries. Lets assume for example that a person named Arnold has three children and they all have three children. To fetch this family tree we could write a query like this:

let people = await Person
  .query()
  .where('firstName', 'Arnold')
  .eager('children.children');

A naive implementation would fetch Arnold's children using one query and then start a query for each of the children to fetch their children. Instead, objection.js uses where in queries and only generates one query per each level in the relation tree. Our example generates three queries that are illustrated in the following picture:

relational-documents-n-plus-1

You can imagine more levels to the tree. Each level would only add one query.

Now back to features. What if we need to fetch Arnold's whole family tree up to the beginning of time? Of course we could write a long enough chain of parent.parent.parent... but that's not pretty. Instead we can use the special ^ character in the expression like this:

let people = await Person
  .query()
  .where('firstName', 'Arnold')
  .eager('[children.^, parent.^]');

// Print all the ancestors of Arnold.
let person = people[0];
while (person) {
  console.log(person);
  person = person.parent;
}

Relations can also be filtered. For example, if we only want to see the good reviews of a movie we could write this:

let movies = await Movie
  .query()
  .where('name', 'like', '%Terminator%')
  .eager('reviews(onlyGood)', {
    onlyGood: (query) => {
      query.where('stars', '>', 3)
    }
  });

console.log(movies[0].reviews[0].stars);
// --> 5 

Here's another example with multiple filters:

let movies = await Movie
  .query()
  .where('name', 'like', '%Terminator%')
  .eager('[reviews(onlyGood).reviewer, actors(orderByAge, teen)]',{
    onlyGood: (query) => {
      query.where('stars', '>', 3);
    },
    orderByAge: (query) => {
      query.orderBy('age');
    },
    teen: (query) => {
      query.whereBetween('age', [13, 19]);
    }
  });

So, the filter names are listed as arguments for the relation. The actual filter functions are given as the second argument to the eager method. The filter functions are called with an instance of objection.js QueryBuilder. You can call any of the query builder's methods but you need to be careful with some of them. For example, calling select('firstName', 'age') would break the next level of queries since objection.js could not generate the queries without parent identifiers. Since being able to select only a subset of properties is important, objection.js provides another solution to this particular problem: the pick method:

let movies = await Movie
  .query()
  .where('name', 'like', '%Terminator%')
  .eager('[reviews.reviewer, actors]')
  .pick(Person, ['firstName', 'age'])
  .pick(Review, ['id', 'stars', 'reviewer']);

The pick method registers an operation to be run for the whole model tree after all the queries have been executed. The first argument is optional, but if a model constructor is given, only models of that class are pruned. The example above selects only firstName and age properties for all Person instances and id, stars and reviewer properties for all Review instances.

Because the relation expressions are strings, they can easily be sent for example as a query parameter of an HTTP request. Allowing a client to fetch an arbitrary set of relations is not very secure. Therefore, objection.js has the allowEager() method. allowEager can be used to limit the allowed relations to a certain subset.

expressApp.get('/people', function (req, res, next) {
  Person
    .query()
    .allowEager('[movies, children.movies]')
    .eager(req.query.eager)
    .then((people) => res.send(people))
    .catch(next);
});

The example above allows req.query.eager any subset:

  • movies
  • children
  • children.movies
  • [movies, children]
  • [movies, children.movies]

All other values cause the query to be rejected.

Nested inserts

Inserting models with relations is as easy as fetching them. You just give the document to the insertWithRelated method:

let terminator = await Movie
  .query()
  .insertWithRelated({
    name: 'The terminator',

    actors: [{
      firstName: 'Arnold',
      lastName: 'Schwarzenegger',

      parent: {
        firstName: 'Gustav',
        lastName: 'Schwarzenegger'
      }
    }, {
      firstName: 'Michael',
      lastName: 'Biehn'
    }],

    reviews: [{
      title: 'Great movie',
      stars: 5,
      text: 'Awesome',

      reviewer: {
        firstName: 'Some',
        lastName: 'Random-Dude'
      }
    }]
  });

insertWithRelated creates a dependency graph for the document and inserts models that don't depend on any other until the whole document is inserted. Let's consider the Arnold's family tree example again. Before inserting a child we need to know it's parentId property and therefore the parent needs to be inserted first. In our example case, the whole document could be inserted in three batches:

relational-documents-insert-graph

If you are using postgresql, the whole graph is inserted using only three database queries: one for each batch. Unfortunately, on other supported databases (sqlite3 and mysql) the models are inserted one at a time. This is because postgresql is the only database that returns the identifiers of all the inserted rows for a batch insert.

You can create references to other models in the document using the special properties #id and #ref. For example, if you want to insert two movies that both have the same actor, you can create a reference to the other movie's actor:

let terminator = await Movie
  .query()
  .insertWithRelated([{
    name: 'The terminator',

    actors: [{
      "#id": 'arnold',
      firstName: 'Arnold',
      lastName: 'Schwarzenegger',

      parent: {
        firstName: 'Gustav',
        lastName: 'Schwarzenegger'
      }
    }]
  }, {
    name: 'Terminator 2: Judgment Day',

    actors: [{
      "#ref": 'arnold'
    }]
  }]);

It is quite easy to create circular dependencies using references, but luckily objection.js detects them and rejects the query with a sensible error message.

allowInsert method can be used to limit which relations can be inserted:

expressApp.post('/people', function (req, res, next) {
  Person
    .query()
    .allowInsert('[movies, children.movies]')
    .insertWithRelated(req.body)
    .then((people) => res.send(people))
    .catch(next);
});

allowInsert works just like allowEager. If the req.body contains models in relations that are not listed in the expression given to the allowInsert method, the query is rejected.

Standalone app for running the examples

To run the examples, copy the following code into an app.js file in a new folder. Then run this command in the folder:

npm install objection knex sqlite3

To run the app just say:

node --harmony app.js

If the node.js installed on your computer is an older version that doesn't support all the ES6 features used in the code, you can run the app using the babel transpiler:

npm install -g babel babel-cli
babel-node app.js

app.js

'use strict';

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

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: './test.db'
  }
});

// Make all models use the same database.
Model.knex(knex);

// Create the tables. Normally this would be 
// done using migration files. For simplicity, 
// we create them in this same file.
const createSchema = Promise.coroutine(function *() {
  yield knex.schema.dropTableIfExists('Movie');
  yield knex.schema.dropTableIfExists('Person');
  yield knex.schema.dropTableIfExists('Review');
  yield knex.schema.dropTableIfExists('Movie_Person');

  yield knex.schema.createTable('Movie', function (table) {
    table.bigincrements('id').primary();
    table.string('name');
  });

  yield knex.schema.createTable('Person', function (table) {
    table.bigincrements('id').primary();
    table.string('firstName');
    table.string('lastName');
    table.biginteger('parentId')
      .references('id')
      .inTable('Person')
      .index();
  });

  yield knex.schema.createTable('Review', function (table) {
    table.bigincrements('id').primary();
    table.string('title');
    table.integer('stars');
    table.string('text');
    table.biginteger('movieId')
      .references('id')
      .inTable('Movie')
      .index();
    table.biginteger('reviewerId')
      .references('id')
      .inTable('Person')
      .index();
  });

  yield knex.schema.createTable('Movie_Person',function(table){
    table.biginteger('movieId')
      .references('id')
      .inTable('Movie')
      .index();
    table.biginteger('personId')
      .references('id')
      .inTable('Person')
      .index();
  });
});

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

  static get relationMappings() {
    return {
      actors: {
        relation: Model.ManyToManyRelation,
        modelClass: Person,
        join: {
          from: 'Movie.id',
          through: {
            from: 'Movie_Person.movieId',
            to: 'Movie_Person.personId'
          },
          to: 'Person.id'
        }
      },
      reviews: {
        relation: Model.OneToManyRelation,
        modelClass: Review,
        join: {
          from: 'Movie.id',
          to: 'Review.movieId'
        }
      }
    };
  }
}

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

  static get relationMappings() {
    return {
      reviewer: {
        relation: Model.OneToOneRelation,
        modelClass: Person,
        join: {
          from: 'Review.reviewerId',
          to: 'Person.id'
        }
      },
      movie: {
        relation: Model.OneToOneRelation,
        modelClass: Movie,
        join: {
          from: 'Review.movieId',
          to: 'Movie.id'
        }
      }
    };
  }
}

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

  static get relationMappings() {
    return {
      parent: {
        relation: Model.OneToOneRelation,
        modelClass: Person,
        join: {
          from: 'Person.parentId',
          to: 'Person.id'
        }
      },
      reviews: {
        relation: Model.OneToManyRelation,
        modelClass: Review,
        join: {
          from: 'Person.id',
          to: 'Review.reviewerId'
        }
      },
      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: Movie,
        join: {
          from: 'Person.id',
          through: {
            from: 'Movie_Person.personId',
            to: 'Movie_Person.movieId'
          },
          to: 'Movie.id'
        }
      }
    };
  }
}

const runQueries = Promise.coroutine(function *() {
  yield Movie.query().insertWithRelated([{
    name: 'The terminator',

    actors: [{
      "#id": 'arnold',
      firstName: 'Arnold',
      lastName: 'Schwarzenegger',

      parent: {
        firstName: 'Gustav',
        lastName: 'Schwarzenegger'
      }
    }, {
      firstName: 'Michael',
      lastName: 'Biehn'
    }],

    reviews: [{
      title: 'Great movie',
      stars: 5,
      text: 'Awesome',

      reviewer: {
        firstName: 'Some',
        lastName: 'Random-Dude'
      }
    }]
  }, {
    name: 'Terminator 2: Judgment Day',

    actors: [{
      "#ref": 'arnold'
    }]
  }, {
    name: 'Predator',

    actors: [{
      "#ref": 'arnold'
    }]
  }]);

  // Copy your examples here. Just
  // replace `await` with `yield`.

  let terminators =  yield Movie
    .query()
    .where('name', 'like', '%terminator%')
    .eager('[actors.parent, reviews.reviewer]');

  console.log(JSON.stringify(terminators, null, 2));
});

// Run the whole thing.
createSchema()
  .then(runQueries)
  .finally(() => knex.destroy());
Sami Koskimäki

Sami Koskimäki

4 kommenttia

Pavan Naganna says:

What is the best module to write UNIT test cases for the module which uses objection js

Wooly says:

Relations have been renamed OneToOneRelation –> BelongsToOneRelation, OneToManyRelation –> HasManyRelation. The old names used in this example don’t seem to be working anymore.

Oyi Nyman says:

Aivan hyvä.

Join the conversation