Query examples

The Person model used in the examples is defined here.

All queries are started with one of the Model methods query, $query, relatedQuery or $relatedQuery. All these methods return a QueryBuilder instance that can be used just like a knex QueryBuilder (opens new window) but they also have a bunch of methods added by objection.

Note that you can chain debug() to any query to get the executed SQL printed to console.

Basic queries

Find queries

Find queries can be created by calling Model.query() and chaining query builder methods for the returned QueryBuilder instance.

In addition to the examples here, you can find more examples behind these links.

• subqueries
• raw queries
• precedence and parentheses

There's also a large amount of examples in the API documentation.

Examples

Fetch an item by id:

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

console.log(person.firstName);
console.log(person instanceof Person); // --> true

select "persons".* from "persons" where "persons"."id" = 1

Fetch all people from the database:

const people = await Person.query();

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

select "persons".* from "persons"

The return value of the query method is an instance of QueryBuilder that has all the methods a knex QueryBuilder (opens new window) has and a lot more. Here is a simple example that uses some of them:

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

console.log('The last name of the first middle aged Jennifer is');
console.log(middleAgedJennifers[0].lastName);

select "age", "firstName", "lastName"
from "persons"
where "age" > 40
and "age" < 60
and "firstName" = 'Jennifer'
order by "lastName" asc

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

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

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

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

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

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

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

select "parent:parent"."firstName" as "grandParentName"
from "persons"
inner join "persons"
  as "parent"
  on "parent"."id" = "persons"."parentId"
inner join "persons"
  as "parent:parent"
  on "parent:parent"."id" = "parent"."parentId"

Objection allows a bit more modern syntax with groupings and subqueries. Where knex requires you to use an old fashioned function an this, with objection you can use arrow functions:

const nonMiddleAgedJennifers = await Person.query()
  .where(builder => builder.where('age', '<', 40).orWhere('age', '>', 60))
  .where('firstName', 'Jennifer')
  .orderBy('lastName');

console.log('The last name of the first non middle aged Jennifer is');
console.log(nonMiddleAgedJennifers[0].lastName);

select "persons".* from "persons"
where ("age" < 40 or "age" > 60)
and "firstName" = 'Jennifer'
order by "lastName" asc

Insert queries

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

In addition to the examples here, you can find more examples behind these links.

• insert API reference
• graph inserts

Examples

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

console.log(jennifer instanceof Person); // --> true
console.log(jennifer.firstName); // --> 'Jennifer'
console.log(jennifer.fullName()); // --> 'Jennifer Lawrence'

insert into "persons" ("firstName", "lastName") values ('Jennifer', 'Lawrence')

Just like with any query, you can mix in raw statements, subqueries, knex.raw instances etc.

const jennifer = await Person.query().insert({
  firstName: 'Average',
  lastName: 'Person',
  age: Person.query().avg('age')
});

Update queries

Update queries are created by chaining the update or patch method to the query. patch and update return the number of updated rows. If you want the freshly updated item 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. See update and patch API documentation for discussion about their differences.

In addition to the examples here, you can find more examples behind these links.

• patch API reference
• raw queries

Examples

Update an item by id:

const numUpdated = await Person.query()
  .findById(1)
  .patch({
    firstName: 'Jennifer'
  });

update "persons" set "firstName" = 'Jennifer' where "id" = 1

Update multiple items:

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

console.log('all people over 60 years old are now dinosaurs');
console.log(numUpdated, 'people were updated');

update "persons" set "lastName" = 'Dinosaur' where "age" > 60

Update and fetch an item:

const updatedPerson = await Person.query().patchAndFetchById(246, {
  lastName: 'Updated'
});

console.log(updatedPerson.lastName); // --> Updated.

update "persons" set "lastName" = 'Updated' where "id" = 246
select "persons".* from "persons" where "id" = 246

Delete queries

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.

Examples

Delete an item by id:

const numDeleted = await Person.query().deleteById(1);

delete from "persons" where id = 1

Delete multiple items:

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

console.log(numDeleted, 'people were deleted');

delete from "persons" where lower("firstName") like '%ennif%'

You can always use subqueries, raw, ref, lit and all query building methods with delete queries, just like with every query in objection. With some databases, you cannot use joins with deletes (db restriction, not objection). You can replace joins with subqueries like this:

// This query deletes all people that have a pet named "Fluffy".
await Person.query()
  .delete()
  .whereIn(
    'id',
    Person.query()
      .select('persons.id')
      .joinRelated('pets')
      .where('pets.name', 'Fluffy')
  );

