# Query

# Examples

A working model with minimal amount of code:

class User extends DB_MODEL {
  static get tableName() {
    return "someTableName";
  }
}

module.exports = User;

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

class Person extends DB_MODEL {
  // Table name is the only required property.
  static get tableName() {
    return "persons";
  }

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

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

  // Optional JSON schema. This is not the database schema!
  // No tables or columns are generated based on this. This is only
  // used for input validation. Whenever a model instance is created
  // either explicitly or implicitly it is checked against this schema.
  // See http://json-schema.org/ for more info.
  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
        // Model.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() {
    // Importing models here is a one way to avoid require loops.
    const Animal = require("@model/Animal");
    const Movie = require("@model/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. We use a model
        // subclass constructor `Animal` here.
        modelClass: Animal,
        join: {
          from: "persons.id",
          to: "animals.ownerId",
        },
      },

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

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

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

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

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. 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, 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"

The query builder allows a bit more modern syntax with groupings and sub-queries. You can also use arrows functions:

const nonMiddleAgedJennifers = await Person.query()
  .where((builder) => builder.where("age", "<", 4).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.

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

insert API reference

# 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, 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('*'). 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.

# 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 ExpressWeb JS. With some databases, you cannot use joins with deletes (db restriction, not ExpressWeb). 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 HasManyRelation the relatedQuery helper may just seem like unnecessary bloat. You can of course simply write the necessary SQL directly.

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

The relatedQuery helper comes in handy with ManyToManyRelation and 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.

// Note that there is no `await` here. This query does not get executed.
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)

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

← Models Migrations →