Services - Nestjs-query

The following ORMs are supported out of the box.

@ptc-org/nestjs-query-core also provides a number of base QueryServices that can be used to create custom query services. See the Services docs All examples assume the following entity.

TypeOrm
Sequelize
Mongoose
Typegoose

todo-item.entity.ts

import { Column, CreateDateColumn, Entity, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';

@Entity()
export class TodoItemEntity {

  @PrimaryGeneratedColumn()
  id!: string;

  @Column()
  title!: string;

  @Column()
  completed!: boolean;

  @CreateDateColumn()
  created!: Date;

  @UpdateDateColumn()
  updated!: Date;
}

todo-item.entity.ts

import {

  Table,
  Column,
  Model,
  AllowNull,
  CreatedAt,
  UpdatedAt,
  PrimaryKey,
  AutoIncrement,
} from 'sequelize-typescript';

@Table
export class TodoItemEntity extends Model<TodoItemEntity, Partial<TodoItemEntity>> {

  @PrimaryKey
  @AutoIncrement
  @Column
  id!: number;

  @Column
  title!: string;

  @AllowNull
  @Column
  description?: string;

  @Column
  completed!: boolean;

  @CreatedAt
  created!: Date;

  @UpdatedAt
  updated!: Date;
}

todo-item.entity.ts

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { Document } from 'mongoose';

@Schema({ timestamps: { createdAt: 'created', updatedAt: 'updated' } })
export class TodoItemEntity extends Document {

  @Prop({ required: true })
  title!: string;

  @Prop()
  description?: string;

  @Prop({ required: true })
  completed!: boolean;

  @Prop({ default: Date.now })
  created!: Date;

  @Prop({ default: Date.now })
  updated!: Date;
}
export const TodoItemEntitySchema = SchemaFactory.createForClass(TodoItemEntity);

todo-item.entity.ts

import { ObjectId } from '@ptc-org/nestjs-query-graphql'
import { Base } from '@typegoose/typegoose/lib/defaultClasses';
import { Prop, modelOptions, Ref } from '@typegoose/typegoose';
import { Types } from 'mongoose';
import { SubTaskEntity } from '../sub-task/sub-task.entity';
import { TagEntity } from '../tag/tag.entity';

@modelOptions({
  schemaOptions: {
    timestamps: { createdAt: 'created', updatedAt: 'updated' },
    collection: 'todo-items',
    toObject: { virtuals: true },
  },
})
export class TodoItemEntity implements Base {

  @ObjectId()
  _id!: Types.ObjectId
  id!: string

  @Prop({ required: true })
  title!: string;

  @Prop()
  description?: string;

  @Prop({ required: true })
  completed!: boolean;

  @Prop({ default: Date.now })
  created!: Date;

  @Prop({ default: Date.now })
  updated!: Date;
}

Creating a Service

Module

The nestjs-query typeorm, sequelize, mongoose, and typegoose packages provide a module that will add providers to inject auto-created QueryServices using the @InjectQueryService decorator. In order to use the decorator you will need to use the module that comes with the nestjs-query orm module providing it your entities that you want the services created for.

TypeOrm
Sequelize
Mongoose
Typegoose

todo-item.module.ts

import { NestjsQueryTypeOrmModule } from '@ptc-org/nestjs-query-typeorm';
import { Module } from '@nestjs/common';
import { TodoItemEntity } from './todo-item.entity';
import { TodoItemResolver } from './todo-item.resolver';

@Module({
  providers: [TodoItemResolver],
  imports: [NestjsQueryTypeOrmModule.forFeature([TodoItemEntity])],
})
export class TodoItemModule {}

todo-item.module.ts

import { NestjsQuerySequelizeModule } from '@ptc-org/nestjs-query-sequelize';
import { Module } from '@nestjs/common';
import { TodoItemEntity } from './todo-item.entity';
import { TodoItemResolver } from './todo-item.resolver';

@Module({
  providers: [TodoItemResolver],
  imports: [NestjsQuerySequelizeModule.forFeature([TodoItemEntity])],
})
export class TodoItemModule {}

todo-item.module.ts

import { NestjsQueryMongooseModule } from '@ptc-org/nestjs-query-mongoose';
import { Module } from '@nestjs/common';
import { TodoItemEntity } from './todo-item.entity';
import { TodoItemResolver } from './todo-item.resolver';

@Module({
  providers: [TodoItemResolver],
  imports: [
    NestjsQueryMongooseModule.forFeature([
      { document: TodoItemEntity, name: TodoItemEntity.name, schema: TodoItemEntitySchema },
    ]),
  ],
})
export class TodoItemModule {}

todo-item.module.ts

import { NestjsQueryTypegooseModule } from '@ptc-org/nestjs-query-typegoose';
import { Module } from '@nestjs/common';
import { TodoItemEntity } from './todo-item.entity';
import { TodoItemResolver } from './todo-item.resolver';

@Module({
  providers: [TodoItemResolver],
  imports: [NestjsQueryTypegooseModule.forFeature([TodoItemEntity])],
})
export class TodoItemModule {}

Decorator

Once you have imported the correct module, use @InjectQueryService decorator to inject a QueryService into your class or resolver.

todo-item.resolver.ts

import { QueryService, InjectQueryService } from '@ptc-org/nestjs-query-core'
import { CRUDResolver } from '@ptc-org/nestjs-query-graphql'
import { Resolver } from '@nestjs/graphql'
import { TodoItemDTO } from './todo-item.dto'
import { TodoItemEntity } from './todo-item.entity'

@Resolver(() => TodoItemDTO)
export class TodoItemResolver extends CRUDResolver(TodoItemDTO) {

  constructor(@InjectQueryService(TodoItemEntity) readonly service: QueryService<TodoItemEntity>) {
    super(service)
  }
}

The above resolver is an example of manually defining the resolver, if you use the NestjsQueryGraphQLModule you do not need to define a resolver.

In the above example the DTO and entity are the same shape, if you have a case where they are different or have computed fields check out Assemblers to understand how to convert to and from the DTO/Entity.

Querying

The nestjs-query QueryService uses a common Query interface that allows you use a common type regardless of the persistence library in use. To query for records from your service you can use the query method which will return a Promise of an array of entities. To read more about querying take a look at the Queries Doc.

Example

Get all records

const records = await this.service.query({})

Filtering

The filter option is translated to a WHERE clause.

Example

To find all completed TodoItems by use can use the is operator.

const records = await this.service.query({
  filter: {
    completed: { is: true }
  }
})

Sorting

The sorting option is translated to a ORDER BY.

Example

Sorting records by completed and title.

const records = await this.service.query({
  sorting: [
    { field: 'completed', direction: SortDirection.ASC },
    { field: 'title', direction: SortDirection.DESC }
  ]
})

Paging

The paging option is translated to LIMIT and OFFSET.

Example

Skip the first 20 records and return the next 10.

const records = await this.service.query({
  paging: { limit: 10, offset: 20 }
})

Find By Id

To find a single record you can use the findById method.

Example

const records = await this.service.findById(1)

Get By Id

The getById method is the same as the findById with one key difference, it will throw an exception if the record is not found.

Example

try {
  const records = await this.service.getById(1)
} catch (e) {
  console.error('Unable to get record with id = 1')
}

Aggregating

To perform an aggregate query you can use the aggregate method which accepts a Filter and AggregateQuery. Supported aggregates are count, sum, avg, min and max. In this example we’ll aggregate on all records.

const aggregateResponse = await this.service.aggregate(
  {},
  {
    count: ['id'],
    min: ['title'],
    max: ['title']
  }
)

The response will look like the following

;[
  {
    count: {
      id: 10
    },
    min: {
      title: 'Aggregate Todo Items'
    },
    min: {
      title: 'Query Todo Items'
    }
  }
]

In this example we’ll aggregate on all completed TodoItems

const aggregateResponse = await this.service.aggregate(
  { completed: { is: true } },
  {
    count: ['id'],
    min: ['title'],
    max: ['title']
  }
)

Creating

Create One

To create a single record use the createOne method.

Example

const createdRecord = await this.service.createOne({
  title: 'Foo',
  completed: false
})

Create Many

To create multiple records use the createMany method.

Example

const createdRecords = await this.service.createMany([
  { title: 'Foo', completed: false },
  { title: 'Bar', completed: true }
])

Updating

Update One

To update a single record use the updateOne method.

Example

Updates the record with an id equal to 1 to completed.

const updatedRecord = await this.service.updateOne(1, { completed: true })

Update Many

To update multiple records use the updateMany method. NOTE This method returns a UpdateManyResponse which contains the updated record count.

Example

Updates all TodoItemEntities to completed if their title ends in Bar

const { updatedCount } = await this.service.updateMany(
  { completed: true }, // update
  { completed: { is: false }, title: { like: '%Bar' } } // filter
)

Deleting

Delete One

To delete a single record use the deleteOne method.

Example

Delete the record with an id equal to 1.

const deletedRecord = await this.service.deleteOne(1)

Delete Many

To delete multiple records use the deleteMany method. NOTE This method returns a DeleteManyResponse which contains the deleted record count.

Example

Delete all TodoItemEntities older than Jan 1, 2019.

const { deletedCount } = await this.service.deleteMany(
  { created: { lte: new Date('2019-1-1') } } // filter
)

Foreign Keys

It is a common use case to include a foreign key from your entity in your DTO. To do this you should add the foreign key to your entity as well as your DTO.

This section only applies when using typeorm and sequelize with relations

Example

Assume TodoItems can have SubTasks we would set up our SubTaskEntity using the following

TypeOrm
Sequelize

sub-task.entity.ts

import {

  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  ObjectType,
  ManyToOne,
  JoinColumn,
} from 'typeorm';
import { TodoItemEntity } from '../todo-item/todo-item.entity';

@Entity({ name: 'sub_task' })
export class SubTaskEntity {

  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  title!: string;

  @Column({ nullable: true })
  description?: string;

  @Column()
  completed!: boolean;
  // add the todoItemId to the model

  @Column({ nullable: false, name: 'todo_item_id' })
  todoItemId!: string;

  @ManyToOne((): ObjectType<TodoItemEntity> => TodoItemEntity, (td) => td.subTasks, {
    onDelete: 'CASCADE',
    nullable: false,
  })
  // specify the join column we want to use.

  @JoinColumn({ name: 'todo_item_id' })
  todoItem!: TodoItemEntity;

  @CreateDateColumn()
  created!: Date;

  @UpdateDateColumn()
  updated!: Date;
}

sub-task.entity.ts

import {

  Table,
  AllowNull,
  Column,
  ForeignKey,
  BelongsTo,
  CreatedAt,
  UpdatedAt,
  Model,
  AutoIncrement,
  PrimaryKey,
} from 'sequelize-typescript';
import { TodoItemEntity } from '../todo-item/entity/todo-item.entity';

@Table({})
export class SubTaskEntity extends Model<SubTaskEntity, Partial<SubTaskEntity>> {

  @PrimaryKey
  @AutoIncrement
  @Column
  id!: number;

  @Column
  title!: string;

  @AllowNull
  @Column
  description?: string;

  @Column
  completed!: boolean;

  @Column
  @ForeignKey(() => TodoItemEntity)
  todoItemId!: number;

  @BelongsTo(() => TodoItemEntity)
  todoItem!: TodoItemEntity;

  @CreatedAt
  created!: Date;

  @UpdatedAt
  updated!: Date;
}

Then we could add the todoItemId to the SubTaskDTO.

sub-task.dto.ts

import { FilterableField, IDField } from '@ptc-org/nestjs-query-graphql'
import { ObjectType, ID, GraphQLISODateTime } from '@nestjs/graphql'

@ObjectType('SubTask')
export class SubTaskDTO {

  @IDField(() => ID)
  id!: number

  @FilterableField()
  title!: string

  @FilterableField({ nullable: true })
  description?: string

  @FilterableField()
  completed!: boolean

  @FilterableField(() => GraphQLISODateTime)
  created!: Date

  @FilterableField(() => GraphQLISODateTime)
  updated!: Date
  // expose the todoItemId as a filterable field.

  @FilterableField()
  todoItemId!: string
}

Relations

This section only applies when you combine your DTO and entity and are using Typeorm or Sequelize

When your DTO and entity are the same class and you have relations defined, you should not decorate your the relations in the DTO with @Field or @FilterableField. Instead decorate the class with @CursorConnection, @OffsetConnection, @UnPagedRelation or @Relation.

Example

Assume you have the following subtask definition.

TypeOrm
Sequelize

sub-task.ts

import {

  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  ManyToOne,
  JoinColumn,
} from 'typeorm';
import { ObjectType, ID } from '@nestjs/graphql';
import { FilterableField, IDField, Relation } from '@ptc-org/nestjs-query-graphql';
import { TodoItem } from '../todo-item/todo-item';

@ObjectType()
@Relation('todoItem', () => TodoItem, { update: { enabled: true } })
@Entity({ name: 'sub_task' })
export class SubTask {

  @IDField(() => ID)
  @PrimaryGeneratedColumn()
  id!: number;

  @FilterableField()
  @Column()
  title!: string;

  @FilterableField()
  @Column({ nullable: true })
  description?: string;

  @FilterableField()
  @Column()
  completed!: boolean;

  @FilterableField()
  @Column({ nullable: false, name: 'todo_item_id' })
  todoItemId!: string;
  // do not decorate with @Field

  @ManyToOne(() => TodoItem, (td) => td.subTasks, {
    onDelete: 'CASCADE',
    nullable: false,
  })

  @JoinColumn({ name: 'todo_item_id' })
  todoItem!: TodoItem;

  @FilterableField()
  @CreateDateColumn()
  created!: Date;

  @FilterableField()
  @UpdateDateColumn()
  updated!: Date;
}

sub-task.ts

import {

  Table,
  AllowNull,
  Column,
  ForeignKey,
  BelongsTo,
  CreatedAt,
  UpdatedAt,
  Model,
  AutoIncrement,
  PrimaryKey,
} from 'sequelize-typescript';
import { FilterableField, IDField } from '@ptc-org/nestjs-query-graphql';
import { ObjectType, ID, GraphQLISODateTime } from '@nestjs/graphql';
import { TodoItem } from '../todo-item/entity/todo-item';

@ObjectType()
@Relation('todoItem', () => TodoItem, { update: { enabled: true } })
@Table
export class SubTaskEntity extends Model<SubTaskEntity, Partial<SubTaskEntity>> {

  @IDField(() => ID)
  @PrimaryKey
  @AutoIncrement
  @Column
  id!: number;

  @FilterableField()
  @Column
  title!: string;

  @FilterableField({ nullable: true })
  @AllowNull
  @Column
  description?: string;

  @FilterableField()
  @Column
  completed!: boolean;

  @FilterableField()
  @Column
  @ForeignKey(() => TodoItemEntity)
  todoItemId!: number;
  // do not decorate with @Field

  @BelongsTo(() => TodoItem)
  todoItem!: TodoItem;

  @FilterableField(() => GraphQLISODateTime)
  @CreatedAt
  created!: Date;

  @FilterableField(() => GraphQLISODateTime)
  @UpdatedAt
  updated!: Date;
}

Notice how the todoItem is not decorated with a field decorator, instead it is exposed through the @Relation decorator.

​Creating a Service​

​Module​

​Decorator​

​Querying​

​Example​

​Filtering​

​Example​

​Sorting​

​Example​

​Paging​

​Example​

​Find By Id​

​Example​

​Get By Id​

​Example​

​Aggregating​

​Creating​

​Create One​

​Example​

​Create Many​

​Example​

​Updating​

​Update One​

​Example​

​Update Many​

​Example​

​Deleting​

​Delete One​

​Example​

​Delete Many​

​Example​

​Foreign Keys​

​Example​

​Relations​

​Example​

Creating a Service

Module

Decorator

Querying

Example

Filtering

Example

Sorting

Example

Paging

Example

Find By Id

Example

Get By Id

Example

Aggregating

Creating

Create One

Example

Create Many

Example

Updating

Update One

Example

Update Many

Example

Deleting

Delete One

Example

Delete Many

Example

Foreign Keys

Example

Relations

Example