delete from "persons"
where "id" in (
  select "persons.id"
  from "persons"
  join "pets" on "pets.ownerId" = "persons.id"
  where "pets.name" = 'Fluffy'
)

// This is another way to implement the previous query.
await Person.query()
  .delete()
  .whereExists(Person.relatedQuery('pets').where('pets.name', 'Fluffy'));

delete from "persons"
where exists (
  select "pets".*
  from "pets"
  where "pets.ownerId" = "persons.id"
  and "pets.name" = 'Fluffy'
)

Relation queries

While the static query method can be used to create a query to a whole table relatedQuery and its instance method counterpart $relatedQuery can be used to query items related to another item. Both of these methods return an instance of QueryBuilder just like the query method.

Relation find queries

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

The static method relatedQuery can be used to create related queries for multiple items using identifiers, model instances or even subqueries. This allows you to build complex queries by composing simple pieces.

In addition to the examples here, you can find more examples behind these links.

• relation subqueries
• relatedQuery

Examples

This example fetches the person's pets. 'pets' is the name of a relation defined in relationMappings.

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

select "persons".* from "persons" where "persons"."id" = 1

const dogs = await person
  .$relatedQuery('pets')
  .where('species', 'dog')
  .orderBy('name');

select "animals".* from "animals"
where "species" = 'dog'
and "animals"."ownerId" = 1
order by "name" asc

The above example needed two queries to find pets of a person. You can do this with one single query using the static relatedQuery method:

const dogs = await Person.relatedQuery('pets')
  .for(1)
  .where('species', 'dog')
  .orderBy('name');

select "animals".* from "animals"
where "species" = 'dog'
and "animals"."ownerId" = 1
order by "name" asc

With HasManyRelations and BelongsToOneRelations the relatedQuery helper may just seem like unnecessary bloat. You can of course simply write the SQL directly. The following code should be clear to anyone even without any objection experience:

const dogs = await Pet.query()
  .where('species', 'dog')
  .where('ownerId', 1)
  .orderBy('name')

select "animals".* from "animals"
where "species" = 'dog'
and "ownerId" = 1
order by "name" asc

The relatedQuery helper comes in handy with ManyToManyRelation where the needed SQL is more complex. it also provides a unified API for all kinds of relations. You can write the same code regardless of the relation type. Or you may simply prefer the relatedQuery style. Now back to the examples 😃

If you want to fetch dogs for multiple people in one query, you can pass an array of identifiers to the for method like this:

const dogs = await Person.relatedQuery('pets')
  .for([1, 2])
  .where('species', 'dog')
  .orderBy('name');

select "animals".* from "animals"
where "species" = 'dog'
and "animals"."ownerId" in (1, 2)
order by "name" asc

You can even give it a subquery! The following example fetches all dogs of all people named Jennifer using one single query:

// Note that there is no `await` here. This query does not get executed.
// jennifersSubQuery is of type QueryBuilder<Person>.
const jennifersSubQuery = Person.query().where('name', 'Jennifer');

// This is the only executed query in this example.
const dogs = await Person.relatedQuery('pets')
  .for(jennifersSubQuery)
  .where('species', 'dog')
  .orderBy('name');

select "animals".* from "animals"
where "species" = 'dog'
and "animals"."ownerId" in (
  select "persons"."id"
  from "persons"
  where "name" = 'Jennifer'
)
order by "name" asc

Relation insert queries

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

Examples

Add a pet for a person:

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

select "persons".* from "persons" where "persons"."id" = 1

const fluffy = await person.$relatedQuery('pets').insert({ name: 'Fluffy' });

insert into "animals" ("name", "ownerId") values ('Fluffy', 1)

Just like with relation find queries, you can save a query and add a pet for a person using one single query by utilizing the static relatedQuery method:

const fluffy = await Person.relatedQuery('pets')
  .for(1)
  .insert({ name: 'Fluffy' });

insert into "animals" ("name", "ownerId") values ('Fluffy', 1)

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

const movie = await Person.relatedQuery('movies')
  .for(100)
  .insert({ name: 'The room', awesomeness: 9001 });

console.log('best movie ever was added');

insert into "movies" ("name")
values ('The room')

insert into "persons_movies" ("movieId", "personId", "awesomeness")
values (14, 100, 9001)

See this recipe for more information about extra properties.

Relation relate queries

Relating means attaching a existing item to another item through a relationship defined in the relationMappings.

In addition to the examples here, you can find more examples behind these links.

• relate method

Examples

In the following example we relate an actor to a movie. In this example the relation between Person and Movie is a many-to-many relation but relate also works for all other relation types.

const actor = await Person.query().findById(100);

select "persons".* from "persons" where "persons"."id" = 100

const movie = await Movie.query().findById(200);

select "movies".* from "movies" where "movies"."id" = 200

await actor.$relatedQuery('movies').relate(movie);

insert into "persons_movies" ("personId", "movieId") values (100, 200)

You can also pass the id 200 directly to relate instead of passing a model instance. A more objectiony way of doing this would be to once again utilize the static relatedQuery method:

await Person.relatedQuery('movies')
  .for(100)
  .relate(200);

insert into "persons_movies" ("personId", "movieId") values (100, 200)

Actually in this case, the cleanest way of all would be to just insert a row to the persons_movies table. Note that you can create models for pivot (join) tables too. There's nothing wrong with that.

Here's one more example that relates four movies to the first person whose first name Arnold. Note that this query only works on Postgres because on other databases it would require multiple queries.

await Person.relatedQuery('movies')
  .for(
    Person.query()
      .where('firstName', 'Arnold')
      .limit(1)
  )
  .relate([100, 200, 300, 400]);

Relation unrelate queries

Unrelating is the inverse of relating. For example if an actor is related to a movie through a movies relation, unrelating them means removing this association, but neither the movie nor the actor get deleted from the database.

Examples

The first example unrelates all movies whose name starts with the string 'Terminator' from an actor.

const actor = await Person.query().findById(100);

select "persons".* from "persons" where "persons"."id" = 100

await actor
  .$relatedQuery('movies')
  .unrelate()
  .where('name', 'like', 'Terminator%');

delete from "persons_movies"
where "persons_movies"."personId" = 100
where "persons_movies"."movieId" in (
  select "movies"."id" from "movies" where "name" like 'Terminator%'
)

The same using the static relatedQuery method:

await Person.relatedQuery('movies')
  .for(100)
  .unrelate()
  .where('name', 'like', 'Terminator%');

delete from "persons_movies"
where "persons_movies"."personId" = 100
and "persons_movies"."movieId" in (
  select "movies"."id"
  from "movies"
  where "name" like 'Terminator%'
)

The next query removes all Terminator movies from Arnold Schwarzenegger:

// Once again, note that we don't await this query. This query
// is not executed. It's a placeholder that will be used to build
// a subquery when the `relatedQuery` gets executed.
const arnold = Person.query().findOne({
  firstName: 'Arnold',
  lastName: 'Schwarzenegger'
});

await Person.relatedQuery('movies')
  .for(arnold)
  .unrelate()
  .where('name', 'like', 'Terminator%');

delete from "persons_movies"
where "persons_movies"."personId" in (
  select "persons"."id"
  from "persons"
  where "firstName" = 'Arnold'
  and "lastName" = 'Schwarzenegger'
)
and "persons_movies"."movieId" in (
  select "movies"."id"
  from "movies"
  where "name" like 'Terminator%'
)

Relation update queries

Relation update queries work just like the normal update queries, but the query is automatically filtered so that only the related items are affected.

See the API documentation of update method.

Examples

await Person.relatedQuery('pets')
  .for([1, 2])
  .patch({ name: raw(`concat(name, ' the doggo')`) })
  .where('species', 'dog');

update "animals"
set "name" = concat(name, ' the doggo')
where "animals"."ownerId" in (1, 2)
and "species" = 'dog'

Relation delete queries

Relation delete queries work just like the normal delete queries, but the query is automatically filtered so that only the related items are affected.

See the API documentation of delete method.

Examples

await Person.relatedQuery('pets')
  .for([1, 2])
  .delete()
  .where('species', 'dog');

delete from "animals"
where "animals"."ownerId" in (1, 2)
and "species" = 'dog'

Eager loading

You can fetch an arbitrary graph of relations for the results of any query by chaining the withGraphFetched or withGraphJoined method. Both methods take a relation expression as the first argument. In addition to making your life easier, eager loading avoids the "N+1 selects" problem and provide a great performance.

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

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

• 'pets'
• 'children'
• 'children.pets'
• '[pets, children]'
• '[pets, children.pets]'

Examples of expressions that would cause an error:

• 'movies'
• 'children.children'
• '[pets, children.children]'
• 'notEvenAnExistingRelation'

In addition to the withGraphFetched and withGraphJoined methods, relations can be fetched using the fetchGraph and $fetchGraph methods.

withGraphFetched uses multiple queries to load the related items. (for details see this blog post (opens new window). Note that withGraphFetched used to be called eager.). withGraphJoined uses joins and only performs one single query to fetch the whole relation graph. This doesn't mean that withGraphJoined is faster though. See the performance discussion here. You should only use withGraphJoined if you actually need the joins to be able to reference the nested tables. When in doubt use withGraphFetched.

Examples

Fetch the pets relation for all results of a query:

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

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

Fetch multiple relations on multiple levels:

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

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

Here's the previous query using the object notation

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

Fetch one relation recursively:

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

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

Limit recursion to 3 levels:

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

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

Relations can be modified using the modifyGraph method:

const people = await Person.query()
  .withGraphFetched('[children.[pets, movies], movies]')
  .modifyGraph('children.pets', builder => {
    // Only select pets older than 10 years old for children
    // and only return their names.
    builder.where('age', '>', 10).select('name');
  });

Relations can also be modified using modifiers like this:

const people = await Person.query()
  .withGraphFetched(
    '[pets(selectName, onlyDogs), children(orderByAge).[pets, children]]'
  )
  .modifiers({
    selectName: builder => {
      builder.select('name');
    },

    orderByAge: builder => {
      builder.orderBy('age');
    },

    onlyDogs: builder => {
      builder.where('species', 'dog');
    }
  });

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

Reusable modifiers can be defined for models using modifiers

// Person.js

class Person extends Model {
  static get modifiers() {
    return {
      defaultSelects(builder) {
        builder.select('id', 'firstName');
      },

      orderByAge(builder) {
        builder.orderBy('age');
      }
    };
  }
}

// Animal.js

class Animal extends Model {
  static get modifiers() {
    return {
      orderByName(builder) {
        builder.orderBy('name');
      },

      // Note that this modifier takes an argument.
      onlySpecies(builder, species) {
        builder.where('species', species);
      }
    };
  }
}

// somewhereElse.js

const people = await Person.query().modifiers({
  // This way you can bind arguments to modifiers.
  onlyDogs: query => query.modify('onlySpecies', 'dog')
}).withGraphFetched(`
    children(defaultSelects, orderByAge).[
      pets(onlyDogs, orderByName),
      movies
    ]
  `);

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

Relations can be aliased using as keyword:

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

      movies.[
        actors
      ]
    ]
  ]`);

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

Example usage for allowGraph in an express route:

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

  res.send(people);
});

withGraphJoined can be used just like withGraphFetched. In addition you can refer to the related items from the root query because they are all joined:

const people = await Person.query()
  .withGraphJoined('[pets, children.pets]')
  .where('pets.age', '>', 10)
  .where('children:pets.age', '>', 10);

Graph inserts

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

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

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 (opens new window).

Examples

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

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

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

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

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

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

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

      movies: [
        {
          '#ref': 'silverLiningsPlaybook'
        }
      ]
    }
  ],
  { allowRefs: true }
);

Note that you need to also set the allowRefs option to true for this to work.

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

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

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

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

Again, make sure you set the allowRefs option to true.

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 its place preserving its type.

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

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

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

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

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

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

The relate option can also contain nested relations:

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

      movies: [
        {
          name: 'Silver Linings Playbook',
          duration: 122,

          actors: [
            {
              id: 2516
            }
          ]
        }
      ]
    }
  ],
  {
    relate: ['movies.actors']
  }
);

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

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

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

Graph upserts

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

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

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

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

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

See the allowGraph method if you need to limit which relations can be modified using upsertGraph method to avoid security issues.

WARNING

WARNING!

Before you start using upsertGraph beware that it's not the silver bullet it seems to be. If you start using it because it seems to provide a "mongodb API" for a relational database, you are using it for a wrong reason!

Our suggestion is to first try to write any code without it and only use upsertGraph if it saves you a lot of code and makes things simpler. Over time you'll learn where upsertGraph helps and where it makes things more complicated. Don't use it by default for everything. You can search through the objection issues to see what kind of problems upsertGraph can cause if used too much.

For simple things upsertGraph calls are easy to understand and remain readable. When you start passing it a bunch of options it becomes increasingly difficult for other developers (and even yourself) to understand.

It's also really easy to create a server that doesn't work well with multiple users by overusing upsertGraph. That's because you can easily get into a situation where you override other user's changes if you always upsert large graphs at a time. Always try to update the minimum amount of rows and columns and you'll save yourself a lot of trouble in the long run.

Examples

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. Of course the delete only applies to relations and not the root. Here's a basic example:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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